Wednesday, April 23, 2014

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 :)  

0 comments:

Post a Comment

Follow by Email

Popular Posts

Pageviews