Return more than one result set with T-SQL
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: I开发者_StackOverflow社区s 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.
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.
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).
I think I might have figured this out. Sometimes writing the problem out helps you understand it better, and then fresh ideas start to pop up :)
I had been using the SqlDataAdapter.Fill(...) method to fill a DataTable. As it turns out, if you fill an empty DataSet it will automatically create a table for each result set returned. So, I can have a few hidden datagrids on hand and when the program detects that the DataSet filled has multiple tables, then I'll just load up each datagrid with data from each DataTable in the DataSet.
精彩评论