Basic Introduction to ADO.NET in ASP.NET

Basic Introduction to ADO.NET in ASP.NET
This article provides information on programming with ADO.NET including DataSet,DataAdapter and DataReader

Introduction

ADO.NET was first introduced in version 1.0 of the .NET Framework and provided an extensive array of features to handle data either live,while connected to the database ,or when disconnected.. With the introduction of ADO.NET 2.0, you can have more features and easier programming model. Some of the newly added features include the capability to to bulk load large quantities of data from varieties of sources, to batch process updates to the database with fewer roundtrips back to the database server, to reuse the same live connection for multiple operations as well as to achieve asynchronous access to the database.

In this chapter,we will be focussing on connection with SQL Server or SQL Server Express. you can actually use ADO.NET to connect to Oracle or MySQL.In .NET 2 Framework, Microsoft only provides two set of classes to connect to 2 databases platform that are Oracle and SQL server. For MySQL, you can download the drivers specifically written for .NET from their official http://www.mysql.com/ website

Introducing SqlConnection, SqlCommand Object in ASP.NET

The SqlConnection class is specifically written to connect to MSSQL 7.0 or higher databases. You will need to pass a connection string to the SqlConnection object before it can connect to the database.
The connectionstring normally consists of the Server IPAddress, Database username and Database Password.

The SqlCommand object uses the SqlConnection object to execute Sql Queries. You can either use InLine SQL queries or stored procedures, or direct table access. If the SQL Query uses the SELECT clause, the result set it returns is usually stored in DataSet or DataReader object.

Using the DataReader object in ASP.NET

The DataReader object is a simple forward only and read only cursor. It requires a live connection with the data source and provides a very efficient way of looping and consuming all part of the result set. This is by far the fastest way of retrieving data if compared to retrieving data using DataAdapter. However there is a pro and cons of retrieving data by using DataReader or DataAdapter. If you need to read the data only once and you don't need to repeatedly access the data,then DataReader will be the best candidate. However if you need to move from one recordset to another recordset forward and backward, and the need to access the data repeatedly, then DataAdapter might be the best choice.

Below is the basic sample code on using SqlConnection, SqlCommand and SqlDataReader objects.

SqlConnection oConn;

SqlCommand oCmd;

SqlDataReader oDr;



try

{

 oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);

 oConn.Open();

 oCmd = oConn.CreateCommand();

 oCmd.CommandText = "Select Top 3 * From Customers";

 oCmd.CommandType = CommandType.Text;



 oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection);

 while(oDr.Read())

 {

     Response.Write (oDr["CustomerID"].ToString() + " " + oDr["ContactName"].ToString() + "

");

 }

}

finally {

oDr.Close();

oConn.Close();



}

Code Sample Binding DataReader with GridView in ASP.NET

<FORM ID="form1" RUNAT="server">

 <DIV>

     Bind with AutoGenerate Columns = true

     <ASP:GRIDVIEW ID="GridView1" RUNAT="server" AUTOGENERATECOLUMNS="true">