Return multiple datasets from sql server stored procedure

Question

I need to return through Web Api a Base64 XML output based upon calling a stored procedures which runs 5 different queries.

Stored procedure is not written ( I need to write it ) but there are 5 queries in which the data is completely different tables and columns etc... so I am wondering if this is even possible?

I know in Oracle you can return multiple cursors, but with SQL Server , can I return into asp.net 4.5 ( mvc c# / Ado.net) multiple datasets or collections? Any examples of this?

Example of just ONE of the queries

   -- Content Tab
SELECT -- vTC.[TemplateId]  
  t.Name as "Client Name and Document"  ,vTC.[SectionName] ,vTC.[ContentId] ,vTC.[ContentName]
  ,vTC.[ContentDescription],vTC.[ContentValue] ,CAL.ContentValue as "Spanish Content" , iif(S.IsClientSection = 1, 'Global Section','Template Section') as "Global or Template Section"
  ,DT.Title as DataType ,iif(vTC.IsRequired = 1, 'Yes', 'No') as "Required" ,vTC.[DisplayType] 
FROM [dbo].[vwTemplateContent] vTC
 left join dbo.Template t on vTC.TemplateId = t.TemplateId
  left join dbo.DataType DT on vTC.DataTypeId = dt.datatypeid
   left join dbo.Section S on S.SectionID = vTC.SectionID
   left join [dbo].[ContentAlternateLanguage] CAL on vTC.ContentId =    CAL.ContentID
  where vTC.templateid in (1) 
  order by DisplayOrder

Solution 1

If you are going to get multiple tables then you have to write multiple select statements into your stored procedure like below:

CREATE PROCEDURE SPName
(
/*Declare your parameters*/
@parm1 dataType
)
AS
BEGIN
/*Write your select statements below*/
-- SELECT * FROM tblName
-- SELECT * FROM tblName2

END

You have to fill these records into your DataSet, DataSet supports multiple table into ADO.net.

Please refer below code to fill your DataSet:

SqlConnection con=new SqlConnection("YourConnection String");
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("SPName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@parm1", id);//if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

After this you can take advantage of different multiple recordsets using

ds.Tables[0]
ds.Tables[1]
..

Hope it will helps you

Thanks

Solution 2

Here's a basic example:

SQL Proc:

CREATE PROCEDURE usp_getStudentsAndClasses
    @ClassName      varchar(50)
,   @IsActive       bit
AS
BEGIN
    --First select is first table
    SELECT  *
    FROM    Students

    --Second select is second table, etc.
    SELECT  *
    FROM    Classes

    --Third table...
    --Can be more complex, as long as there is a result set
    SELECT  s.FirstName
    ,       s.LastName
    FROM    Students            s
    JOIN    StudentSeating      ss
    ON      s.StudentID     =   ss.StudentID
    JOIN    Classes             c
    ON      c.ClassID       =   ss.ClassID
    WHERE   s.IsActive      =   @IsActive
    AND     c.Name          =   @ClassName

END

C# function:

    public DataSet GetDataSet(SqlConnection connection, string storedProcName, params SqlParameter[] parameters)
    {
        var command = new SqlCommand(storedProcName, connection) { CommandType = CommandType.StoredProcedure };
        command.Parameters.AddRange(parameters);

        var result = new DataSet();
        var dataAdapter = new SqlDataAdapter(command);
        dataAdapter.Fill(result);

        return result;
    }

C# usage:

        var connection = new SqlConnection("Your_connection_string");
        var parameters = new SqlParameter[]
        {
            new SqlParameter("ClassName", "Robotics"),       //example of string value
            new SqlParameter("IsActive", true)               //example of numeric value
        };
        var dataSet = GetDataSet(connection, "usp_getStudentsAndClasses", parameters);

        var firstTable = dataSet?.Tables?[0];   //use as any other data table...

Notice, it is almost the same code as you would use for a single-table stored procedure, except the data type returned is a DataSet, not a DataTable. A DataSet contains a DataTableCollection More info on MSDN

Solution 3

Yes, It is possible. You just need to write your select queries and you will get the data in a DataSet. If you have a single select query, you will get DataTable and if you have number of select queries (Say 5), then you will get a DataSet that has 5 DataTables. It is so simple. Just write your procedure and have fun.

Edit: Example of Stored Procedure (pseudo code) is given below:

create Proc Name_Of_Proc
(
   @FirstParam DataType,
   @SecondParam DataType
)
AS
Begin
   Select statement 1
   Select statement 2 
   Select statement 3 --and so on upto n.
end

You need to do this in your database. After doing this, you need to execute this procedure from c# by using ADO.NET. You need to use SqlConnection SqlCommand and SqlDataReader object to do this. You can search on google or SO itself for more examples. One such link on SO is How to execute Stored procedure in c#