How to return more than one table from store procedure

Return a single table full of data from store procedure we can use a DataTable but to return multiple tables from store procedure we have to use DataSet. DataSet is a bunch of DataTables. So the following code you can use to return single table.

SqlCommand cmd = new SqlCommand("sp_Login", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;

cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;

cmd.Connection = con;

if (con.State == ConnectionState.Closed)

{

   con.Open();

}

SqlDataReader dr = cmd.ExecuteReader();

DataTable dt = new DataTable();

dt.Load(dr);

return dt;

Now the store procedure is something like this one.

CREATE PROCEDURE sp_Test

    @Email nvarchar(255)

AS

BEGIN

    SET NOCOUNT ON;

    select * from <tblName> whhere email = @Email

END

Its time to found how to get multiple table value in one DataSet.

Suppose the store procedure is like ...

CREATE PROCEDURE sp_Test

AS

BEGIN

    SET NOCOUNT ON;

    select * from Tbl1

    select * from Tbl2

    select * from Tbl3

    select * from Tbl4

END

And the C# code will be...

SqlCommand cmd = new SqlCommand("sp_test", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

if (con.State == ConnectionState.Closed)

{

    con.Open();

}

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

sda.Fill(ds);

con.Close();

// Retrieving total stored tables from DataSet.              

DataTable dt1 = ds.Tables[0];

DataTable dt1 = ds.Tables[1];

DataTable dt1 = ds.Tables[2];

DataTable dt1 = ds.Tables[3];

So I hope you have found the solution of your problem. So enjoy the coding :)