Looking for suggestions on populating dropdown from database table

  • Thread starter Thread starter pantichd
  • Start date Start date
P

pantichd

Hello,

I want to populate an employee name dropdown in a webform with values from a database table. I know I can use a data adapter to retrieve the whole table into a dataset and bind the table from the dataset to the dropdown. However, I thought I could do it a little more efficiently by using a datareader to retrieve the name and id columns from the table, put them in a collection and then bind the collection to the dropdown.

Below is the method I'm using to retrieve a key/value pair from a database table. It returns a collection. Well, now I'm stuck. I can't figure out how to get the collection into the dropdown.

When reading about dropdowns I keep running into documentation about ListItemCollection but I can't figure out how to go from collection to ListItemCollection and then how to get that bound to the dropdown.


Any help would be greatly appreciated.


David


Public Function getKeyValueList(ByVal sql As String) As Collection

Dim coll As Collection = New Collection

readerConn.Open()

readerCmd.Connection = readerConn

readerCmd.CommandText = sql

reader = readerCmd.ExecuteReader

If reader.HasRows Then

Do While reader.Read()

coll.Add(reader.GetString(0), reader.GetString(1))

Loop

End If

reader.Close()

readerConn.Close()

End Function
 
DataSet or Collection, you are doing the same thing. You are pulling from a
bindable object into something else. There are times where this makes sense,
but the most efficient use of a DataReader is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
BindPropertyTypeDropDown();
}

private void BindPropertyTypeDropDown()
{
//Working with Northwind
string connString = ConfigurationSettings.AppSettings["connString"];
//Would normally have a stored procedure here
string sql = "SELECT EmployeeID, FirstName+' '+LastName AS WholeName FROM
Employees";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);

try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();

//Note that you have to leave a reader open while you bind
ddlPropertyType.DataSource = dr;
ddlPropertyType.DataValueField = "EmployeeID";
ddlPropertyType.DataTextField = "WholeName";
ddlPropertyType.DataBind();
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();

conn.Dispose();
}
}

Of course, if you are running a multi-tiered application, it is more
efficient to either use business objects or set up a DataSet as a
quasi-business object. The main key here is making sure you are using the
proper interfaces or setting up specific attributes. A good primer:

http://www.codeproject.com/useritems/AspNetBindDatagridVT.asp

Check out the links at the bottom, as well.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
I'm not sure what your concern is, or what you mean by more efficiently.

I can only guess that when you are using the dataset and say "retrieve the
whole table into a dataset " you mean the results of "SELECT * FROM ...". If
you are comparing that with using a DataReader to populate the collection
with the only required data (name and id), then yes, that's more efficient.
But the better solution would be to change your query to something like
"SELECT name,id FROM ...", which is even more efficient, and if you simply
load that into the DataTable you should be fine.

Remember too that the DataAdapter uses a DataReader to Fill the table, so
you won't get more efficiency by using the DataReader, only more control.

But it starts with the source, the SQL query, if that's inefficient then no
amount of tuning further down the pipe is going to catch up.

On the other hand, if you need _both_ the whole table and want a subset for
the dropdown, then Fill the table with a DataAdapter and then use a foreach
against the DataTable.Rows to extract the subset.
 
Gregory (or do you go by "Cowboy"?),

Thanks for responding so quickly.

I found an example in the documentation that showed something almost exactly
like your example. However, since I'm doing a layered approach I didn't use
it.

Even though in this case I want to end up with the data in a dropdown there
will be other cases where I want to use the key/value collection for
different purposes. That's why I built such a generic method.

I considered passing in the control to the method but then I'd have many
methods doing basically the same thing but just with different method
signatures. Not that that is a terrible thing but I didn't want to go that
way. I also thought about returning the data reader object to the caller but
that wasn't very elegant and presented other problems.

Thanks for the links. I'll take a look and see if that triggers any ideas.

David



Cowboy (Gregory A. Beamer) - MVP said:
DataSet or Collection, you are doing the same thing. You are pulling from a
bindable object into something else. There are times where this makes sense,
but the most efficient use of a DataReader is as follows:

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
BindPropertyTypeDropDown();
}

private void BindPropertyTypeDropDown()
{
//Working with Northwind
string connString = ConfigurationSettings.AppSettings["connString"];
//Would normally have a stored procedure here
string sql = "SELECT EmployeeID, FirstName+' '+LastName AS WholeName FROM
Employees";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);

try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();

//Note that you have to leave a reader open while you bind
ddlPropertyType.DataSource = dr;
ddlPropertyType.DataValueField = "EmployeeID";
ddlPropertyType.DataTextField = "WholeName";
ddlPropertyType.DataBind();
}
finally
{
if(conn.State == ConnectionState.Open)
conn.Close();

conn.Dispose();
}
}

Of course, if you are running a multi-tiered application, it is more
efficient to either use business objects or set up a DataSet as a
quasi-business object. The main key here is making sure you are using the
proper interfaces or setting up specific attributes. A good primer:

http://www.codeproject.com/useritems/AspNetBindDatagridVT.asp

Check out the links at the bottom, as well.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

pantichd said:
Hello,

I want to populate an employee name dropdown in a webform with values
Click to expand...
Click to expand...
from a database table. I know I can use a data adapter to retrieve the whole
table into a dataset and bind the table from the dataset to the dropdown.
However, I thought I could do it a little more efficiently by using a
datareader to retrieve the name and id columns from the table, put them in a
collection and then bind the collection to the dropdown.database table. It returns a collection. Well, now I'm stuck. I can't figure
out how to get the collection into the dropdown.ListItemCollection but I can't figure out how to go from collection to
ListItemCollection and then how to get that bound to the dropdown.
 
Piebald,

My understanding is that datasets have a of overhead because of all the functionality built into them.

All through the documentation I've seen references such as this "You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, rather than waiting for the entire results of the query to be returned, and (by default) storing only one row at a time in memory, reducing system overhead."

This seems to fit my requirements perfectly which is why I chose the DataReader avenue. I'm beginning to wonder now. This is turning out to be more difficult than I expected.

Anyway, back to my original question: Having a collection of key/value pairs, how do I get that bound to a dropdown list in a webform?

Thanks!

David.
 
But the points in that quote don't apply here because you're going to read
and store the complete results anyway. If, for instance, you were going to do
something with each DataRow and then replace the DataRow with the next, the
DataReader makes sense.

You also can reduce "overhead" by using a DataTable rather than a DataSet (I
hardly ever use a DataSet).
 
Good point about DataTable. I have my blinders on and didn't even consider
that.

Thanks!
 
Back
Top