ADO.NET Cookbook

5.3. Converting a DataReader to a DataSet

Problem

You need to transfer data from a DataReader to a DataSet.

Solution

Create a DataTable schema in the destination DataSet using the schema information returned by the GetSchemaTable( ) method of the DataReader. Then, use the GetData( ) method of the DataReader to load each row of data into an array of objects, and add it to the DataTable using the Add( ) method of the contained DataRowCollection.

The C# code is shown in Example 5-3.

Example 5-3. File: ConvertDataReaderToDataSetForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Collections; using System.Data; using System.Data.SqlClient; // Table name constants private const String ORDERS_TABLE = "Orders"; private const String ORDERDETAILS_TABLE = "OrderDetails"; // Relation name constants private const String ORDERS_ORDERDETAILS_RELATION = "Orders_OrderDetails_Relation"; // Field name constants private const String ORDERID_FIELD = "OrderID"; // . . . DataSet ds = new DataSet( ); // SQL for batch query String sqlText = "SELECT * FROM Orders; " + "SELECT * FROM [Order Details];"; // Create connection and command. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand(sqlText, conn); // Open DataReader with KeyInfo. conn.Open( ); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo); // Loop over the result sets of the DataReader. do { DataTable schemaTable ...