Object cannot be cast from DBNull to other types in C#-CodePudding

I wrote this code :

 MySqlCommand command1 = new MySqlCommand("SELECT SUM(standard_one) FROM `challenge` WHERE (SELECT DAYOFWEEK(date)=1) AND challenge_id = @challenge_id and username = @username", db.getConnection());
                command1.Parameters.Add("@challenge_id", MySqlDbType.Int32).Value = comboBox1.SelectedItem;
                command1.Parameters.Add("@username", MySqlDbType.VarChar).Value = globals.username;

the problem is some times this command returns null.

how can I check if the command will return null? and if so it returns 0?

CodePudding user response：

if the command will return null? and if so it returns 0?

Make the SQL:

SELECT COALESCE(SUM(standard_one), 0) ...

Bear in mind that you then won't be able to tell the difference between "there were no values that matched the where clause" and "the sum of all values that matched was 0".. If that's important, you'll either need to coalesce to a different value that would never occur in the sum, like -2_147_483_648 or persist with what you have and check for null on the C# side

var x = command1.ExecuteScalar();
if(x == null || x.GetType() == typeof(System.DBNull))
  ...
else
  ..cast to int etc..