C # Read multiple result sets in a stored procedure

C # Read multiple result sets in a stored procedure

-- SQL Server test environment setup:

 

Create database Test; goUSE [Test]GOif OBJECT_ID('Tab','U') is not nulldrop table TabgoCREATE TABLE [dbo].[Tab]([ID] [int] identity(1,1) NOT NULL,[name] [sysname] NOT NULL,)goif OBJECT_ID('Tab2','U') is not nulldrop table Tab2goCREATE TABLE [dbo].[Tab2]([ID] [int] IDENTITY(1,1) NOT NULL,[TabID] [int] NOT NULL,[Name2] [nvarchar](50) NULL) ON [PRIMARY]GO

-- Create a stored procedure:

 

 

if OBJECT_ID('P3','P') is not nulldrop procedure P3gocreate procedure P3asselect * from Tab;select * from Tab2;

-- Open Visual Studio-create a project-select console application]

 

 

#region Using Directivesusing System;using System.Data;using System.Data.SqlClient;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;#endregionnamespace TestReadingStoreProc{    class Program    {        static void Main(string[] args)        {            SqlConnection thisConnection = new SqlConnection(@Server=(Local);Database=Test;User ID=sa;Password=1);              SqlCommand thisCommand = thisConnection.CreateCommand();            thisCommand.CommandType = CommandType.StoredProcedure;            thisCommand.CommandText = P3;            SqlDataAdapter thisAdapter = new SqlDataAdapter(thisCommand);            DataSet thisDataSet = new DataSet();            thisAdapter.Fill(thisDataSet);            if (thisDataSet.Tables.Count > 0)            {                Console.WriteLine(Table Name:{0}Table Rows:{1},thisDataSet.Tables[0].TableName, thisDataSet.Tables[0].Rows.Count);                Console.WriteLine(Table Name:{0}Table Rows:{1}, thisDataSet.Tables[1].TableName, thisDataSet.Tables[1].Rows.Count);            }            thisConnection.Close();            Console.ReadKey();        }    }}

 

---- Press F5 to run the result: