Post date: Jan 25, 2012 12:34:26 PM
Just a snippet of how to get multiple resultsets from a query or stored procedure.
Each select statement in the query will result in one DataTable in the Tables array of the DataSet object.
The example only show how to get multiple results from a query but the method can be used the same if you use stored procedures. Just do multiple selects in your stored procedure, they will end up on one DataTable each.
Also, for very advanced cases you might want to get the results back in a hierarchy. For that you can use the method I described here: https://sites.google.com/site/mrmbookmarks/msg/C---Dataset-from-XSD-schema-with-multiple-datatables-in-hierachy-Data-from-only-one-stored-procedure
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;namespace ConsoleApplication1 { class Program { static void Main(string[] args) { using (SqlConnection con = new SqlConnection("Data Source=Localhost;Initial Catalog=CustomerDB;Integrated Security=SSPI;")) { using(SqlCommand cmd = new SqlCommand(@"select top 2 * from Customer; Select top 3 * from Prospect", con)) { SqlDataAdapter ad = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); DataTable customers = ds.Tables[0]; DataTable prospects = ds.Tables[1]; } } } private static void PrintTable(DataTable table) { foreach (DataRow row in table.Rows) { for (int i = 0; i < row.Table.Columns.Count; i++) { Console.WriteLine(row.Field<object>(i)); } } } }}