Using the ADO Recordset in ADO.NET

In this example, you'll see how to import the ADO type library and use the ADO recordset to access a database. After that, you'll fill data to a DataSet from the ADO recordset using a DataAdapter.

Caution

There's a cost involved in terms of performance when you use ADO in managed code because of COM Interop. Using ADO in managed code doesn't give you the same performance you can get using and ADO.NET DataSet and DataReader. We use an ADO recordset only when we have no other option--for example, when you have a library that uses ADO and you need to use this library in managed applications.

To test this application, create a Windows application and drag a DataGrid control to the form from Toolbox. To add a reference to a COM type library, go to the Add Reference option from the Project menu and select the COM tab in the Add Reference dialog box. As you can see from the Figure 20-1, we selected Microsoft ActiveX Data Objects 2.7 Library. Click Select to add the selection to the Selected Components list. Now click OK.

Figure 20-1: Adding a reference to a COM library

This action adds the ADODB namespace to the project. You can see this namespace now listed in the project's namespaces (see Figure 20-2).

Figure 20-2: Namespace after adding ADODB namespace to the project

After adding a namespace reference to a project, its members are available for use in your project. You include this namespace in your application by calling Imports. You also should add a reference to the System.Data.OleDb namespace because you'll use the OleDb DataAdapter to fill a DataSet. So, add these two name-space references to the project with this:

 Imports ADODB Imports System.Data.OleDb 

Now you can use the ADO recordset and connection to access a database. As you can see from Listing 20-1, you create a Connection object and set the connection mode and cursor location. After that, you call the connection's Execute method to execute a SQL statement that returns the _Recordset object. The Fill method of the DataAdapter reads data from a recordset and fills data to a DataSet. As you can see from this code, you create a DataSet and DataAdapter and call the DataAdapter's Fill method. The Fill method takes three parameters: a DataSet, a recordset, and a DataSet name. Finally, you bind the DataSet to a DataGrid to fill data from the DataSet to the DataGrid.

Listing 20-1: Using an ADODB Namespace to Access a Database

 Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create SQL and Connection strings 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 = CType(ConnectModeEnum.adModeUnknown, Integer) conn.CursorLocation = CursorLocationEnum.adUseServer conn.Open(ConnectionString, "", "", connMode) Dim recAffected As Object = Nothing Dim cmdType As Integer = CType(CommandTypeEnum.adCmdText, Integer) Dim rs As _Recordset = conn.Execute(sql, recAffected, cmdType) ' Create dataset and data adapter 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 Listing 20-1 looks like Figure 20-3.

Figure 20-3: Displaying data from an ADO recordset to a DataGrid

Once you know to access ADO in managed code, you can do anything you want including adding, updating, and deleting data using an ADO recordset.