Filling multiple tables

[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

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Guest]

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

 

[Sambathraj]

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

 

[Sahil Malik [MVP]]

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/

 

[tshad]

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.

How would I do that?

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

Thanks,

Tom

 

[Guest]

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

 

[tshad]

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.

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