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 C# 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 C# 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. 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 following code in the button click event. The sql query will take title as 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 statment
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 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();
}
The result of title search will be displayed as follows:
