ADO.NET provides an API for accessing database systems according to the programs. ADO.NET was created for the .NET Framework and it can be said that they are next generation of Active Data Objects (ADO). The .NET Framework contains several namespaces and classes, which are devoted to database accesses. Microsoft has created separate namespaces that are optimized for working with different data providers (different types of databases). Before starting this topic, the user should have good knowledge of Databases such as MS Access and SQL Server or Oracle.

The following data provides specific namespaces that are included with ADO.NET:

System.Data.SqlClient

Contains classes for connecting to Microsoft SQL Server version 7.0 or higher

System.Data.OleDb

Contains classes for connecting to a data source that has an OLE DB provider

System.Data.Odbc

Contains classes for connecting to a data source that has an ODBC driver

System.Data.OracleClient

Contains classes for connecting to an Oracle database server

The System. Data. SqlClient namespace includes the following three classes:

• SqlConnection
• SqlCommand
• SqlDataReader

You will use these classes very often when you plan to build your ASP.NET application with Microsoft SQL Server (version 7.0 or higher). These classes enable you to execute SQL state­ments and retrieve data quickly from a database query.

The SqlConnection class represents an open connection to a Microsoft SQL Server data­base.

The SqlCommand class represents a SQL statement or stored procedure.

Finally, the SqlDataReader class represents the results from a database query.

The next section of this chapter goes into the details of using each of these classes.

The first group of classes, from the System.Data.SqlClient namespace, works only with Microsoft SQL Server. If you want to work with another type of database, such as an Access or Oracle database, you have to use the classes from one of the other data provider-specific namespaces.

For example, if you want to connect to a Microsoft Access database, then you'll need to use the classes from the System. Data. OleDb namespace. The System.Data.OleDb name­space includes the following classes:

• OleDbConnection
• OleDbCommand
• OleDbDataReader

It has to be noted that these classes have the same names as the ones in the previous group, except that these class names start with OleDb rather than SQL.

The OleDbConnection class represents an open database connection to a database.

The OleDbCommand class represents a SQL statement or stored procedure.

The OleDbReader class represents the results from a database query.

Performing Common Database Tasks

In the following sections, the method of performing common database tasks using ADO.NET will be discussed. First, you will create and open a database connection.

Second, you will retrieve and display database records, add new database records, update existing database records, and delete database records.

Third, you will perform all these tasks using classes from both the System.Data.SqlClient and System.Data.OleDb namespaces.

When you work with SQL Server, you need to import the System.Data.SqlClient namespace by adding the follow­ing page directive at the top of your ASP.NET page:

<%@ Import Namespace="System.Data.SqlClient" %>

When working with other databases, such as Microsoft Access or Oracle databases, you need to import the System.Data.OleDb namespace by using the following page directive:

<% @ Import Namespace="System.Data.OleDb" %>

How to Open a Database Connection ?

The following Example would show how to create and open a connection for a Microsoft SQL Server databases.

Connection has been opened!

ExpertRating ASP.NET Certification - Get Certified Now!!