Sum sql columns using float(C#)

Question

I'm using this code:

SqlConnection con = new SqlConnection(@"Data Source=(local);Initial     
Catalog=dbsoft;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("SELECT SUM(price) FROM fiscal");
cmd.Connection = con;
int sum = cmd.ExecuteScalar();
label3.Text = sum.ToString();
con.Close();

i get this error message:

Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)

Did i miss something?

Answer 1

SqlCommand.ExecuteScalar returns an object, you have to cast/convert it to your required type. If you are expecting your SUM to be of type float in DB then use double since float in SQL server maps to double in C#. like:

double sum = (double) cmd.ExecuteScalar();

Also consider enclosing your SqlConnection and SqlCommand object it using statement, that will ensure resource disposal, even in case of an exception.

Answer 2

When summing up prices like 12.34, 56.78 etc. we might expect to get floating point value. In case of money, Decimal is a typical choice (instead of int):

  ...
  // Convert.ToDecimal - let .Net convert, not just cast;
  // what if, say, String has been returned?
  Decimal sum = Convert.ToDecimal(cmd.ExecuteScalar()); // Decimal sum

  label3.Text = sum.ToString();

Implementation:

String connString = @"Data Source=(local);Initial     
Catalog=dbsoft;Integrated Security=True";

// Wrap IDisposable into using
using (SqlConnection con = new SqlConnection(connString)) {
  con.Open();

  // Wrap IDisposable into using
  using (SqlCommand cmd = new SqlCommand(
    @"SELECT SUM(price) 
        FROM fiscal")) {

    cmd.Connection = con;

    Decimal sum = Convert.ToDecimal(cmd.ExecuteScalar()); // Decimal sum

    label3.Text = sum.ToString(); 

    // or
    // Shortcut - you have no need in additional convert/cast 
    //label3.Text = Convert.ToString(cmd.ExecuteScalar());
  }
}