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() + " <BR>");
}
}
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">
</ASP:GRIDVIEW>
<BR />
<BR />
<BR />
Bind with AutoGenerate Columns = false
<ASP:GRIDVIEW ID="GridView2" RUNAT="server" AUTOGENERATECOLUMNS="false">
<COLUMNS>
<ASP:BOUNDFIELD DATAFIELD ="CustomerID" HEADERTEXT="CustomerID" />
<ASP:BOUNDFIELD DATAFIELD ="CompanyName" HEADERTEXT="CompanyName"/>
<ASP:BOUNDFIELD DATAFIELD ="ContactName" HEADERTEXT="ContactName"/>
</COLUMNS>
</ASP:GRIDVIEW>
</DIV>
</FORM>C# Code Behind SqlConnection oConn = null;
SqlCommand oCmd = null;
SqlDataReader oDr = null;try
{
oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"]);
oConn.Open();
oCmd = oConn.CreateCommand();
oCmd.CommandText = "Select Top 3 * From Customers";
oCmd.CommandType = CommandType.Text;
oDr = oCmd.ExecuteReader();
GridView1.DataSource = oDr;
GridView1.DataBind();
oDr.Close();//Because this is DataReader , we have to re -Execute the ReaderoDr = oCmd.ExecuteReader();
GridView2.DataSource = oDr;
GridView2.DataBind();
}
finally
{
oDr.Close();
oConn.Close();
}
You can see from the sample code behind, we actually call the execute reader twice because we need to bind two
GridView.This is because
DataReader is a forward only reader and once it finish reading the data, there is no way it can go back to previous record. And for this scenario,
DataReader is not a good candidate, because we have to retrieve the data twice from the database. You can use the
DataAdapter object that can save the retrieved data into the memory and this will enhance the performance greatly, because the second time the data binding, is from the memory and not from the database. I will show you the sample code below.
Using Data Adapter in ASP.NET
The SQL DataAdapter is a special class whose purpose is to bridge the gap between the disconnected DataTable objects and the physical data source. The SqlDataAdapter provides a two way data transfer mechanism. It is capable of executing a SELECT statement on a datasource and transferring the result set into a DataTable object. It is also capable of executing INSERT,UPDATE and DELETE statements.
SqlConnection oConn = null;
SqlCommand oCmd = null;
SqlDataAdapter oDa = null;
DataTable oDt = new DataTable();
oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"]);
oCmd = oConn.CreateCommand();
oCmd.CommandText = "Select Top 3 * From Customers";
oCmd.CommandType = CommandType.Text;
oDa = new SqlDataAdapter();
oDa.SelectCommand = oCmd;
oDa.Fill(oDt);for (int i = 0; i < oDt.Rows.Count; i++)
{
Response.Write(oDt.Rows[i]["CustomerID"] + " <BR>");
}
If you see the code above, you will notice that we don't open the Connection objects at all. We just use the
SqldataAdapter.Fill method to fill the
DataTable, and its all done. That is the power of the
SqlDataAdapter. When you use the
SqlDataAdapter.Fill Method, it basically know how to handle the open and close the connection objects automatically. But before you use the Fill Method, you need to assign the
SelectCommand method to the
SqlCommand object.
You can think of DataTable is just like a memory representation of Database table. This means that the data retrieved will be stored in memory as a Datatable object. Datatable can be access just like two dimensional array where you can specify the rows and columns. And because data table is stored in memory, you can actually rebind the datatable into another grid, repeater, read the data table again and again, without have to reconnect to the database.
Code Sample Binding DataAdapter with GridView in ASP.NET SqlConnection oConn = null;
SqlCommand oCmd = null;
SqlDataAdapter oDa = null;
DataTable oDt = new DataTable();
oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"]);
oCmd = oConn.CreateCommand();
oCmd.CommandText = "Select Top 3 * From Customers";
oCmd.CommandType = CommandType.Text;
oDa = new SqlDataAdapter();
oDa.SelectCommand = oCmd;
oDa.Fill(oDt);
GridView1.DataSource = oDt;
GridView2.DataSource = oDt;
GridView2.DataBind();
GridView1.DataBind();
You can see that from the code above, we can bind two GridView without have to reconnect to the database again. That is the power of disconnected DataTable objects.
Understanding DataTable and DataSet
DataSet probably can be said as the most commonly used and powerful part of ADO.NET in real world, database driven applications. The class provides the mechanisms for managing data when it is disconnected from data source. This capability to handle data in a disconnected state was first introduced in .NET since version 1.0. The current 2.0 version of DataSet retains all the features of its predecessors and provides a few newer much needed features.
A DataSet object basically works as a container for other objects that are created from the DataTable class. The DataTable object represents a logical table in memory. You can host multiple Datatable objects in one DataSet objects. The previous version of ADO.NET didn't allow you to work directly with the DataTable object for some very important tasks such as reading and writing data to and from XML file. It didnt even allow you to serialize the datatable object independently. Now in ASP.NET 2.0, all this limitation has been removed. You can perform all the task above by using the Data Table object independently. In fact, it is better to use DataTable objects rather than DataSet objects unless if you need a container DataSet objects to manage multiple DataTable objects.
The current version of ADO.NET also provides you the capability to load a Datatable in memory by using a datasource using a DataReader.
CodeSample on How to Load DataTable from DataReader
SqlConnection oConn = null;
SqlCommand oCmd = null;
SqlDataReader oDr = null;try
{
oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"]);
oConn.Open();
oCmd = oConn.CreateCommand();
oCmd.CommandText = "Select Top 3 * From Customers";
oCmd.CommandType = CommandType.Text;
oDr = oCmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(oDr);
oDr.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
GridView2.DataSource = dt;
GridView2.DataBind();
}
finally
{
oDr.Close();
oConn.Close();
}
Since,we have loaded the DataTable from the DataReader, now, we can bind the GridView to the DataTable and will greatly enhance the performance, because we don't have to re-connect to the database again.
Conclusion
This article covers basic and introduction to ADO.NET in ASP.NET 2.0. It list down the Usage of DataReader,DataAdapter, DataTable, DataSet and explain the difference between the Disconnected and connected way of retrieving data from the database.
If however, you only need to retrieve and display the data once only , without the need to reprocess the data again, then the fastest way would be using the DataReader. If you need to reprocess the data more than once, then the DataAdapter will be the best one to use.
Download Source Code