How to get sum value from SQL Server in C#

Question

I try to get sum of all of my TUTAR column with SQL Server in C# but can't do it. When I try the code above it works fine.

string qry = "SELECT * FROM BENZIN WHERE DATE>='" + invoicedate + "' AND DATE<='" + invoicedate1 + "' AND PLATE= '" + plaka + "'";

conn.Open();

SqlDataReader dr = new SqlCommand(qry,conn).ExecuteReader();

while (dr.Read())
{
    var item = new ListViewItem();
    item.SubItems.Add(dr["TUTAR"].ToString());
    listView1.Items.Add(item);
}

But when I try

string qry = "SELECT SUM(TUTAR) FROM BENZIN WHERE DATE LIKE '%" + yılcombobox.Text + "'";
MessageBox.Show(qry);

conn.Open();

SqlDataReader dr = new SqlCommand(qry, conn).ExecuteReader();

while (dr.Read())
{
    yıllıktutar = dr.GetValue(0).ToString();
}

MessageBox.Show(yıllıktutar);

it returns nothing. yılcombobox is a textbox and it contains selected year like 2017 or 2018 or something like that

You should not be using `LIKE` on a date. *And* you should be using proper parameters rather than munging query strings. — Gordon Linoff, Aug 01 '18 at 14:22
Have you tried executing `qry` in your database editor? Does it show an error? What value has `yılcombobox.Text`? Does it match to your `DATE` column? — nilsK, Aug 01 '18 at 14:25
Have you tried running the SQL that is shown in the Message Box directly, and if so does it return anything? If you think the problem is with `GetValue(0)` then you could name the result, e.g. `SELECT SUM(TUTAR) AS MySum FROM...`, then use `dr["MySum"].ToString()`? I also agree that this is not a safe way to construct SQL, as it's prone to SQL injection. — Richard Hansell, Aug 01 '18 at 14:25
so how can I get sum(tutar) without using LIKE.I want to get all sum(tutar) in year of 2017 or which year selected.My date format is ex: 01.07.2017 — Dj Sucuk, Aug 01 '18 at 14:26
@DjSucuk you could use BETWEEN for dates. There are plenty of ways to compare dates. — nilsK, Aug 01 '18 at 14:27
@DjSucuk: First confirm that your SQL query returns anything at all. *Then* debug the C# code. You could be chasing a false assumption here. Your first (working) query and second (non-working) query have more differences than just the use of `SUM()`. — David, Aug 01 '18 at 14:30
Use `YEAR(DATE) = 2017` or whatever the equivalent syntax is for your particular database vendor. — Richard Hansell, Aug 01 '18 at 14:31
When I try in my database side it returns the value but in c# no :( — Dj Sucuk, Aug 01 '18 at 14:31

score 0 · Answer 1 · answered Aug 01 '18 at 14:33

When you read a SQL result with a single value, you should call ExecuteScalar method instead of ExecuteReader(), because it lets you avoid the loop.

The most likely problem with your code is that you are performing a LIKE on a datetime column, which yields no results.

I want to get all sum(tutar) in year of 2017 or which year selected

A bigger problem is that you are not parameterizing your SQL, making it an easy target of SQL injection attacks. You should fix both problems:

conn.Open();
using (var cmd = new SqlCommand("SELECT SUM(TUTAR) FROM BENZIN WHERE YEAR(DATE)=@Year", conn)) {
    cmd.Parameters.AddWithValue("Year", int.Parse(yılcombobox.Text));
    var res = cmd.ExecuteScalar();
    if (res != DbNull.Value) {
        yıllıktutar = res.ToString();
    } else {
        yıllıktutar = "<NULL>";
    }
}

How to get sum value from SQL Server in C#

1 Answers1