ADODB Services in .NET Applications using VB.NET

With the advent of ADO.NET, the .NET Framework lets you work with your data in a paradigm shift. What about those legacy applications which have been written using ADODB? We should approach the approach the common problem of re-using or revitalizing existing ADO code. Any middle-tier made of data access components that internally use ADO and return recordsets to ASP pages can be ported to .NET more easily and progressively.

This article walks you through the usage of ADODB services in .NET application using VB.NET language. The example details the data access using ADODB, fetching recordset, filling ADO.NET dataset from the recordset and binding the same to datagrid for user display.

Step 1:

Create a new VB Projects Windows Application in VS.NET, drag textbox, label, button & datagrid control from the toolbox.

Step 2:

Set reference to MS ADO objects 2.7 library using Project->Add Reference menu item and then select COM tab shown in figure below:

Once you do this, ADO objects are available to .NET code as native classes, thus you should be able to see ADODB namespace.

We need to import the System.Data & System.Data.OleDb

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

Step 3:

Include following code in the button click event. The sql query will take title as parameter based on user input in the textbox.

Private Sub btnSearchTitles_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' database connection string
Dim DBConnection As String = Provider = SQLOLEDB;uid = sa;password = password;database = Pubs;DataSource = local
' sql statment
Dim SQL As String = "select title_id,title,type,price from Titles where title like '%" + txtTitles.Text.ToString() + "%' order by title" '
'create ADODB Connection object
Dim Conn As New ADODB.Connection
'create ADODB Recordset object
Dim rs As New ADODB.Recordset
'create OleDb Adapter object
Dim daTitles As New OleDbDataAdapter
' finally Dataset to store returned recordset
Dim dsTitles As New DataSet("Authors")
'open connection with the string as above
Conn.Open(DBConnection, "", "", -1)
'execute the query specifying static sursor, batch optimistic locking
rs.Open(SQL, DBConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
'use the overloaded version of Fill method which takes recordset as parameter
daTitles.Fill(dsTitles, rs, "Tiltes")
'bind datagrid to dataset
dataGridTitles.SetDataBinding(dsTitles, "Tiltes")
'close the connection
Conn.Close()
End Sub 'btnSearchTitles_Click

The result of title search will be displayed as follows: