Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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
0
bamapie
Asked:
bamapie
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Can't speak to the C# aspects of this quesion, but a common T-SQL reason is that if there are any messages that appear in your SSMS that are not sets, such as '(123 rows updated)', that a calling application like C# will interpret as an empty set.  

To prevent this from happening you'll have to execute statements such as SET NOCOUNT ON, SET ANSI_WARNINGS OFF, etc. in your T-SQL code before executing the code that returns the sets.

You may also want to consider changing your design so that multiple sets are not called by a single C# data set.
0
 
zephyr_hex (Megan)DeveloperCommented:
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

0
 
zephyr_hex (Megan)DeveloperCommented:
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.
0
 
bamapieAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now