Thread: How to Get Multiple Tables From Stored Procedure - MS SQL, ASP .NET

How to Get Multiple Tables From Stored Procedure - MS SQL, ASP .NET

Lets say you have a stored procedure that makes multiple select statements to return multiple sets of data. And you like to get them on your Asp .Net code. Take this stored procedure for example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[TestSproc]

AS
BEGIN

SET NOCOUNT ON;

Select top 10 * from MyTable1
Select top 10 * from MyTable2

END


On your Asp .Net you can code like this to retrieve multiple tables from the stored procedure. This code is in C#.

DataTable table1=new DataTable();
DataTable table2=new DataTable();

string connectionString="------------------"; //set the connectionString
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionStri ng);
connection.Open();

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = "TestSproc";
command.CommandType = CommandType.StoredProcedure;
//set sproc params

SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables.Count > 0)
table1 = dataSet.Tables[0];
if (dataSet.Tables.Count > 1)
table2 = dataSet.Tables[1];

connection.Close();

i think you get the multitable from the stroed procdure..

ok and thanks for this.....