Search code examples
c#ado.netexecutescalar

C# ADO.NET - return sum of one columns in table

In my table in databse I have a column called "Cena" with the type of Integer. I need to SUM these columns to count the whole "Cena" of the table.

To do it I'm using SqlCommand and the method ExecuteScalar().

string sqlString = string.Format("Select Sum(Cena) From Ksiazki");
int returnValue ;

SqlCommand sqlComm = new SqlCommand();
sqlComm.CommandText = sqlString;
sqlComm.Connection = sqlConn;

returnValue = (int)sqlComm.ExecuteScalar();

It works fine as long as I got any record in the table, but when my table in database is empty it crashes.

To be honest I have no idea why it happens like that, so I would be really thankful if someone could tell me what's wrong when the table is empty and why it just doesn't return 0.

Solution

  • Your SQL query is returning a NULL when the table is empty. You cannot cast this return value to an int, which is why C# is throwing an error.

    If you're using SQL Server, you can check for NULL and replace with 0 in the database.

    Select Isnull(Sum(Cena), 0) From Ksiazki

    You can also use Coalesce, which is the more general form of Isnull (and is actually part of the SQL standard).