multiple tables within a dataset

[Craig G]

i was told that its possible to load more than 1 datatable into a dataset
using a stored procedure

i need to fill 3 combo's on my form
if i had a SQL Stored Proc that had 3 different select statments

SELECT.......
go
SELECT.......
go
SELECT........
go

is it just a case of saying:-
dataAdapt.Fill(dsResultSet)

and it will add 3 tables 0,1,2 with the appropriate data from each select?
or is there more to it?

 

[Cor Ligthert]

Graig,

As far as I know does a SQL batch procedure not work and do you need 3
seperated dataadapters (and therefore select statements). If that is in a
storedprocedure or just given does not matter, it is just a SQLcommand.

You can than do
da1.fill(ds,table1)
da2.fill(ds,table2)
da3.fill(ds,table3)

Or use the same dataadapter and change the select command (For what I see
not any benefit)

I hope this helps

Cor

 

[Guest]

i was told that its possible to load more than 1 datatable into a dataset
using a stored procedure

i need to fill 3 combo's on my form
if i had a SQL Stored Proc that had 3 different select statments

SELECT.......
go
SELECT.......
go
SELECT........
go

is it just a case of saying:-
dataAdapt.Fill(dsResultSet)

and it will add 3 tables 0,1,2 with the appropriate data from each select?
or is there more to it?

I have used 1 dataadapter with 1 command & its stored proceedure to fill
multiple datatables in a single dataset with just 1 fill command. However I
only had the go at the very end of the sp, not in the middle.

 

[JD]

Yep thats about it. I created a stored procedure on NorthWind database:

CREATE PROCEDURE TestTables AS
select * from products
select * from categories


Then did the following code:

Dim SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Dim SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Dim SqlConnection1 As System.Data.SqlClient.SqlConnection
SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
SqlConnection1 = New System.Data.SqlClient.SqlConnection
'
'SqlDataAdapter1
'
SqlDataAdapter1.SelectCommand = SqlSelectCommand1
SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "TestTables", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("ProductID", "ProductID"), New
System.Data.Common.DataColumnMapping("ProductName", "ProductName"), New
System.Data.Common.DataColumnMapping("SupplierID", "SupplierID"), New
System.Data.Common.DataColumnMapping("CategoryID", "CategoryID"), New
System.Data.Common.DataColumnMapping("QuantityPerUnit", "QuantityPerUnit"),
New System.Data.Common.DataColumnMapping("UnitPrice", "UnitPrice"), New
System.Data.Common.DataColumnMapping("UnitsInStock", "UnitsInStock"), New
System.Data.Common.DataColumnMapping("UnitsOnOrder", "UnitsOnOrder"), New
System.Data.Common.DataColumnMapping("ReorderLevel", "ReorderLevel"), New
System.Data.Common.DataColumnMapping("Discontinued", "Discontinued")}), New
System.Data.Common.DataTableMapping("Table1", "Table1", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("CategoryID", "CategoryID"), New
System.Data.Common.DataColumnMapping("CategoryName", "CategoryName"), New
System.Data.Common.DataColumnMapping("Description", "Description"), New
System.Data.Common.DataColumnMapping("Picture", "Picture")})})
'
'SqlSelectCommand1
'
SqlSelectCommand1.CommandText = "[TestTables]"
SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
SqlSelectCommand1.Connection = SqlConnection1
SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
'
'SqlConnection1
'
SqlConnection1.ConnectionString = <<SOME CONNECTION STRING>>

Dim DS As New DataSet
SqlDataAdapter1.Fill(DS)

Worked like a charm.

JD

 

[Greg Burns]

Simply don't use GO between you SELECT statements and Fill will
automatically create multiple tables.

When it finishes you can then name the autonamed tables as you like.

da.Fill(dsResultSet)
dsResultSet.Tables("Table").TableName="MyFirstTable"
dsResultSet.Tables("Table1").TableName="MyNextTable"
dsResultSet.Tables("Table2").TableName="MyLastTable"

Greg