Return more than one result set using T-SQL

Question

I am trying to imitate the functionality of the SQL Server Management Studio query analyzer part using .NET. The user enters an SQL script, and the program runs it. If it returns the result set, the program will load it into the datagrid and show it to the user.

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

Answer 1

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

When the specified query returns multiple results, the result set for each row query returned is placed in a separate table. Additional result sets are called by adding integral values ​​to the specified table name (for example, "Table", "Table 1", "Table 2", etc.).

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 break through various result sets using the sqlReader.NextResult() method. Then you can use sqlReader.Read() to get each individual record in this result set.

Answer 3

I think I could understand that. Sometimes writing a problem helps to understand it better, and then fresh ideas appear :)

I used the SqlDataAdapter.Fill (...) method to populate the DataTable. As it turned out, if you fill out an empty DataSet, it will automatically create a table for each returned result set. Thus, I can have several hidden datagrids at hand, and when the program detects that the populated DataSet has several tables, I simply load each datagrid with data from each DataTable into a DataSet.