Ado.net Century Problem for All Can you return 10 result set and show it in one table ? Answer : ??

Question

hi Everyone : i have 2 problems : 1- Returing Multiple Result to one table 2- calling stored procedure multiple time with differernt value both problem connected to each other i will show yo how >>> developing tool : VS2005 C# database tool : sql server 2005 database has : entry : table name ( all below cols in one table entry ) main_name : sold item cost : cost of item daydate : the date of selling What i want ? i want my datagridview to reutrn this results from store procedure : Year Year Year item 2007 2006 2008 ------------------------------------------ cds 430$ 500$ 100$ Dvds 1000$ 200$ 600$ i am able to show one result at a time using this query : select main_item , sum (cost ) form entry where datepart(yyyy,daydate) = @passyear group by main_item but if i try to show multiple selecte quere only one result is shown in datagrideview : i tryed all possiblity like to solve this issus : -dataReader.NextRow(); useless -Union ; freak -dataset with different table ugly way if i have 12 result then createing 12 table is painfull ; true 12 hours of searching no use no one give even 10 % of the soultion could you please help me Hope to hear form you soon ... thanks in advance guys ... cszaidan

Ryan Alford · Answer

you will need to use a DataTable. here are your steps. 1. get a list of the "Items" from your Table. Use a DataSet and DataTable: DataSet ds = new DataSet(); using(SqlConnection cn = new SqlConnection("your connection string")) { SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "sp_GetItems" //The name of your stored procedure cmd.CommandType = CommandType.StoredProcedure; // If you need parameters for your stored procedure //SqlParameterCollection sqlParams = cmd.Parameter; //sqlParams.AddWithValue("[name of parameter]", [data for parameter]); SqlDataAdapter myDA = new SqlDataAdapter(cmd); DataTable myDT = new DataTable("ITEMS"); DataColumn myDC = myDT.Columns.Add("main_name", Type.GetType("System.String", true, true)); // Name of the column that is being retrieved from SP myDA.Fill(ds, "ITEMS"); } 2. Now, create a DataTable to store the list of Items, and create columns for the yearly totals....ie: DataTable myItemDT = ds.Tables["ITEMS"]; DataColumn myDC = new DataColumn(); myDC.ColumnName = "Year 2007"; myDC.DataType = Type.GetType("System.String", true, true); myDT.Columns.Add(myDC); myDC = null; myDC = new DataColumn(); myDC.ColumnName = "Year 2006"; myDC.DataType = Type.GetType("System.String", true, true); myDT.Columns.Add(myDC); myDC = null; myDC = new DataColumn(); myDC.ColumnName = "Year 2008"; myDC.DataType = Type.GetType("System.String", true, true); myDT.Columns.Add(myDC); myDC = null; 3. Now, requery for the all the data that you want... SQL Statement: "SELECT Main_Name, Cost, dayDate FROM ENTRY" (use a stored procedure, I will name it "sp_GetEntries") 4. Now, you will run the SP, then loop through the data, compare it to the first DataTable, and roll-up the totals.... DataRow myDR = new DataRow(); using(SqlConnection cn = new SqlConnection("your connection string")) { SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "sp_GetEntires" //The name of your stored procedure cmd.CommandType = CommandType.StoredProcedure; cn.Open(); SqlDataReader myEntries = cmd.ExecuteReader(); while(myEntries.Read()) { myDR = myNewDT.Rows.Find(myEntries["Main_Name"]; if (myDR != null) { myDR.BeginEdit(); if (Convert.ToDateTime(myEntries["dayDate"]).Year == 2007) { if (myDR["Year 2007"] is System.Null) { // If no data has been written to this cell in the DataTable --- blank myDR["Year 2007"] = myEntries["Cost"].ToString(); } else { // If data has been written to this cell in the DataTable, we want to add the current Cost to the already accumilated cost myDR["Year 2007"] = Convert.ToDecimal(myDR["Year 2007"]) + Convert.ToDecimal(myEntries["Cost"]); } if (Convert.ToDateTime(myEntries["dayDate"]).Year == 2006) { if (myDR["Year 2006"] is System.Null) { // If no data has been written to this cell in the DataTable --- blank myDR["Year 2006"] = myEntries["Cost"].ToString(); } else { // If data has been written to this cell in the DataTable, we want to add the current Cost to the already accumilated cost myDR["Year 2006"] = Convert.ToDecimal(myDR["Year 2006"]) + Convert.ToDecimal(myEntries["Cost"]); } if (Convert.ToDateTime(myEntries["dayDate"]).Year == 2008) { if (myDR["Year 2008"] is System.Null) { // If no data has been written to this cell in the DataTable --- blank myDR["Year 2008"] = myEntries["Cost"].ToString(); } else { // If data has been written to this cell in the DataTable, we want to add the current Cost to the already accumilated cost myDR["Year 2008"] = Convert.ToDecimal(myDR["Year 2008"]) + Convert.ToDecimal(myEntries["Cost"]); } } myDR.EndEdit(); } } myEntries.Close(); } dataGridView1.DataSource = myDT.DefaultView; that should do it for you. If you want a $ with the cost, then you can do a String.Format for currency.

Ryan Alford · Answer

yes, a pivot SQL statement will also work. for me, code is easier than writing complex SQL statements, so that's why I gave you the code.

cs cs · Answer

Sorry dude for begin late to replay and thanks for you kind co-operation .... Your code seems to be lots of code to be written even though imagin i have 12 months instead of year should i reapete if ..else statmentes 12 tiems ... ?? but still i can use for some related problem i will keep it in my Best code list ... Now the soultion is : instead i found alternative solution : ALTER PROCEDURE [dbo].[test] as begin with tempEntry as ( select main_name ,cost , month ( daydate ) as Emonth from [entry] where year(daydate) = 2008 ) SELECT * FROm tempEntry PIVOT ( sum(cost) FOR Emonth IN ([1],[7],[11] )) AS T // here I specifie my months Using above querey i will be able to get the total of each month or year as i like The story is not yet finished another issuse Related but not same one Kindly read the below lines : now suppose that i want to return the total for each main_item as well as ( sub_item (which is a part of main_item )) in one coulmn like this : total cost for each month : item jun feb may car 1000 500 250 electricity 1000 2000 3000 oil 50 25 15 where as : car = is main_item which contain ( oil , petrol , maintence , .... ) electricity = is sub_item which comes under Home oil = sub_item which comes under car ( as mentioned eariler ) Thanks ....