[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

ASP.Net SQL Server Stored Procedure

Hi Experts,

Consider the Stored Procedure,

…………..
………….

Select * from Table1

Select Count(*) from Table2

GO

…………………..

Now, this Stored Procedure is returning 2 values ( DataTable and the Count), hence how could this be handled in the .Net Application.
I have read on some websites that, a stored Procedure is like a function and can only return one value/datatable.

Does the above the Stored Procedure return 2 datatables?

Please help me clarify, how could I use the above Stored Procedure in the front-end like C#/VB.Net

Thanks,

0
vad2319
Asked:
vad2319
1 Solution
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The questions listed above don't seem to address your issue.

The thing you need to know is depending on what you want to use to get the data back.

The bottom line is that when you have a stored procedure return 2 rowsets, which is what you are doing in your example, is that these are some of your options.

DataSet ds = new DataSet();

SqlConnection sql = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand("dbo.somestoredprocedure", sql);
SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(ds);
if(da.Tables.Count > 1)
{
    // Table 1 is ds.Tables[0] and Table 2 is ds.Tables[1].
}

If you use a DataReader then you can use the DataReader.NextResultSet() to get the next result into place, then you can Read just like any resultset.

Let me know if you need any more information.
Ben Miller
0
 
vad2319Author Commented:
Thank you Experts. Nauman, your answer was also very nice, but dbaduck's answer was what I was looking for.
Thanks again.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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