ADO.NET 3.5 Cookbook, 2nd Edition

6.15. Loading an ADO Recordset into a DataSet

Problem

You want to convert an ADO Recordset generated within a legacy application to a DataSet so that you can use it in a .NET application.

Solution

Use the Fill() method of the OLE DB data provider DataAdapter.

Add a reference to the adodb assembly from the .NET tab in the Add Reference dialog.

The solution creates an ADO Recordset for the HumanResources.Department table in AdventureWorks. The Fill() method of the OleDbDataAdapter is used to load the Recordset into a DataTable.

The C# code in Program.cs in the project LoadAdoRecordsetIntoDataSet is shown in Example 6-15.

Example 6-15. File: Program.cs for LoadAdoRecordsetIntoDataSet solution

using System;
using System.Data;
using System.Data.OleDb;

namespace LoadAdoRecordsetIntoDataSet
{
    class Program
    {
        static void Main(string[] args)
        {
            string adodbConnectString = "Provider=SQLOLEDB;" +
                "Data Source=(local);Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT * FROM Person.Contact";

            // Open an ADO connection.
            ADODB.Connection connection = new ADODB.Connection(  );
            connection.Open(adodbConnectString, "sa", "password", 0);

            // Create an ADO recordset. ADODB.Recordset rs = new ADODB.Recordset( ); rs.Open(sqlSelect, connection, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0); // Create and fill a dt from the ADO recordset. DataTable dt = new DataTable( ); (new OleDbDataAdapter( )).Fill(dt, rs); connection.Close( ); // Output 5 records from DataTable loaded ...