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

Question

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:

What kind of ORM are you using? It seems Dapper but something is not quite right. — Steve, May 16 '17 at 20:02
dapper. I added a screen shot. sorry for initial lack of detail — mrcavanaugh09, May 16 '17 at 20:03
I think you need just _var x = connection.ExecuteScalar(.....);_ By the way, if the return is an integer I don't understand why you transform it in a string and, of course, the usual warning about Sql Injection (parameters, parameters, always parameters) — Steve, May 16 '17 at 20:07
That resolved it the way i was looking for. I must have missed the documentation as I've never used ExecuteScaler before. I'm still new to pulling data from SQL query's. Thanks! — mrcavanaugh09, May 16 '17 at 20:16

Matthew S · Answer 1 · 2017-05-16T20:06:41.813

2

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!

edited May 16 '17 at 20:06

answered May 16 '17 at 20:04

Matthew S

343
3
11

1

Doesn't the connection close at the end of the using statement? The Entire thing would be garbage collected at that point i thought. – mrcavanaugh09 May 16 '17 at 20:06
And this is vanilla ADO.NET, it is not Dapper. – Steve May 16 '17 at 20:07
@mrcavanaugh09 Perhaps it would be. I like to live by the philosophy of leaving the connections the way they came. If the connection was opened by the time you run this code, then you can let whoever gave you the open connection to close it themselves. If this method was the one to open the connection, then it should be the one to close it (just to be on the safe side). – Matthew S May 16 '17 at 20:10
1

Thanks for the information. as mentioned above this is not utilizing dapper. It did work but required me to add code to open and close the connection where using what @Steve put above in the comment on the question didn't. two less lines of code. still interesting. – mrcavanaugh09 May 16 '17 at 20:14
Glad I could help! I will look into Dapper and see what that's all about. – Matthew S May 16 '17 at 20:17
Its supposed to be faster and cleaner but now that this is working i'm still getting heavy performance issues when the query is run which is my primary concern right now. still researching – mrcavanaugh09 May 16 '17 at 20:18

score 2 · Accepted Answer · edited May 23 '17 at 12:18

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.

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

2 Answers2