2

I have a stored procedure in SQL Server which returns seven result sets. I would like to call this stored procedure from ASP.NET and populate seven GridViews on my ASP.NET page with the results. I am using a SqlDataReader to get the data, however I'm struggling with the C# code to fill the GridViews.

I have created a DAL class to get the data and I have this method in there:

public SqlDataReader CheckDataIntegrity()
{
    SqlCommand cmd = new SqlCommand("cc.DataCheck");
    return MultipleResults(cmd);
}

The helper method MultipleResults looks like this:

private SqlDataReader MultipleResults(SqlCommand cmd)
{
    SqlConnection con = new SqlConnection(_connectionString);
    cmd.Connection = con;

    con.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    con.Close();
    return dr;

}

I'm trying to call the component on my page with something like:

private void FillGridViews()           
{
    DBUtil DB = new DBUtil();
    using (SqlDataReader dr = DB.CheckDataIntegrity())
    {
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                GridView1.DataSource = dr;
                GridView1.DataBind();
            }
        }
    }
}

I did search the web for an example of this, but couldn't find anything.

Do you know of a resource, or have a small example to share?

Thanks.

Mark Allison
  • 6,838
  • 33
  • 102
  • 151
  • In your multipleResults method, you should surround your SqlConnection with a using statement. – Lareau Nov 18 '10 at 12:59
  • @Laraeu - this made no difference, I still get `Invalid attempt to call HasRows when reader is closed.` – Mark Allison Nov 18 '10 at 13:09
  • I saw the issues you were having in the answer's comments. I would consider changing the parameters of the MultipleResults to accept a sqlconnection that way you can manage the opening & closing efficiently. – Lareau Nov 18 '10 at 16:06

2 Answers2

5

You should use DataSet which can have multiple tables to be bound to your GridViews.

var dataset = GetDataSetForMy7GridViews();

// also valid: dataset.Tables["TableName"];
GridView1.DataSource = dataset.Tables[0]; 
GridView1.DataBind();

GridView2.DataSource = dataset.Tables[1];
GridView2.DataBind();

GridView3.DataSource = dataset.Tables[2];
GridView3.DataBind();

GridView4.DataSource = dataset.Tables[3];
GridView4.DataBind();

GridView5.DataSource = dataset.Tables[4];
GridView5.DataBind();

GridView6.DataSource = dataset.Tables[5];
GridView6.DataBind();

GridView7.DataSource = dataset.Tables[6];
GridView7.DataBind();

Since you are binding to 7 different GridView, you need to do it this way. If you do it according to your code:

>             while (dr.Read())
>             {
>                 GridView1.DataSource = dr;
>                 GridView1.DataBind();
>             }

It will only bind to 1 GridView.

added: maybe this link is an answer to your question? http://www.codeguru.com/csharp/csharp/cs_network/database/article.php/c8715

Arief
  • 6,055
  • 7
  • 37
  • 41
  • this is exactly what my thoughts are, simplicity at its best, gets +1 from me. – Dalbir Singh Nov 18 '10 at 13:15
  • thanks for the solution. I was trying to stay away from Datasets because of the lower performance compared to a SqlDataReader. However if I can't get the SqlDataReader solution to work, I may have to use the DataSet. Thanks for the reply. – Mark Allison Nov 18 '10 at 13:33
  • Mark, I added a link to my answer, check it out if it helps you a bit. – Arief Nov 18 '10 at 14:00
1

You need to use the .NextResult() method of your DataReader to advance from the first result set to the next. The method returns True if more results exist in your set, or False if no more result sets exist.

After calling .NextResult(), you can then bind your GridView to the current result set.

Your code might look like this:

SqlDataReader dr = DB.CheckDBIntegrity();

while (!dr.NextResult())
    {
       // bind results to appropriate grid
    }
Jazza
  • 1,042
  • 1
  • 9
  • 22
  • I've updated my code in my questiona and I get this error: `Invalid attempt to call HasRows when reader is closed.` which is confusing. – Mark Allison Nov 18 '10 at 12:39
  • @Mark: this is because you have closed the connection to the database before you have read/used the data in the datareader. – Jazza Nov 18 '10 at 13:19
  • I have removed con.Close() from the MultipleResults method but I get the same error: `Invalid attempt to call HasRows when reader is closed.` – Mark Allison Nov 18 '10 at 13:33