• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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();
        }
    }
}
0
dodgerfan
Asked:
dodgerfan
  • 3
1 Solution
 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now