Biding Multiple Gridviews one one page

yv989c:
The code you posted works great. I have another related question. What is the best way to to do the databinding when I need to do it multiple times on one page. I actually have 5 grids on the page, each using a different stored procedure (but the same UserID parameter) to pull data from different tables. I guess I'm looking for the cleanest, best way to do it. And where is the best place to close the connection?

private void BindData()
{
    using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString))
    {
        SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = SessionHelper.GetUserId();
        cn.Open();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid.DataSource = dr;
            MyGrid.DataBind();
        }
    }
}
dodgerfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, if you have a SP to get the data for each control, and the only parameters that you need is the UserId, the simple thing that you can do is this:
private void BindData()
{
    using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString))
    {
        SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = SessionHelper.GetUserId();
        cn.Open();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid.DataSource = dr;
            MyGrid.DataBind();
        }

        cm = new SqlCommand("MyStoredProcedure2", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = SessionHelper.GetUserId();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid2.DataSource = dr;
            MyGrid2.DataBind();
        }

        cm = new SqlCommand("MyStoredProcedure3", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = SessionHelper.GetUserId();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid3.DataSource = dr;
            MyGrid3.DataBind();
        }
    }
}

Open in new window


Another way is to you use only one SP to initialize your controls, example (T-SQL):
CREATE PROC
	dbo.SpMyStoredProcedure
(
	@pUserId INT
)
AS

-- Data for your Grid1
SELECT
	Id,
	Name
FROM
	dbo.MyTable1
WHERE
	UserId = @pUserId


-- Data for your Grid2
SELECT
	Id,
	Name
FROM
	dbo.MyTable2
WHERE
	UserId = @pUserId


-- Data for your Grid3
SELECT
	Id,
	Name
FROM
	dbo.MyTable3
WHERE
	UserId = @pUserId

Open in new window


Then by using that SP you can get the data for your grids in this way:
private void BindData()
{
    using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString))
    {
        SqlCommand cm = new SqlCommand("dbo.SpMyStoredProcedure", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@pUserId", SqlDbType.Int).Value = SessionHelper.GetUserId();
        cn.Open();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid.DataSource = dr;
            MyGrid.DataBind();
            
            dr.NextResult();
            MyGrid2.DataSource = dr;
            MyGrid2.DataBind();

            dr.NextResult();
            MyGrid3.DataSource = dr;
            MyGrid3.DataBind();
        }
    }
}

Open in new window

This last approach is better because it does less ground trips to the DB server.

About your comment:
where is the best place to close the connection?

The using statement that enclose your SqlConnection will guarantee that it will be closed when the execution go out that code scope, basically it automatically call the Dispose method of your connection, that will close your connection.
If you want to know more about this, please refer to:
http://msdn.microsoft.com/en-us/library/yh598w02.aspx

Let me know if you need a better explanation.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carlos VillegasFull Stack .NET DeveloperCommented:
Another recommendation about sql connections, when you open a connection, always keep in mind to have it open the less time that you can. Is better to open and close it multiples times inside a long running method that having it opened all the way. That is because there is something called the connection pool, and when you leave a connection opened a long time it is not available to other threads, however, if you close it, it return to the connection pool (and remains open for a while), in a way that can be reused for another thread. I'm not sure if I'm explaining this very well, is a little more complex. Just keep this in mind:
Open late, Close soon
0
dodgerfanAuthor Commented:
Perfect, again. And thanks for the tips on db connections. It's easy to forget about doing things properly when you get caught up in trying to get it done. Thanks again.
0
Carlos VillegasFull Stack .NET DeveloperCommented:
You're welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.