Getting at 2 recordsets returned by SP? Help?

[Christopher Ambler]

I have a stored procedure that one of the DB guys wrote for me. It's going
to return two recordsets in its output.

Normally, I'd use ExecuteReader() and just loop inside of a while
(reader.Read()) loop.

In this case, I need to get the data into a couple of DataSet objects so I
can set properties on them and spew them out as XML (my final output has to
be in XML).

I'm very unsure as to the best way to do this. I don't even see how a
SqlDataReader can get me a DataSet, much less two of them, properly loaded
with the two recordsets coming back.

Or if this is even the best way to get XML out!

Some adult supervision would be very helpful about now

Christopher

 

[Joe Fallon]

Why not just load a dataset instead?
The SP will return 2 result sets which will create 2 tables.
Once you have the data in 2 datatables you can re-name the tables and output
your XML.
One way is to just save the dataset itself as XML.

 

[Hello]

-----Original Message-----
Why not just load a dataset instead?
The SP will return 2 result sets which will create 2 tables.
Once you have the data in 2 datatables you can re-name the tables and output
your XML.
One way is to just save the dataset itself as XML.

Hi,
But if I want to fill this two recordsets(rs) into the sp,
how can I do so? And I also want to use this resulted sp
use in .Net crystal report. How? Because I just cannot
get the which rs I was getting to fill the result in sp
and as a result the report/sp fill nothing.
Can someone help me? Thx,
Hello

 

[Mike Kiefer]

Configure a SqlDataAdapter to execute the stored procedure and then put the
results in a single DataSet. The resulting dataset will contain two tables.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_yourspname";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = *** your sql connection object here ***
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);
// if you need to add parameters to the SP, you can do it here
// cmd.Parameters.Add(...)
adapter.Fill(ds);

In the above example, ds.Table[0] will contain the results of the first
recordset and ds.Table[1] will contain the results of the second recordset
after the Fill() executes the query.

Mike