Filling multiple tables

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

I have about 10 dropdownlists that I need to fill when the page loads.

At the moment I am doing the following over and over for each dropdown:

*****************************************************************
Sub GetExperience(sender As Object, e As System.EventArgs)
Dim emailReader As SqlDataReader
trace.warn("inside GetExperience")
Dim ConnectionString as String
=System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftso")
Dim objConn as New SqlConnection (ConnectionString)
Dim CommandText as String = "Exec GetExperienceLevel"
Dim objCmd as New SqlCommand(CommandText,objConn)
objConn.Open()

ExperienceLevel.DataSource=objCmd.ExecuteReader
ExperienceLevel.DataTextField= "Description"
ExperienceLevel.DataValueField="EducationLevelID"
ExperienceLevel.databind()
trace.warn("exiting GetZipCodes")
end sub
*******************************************************************

The tables and objects are different for each one.

I was curious if this too inefficient and if there is a better way to do
this. If there were only 1 or 2 I assume this would be fine. But with 10
or so, would the resetting up and the objConn.Open() for each one be
draining on the system?

Thanks,

Tom
 
SQL Server can execute several operations from a single CommandText. Simply
concatenate the SELECT statements together (separate with ";" if you want
to) and execute it with Fill. The resulting DataSet will be populated with N
tables--one for each rowset. Bind to the tables and you're done.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Assuming you are using the same connection string your code should take
advantage of connection pooling. So it is not as bad of a performance hit as
you might think to load 10 lists like this. You could call a single proc that
returns all 10 rowsets into a DataSet, as well.

// John Papa
// http://codebetter.com/blogs/john.papa
 
Hi,
Better way to do this is to put all your select statements for 10 dropdown
into single stored procedure. Then use the sqladapter.Fill method to
populate a dataset. Then bind each tables inside the dataset to each
dropdown.
That's it!.
Regards,
Sambath
 
You can do

SQL Server -
a) Batched SQL
b) Stored proc with multiple resultsets

Oracle -
a) Batched SQL --> Not recommended
b) Stored proc/package with multiple ref cursors

... That way you can fill multiple tables in one database hit.

BTW - you might want to leverage schemas if you intend to refill multiple
times, or keep the data fresh by multiple fills.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
 
Sambathraj said:
Hi,
Better way to do this is to put all your select statements for 10 dropdown
into single stored procedure. Then use the sqladapter.Fill method to
populate a dataset. Then bind each tables inside the dataset to each
dropdown.
Click to expand...

How would I do that?

How would I know where one table ends and another begins?

Thanks,

Tom
 
If you call a proc that has 10 select statements in it, associate that proc
to a Command and use the DataAdapter.Fill method to fill a DataSet, the
DataSet will be loaded with 10 DataTables ... 1 for each select statement.
Then, just bind the appropriate DataTable to the appropriate dropdownlist.

// John Papa
// http://codebetter.com/blogs/john.papa
 
John Papa said:
If you call a proc that has 10 select statements in it, associate that
proc
to a Command and use the DataAdapter.Fill method to fill a DataSet, the
DataSet will be loaded with 10 DataTables ... 1 for each select statement.
Then, just bind the appropriate DataTable to the appropriate dropdownlist.
Click to expand...

But how do I differentiate between the tables?

You typically fill the adapter like so:

oAdpt.Fill(oData,"tempTable")

How do I tell it there are actually 10 tables and how do I get access to
them?

Also, if I am just filling dropdowns, whouldn't it be better to use a
reader?

Thanks,

Tom
 
Back
Top