Fill Multiple Datatables with One Request

[LouieG]

Very simply, I have a strongly typed dataset and I want to fill more than
one data table at a time.
Sending 8 requests each with one query to my SQL server is much less
efficient than if I could send all 8 queries with one request. SQL has no
problem handling this and I would have thought that the data adapter would
be intelligent enough to know that results from 'select * from MyTable'
should fill 'MyTable' in my strongly typed dataset and results from 'select
* from MyTable2' should fill 'MyTable2' . The fill method of the
dataadapter can either fill the dataset itself or can specify ONE specific
strongly typed datatable, but what about 2 or 3 or 4 of them?
Is there a way to accomplish this?

Something like:

strSQL = "multiple sql queries"
cmdSQL = new sqlCommand(strSQL, MyOpenConnection)
daSQL = new sqldataadapter(cmdSQL)

daSQL.Fill(MyStronglyTypedDataset)


Thiscontrol.datasource = MyStronglyTypedDataset.MyTable1
ThisOthercontrol.datasource = MyStronglyTypedDataset.MyTable2
ThisThirdcontrol.datasource = MyStronglyTypedDataset.MyTable3

One trip to the SQL server, as many tables worth of results as I need.

 

[Leigh Kendall]

Check out the tablemappings property of the dataadapter; you can rename the
default table names to something more meaningful. The SDK has details...

HTH,

 

[Ziga Jakhel]

Not going to happen within a single call of a dataadapter.

The only way to do it is to use a DataReader object and manually fill tables
with data, whereby you switch resultsets for different tables (call
DataReader.NextResult to move to next resultset).

This is more or less what the dataadapter does for you - filling a single
table with data, using the datareader object.
If you need more functionallity, you can always do it yourself.

Regards,

Ziga Jakhel, MCAD.NET
IUS SOFTWARE d.o.o.
www.ius-software.si

 

[Leigh Kendall]

Not sure what you mean... A single call to fill on the dataadapter can fill
multiple tables. Of course you have to return multiple select's from your
query.

If you have a stored procedure for example that returns three results:
select * from table1
select * from table2
select * from table3

When you call fill on the dataadapter and pass in a dataset, that dataset by
default will now have three new tables; Table1, Table2 and Table3. If you
don't like those names, then you can use the tablemappings property to
change them.

Leigh

 

[LouieG]

Much thanks Leigh,
after looking into the tablemappings property I was able to combine quite a
few data calls into one turning what was a 4 second operation into 3
seconds. Still not as fast as I want overall but much better. Now I just
have my own code to look at to improve performance further.