ADODB Services in .NET Applications Using C#

Modernizing ADO Applications in C# with ADO.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 that have been written using ADODB? We should 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 C# language. The example details the data access using ADODB, fetching record sets, filling the ADO.NET dataset from the record set, and binding the same to data for user display.

Step 1. Create a new C# Windows Application in VS.NET, drag the textbox, label, button & data control from the toolbox.

Step 2. Set a reference to MS ADO objects 2.7 libraries using the Project->Add Reference menu item and then select the COM tab shown in the figure below:

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

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

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

Step 3. Include the following code in the button-click event. The SQL query will take the title as a parameter based on user input in the textbox.

private void btnSearchTitles_Click(object sender, System.EventArgs e)
{
    // Database connection string
    string DBConnection = "Provider=SQLOLEDB.1;uid=sa;password=password;database=Pubs;DataSource={local}";

    // SQL statement
    string SQL = "select title_id, title, type, price from Titles where title like '%" + txtTitles.Text.ToString() + "%' order by title";

    // Create ADODB Connection object
    ADODB.Connection Conn = new ADODB.Connection();

    // Create ADODB Recordset object
    ADODB.Recordset rs = new ADODB.Recordset();

    // Create OleDb Adapter object
    OleDbDataAdapter daTitles = new OleDbDataAdapter();

    // Finally, Dataset to store returned recordset
    DataSet dsTitles = new DataSet("Authors");

    // Open connection with the string as above
    Conn.Open(DBConnection, "", "", -1);

    // Execute the query specifying static cursor, 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 a parameter
    daTitles.Fill(dsTitles, rs, "Titles");

    // Bind datagrid to dataset 
    dataGridTitles.SetDataBinding(dsTitles, "Titles");

    // Close the connection
    Conn.Close();
}

The result of the title search will be displayed as follows.