ADO.NET Cookbook

5.9. 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 COM interop or the Fill( ) method of the OLE DB data provider DataAdapter.

You’ll need a reference to the Primary Interop Assembly (PIA) for ADO provided in the file ADODB.DLL. Select adodb from the .NET tab in Visual Studio .NET’s Add Reference Dialog.

The sample code creates an ADO Recordset for the Orders table in Northwind. The Fill( ) method of the OleDbDataAdapter is used to load the Recordset into a DataTable.

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

Example 5-9. File: AdoRecordsetForm.cs

// Namespaces, variables, and constants
using System;
using System.Data;
using System.Data.OleDb;

//  . . . 

// Open an ADO connection.
ADODB.Connection conn = new ADODB.Connection( );
conn.Open("Provider = SQLOLEDB;Data Source = (local);" +
    "Initial Catalog = northwind","sa","",0);

// Create an ADO recordset.
ADODB.Recordset rs = new ADODB.Recordset( );
rs.Open("SELECT * FROM Orders", conn,
    ADODB.CursorTypeEnum.adOpenForwardOnly,
    ADODB.LockTypeEnum.adLockReadOnly, 0);

// Create and fill a dt from the ADO recordset.
DataTable dt = new DataTable("Orders");
(new OleDbDataAdapter( )).Fill(dt, rs);
conn.Close( );

// Bind the default view of the dt to the grid.
dataGrid.DataSource = dt.DefaultView;

Discussion

One of the overloads of the OLE DB .NET DataAdapter.Fill( ) method accepts an ADO Recordset or Record ...