Return more than one result set with T-SQL

Question

I'm trying to mimic the functionality of the Query Analyzer piece of the SQL Server Management Studio using .NET. The user will input a SQL script, and the program will run it. If it returns a result set, the program will load that up into a datagrid and show the user.

My question is: Is there a way to return more than one result set from a single script? I know Query Analyzer runs this and loads up multiple datagrids if several result sets are returned, but as far as I know when you try to do this with SqlDataAdapter.Fill(...), it only returns the last result set in the script.

Answer 1

You can call SqlDataAdapter.Fill passing in a DataSet. Each query result will populate a table in the DataSet:

When the query specified returns multiple results, the result set for each row returning query is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on).

Answer 2

This will show you how to return multiple result sets: http://vb.net-informations.com/ado.net-dataproviders/ado.net-multiple-result-sets.htm

You loop through the different result sets using the sqlReader.NextResult() method. You can then use sqlReader.Read() to get each individual record in that result set.