CODEWITHSUNDEEP
c#sql-serverdapper
mrcavanaugh09
mrcavanaugh09

Reputation: 363

Trying to get the value of a SQL query into a string

I am running a SQL query on SQL Server inside of my WPF C# application. The query returns a string which contains the value called MYCOUNT that I want to return to GetNumber. How do I return the value MYCOUNT only to get number and not the entire string?

public string GetNumber(string SkillNumber)       
{
    DateTime dte = DateTime.Today;
    string fixedStartDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte);
    string fixedEndDate = String.Format("{0:yyyy-MM-dd " + "05:00:00.000" + "}", dte.AddDays(1));

    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SQLHelper.CnnCal("OADB")))
    {
        var x = connection.Query($"SELECT COUNT(SOURCEID) AS 'MYCOUNT' "
                         + "FROM [OADB].[oadb].[CmsCallHistory] "
                         + "WHERE disposition = 2 and DISPSPLIT in (" + SkillNumber + ") AND SEGSTOP BETWEEN '" +
                         fixedStartDate + "' and '" + fixedEndDate + "'");

        return x.ToString();
    }
}

This is what returns. I only need to return the value:

enter image description here

Upvotes: 2

Views: 2883

Answers (2)

Steve
Steve

Reputation: 216323

You get an error because the Dapper Query method returns an IEnumerable, not a single value (indeed you can see that you have one object (an int) in the IEnumerable returned and this object has the value '767')

In Dapper you can use ExecuteScalar to get a single scalar value as your query returns

var x = connection.ExecuteScalar<int>(.....)

You can also still use the Query method but with a Linq twist

var x = connection.Query(.....).Single();

The usual warning about Sql Injection applies to your code albeit parameterize an IN clause is not so simple. And the two dates should be definitively parameters to avoid parsing incompatibilities between what you consider to be a date and what instead the sql server and its locale thinks about transforming a string to a datetime.

Upvotes: 2

Matthew S
Matthew S

Reputation: 393

Your answer is in the msdn documentation: https://msdn.microsoft.com/en-us/library/system.data.idbconnection(v=vs.110).aspx

You can try using

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SQLHelper.CnnCal("OADB")))
{
    IDbCommand command = connection.createCommand();
    command.CommandText = $"SELECT COUNT(SOURCEID) AS 'MYCOUNT' "
                     + "FROM [OADB].[oadb].[CmsCallHistory] "
                     + "WHERE disposition = 2 and DISPSPLIT in (" + SkillNumber + ") AND SEGSTOP BETWEEN '" +
                     fixedStartDate + "' and '" + fixedEndDate + "'";
    return (string)command.ExecuteScalar();
}

Another note: You want to try and close the connection once you are done. I recommend putting this in a try/catch/finally statement so you can close your connection in the 'finally' section.

Hope this helps!

Upvotes: 2

Related Questions