Using ADO RecordSet in ADO.NET

The code snippet used in this article is a VB version from my ADO.NET C# book. This book will be published in Jan 2002.  

Using ADO recordset in managed code using ADO.NET data providers is pretty simple. Visual studio .NET provides you to add reference to the COM libraries. In this article I'll show you how to access data using ADO recordset and fill a ADO.NET data provider from the recordset data.

This application is a Windows application. Create a Windows application and drag a data grid control to the form from toolbox.

The first step to add a reference to the ADO library. You can add reference to the ADO library by using Project->Add Reference menu item and then select COM tab as you can see in Figure 1.

Figure 1. Adding reference to ADO Library.

After adding this reference, you'll see the ADODB namespace is added to your project as you can see from Figure 2.

Figure 2. The ADODB namespace available in the project.

Now when you type Imports, ADODB namespace will be available in your namespaces list as you can see from Figure 3.

Figure 3. Importing ADODB namespace.

You also need to import the System.Data and the System.Data.OleDb since I'll use OldDb data provider to access an Access database. Import these namespaces in your project.

Imports System.Data
Imports System.Data.OleDb
Imports ADODB

Now write the following code on the form load constructor. As you can see from the following code, I simply create a Connection object, set its mode, and call Execute method to execute a SQL statement. The Execute method returns
a _Recordset object.

After that I simply create a dataset and data adapter and call Fill method of data adapter by passing recordset object as a parameter. The Fill method of data adapter fills data from a recordset to a data setr.

After that I bind data set object to a data grid.

Source Code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Create a connection string
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Northwind.mdb"
Dim sql As String = "SELECT CustomerId, CompanyName, ContactName From Customers"
' Create a Connection object and open it
Dim conn As Connection = New Connection
Dim connMode As Integer = ConnectModeEnum.adModeUnknown
conn.CursorLocation = CursorLocationEnum.adUseServer
conn.Open(ConnectionString, "", "", connMode)
Dim recAffected As Object
Dim cmdType As Integer = CommandTypeEnum.adCmdText
Dim rs As _Recordset = conn.Execute(sql)
' Create dataset and data adpater objects
Dim ds As DataSet = New DataSet("Recordset")
Dim da As OleDbDataAdapter = New OleDbDataAdapter
' Call data adapter's Fill method to fill data from ADO
' Recordset to the dataset
da.Fill(ds, rs, "Customers")
' Now use dataset
DataGrid1.DataSource = ds.DefaultViewManager
End Sub

The output of the program looks like Figure 4.

Figure 4. Data in a data grid accessed via ADO recordset.