ExecuteScalar if DBNull issue

Question

I was trying to handle DbNull exception like this:

string sql_com_sumcastka = "SELECT SUM(price) AS sumprice FROM kliplat WHERE akce='" + zakce.Text + "' AND year=" + year;
            SqlCommand sc2 = new SqlCommand(sql_com_sumprice, spojeni);
            spojeni.Open();

            if (sc2 != DBNull.Value)
            {
                int result = Convert.ToInt32(sc2.ExecuteScalar());
            }
            else
            {
                int result = 0;
            }
            spojeni.Close();

            string sql_com_update_sum = "UPDATE zajezd SET s_prijmy=@s_prijmy WHERE akce='"+zakce.Text+"' AND year="+year;
            SqlCommand sc3 = new SqlCommand(sql_com_update_sum,spojeni);

            sc3.Parameters.AddWithValue("@s_prijmy", result );
            spojeni.Open();
            sc3.ExecuteNonQuery();
            spojeni.Close();

But as I don't know how to properly handle if result is DBNull I get this erros: Operator '"=' cannot be applied to operands of type system.Data.SqlClient.SqlCommand and System.Dbnull

and

The name 'result' does not exist in the current context

My problem is this line of code:

if (sc2 != DBNull.Value)
        {
            int result = Convert.ToInt32(sc2.ExecuteScalar());
        }
        else
        {
            int result = 0;
        }

Thanks for helping.

Answer 1

ExecuteScalar doesn't return DBNull (unless..., please read comments below) but null and you need to test the return value of ExecuteScalar not the SqlCommand that executes the command

   int sumOfPrice = 0;
   object result = sc2.ExecuteScalar();
   if(result != null)
       sumOfPrice = Convert.ToInt32(result);

From MSDN

ExecuteScalar returns the first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty.

As a side note, do not use string concatenation to build a command text to pass to your database. You risk Sql Injection and parsing erroros. Use instead a parameterized query like this

string sql_com_sumcastka = "SELECT SUM(price) AS sumprice FROM kliplat " + 
                           "WHERE akce=@zak AND year=@year";
SqlCommand sc2 = new SqlCommand(sql_com_sumprice, spojeni);
sc2.Parameters.AddWithValue("@zak", zakce.Text);
sc2.Parameters.AddWithValue("@year", year);

Answer 2

Here is the correct way to do that

var objResult = sc2.ExecuteScalar();

if (objResult != DBNull.Value && objResult != null )
{
    int result = (int)objResult; //you can just do a normal cast, "SUM(X)" returns a int.
}
else
{
    int result = 0;
}