Writing ADO.NET Applications

This article explains the necessary steps required to write a database application using ADO.NET.

 

Choosing a .NET Data Provider

 

The first step is to decide what .NET data provider you will use in your application. There are many data providers that work with multiple data sources, including OleDb, Sql, and ODBC. You use OleDb data providers to access the OLE-DB data source. SQL data providers work with ODBC data sources. You can also use more than one data provider to access the same database. For example, you can use OleDb and ODBC data providers to work with Microsoft Access databases.

 

I'm going to show you sample applications. The application uses Ole Db data providers to data and access 2000 database.

 

Adding namespace references

 

The second step is to add references to the assembly and includes the namespaces in your project. You can add references to the project using the project > Add reference option. The figure shows you how to add a reference to the System.Data.dll assembly. If you are compiling your program from the command line, you can add a reference to the assembly from the command line. I'll discuss this process in the following example.

 

 

Figure: Adding a reference to the System.Data.dll assembly

 

After adding a reference to the assembly, you need to include namespaces in your project by using the using namespace. The following code shows how to include the System, System.Data, System.Data.Common and System.Data.OleDb namespaces:

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

Note: If you are using Sql or ODBC data providers, you need to include the System.Data.Sql or Microsoft.Data.Odbc namespaces.

 

Establishing a connection

 

Now the next step is to create a Connection object. You create a ConnectionObject using the data provider's Connection class. In the following code, you use the OleDbConnection class to create a connection with an Access 2000 database. The OleDbConnection constructor takes one parameter of a string type, connectionString, which has two parts. First, it has a provider, and second, it has the path of the data source. As you can see from the following code, I used Microsoft.Jet.OLEDB.4.0 provider and the northwind.mdb data source:

string connectionString = "Provider= Microsoft.JET.OLEDB.4.0; Data source =c:\\ Northwind.mdb";
OleDbConnection conn = new OleDbConnection (connectionString);

Note: In this sample, the northwind.mdb database path is "C:\\northwind.mdb". You can use any database you want. You just need to change the path and name of the database and the table names you are using in SQL statements to access the data.

 

Creating a Command or Data Adapter object

 

The next step is to create a DataAdapter or Command object. You create a Command object by using OleDbCommand class.

 

The OleDbCommand constructor takes two parameters. The first is the SQL query and the second is the Connection object. You create a SELECT SQL query from the Customers table in the Northwind database. The following code shows how to create a Command object:

// SELECT SQL Query
string sql = "SELECT CustomerID, ContactTitle FROM Customers";

Filling Data to a Dataset or Data Reader Object

 

The next step is to open the Connection by calling the Open method of the Connection object and reading data from the Command object. The ExecuteReader method, OleDbCommand returns data in an OleDbReader object. A DataReader object reads fast and forwards only cached data. The following lines of code show this:

conn.Open();
OleDbDataReader reader;
reader = cmd.ExecuteReader();

Displaying Data

 

The Next step is to do some operation on the data. In this example, you'll display data on the console. The Read method of OleDbDataReader reads data. The DataReader class has Getxxx methods, which return different types of data. The Getxxx methods take an index of the field you want to read data of. In the following code, you read data from two fields of the Customers table, whose indexes are 0 and 1:

while (reader.Read()) {
    Console.Write(reader.GetString(0).ToString() + " , ");
    Console.Write(reader.GetString(1).ToString() + " ,");
    Console.WriteLine("");
}

Closing the Connection

 

The last step is to close the reader and connection objects by calling their Close methods:

reader.Close();
conn.Close();

Conclusion

 

Hope this article would have helped you in understanding the basic steps required to write an ADO.NET Application. See my other articles on the website on ADO.NET.