How to Return Multiple Result Sets with SQLcommand

How do I return multiple result sets with SqlCommand?

See SqlDataReader.NextResult (an SqlDataReader is returned from calling SqlCommand.ExecuteReader):

Advances the data reader to the next result [set], when reading the results of batch Transact-SQL statements.

Example:

string commandText = @"SELECT Id, ContactId
FROM dbo.Subscriptions;

SELECT Id, [Name]
FROM dbo.Contacts;";

List<Subscription> subscriptions = new List<Subscription>();
List<Contact> contacts = new List<Contact>();

using (SqlConnection dbConnection = new SqlConnection(@"Data Source=server;Database=database;Integrated Security=true;"))
{
    dbConnection.Open();
    using (SqlCommand dbCommand = dbConnection.CreateCommand())
    {
        dbCommand.CommandText = commandText;
        using(SqlDataReader reader = dbCommand.ExecuteReader())
        {
            while(reader.Read())
            {
                subscriptions.Add(new Subscription()
                {
                    Id = (int)reader["Id"],
                    ContactId = (int)reader["ContactId"]
                });
            }

            // this advances to the next resultset 
            reader.NextResult();

            while(reader.Read())
            {
                contacts.Add(new Contact()
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"]
                });
            }
        }
    }
}

Other examples:

• C# Multiple Result Sets
• Executing a Query That Returns Multiple Result Sets with SqlDataReader : SqlCommand Select « ADO.Net « C# / CSharp Tutorial

Return multiple recordsets from stored proc in C#

SqlConnection con=new SqlConnection("YourConnection String");
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("name of your Stored Procedure", con);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@SuperID", id);//if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

After this you can take advantage of different (7) recordsets using

ds.Tables[0]
ds.Tables[1]
ds.Tables[2]
ds.Tables[3]
ds.Tables[4]
ds.Tables[5]
ds.Tables[6]

Is it possible to return multiple resultsets from a stored procedure?

You could use SqlDataAdapter.Fill to load a DataSet with multiple result sets, containing a table for each result returned by the stored procedure:

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("myproc", connection) {  CommandType = CommandType.StoredProcedure })
using (var dataAdapter = new SqlDataAdapter(command))
{
    command.Parameters.Add("@id", SqlDbType.Int).Value = id;
    dataAdapter.Fill(dataSet);
}

T-SQL Return Multiple Result Sets

You should use CURSOR in you stored procedure like this:

DECLARE @foo VARCHAR(XXX)

DECLARE Curs CURSOR FAST_FORWARD READ_ONLY FOR
SELECT 
    foo 
FROM sometable 
WHERE 
    somecolumn = @parameter

OPEN Curs

FETCH NEXT FROM Curs INTO @foo

WHILE @@FETCH_STATUS = 0
BEGIN

    -- here you should put actual query
    SELECT
    *
    FROM dbo.SomeTable
    WHERE
        foo = @foo

FETCH NEXT FROM Curs INTO @foo

END

CLOSE Curs
DEALLOCATE Curs

and then read cursor results using code like:

var sqlCommand = new SqlCommand("spYourStoredProcedure", connection)
sqlCommand.CommandType = CommandType.StoredProcedure;
var reader = sqlCommand.ExecuteReader();

do 
{
    while (reader.Read())
    {
        // DO SOMETHING
    }
} while(reader.NextResult());

Get multiple result set with stored procedure using Entity Framework calculated column

SQL Server and the .NET Framework are based on different type systems, that is why while reading data on .NET side you need to use data mapping carefully.

Here you can find list of type mapping or type equivalents between SQL Server and .NET Framework https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

In your question, it wasn't clear what was the type of Balance column on SQL side or what type was coming from your stored procedure. In your code, you are trying to read Balance column as Double, that is where you are getting Specified cast is not valid in sql server. According to Microsoft's document(link shared above), it is stated that column type must be Float on SQL Server side so that on .NET side you can read it as Double type.

As you are getting type cast error, I guess you are returning non Float value from database. As a solution, you can update your stored procedure to cast your Balance to Float type.

Sample usage: SELECT CAST(Balance AS FLOAT)

Finally, just as a suggestion, wrap your Connection and Reader objects with using statement so that at the end of your execution, they are disposed, not to leave open connections, which eventually can result in all pooled connections are in use exception.

Return multiple datasets from sql server stored procedure

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

Execute Stored Procedure with multiple result sets

Solution Overview

I made 2 Experiments on that issue, the first experiments showed that in case of stored procedures with no parameters, nothing changed in SQL Server 2016 and SSIS 2016, the first Result Set is returned and others are ignored.

The second experiment showed that when using parameters, this will throw an exception, so you have to define metadata using WITH RESULT SETS option, then remove this option.

---

Detailed Solution

Experiment 1

The following experiment are made using SQL Server 2016 and Visual Studio 2015 with SSDT 2016

1. First i created this stored procedure

   CREATE PROCEDURE sp_Test
   
   AS
   BEGIN
   
   SET NOCOUNT ON;
   
   SELECT TOP 10 PersonType,NameStyle,Title 
     FROM [AdventureWorks2016CTP3].[Person].[Person]
   
   SELECT  TOP 10 PersonType,Firstname,Lastname
     FROM [AdventureWorks2016CTP3].[Person].[Person_json]
   END
   GO
   

2. Then i added a Data flow task to SSIS package
3. Added an OLEDB Source, Recordset destination
4. In OLEDB source i select the Data access mode to SQL command

5. an use the following commnad

   EXEC sp_Test
   

6. When clicking on Columns Tab it shows the first ResultSet structure

7. And we i executed the package it runs succesfully

Experiment 2

I changed the stored procedures to the following:

ALTER PROCEDURE [dbo].[sp_Test]

    @param1 varchar(10),
    @param2 varchar(10),
    @param3 varchar(10)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT TOP 10 PersonType,NameStyle,Title ,@param2 as 'Param'
  FROM [AdventureWorks2016CTP3].[Person].[Person]

    SELECT  TOP 10 PersonType,Firstname,Lastname,@param3 as 'Param'
  FROM [AdventureWorks2016CTP3].[Person].[Person_json]
END

And i used the following SQL Command in the OLEDB Source:

EXEC sp_Test ?,?,?

WITH RESULT SETS (
(
    PersonType NVarchar(10),
    NameStyle NVarchar(10),
    Title  NVarchar(10),
    Param Varchar(10)
)
)

And i mapped the parameters correctly.

When running the package it throws the following exception.

[OLE DB Source 2] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.".

After that i tried to remove the With RESULT SETS option, so the command is :

EXEC sp_Test ?,?,?

I tried to execute the package again, so it is executed with no errors.

Conclusion

Try to use the WITH RESULT SETs option to define the OLEDB Source metadata, after that the metadata is defined, just remove this option and run the package, so it will just take the first Result Set succesfully.

---

Related Topics