generic function to populate listboxes/combos/etc

[bdubon]

I'm trying to create a generic function to populate listboxes/combos/etc. I want the function to use a DataReader to get the data. The function will reside at the DAL and will be used by ALL listboxes, therefore I can't 'hardcode' a specific control to be populated during the reader.Read step.

 

How should I do this?

[mocella]

One approach would be to have a function that takes your control and the select string (or stored proc name) and perhaps a paramter array as parameters for this function.

 

You can then have this function call to a "GetDataReader" function that takes the select string/parm array from the other function above, and sets up and returns a data-reader.

 

Now you can just populated your listbox/combobox or whatever. I'd probably set up a function for each type of control "FillComboBox"/"FillListBox" in the case that there were specific requirements for each specified control type, but you could probably work out a generic function in this case since the mentioned controls are "ListControls".

[bdubon]

Mocella,

FYI, I'm using SQL Server, Stored Procs w/ no parameters.

 

By "..a function that takes your control .." do you mean pass-in the control? I'd prefer to only pass-in the stored proc name and have the function return a populated object to which the listbox/combo could then bind to.

 

Also, to return a datareader, the function "GetDatareader" must be declared as datareader. No? I've tried that but it's not a valid type.

 

FYI, I've been working on a function that will return a DataTable (all new to me!). Here's what it looks like so far:

 

Public Overloads Overrides Function GetDataReader(ByVal storedProcName As String) As DataTable

 

GetDataReader = New DataTable("ListTable")

 

 

 

'Create the connection to the database...

Dim cnSQLConnection As New SqlConnection(GenericDataAccessProvider._Dbstring)

cnSQLConnection.Open()

 

'Create the readers command

Dim command As New SqlCommand(storedProcName, cnSQLConnection)

command.CommandType = CommandType.StoredProcedure

command.CommandText = storedProcName

 

'Create the DataReader object

Dim SQLReader As SqlDataReader

 

Try

'Set up the DataTable

Dim displayMemberCol As DataColumn = New DataColumn

Dim valueMemberCol As DataColumn = New DataColumn

 

With valueMemberCol

.DataType = System.Type.GetType("System.Integer")

.ColumnName = "Key"

End With

 

With displayMemberCol

.DataType = System.Type.GetType("System.String")

.ColumnName = "Text"

End With

 

GetDataReader.Columns.Add(valueMemberCol)

GetDataReader.Columns.Add(displayMemberCol)

 

'Populate the Reader

SQLReader = command.ExecuteReader()

 

Do While SQLReader.Read

GetDataReader.NewRow()

 

Loop

 

Catch ex As Exception

MsgBox("The Following Error Occurred: " & ex.Message, , "Class -MSSQLServer.GetDataReader()")

Finally

 

End Try

 

SQLReader.Close()

cnSQLConnection.Close()

 

End Function

 

Thanks!

[mocella]

Okay, then go with a common function to return you a datatable - your method signature should look like this:

private function GetSomeData( byVal spName as String) as DataTable

 

You need to use a dataadapter to fill your datatable - see MSDN.

 

Once you get back from GetSomeData, you can assign your databindings like so (I'll assume your proc returns data in this format (ID, Description):

DataTable myComboData = GetSomeData("dbo.GetComboData")
cboSomeControl.DataSource = myComboData
cboSomeControl.ValueMember = myCombo.Columns(0).ToString() 'ID
cboSomeControl.DisplayMember = myCombo.Columns(1).ToString() 'Desc

 

and so forth. I haven't actually tried this code (or any VB.net for that matter) in a while, so this may not be exact but you'll be on the right track.

[bdubon]

Is there a better way? I'm trying to benefit from the performance of Datareaders. Could you post some code that returns a datareader? Thanks again