How to read several different commands in one method

Question

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; }

Suraj Kumar · Answer

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 { sum += Convert.ToInt32(ds.Tables[i].Rows[0][ "Cnt" ]) ; }

Mageshwaran R · Answer

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

Mario Scheeren · Answer

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; }

How to read several different commands in one method

Answers (3)