Monday, March 21, 2011

run query on DataSet


I had a stored procedure that returned indistinct data... like it returned
P1
CompanyProductTotal
C1P1T1
C1P2T2
C2P3T3
C2P4P4

I wanted a report like

Company 1
P1T1
P2T2
Company 1
P3T3
P4P4

instead of running a query on the database again and again, I wanted to do it using the table returned by the DataSet

using a datalist that has a grid in its template colum see http://www.aspdotnetcodes.com/GridView_Inside_DataList.aspx

Using the above technique ,
when binding the list

private void bindList()
        {
            string prevCo = "";
            DataTable table = new DataTable();
            table.Columns.Add("CompanyName");
            //get the companies
            //if 'Dictinct' could be run on the dataset, it would be cool
            foreach (DataRow row in mainDs.Tables[0].Rows)
            {
                if (prevCo == row["CompanyName"].ToString())
                {
                    //do nothin'
                }
                else
                {
                    DataRow dr = table.NewRow();
                    dr["CompanyName"] = row["CompanyName"].ToString();
                    table.Rows.Add(dr);
                }
                prevCo = row["CompanyName"].ToString();
            }
            table.AcceptChanges();

            DataList1.DataSource = table;
            DataList1.DataBind();
        }

and bind the grid on DataList's itemdatabound method

protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
        {
            GridView GridView1 = (GridView)e.Item.FindControl("GridView1");
            BindGrid(GridView1, DataList1.DataKeys[e.Item.ItemIndex].ToString());
        }

where datakeys[] is set to the company name.
<asp:DataList id=DataList1 runat=server DataKeyField="CompanyName" OnItemDataBound="DataList1_ItemDataBound" >

and

private void BindGrid(GridView GridView, string CompanyName)
        {

            DataTable table = new DataTable();
            table.Columns.Add("Sno");
            table.Columns.Add("ProductName");
            table.Columns.Add("Total");
            int counter = 1;
            foreach (DataRow row in mainDs.Tables[0].Rows)
            {
                if (row["CompanyName"].ToString() == CompanyName)
                {
                    DataRow dr1 = table.NewRow();
                    dr1["Sno"] = counter.ToString();
                    dr1["ProductName"] = row[0].ToString();
                    dr1["Total"] = row[2].ToString();
                    table.Rows.Add(dr1);
                }
                counter++;
            }
            table.AcceptChanges();

            GridView.DataSource = table;
            GridView.DataBind();
        }

Im sure this can be done better with Linq.
this seemed pretty easy in this scenario.

and there is also http://www.queryadataset.com/

UPDATE: you can download the source of the test site I created, and please make sure you have the nothwind database of the SQL server sample databases.
download : http://saudkhan.net/work/Web/dataListTest.rar

No comments:

Post a Comment