asked on

multiple from recordsets SQL->C# app

I have a SQL stored proc that returns multiple recordsets from multiple queries. When I run it in SQL Mgmt Studio, it works great. Currently I get back 3 tables with a few dozen records in each. Correct results.

When I call this from C# code, using SqlAdapter.Fill() into a DataSet, I do indeed get 3 tables in my DataSet. It's just that they're totally empty.

I'm using the same parameters in each call.

So let me just ask this, right off the bat: Is reading these multiple recordsets into a DataSet not going to ever return results? If it should be working, is there perhaps a known wrinkle regarding multiple recordsets somewhere that I'm missing?

Thanks

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zephyr_hex (Megan)

Here's the pattern I use for this kind of scenario:

In my SQL stored procedure, I return 3 datasets using select statements:

SELECT col FROM table1;
SELECT col FROM table2;
SELECT col FROM table3;

Open in new window

Then, in C#:

Assign parameters and call the stored procedure with execute():

oDs.Execute();
if (oDs.ErrorString != "")
{
  //handle error
}
//iterate over the datasets:
foreach (DataSet ds in oDs.DataSet)
{
  //put a breakpoint here and inspect
  var table = ds.Tables[0];
 }

Open in new window

zephyr_hex (Megan)

You can test Jim's theory by only returning 1 dataset from your stored procedure and see if it works. If it doesn't, then the issue is not with multiple datasets, but something else, like not setting SET NOCOUNT ON in your stored procedure.

bamapie

ASKER

Thanks!

multiple from recordsets SQL-&gt;C# app

multiple from recordsets SQL->C# app