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.
Example 17 OpenSqlConnection.aspx

<%@ Import Namespace=”System.Data.SqlClient” %>
<Script Runat=”Server”>Sub Page_Load

Dim conNorthwind As SqlConnection

conNorthwind=NewSqlConnection(“Server=localhost;uid=sa;pwd=secret;database= Northwind” )

conPubs. Open ()

End Sub

</Script>
Connection has been opened!

Firstly the System.Data.OleDb namespace is imported at the top of the page rather in the System. Data.SqlClient namespace.

Secondly,Instead of SQL classes OleDb classes are used in all the statements.

After you finish displaying the records from a DataReader, be sure to explicitly close it (using the Close () method).

Retrieving a Single Database Record

The developers generally need a single record from a database. Therefore it is very important to know how to retrieve it. For example, given a username you might have to look up someone’s password or retrieve a phone number for John from a database table.

Another common situation which requires retrieval of single database record is aggre­gate functions like MAX (), MIN (), COUNT () etc.

The previous section dealt with the use of a DataReader representing the results of a database query. But if there is a need, to retrieve a single result from a query, the ExecuteScalar () method is more efficient than the ExecuteReader () method, as it requires less coding.

The following example counts the no. of records from Employees table. We use the ExecuteScalar () method to retrieve and display the value of the SQL Count (*) aggregate function.

Example 21 SqlExecuteScalarCount.aspx

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

<Script Runat=”Server”>

Sub Page_Load

Dim conNorthwind As SqlConnection

Dim cmdSelectCount As SqlCommand

conNorthwind = New SqlConnection( “Server=localhost;UID=sa;PWD=secret;database=Northwind” )

conNorthwind.Open()

cmdSelectCount = New SqlCommand( “Select Count(*) From Employees”, conNorthwind )

lblEmp.Text = cmdSelectCount.ExecuteScalar()

conNorthwind.Close()

End Sub

</Script>

<html>

<body>

There are

<asp:Label

ID=”lblEmp”

Runat=”Server” />

Employees in this Table

</body>

</html>