Speed up my nested repeater!

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_RowDataBound(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.GridViewRowEventArgs) Handles
gvITV.RowDataBound
>
* * * * Dim nestedRepeater As Repeater =
TryCast(e.Row.FindControl("NR"), Repeater)
* * * * If nestedRepeater IsNot Nothing Then
* * * * * * Dim x As HiddenField = e.Row.FindControl("hdnID")
* * * * * * Dim con As SqlConnection = New
SqlConnection(System.Web.Configuration.WebConfigur ationManager.ConnectionSt*rings("ConnectionString" ).ToString())
>
* * * * * * Dim sdap As SqlDataAdapter = New
SqlDataAdapter("dbo.Fsn_WEB", con)
* * * * * * sdap.SelectCommand.CommandType =
CommandType.StoredProcedure
* * * * * * sdap.SelectCommand.CommandTimeout = 0
* * * * * * sdap.SelectCommand.Parameters.Add("new_id", SqlDbType.Int)
* * * * * * sdap.SelectCommand.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

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_RowDataBound(ByVal sender As Object, ByVal e
As System.Web.UI.WebControls.GridViewRowEventArgs) Handles
gvITV.RowDataBound
>
Dim nestedRepeater As Repeater =
TryCast(e.Row.FindControl("NR"), Repeater)
If nestedRepeater IsNot Nothing Then
Dim x As HiddenField = e.Row.FindControl("hdnID")
Dim con As SqlConnection = New
SqlConnection(System.Web.Configuration.WebConfigur ationManager.ConnectionStrings("ConnectionString") .ToString())
>
Dim sdap As SqlDataAdapter = New
SqlDataAdapter("dbo.Fsn_WEB", con)
sdap.SelectCommand.CommandType =
CommandType.StoredProcedure
sdap.SelectCommand.CommandTimeout = 0
sdap.SelectCommand.Parameters.Add("new_id", SqlDbType.Int)
sdap.SelectCommand.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
>