C# SQL Aggregate ExecuteScalar Return Query

Question

An SQL aggregate function counting number of name enteries in DB.

string cnnStr = ConfigurationManager.ConnectionStrings["LGFConnectionString"].ConnectionString;
string mySQL = "SELECT COUNT(*) FROM  " + which.table + " WHERE " + which.column + " = ?pram;";
string value = null;

using (MySqlConnection cnn = new MySqlConnection(cnnStr))
{
        using (MySqlCommand cmd = new MySqlCommand(mySQL, cnn))
        {
            MySqlParameter param = new MySqlParameter("?pram", MySqlDbType.VarChar, 128);
            param.Value = which.text;
            cmd.Parameters.Add(param);

            cnn.Open();

            value = cmd.ExecuteScalar() as string;
            value = cmd.ExecuteScalar().ToString();

            cnn.Close();
        }
}

Notice that I have called cmd.ExecuteScalar twice. Interesting part is that the query returns different results.

value = cmd.ExecuteScalar() as string;

doesn't return the correct value. It returns null for both if name is present or missing in the name column.

value = cmd.ExecuteScalar().ToString();

returns correctly. This returns "1" if present and "0" if missing.

In searching the web, I haven't found an understandable explanation.

I have read that if name is missing from name column, then cmd.ExecuteScalar will return null.

What is the difference between:

value = cmd.ExecuteScalar() as string;
value = cmd.ExecuteScalar().ToString();

Thank you, deDogs

Similar question asked and answered here: http://stackoverflow.com/questions/2099900/difference-between-tostring-and-as-string-in-c-sharp — 03Usr, Jun 23 '12 at 14:33

Zbigniew · Accepted Answer · 2012-06-23T14:55:57.890

3

as in docs

The as operator is used to perform conversions between compatible types.

The as operator is like a cast except that it yields null on conversion failure instead of raising an exception

// if cmd.ExecuteScalar() is string then return string
// if not then return null
// this will return null, because cmd.ExecuteScalar() won't return string
// for your code it should return Int32
value = cmd.ExecuteScalar() as string;

ToString() in docs

ToString is the major formatting method in the .NET Framework. It converts an object to its string representation so that it is suitable for display. (For information about formatting support in the .NET Framework, see Formatting Types.)

// return a string that represents the current object
// will return correct value because it casts Int32 value to string value
value = cmd.ExecuteScalar().ToString();

edited Jun 23 '12 at 14:55

answered Jun 23 '12 at 14:34

Zbigniew

27,184
6
59
66

Thank you, yes I had read this and understand, but when executed, cmd.ScalarExecute() as string returns null for if name is present or missing. Where cmd.ScalarExecute().ToString(); returns correct results. – deDogs Jun 23 '12 at 14:50
I have added additional comments. Simply `ExecuteScalar` returns int. First option (`as`) will return null because `int` is not `string`, second option will return correct value because you are calling `int.ToString()` method, which converts `int` value to `string` value. – Zbigniew Jun 23 '12 at 14:57
Thank you, I missed the idea of compatible types. So, I tried: int? value = (int?)cmd.ExecuteScalar(); I would think this would work, but it throws an invalid cast? – deDogs Jun 23 '12 at 15:48
Take a look at [this](http://stackoverflow.com/questions/1174930/int32-tryparse-or-intcommand-executescalar), you may as well try casting it to `long?` – Zbigniew Jun 23 '12 at 18:00

David Thielen · Answer 2 · 2012-06-23T14:40:29.767

0

You are doing two different things above. Let's change your code to the following:

decimal value = cmd.ExecuteScalar();
string str1 = value as string;
string str2 = value.ToString();

str1 will be null because decimal cannot be cast to a string. str2 will be the value because you can call ToString() on a decimal.

edited Jun 23 '12 at 14:40

answered Jun 23 '12 at 14:34

David Thielen

28,723
34
119
193

Using your code, there are two lines with error: decimal value = cmd.ExecuteScalar(); compile error: cann't convert object to decimal. string str1 = value as string; compile error: cann't convert decimal to string. – deDogs Jun 23 '12 at 14:43
That's possible - I did not try to compile it. I just wrote it to illustrate why you see the different results. – David Thielen Jun 23 '12 at 14:45
What if the Execute scalar return null or DBNull ? Standard decimal is not nullable type ... – aleroot Jun 23 '12 at 14:47
As he/she was getting a numeric return value based on the question it was not returning null. In my answer I was trying to illustrate the difference between as and ToString(), not address all the other possibilities. – David Thielen Jun 23 '12 at 15:17

score 0 · Answer 3 · answered Jun 23 '12 at 14:40

0

The 'as' keyword will return null if the objects type does not match what you are attempting to cast it to.

What is happening in your case is that the returned object is an int and when you call toString on this, it will give you a string representation of the integer. When you use as against it, it gives you a null.

answered Jun 23 '12 at 14:40

Blueberry

2,211
3
19
33

**bold**When you use as against it, it gives you a null. **bold** Using the as operator on a integer returns null? – deDogs Jun 23 '12 at 14:55
In this case, yes. Effectively, as is similar to casting, except you will get a null instead of an exception being thrown. Since you have an int and are using 'as String', you will get a null back. – Blueberry Jun 23 '12 at 16:53
Thank you, something I didn't realize. I just learned something very important. Thank you -- All... – deDogs Jun 23 '12 at 17:25

C# SQL Aggregate ExecuteScalar Return Query

3 Answers3