O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required