Introduction to ADO.NET

The DataReader

With classic ASP, when we needed a method for data retrieval, we'd use the appropriate data object and set its cursors to the task at hand. If we wanted a quick forward-only data read, we would set our Recordset's CursorType to adOpenForwardOnly and its LockType to adLockReadOnly (often referred to as a "fire-hose" cursor). Well, with .NET, all you need is the DataReader, which offers many features by which you can further tweak the efficiency of its output.

Command Object Methods

Now that we know what the DataReader does, there are numerous methods that can be used with it to achieve your specific goals. Here are some methods the DataReader works with through its Command object. The four Execute methods all pertain to an action performed by the Command object, wile the remaining methods are used to enhance the Command object's own functionality.

• ExecuteReader - Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below
• ExecuteNonQuery - Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below
• ExecuteScalar - Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null.
• ExecuteXmlReader - Executes the SQL query against SQL Server only, while returning an XmlReader object. See .NET documentation for more information
• Prepare - Equivalent to ADO's Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once. Ex. Command.Prepare();
• Dispose - Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object's Close method as well.

Thus, after we establish our initial database connection all we need to do to retrieve data with a DataReader is to use the Command object to query the open database. The Command.ExecuteReader method used via the Command Object creates our DataReader.

SqlCommand objCommand = new SqlCommand(Sql String, objConnect);  
 
SqlDataReader objDataReader = objCommand.ExecuteReader();

If you hadn't noticed, the connection object above is obviously set for SQL Server. For the others like Access, you'd use the OleDbConnection object, easily accommodated by replacing the SQL part in SqlCommand with OleDB, i.e. OleDbCommand objCommand. This applies to the SqlDataReader (which must be changed to OleDbDataReader) as well. This interchangeability works across the .NET framework, so keep it in mind.

Once we have our DataReader object, we set up our DataReader's Read() method in a while loop and traverse and display our data results by index position, rather than objDataReader["Name"].ToString(). I prefer this method as it's a little quicker, provided you can remember your column names!

while (objDataReader.Read() == true) {  
 
Response.Write (objDataReader[0].ToString() + "<BR>");  
 
}

DataReader Methods

Here are some common methods available to the Datareader:

• Read - Moves the record pointer to the first row, which allows the data to be read by column name or index position. Can check for data existence with conditional, if (DataReader.Read() = true)
• HasRows - New only with .NET v1.1. HasRows checks if any data exists, and is used instead of the Read method. Ex. if (DataReader.HasRows).
• IsClosed - A method that can determine if the DataReader is closed. Ex. if (DataReader.IsClosed == false)
• NextResult - Equivalent to ADO's NextRecordset Method, where a batch of SQL statements are executed with this method before advancing to the next set of data results. As with the loop just listed, you can add DataReader.NextResult() after the first loop within multiple SQL statements, and then begin a new loop for the next set.
• Close - Closes the DataReader

After all's said and done, it's always good programming practice to close, release and clear all connections and resources:

objDataReader.Close();  
objDataReader = null;  
objCommand.Dispose();  
objCommand = null;  
objConnect.Close();  
objConnect= null;

DataReader Object Command Behaviors

But that's not all! The DataReader object also has some cool optional Command Behaviors that you should use whenever you can for added scalability and the improved performance of your application. These can be implemented within the DataReader object. For example:

objCommand.ExecuteReader(CommandBehavior.CloseConnection);  
or objCommand.ExecuteReader(CommandBehavior.SingleResult);

The behaviours are:

• SingleResult - Similar to ExecuteScalar, returns a single value
• SingleRow - As it sounds, returns us a single row
• SequentialAccess - Is a very efficient method of in-order data access and useful in two ways. Firstly, it's used with the DataReader's Get Methods where you can return results in sequential order from the database using the columns' ordinal positions, it's superior (in terms of speed) to using the objDataReader["Column1"].ToString() or objDataReader[0].ToString() methods. And secondly, the Data Reader's many available Get Methods, i.e. objDataReader.GetString(0) or objDataReader.GetInt32(0), allow us to bypass any initial data conversion that would take place. However, non-Null data must be present for it to work, and all data retrieval must be in sequential order, dependent on your SQL query.
• CloseConnection - Forces the DataReader and its Connection object to close, once it reads through the data

The Completed DataReader

Here is our Datareader code in its entirety. You could run this within any function, or on Page_Load. Furthermore, the tags that enclose the ADO code below are common to all code examples listed throughout this article:

<%@ Page Language="C#" Debug="False" Explicit="True"%>  
<%@ Import Namespace="System.Data" %>  
<%@ Import Namespace="System.Data.SqlClient" %>  
 
<html>  
<head>  
<title>ADO.NET</title>  
</head>  
<body>  
 
<script runat="server" language="C#">  
 
 void Page_Load (Object Source, EventArgs E) {  
 
   SqlConnection objConnect = new SqlConnection  
("server=(local);uid=sa;pwd=;database=Northwind;");  
       objConnect.Open();  
 
   SqlCommand objCommand = new SqlCommand("Select * from  
Suppliers Order by ContactName Asc", objConnect);  
   objCommand.Prepare(); SqlDataReader objDataReader =  
objCommand.ExecuteReader CommandBehavior.CloseConnection);  
 
   while (objDataReader.Read() == true) {  
       Response.Write (objDataReader  
[2].ToString() + "<BR>");  
   }  
 
   objDataReader.Close();  
       objDataReader = null;  
       objCommand.Dispose();  
       objCommand = null;  
       objConnect.Close();  
       objConnect= null;  
   
 }  
 
</script>  
</body>  
</html>

In the example above we set up our page, and import our necassary namespaces that allow us to work with our data (keep in mind SqlClient is case sensitive in C#). Next, we place our datareader code between the server side tags that connect to our SQL Database, and in turn, display our results. After this, we close all our objects.