Logo: C# Computing
 
Web CsharpComputing.com

C# Tutorial Lesson 17: Reading a database with ADO.NET

ADO.NET is an extended version Active Data Object (ADO) whatever it means. Someday, I hope to see Passive Data Objects (PDO) where computer does all the work and a programmer simply checks that the hard drive is alive during code generation. ADO.NET is a database connectivity platform that provides  a three tier support for database connectivity. First tier are Oracle and SQL Server that have their own OLEDB providers with managed extensions. Second tier is unmanaged OLEDB providers, and the third tier is ODBC providers. Of course, even within the tier, there is a considerable difference in performance, connection quality and features depending on the database vendor.

Both Microsoft and Oracle extended considerable efforts to optimize these providers. Microsoft came up with a benchmark that shows that the managed provider for SQL Server 2000 outperforms native OLEDB provider by a factor of two. This Managed OLEDB benchmark is important, because it "facilitates" upgrades from SQL Server 7.0 to SQL Server 2000.  The truth of the matter is that Oracle, Microsoft and Sun are constantly coming up with benchmarks that show that their product is faster than competitor's and it is a good idea to verify any benchmark before placing a big purchase order.

ADO.NET is a 4+1 platform. It has four objects Connection, Command, Data Adapter and Data Set supporting a disconnected architecture, and Data Reader that requires a persistent connection.

In addition, ADO.NET supports XSLT 1.0, XPATH 1.0 and XML DOM 1.0. ADO.NET 2.0 extends XML support with XQuery 1.0.

Most of ADO.NET APIs are part of System.Data namespace. Provider specific APIs are in System.Data.Odbc, System.Data.OleDb, System.Data.SqlClient, Oracle.DataAccess.Client etc.

In this example, I have created Bank customer data table and use a cursor based DataReader to read data row after row.

 

ADO.NET DataGrid

I store table Bank_customer_data in a database db2.mdb located in the directory C\Temp. Firstly, I read a name and the amount of assets of all customers of the bank. To do that, I use the following SQL command  SELECT Name, Assets FROM Bank_customer_data. To retreive the data, I create an instance of OleDbCommand class. OleDbCommand class takes care of passing queries to the database. OleDbCommand instance can be created by passing the address of the data source as well as the name of the table as string parameters. I also need an instance of OleDbConnection which represents an open connection to the database. I use OleDbConnection constructor which takes a string containing the address of the database file. After an instance of the OleDbConnection is created, I connect to the database using Open method. Opening  already open connection or closing already closed connection results in an Invalid Operation Exception.  An instance of OleDbCommand is associated with the instance of an open OleDbConnection through Connection property.

The simplest and fastest way to execute a query and get results back row by row, is to use OleDbDataReader class. OleDbDataReader has a private constructor and cannot be instantiated with the new keyword. Instead, an instance of OleDbDataReader is created by calling Execute method of OleDbCommand object. The actual reading of data is done line by line with  Read method. Table data is not typed when it is returned by the Data Reader. Always add exception handling code when working with ADO.NET. At the end of all connection sessions, don't forget to close your DB connection.  When you do forget to close a DB connection, it is close when your class is garbage collected, which may take hours for an ASP.NET application.

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

public class Test{
    public static void Main()
    {
    string source = "Provider=Microsoft.JET.OLEDB.4.0;" + "data     source=""C:\\Winnt\\Profiles\\Administrator\\Personal\\db2.mdb
    string command="SELECT Name, Assets FROM Bank_customer_data";
    OleDbCommand mCommand = new OleDbCommand(); 
    OleDbConnection mConnection=new OleDbConnection(source);
    mConnection.Open();
    mCommand.ActiveConnection=mConnection; 
    mCommand.CommandText=command;
    OleDbDataReader mReader;
    mCommand.Execute(out mReader); 
    // Use Read to  read data line by line.
    while (mReader.Read()) 
        { //The data is extracted with the methods GetString and GetInt32
        Console.WriteLine(mReader.GetString(0) + ":" + mReader.GetInt32(1));
        }
    // Close the Reader when done. 
    mReader.Close();
    // Close the connection when done. 
    mConnection.Close(); 
    }
}