CODEWITHSUNDEEP
c#asp.netmultiple-resultsets
Mark Allison
Mark Allison

Reputation: 7228

How to populate many GridView controls from one stored procedure?

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.

Upvotes: 2

Views: 4305

Answers (2)

Arief
Arief

Reputation: 6085

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

Upvotes: 5

Jazza
Jazza

Reputation: 1082

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
    }

Upvotes: 1

Related Questions