Use the same datasource for multiple dropdownlists in a Gridview

[apchambers]

I want to make one call to the database to populate multiple dropdownlists in gridview template fields. I have tried populating hashtables, datasets etc but I can't work out how to make one call to the database.

This code currently works but is very slow

<asp:TemplateField HeaderText="Player 1" SortExpression="lngCDPL1ID" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center">
<ItemTemplate>
<aspropDownList ID="lngCDPL1IDEdit" runat="server" DataSource='<%# GetPlayerds() %>' Text='<%# Bind("lngCDPL1ID") %>'
DataTextField="txtPLName" DataValueField="lngPLID" AutoPostBack="false" >
</aspropDownList>
</ItemTemplate>

Public Function GetPlayerds() As DataSet
Dim CurrConn As New SqlClient.SqlConnection
Dim myConn As New GetConnection
CurrConn.ConnectionString = myConn.GetMyConnection
Dim mystr As String = "SELECT tblPlayers.lngPLID, dbo.fnPlayerName(txtPLFirstName,txtPLLastName,lngPLDeleted) as txtPLName from tblPlayers" & _
" where(tblPlayers.lngPLVisitor = 0 Or tblPlayers.lngPLID = 0)" & _
" order by lngPLDeleted, txtPLFirstName, txtPLLastName"
Dim ds As New DataSet
Dim ad As New SqlDataAdapter(mystr, CurrConn)
ad.Fill(ds, "Players")

Return ds

End Function

 

[jmeckley]

class MyPage : Page
{
   private DataSet data = null;

   GridViewRowDataBind(object sender, GridViewRowEventArgs e)
   {
      LoadData();
      find the drop down in the datarow
      if drop down exists, bind data table.
   }

   private void LoadData()
   {
      if(data != null) return;
      data = new DataTable();
      ... load data into datatable
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

If it is data that will not change often, you can load it into cache in the global.asax and then retreive it from cache to load your ddls.

 

[jmeckley]

cache *would* work too, but I would only use this as a last resort. I have found cache is used as a catch all. if you're not careful cache can actually be a determent to preformance. instead of enhancing it.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

I agree. It is something that can be abused. But is also dependent on your hardware. I work for a large real estate company. We load a good amount of data into cache to speed up searches. But then again, we have very good hardware. Like most things, you have to carefully balance it, and not just use it as a catch all as you said.

 

[jshurst]

If you are using stored procedures then you can return multiple sets of data and load then into different tables in a dataset. For example (might not be entirely correct syntax):

ALTER PROCEDURE MyStoredProc

SELECT myId FROM Table1

SELECT myOtherId FROM Table2

END

Then in your code:

'Create the DS and call the stored proc
Dim ds As DataSet = FunctionToCallMySP()

Me.DropDown1.DataSource = ds.Tables(0)
Me.DropDown1.DataBind()

Me.DropDown2.DataSource = ds.Tables(1)
Me.DropDown2.DataBind()