How to Fill Dataset with results from sproc with multiple selects

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Heres the situation:

1 stored procedure

2 select statements

into 1 Dataset

I can load the first set of results into a table, but how do I push to the
results of the second select statement and load it into the dataset as well
in a different table?

Old way - .nextRecordSet

New way ???

Thanks in advance.

Jim Hawley
 
Hi,

DJ said:
Heres the situation:

1 stored procedure

2 select statements

into 1 Dataset

I can load the first set of results into a table, but how do I push to the
results of the second select statement and load it into the dataset as
well
in a different table?

Old way - .nextRecordSet

New way ???
Click to expand...

A DataAdapter can load all resultsets into DataTable's inside a DataSet.
The resultset's are originally named Table, Table1, Table2 and so one, but
you can use TableMappings to specify a different DataTable name :

SqlConnection conn = new SqlConnection( ... );

SqlDataAdapter adapt = new SqlDataAdapter("SomeStoredProcedure", conn);

adapt.TableMappings.Add("Table", "DataTableName1");
adapt.TableMappings.Add("Table1", "DataTableName2");

DataSet ds = new DataSet();
adapt.Fill(ds);


HTH,
Greetings
 
Following code fill a dataset with two datatables:

SqlAdapter dap = new SqlAdapter(“Select * From table_1; Select * From
table_2â€, CONNECTION_STRING);
DataSet ds = new DataSet();
dap.Fill(ds);

In your situation, if should fill two table in one fill command.

HTH

Elton Wang
 
Dear DJ,

You can fill the DataSet with 2 Tables like below

Create a connection string

const string dsn="server=(local);trusted_connection=yes;database=pubs";

Using(SqlConnection conn = new SqlConnection(dsn);

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Authors;SELECT * FROM
Publishers", conn);

or else

AS YOU SAID, YOU CAN ALSO CALL A SINGLE STORED PROCEDURE

SqlDataAdapter adapt = new SqlDataAdapter("YourStoredProcedure", conn);

then you can fill the DataSet like this
=======================
DataSet ds = new DataSet();

da.Fill(ds,"Authors");

//The Table Name "Authors" is assigned to the First (1st) DataTable. You
have to Specifically Assign the Table Name for the Second (2nd) DataTable
like below

ds.Tables[1].TableName="Publishers"

you can user the DataTable by refereing either their Ordinal Number or Name
of the Table like below

ds.Tables["Authors"]
or
ds.Tables[0]

AND / OR

ds.Tables["Publishers"]
or
ds.Tables[1]

For Anything & Everything, Please Let Me Know,

Bye
Venkat_KL
 
Sorry it took so long to reply, been enjoying the beaches of sunny Platje de
Palma, Mallorca in the Med for the last few days, much needed vacation.

Thanks for all the input, just what I needed, appreciate the help folks!

Jim Hawley
 
Back
Top