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开发者_JAVA技巧

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.

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;