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

Question

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.

Put `returnValue = (int)sqlComm.ExecuteScalar();` in a `try-catch` and see what exception you get — oleksii, May 22 '13 at 16:33

score 3 · Accepted Answer · answered May 22 '13 at 16:35

3

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).

answered May 22 '13 at 16:35

voithos

68,482
12
101
116

@CSharpBeginner: You're welcome! If your question has been answered, you can indicate so by upvoting / marking as "best answer" the answer which was most useful. – voithos May 22 '13 at 16:42

score 2 · Answer 2 · answered May 22 '13 at 16:35

2

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

Because sum on an empty resultset will return null.

An int cannot be null (and you cast your result to an int)

The coalesce operator will return 0 if you get null from SUM

answered May 22 '13 at 16:35

Raphaël Althaus

59,727
6
96
122

score 0 · Answer 3 · answered May 22 '13 at 16:47

In your code last line

returnValue = (int)sqlComm.ExecuteScalar();

is culprit, you are trying to parser the null to int. This is why your code is throwing exception. Before parsing to is you should check is object is null. You can use try and catch to prevent unwanted error.

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

3 Answers3