How to read several different commands in one method

Abdalla Omran

Hallo

i want to read many different commands in one method passing int year as parameter for method . the method should read 3 different commands for example 2017,2018 and 2019 .

what i have done so far is the following

What I have tried:

public int GetSumYear(int year)
{
int sum = 0;
string CS = "+++++"
using (SqlConnection connection = new SqlConnection(CS))
{
connection.Open();
SqlCommand command = new SqlCommand("select COUNT (ÜbertragenAm) from BranchSale_all where ÜbertragenAm between '2017-01-01' and '2018-01-01'", connection);
// here i want to read the same command for 2018 and 2019
sum = (int)command.ExecuteScalar();
}
return sum;
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Please refer the below link, it might helps you
https://www.codeproject.com/Questions/5160381/How-to-read-several-different-commands-in-one-meth
+1
Mario Scheeren7y ago
static Dictionary<int, long> GetSumYears(string connectionString)
{
    Dictionary<int, long> retval = new Dictionary<int, long>();
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        var command = connection.CreateCommand();
        command.CommandText = "Select YEAR(ÜbertragenAm) as Jahr, COUNT(ÜbertragenAm) as Anzahl from BranchSale_all GROUP BY YEAR(ÜbertragenAm);";

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                retval.Add(reader.GetInt32(0), reader.GetInt64(1));
            }
        }
    }
    return retval;
}
+1
Suraj Kumar7y ago
In one command you run only one query.

You need to know about dataset in which you can get data in multiple table as shown bleow. And the multiple statement you want to execute that may be either a normal select statement with union, union all or stored procedure with multiple select.
SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int i = 0;

            string connetionString = "";
            string sqlQuery = "Select COUNT (ÜbertragenAm) as Cnt from BranchSale_all where ÜbertragenAm between '2017-01-01' and '2018-01-01' UNION Select COUNT (ÜbertragenAm) as Cnt from BranchSale_all where ÜbertragenAm between '2018-01-01' and '2019-01-01'";

            SqlConnection connection = new SqlConnection(connetionString);

            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            da.SelectCommand = command;
            da.Fill(ds);
            da.Dispose();
            command.Dispose();
            connection.Close();

            int sum = 0;
            for (i = 0; i <= ds.Tables[0].Rows.Count; i++)
            {
                sum += Convert.ToInt32(ds.Tables[i].Rows[0]["Cnt"]);

            }
+2