Speed up my nested repeater!

[ASF]

Hey all,

I have a gridview which pulls from a BLL which pulls from a DAL
(an .XSD file). Each row on that gridview has a nested repeater which
pulls from another table. The code which populates each nested
repeater is shown below (it's in the gridview's RowDataBound event).
My question is this: what can I do to speed up this process? Currently
EVERY time a new gridview row is generated the code reaches out to the
database, grabs the specific records for the nested repeater, and
closes the connection. This can be a huge overhead if many master
records are displayed.

Naturally my inclination is to grab ALL the records needed in one
database pull and then relate them in the codebehind, but I'm not sure
how to accomplish this given the current structure (with a DAL and
all).

Does anyone have any suggestions to increase efficiency here?

Protected Sub gvITV_RowDataBo und(ByVal sender As Object, ByVal e
As System.Web.UI.W ebControls.Grid ViewRowEventArg s) Handles
gvITV.RowDataBo und

Dim nestedRepeater As Repeater =
TryCast(e.Row.F indControl("NR" ), Repeater)
If nestedRepeater IsNot Nothing Then
Dim x As HiddenField = e.Row.FindContr ol("hdnID")
Dim con As SqlConnection = New
SqlConnection(S ystem.Web.Confi guration.WebCon figurationManag er.ConnectionSt rings("Connecti onString").ToSt ring())

Dim sdap As SqlDataAdapter = New
SqlDataAdapter( "dbo.Fsn_WE B", con)
sdap.SelectComm and.CommandType =
CommandType.Sto redProcedure
sdap.SelectComm and.CommandTime out = 0
sdap.SelectComm and.Parameters. Add("new_id", SqlDbType.Int)
sdap.SelectComm and.Parameters. Item(0).Value = x.Value

con.Open()
Dim dt As Data.DataTable = New Data.DataTable
sdap.Fill(dt)
con.Close()

nestedRepeater. DataSource = dt
nestedRepeater. DataBind()
End If
End Sub

[ASF]

Anyone? I'd appreciate any advice you all may have.

Thanks
On May 6, 2:44*pm, ASF wrote:

Hey all,

I have a gridview which pulls from a BLL which pulls from a DAL
(an .XSD file). Each row on that gridview has a nested repeater which
pulls from another table. The code which populates each nested
repeater is shown below (it's in the gridview's RowDataBound event).
My question is this: what can I do to speed up this process? Currently
EVERY time a new gridview row is generated the code reaches out to the
database, grabs the specific records for the nested repeater, and
closes the connection. This can be a huge overhead if many master
records are displayed.

Naturally my inclination is to grab ALL the records needed in one
database pull and then relate them in the codebehind, but I'm not sure
how to accomplish this given the current structure (with a DAL and
all).

Does anyone have any suggestions to increase efficiency here?

* * Protected Sub gvITV_RowDataBo und(ByVal sender As Object, ByVal e
As System.Web.UI.W ebControls.Grid ViewRowEventArg s) Handles
gvITV.RowDataBo und

* * * * Dim nestedRepeater As Repeater =
TryCast(e.Row.F indControl("NR" ), Repeater)
* * * * If nestedRepeater IsNot Nothing Then
* * * * * * Dim x As HiddenField = e.Row.FindContr ol("hdnID")
* * * * * * Dim con As SqlConnection = New
SqlConnection(S ystem.Web.Confi guration.WebCon figurationManag er.ConnectionSt *rings("Connect ionString").ToS tring())

* * * * * * Dim sdap As SqlDataAdapter = New
SqlDataAdapter( "dbo.Fsn_WE B", con)
* * * * * * sdap.SelectComm and.CommandType =
CommandType.Sto redProcedure
* * * * * * sdap.SelectComm and.CommandTime out = 0
* * * * * * sdap.SelectComm and.Parameters. Add("new_id", SqlDbType.Int)
* * * * * * sdap.SelectComm and.Parameters. Item(0).Value = x.Value

* * * * * * con.Open()
* * * * * * Dim dt As Data.DataTable = New Data.DataTable
* * * * * * sdap.Fill(dt)
* * * * * * con.Close()

* * * * * * nestedRepeater. DataSource = dt
* * * * * * nestedRepeater. DataBind()
* * * * End If
* * End Sub

[=?Utf-8?B?QnJ1Y2U=?=]

ASF,

Any luck on that? I'm in the same boat. I read in this article
http://msdn.microsoft.com/en-us/library/aa992038.aspx where it mentions a
similar issue with nesting a gridview. It recommends caching for smaller
databases and using custom data object bound to an ObjectDataSourc e if your
database is large, but with no clear cut direction. I just posted to an
www.asp.net forum if you want to follow the thread.
http://forums.asp.net/t/1269591.aspx

If you found a good solution, please let me know!

"ASF" wrote:

Hey all,

I have a gridview which pulls from a BLL which pulls from a DAL
(an .XSD file). Each row on that gridview has a nested repeater which
pulls from another table. The code which populates each nested
repeater is shown below (it's in the gridview's RowDataBound event).
My question is this: what can I do to speed up this process? Currently
EVERY time a new gridview row is generated the code reaches out to the
database, grabs the specific records for the nested repeater, and
closes the connection. This can be a huge overhead if many master
records are displayed.

Naturally my inclination is to grab ALL the records needed in one
database pull and then relate them in the codebehind, but I'm not sure
how to accomplish this given the current structure (with a DAL and
all).

Does anyone have any suggestions to increase efficiency here?

Protected Sub gvITV_RowDataBo und(ByVal sender As Object, ByVal e
As System.Web.UI.W ebControls.Grid ViewRowEventArg s) Handles
gvITV.RowDataBo und

Dim nestedRepeater As Repeater =
TryCast(e.Row.F indControl("NR" ), Repeater)
If nestedRepeater IsNot Nothing Then
Dim x As HiddenField = e.Row.FindContr ol("hdnID")
Dim con As SqlConnection = New
SqlConnection(S ystem.Web.Confi guration.WebCon figurationManag er.ConnectionSt rings("Connecti onString").ToSt ring())

Dim sdap As SqlDataAdapter = New
SqlDataAdapter( "dbo.Fsn_WE B", con)
sdap.SelectComm and.CommandType =
CommandType.Sto redProcedure
sdap.SelectComm and.CommandTime out = 0
sdap.SelectComm and.Parameters. Add("new_id", SqlDbType.Int)
sdap.SelectComm and.Parameters. Item(0).Value = x.Value

con.Open()
Dim dt As Data.DataTable = New Data.DataTable
sdap.Fill(dt)
con.Close()

nestedRepeater. DataSource = dt
nestedRepeater. DataBind()
End If
End Sub