ADO.NET 3.5 Cookbook, 2nd Edition

3.5. Retrieving a Result Set Using a DataTable or a DataSet

Problem

You want to fill a DataTable or DataSet with a result set.

Solution

Use the Fill() method of the DataAdapter object to load the result set into a DataTable or into a DataSet.

The solution fills both a DataTable and a DataSet with data from the Person.Contact table in AdventureWorks. Results are output to the console.

The C# code in Program.cs in the project RetrieveDataIntoDataTable is shown in Example 3-6.

Example 3-6. File: Program.cs for RetrieveDataIntoDataTable solution

using System; using System.Data; using System.Data.SqlClient; namespace RetrieveDataIntoDataTable { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 FirstName, LastName FROM Person.Contact"; // Create a data adapter SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); // Fill a DataTable using DataAdapter and output to console DataTable dt = new DataTable( ); da.Fill(dt); Console.WriteLine("---DataTable---"); foreach (DataRow row in dt.Rows) Console.WriteLine("{0} {1}", row[0], row["LastName"]); // Fill a DataSet using DataAdapter and output to console DataSet ds = new DataSet( ); da.Fill(ds, "Contact"); Console.WriteLine("\n---DataSet; DataTable count = {0}---", ds.Tables.Count); Console.WriteLine("[TableName = {0}]", ds.Tables[0].TableName); foreach (DataRow row in ds.Tables["Contact"].Rows) ...