June 4, 2015 08:12 by
Peter
In this post, I will tell you about how to return more than one table from store procedure in SQL Server 2014. Return a single table full of data from store procedure we are able to use a DataTable however to come back multiple tables from store procedure we've got to use DataSet. DataSet could be a bunch of DataTables. so the following code you'll 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 write the store procedure like the following code:
CREATE PROCEDURE sp_Test
@Email nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
select * from <tblName> whhere email = @Email
END
And then, It’s time to found how to get multiple table value in one DataSet. Write the code below:
CREATE PROCEDURE sp_Test
AS
BEGIN
SET NOCOUNT ON;
select * from Tbl1
select * from Tbl2
select * from Tbl3
select * from Tbl4
END
And here is the C# code:
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];
I hope it works for you!
HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.