How to populate many GridView controls from one stored procedure?

Question

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.

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