How to fill multiple datatables in a dataset in a single fill

  • Thread starter Thread starter Saladin
  • Start date Start date
S

Saladin

Anyone who knows how to fill more than one datatable in a single fill using
a dataadapter? I have 4 joined tables in a select-statement that I want to
fill into their datatables. Now I have to make 4 selects to do that, and
that's no good. Alternatively make a datatable representing all 4 tables
alltogether.
 
Anyone who knows how to fill more than one datatable in a single fill
using a dataadapter? I have 4 joined tables in a select-statement that
I want to fill into their datatables. Now I have to make 4 selects to
do that, and that's no good. Alternatively make a datatable
representing all 4 tables alltogether.
Click to expand...

As far as I know the DataAdapter is designed to to handle a single
table.
However, your SelectCommand can be a batch of SELECT statements like

string stmt = @"
SELECT * FROM Customer
SELECT * FROM Order
SELECT * FROM OrderLine";

Or you use a stored procedure that returns all your tables.

When using an untyped DataSet the batch above will result in 3
DataTables
called "Table", "Table1", "Table2". The joins are not reflected until
you add DataRelations to your DataSet.

For a typed DataSet I recommend adding a TableMapping for each table
returned by your statement. Otherwise new DataTable will be added to
your DataSet. Example:

SqlDataAdapter da= new SqlDataAdapter(stmt, sqlConnection);
da.TableMappings.Add("Table", "Customer");
da.TableMappings.Add("Table1", "Order");
da.TableMappings.Add("Table2", "OrderLine");

da.Fill(dataset);

Daniel
 
Like Daniel mentions - you can use batched select statements - however I'd
throw in one caveat: it depends on the DB that you are using, it's not
really an ADO.NET thing.

If you use the Batch select, then you'll most likely want to use a
DataRelation on the datatables so the integrity constraints match what is
being done on the back end - this way you minimize the risk of sending data
back in an update that won't be accepted.

If you want all of the data in one datatable - as opposed to a dataset with
4 tables, than a union done on the server may be your best bet - however if
you are going to use this data for updates - this approach won't work (or
will be too much hassle to probably be worth it).

HTH,

Bill
 
Bill is right .. I wanna add a little bit more.

Sql Server - batched good
Oracle - Batched bad (I read somewhere that it will not be supported 10
onwards)

One more thing - When filling a dataset, enable or add relations in the
dataset after you have filled in the data for much better performance.

- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik
 
Back
Top