ADO.NET 3.5 Cookbook, 2nd Edition

6.4. Converting a DataReader to a DataSet

Problem

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

Solution

Use the Load() method of the DataSet class.

A DataReader is created returning the top five records in the HumanResources.Department and in the Person.Contact tables in AdventureWorks. The Load() method of the DataSet is used to load the DataReader into a DataSet. Records from the DataSet are output to the console together with some DataSet metadata.

The C# code in Program.cs in the CovertDataReaderToDataSet project is shown in Example 6-4.

Example 6-4. File: Program.cs for ConvertDataReaderToDataSet solution

using System; using System.Data; using System.Data.SqlClient; namespace ConvertDataReaderToDataSet { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 DepartmentID, Name, GroupName " + "FROM HumanResources.Department;" + "SELECT TOP 5 ContactID, FirstName, LastName " + "FROM Person.Contact;"; SqlDataReader dr; SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); connection.Open( ); dr = command.ExecuteReader( ); // Create the DataSet using the DataSet.Load( ) method DataSet ds = new DataSet( ); ds.Load(dr, LoadOption.OverwriteChanges, new string[] {"Department", "Contact"}); int tableCount = 0; foreach(DataTable dt in ds.Tables) { Console.WriteLine("Table ...