Monday, April 23, 2012

ASPX & SQL returning multiple recordsets - identify them

I am creating a simple report in aspx.



I have a stored procedure that I'm calling to get 5 recorsets.



First 4 have 7 columns and second has 6 columns.



This is my C# code:



protected void bindGridView()
{
string _connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString();
DataSet dSet = new DataSet();
try
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand("MyProcedure", conn))
{
cmd.CommandTimeout = 120;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@d", SqlDbType.VarChar).Value = dlDzial.SelectedValue;
cmd.Parameters.Add("@g", SqlDbType.Int).Value = dlGrupa.SelectedValue;
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
ad.Fill(dSet);
if (dSet.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = dSet.Tables[0];
GridView1.DataBind();
}
else
{
ShowNoResultFound(dSet.Tables[0], GridView1);
}
if (dSet.Tables[1].Rows.Count > 0)
{
GridView2.DataSource = dSet.Tables[1];
GridView2.DataBind();
}
else
{
ShowNoResultFound(dSet.Tables[1], GridView2);
}
if (dSet.Tables[2].Rows.Count > 0)
{
GridView3.DataSource = dSet.Tables[2];
GridView3.DataBind();
}
else
{
ShowNoResultFound(dSet.Tables[2], GridView3);
}
if (dSet.Tables[3].Rows.Count > 0)
{
GridView4.DataSource = dSet.Tables[3];
GridView4.DataBind();
}
else
{
ShowNoResultFound(dSet.Tables[3], GridView4);
}
lbl1.Visible = lbl2.Visible = lbl3.Visible = lbl4.Visible = true;
}
}
}
}
catch (Exception ex)
{
lblErrorMsg.Text = ex.Message;
}
}


But I have a problem. Depending on parameters sometimes recordset 2 & 3 are empty, thats why I get errors when trying to get some values from rows. recordset 4 is then Table[2] and is displayed in gridview 2 (it should be displayed in fourth gridview)



I would like to know how to identify recordsets that come from sql into C#.



I was searching a bit over the internet and I found that I can add extra column to every recordset and using this identify recordsets.



How to identify every recordset, so even if I change order of selects in sql I will be able to map then to correct GridView.





No comments:

Post a Comment