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: stackoverflow.com/questions/2099900/… — 03Usr
– 03Usr, Commented Jun 23, 2012 at 14:33

Zbigniew · Accepted Answer · 2012-06-23 14:55:57Z

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, 2012 at 14:55

answered Jun 23, 2012 at 14:34

Zbigniew

27.6k6 gold badges62 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

deDogs Over a year ago

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.

Zbigniew Over a year ago

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.

deDogs Over a year ago

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?

Zbigniew Over a year ago

Take a look at this, you may as well try casting it to long?

Blueberry · Accepted Answer · 2012-06-23 14:40:03Z

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, 2012 at 14:40

Blueberry

2,2413 gold badges21 silver badges34 bronze badges

3 Comments

deDogs Over a year ago

boldWhen you use as against it, it gives you a null. bold Using the as operator on a integer returns null?

Blueberry Over a year ago

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.

deDogs Over a year ago

Thank you, something I didn't realize. I just learned something very important. Thank you -- All...

David Thielen · Accepted Answer · 2012-06-23 14:40:29Z

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, 2012 at 14:40

answered Jun 23, 2012 at 14:34

David Thielen

33.7k42 gold badges158 silver badges293 bronze badges

4 Comments

deDogs Over a year ago

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.

David Thielen Over a year ago

That's possible - I did not try to compile it. I just wrote it to illustrate why you see the different results.

aleroot Over a year ago

What if the Execute scalar return null or DBNull ? Standard decimal is not nullable type ...

David Thielen Over a year ago

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.

Collectives™ on Stack Overflow

C# SQL Aggregate ExecuteScalar Return Query

3 Answers 3

4 Comments

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related