CODEWITHSUNDEEP
stored-procedures
Neo
Neo

Reputation: 16219

Can we return a multiple result set in stored procedure and how to handle it in .net

Can we return a multiple result set in stored procedure and how to handle it in .net

??? if yes how?? if no why not ?? please explain

Upvotes: 0

Views: 3184

Answers (2)

Alireza Maddah
Alireza Maddah

Reputation: 5885

If you are retrieving data using DataReader, you can use NextResult method to iterate through multiple resultsets returned by stored procedure. More information can be found here. The following code is a simple example from MSDN:

static void RetrieveMultipleResults(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM dbo.Categories;" +
          "SELECT EmployeeID, LastName FROM dbo.Employees",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.HasRows)
        {
            Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                reader.GetName(1));

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
            reader.NextResult();
        }
    }
}

If you are populating a Dataset from a DataAdapter, note that if DataAdapter encounters multiple resultsets, it will create multiple DataTables (one for each resultset) and will add them to the resulting DataSet.

Upvotes: 3

iain
iain

Reputation: 1941

http://msdn.microsoft.com/en-us/library/yf1a7f4f.aspx has some good examples.

you need to change your connectionString to support it. MultipleActiveResultSets=True

http://msdn.microsoft.com/en-us/library/h32h3abf.aspx for config settings;

Upvotes: 0

Related Questions