Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
Like this article? We recommend

DataReader

The DataReader is the read-only, forward cursor over a rowset of ADO.NET data. The big change between it and the ADO Recordset is the use of a Read method at the start of data operations to advance the cursor to the first row. Subsequent Read calls load new rows into the object. If the Read returns a false Boolean we fall out of the loop. This helps prevent those infinite loops of the ADO Recordset where we forgot to call MoveNext.

The DataReader provides two main ways of getting column data: by ordinal position or by column name. The Listing 2 demo retrieved the CustomerID column by using the 0 position ordinal along with the strongly typed GetString method for returning characters. The ContactName column was chosen using the more convenient and more maintainable indexer syntax. I prefer the second way of retrieving the column value despite the small performance loss required to do a name lookup on the fly.

This chunk of demo code doesn't bother with a local transaction since we're doing a read-only operation. It still uses the try/finally structure to ensure that resources are closed appropriately. One key difference in our technique in this example is the use of the CommandBehavior enumeration when we call ExecuteReader.

A CloseClonnection value of the CommandBehavior type saves us some work in the finally block because we only have to check on and close the DataReader object. It calls the close of the Connection for us and prevents situations where the Connection is closed while the DataReader is still being used.

Batch Reads

The DataReader has the capability of receiving multiple result sets if the data provider supports batch SQL commands. Listing 4 shows a SELECT statement against both the Customers and Orders table from Northwind in a single SQL batch statement. The only difference in handling the data is the use of the NextResult to move the DataReader from the first result set to the next.

Listing 4 SQL Provider Batch Query Demo

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConnectedOpsDemo
{

class SqlCmdBatchDemo
{
[STAThread]
static void Main(string[] args)
{
   SqlConnection conn = null;
   SqlDataReader rdr = null;

   try
   {
     // open connection to the data source
     conn = new
     SqlConnection(
      "server=(local);database=Northwind; " +
      "integrated security=true;");
     conn.Open();

     SqlCommand cmd = new SqlCommand(
      "SELECT TOP 10 * FROM Customers; " +
      "SELECT TOP 10 * FROM Orders;", conn);

     // execute command to return a datareader that closes our
     // parent connection when it is closed
     rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

     Console.WriteLine("\nQUERY: {0} \nReturned the following" + 
      " result sets from ExecuteReader",
     cmd.CommandText);

     Console.WriteLine("\nCustomers");

     // read through Customers rows from the first result set
     while (rdr.Read())
     {
        // display CustomerID (position 0),
        // ContactName (by name) columns
        // from the DataReader
        Console.WriteLine("CustomerID: {0} ContactName: {1}",
        rdr.GetString(0), rdr["ContactName"]);
     }

     // move to the second result set
     rdr.NextResult();

     Console.WriteLine("\nOrders");

     // read through Orders rows
     while (rdr.Read())
     {
        // display CustomerID (position 0),
       // ContactName (by name) columns
        // from the DataReader
        Console.WriteLine("OrderID: {0} OrderDate: {1}" +
         " CustomerID:{2}",
         rdr["OrderID"], rdr["OrderDate"], rdr["CustomerID"]);
     }
   }
   finally
   {
     // close the reader in the face of exceptions
     if (rdr != null)
     {
        if (!rdr.IsClosed)
          rdr.Close();
     }
   }
}
}
}

The output from the batch execute is shown in Listing 5. Notice how we can get two tables' worth of information in a single round trip. This is a great way to enhance data access performance.

Listing 5 SQL Provider Batch Query Demo Results

QUERY: SELECT TOP 10 * FROM Customers; SELECT TOP 10 * FROM Orders;
Returned the following result sets from ExecuteReader

Customers
CustomerID: ALFKI ContactName: Maria Anders
CustomerID: ANATR ContactName: Ana Trujillo
CustomerID: ANTON ContactName: Antonio Moreno
CustomerID: AROUT ContactName: Thomas Hardy
CustomerID: BERGS ContactName: Christina Berglund
CustomerID: BLAUS ContactName: Hanna Moos
CustomerID: BLONP ContactName: Frédérique Citeaux
CustomerID: BOLID ContactName: Martín Sommer
CustomerID: BONAP ContactName: Laurence Lebihan
CustomerID: BOTTM ContactName: Elizabeth Lincoln

Orders
OrderID: 10248 OrderDate: 7/4/1996 12:00:00 AM CustomerID:VINET
OrderID: 10249 OrderDate: 7/5/1996 12:00:00 AM CustomerID:TOMSP
OrderID: 10250 OrderDate: 7/8/1996 12:00:00 AM CustomerID:HANAR
OrderID: 10251 OrderDate: 7/8/1996 12:00:00 AM CustomerID:VICTE
OrderID: 10252 OrderDate: 7/9/1996 12:00:00 AM CustomerID:SUPRD
OrderID: 10253 OrderDate: 7/10/1996 12:00:00 AM CustomerID:HANAR
OrderID: 10254 OrderDate: 7/11/1996 12:00:00 AM CustomerID:CHOPS
OrderID: 10255 OrderDate: 7/12/1996 12:00:00 AM CustomerID:RICSU
OrderID: 10256 OrderDate: 7/15/1996 12:00:00 AM CustomerID:WELLI
OrderID: 10257 OrderDate: 7/16/1996 12:00:00 AM CustomerID:HILAA

Stored Procedures

Stored procedures are used to encapsulate database logic and increase database performance in a database application. So far we have not demonstrated their use and have stuck to raw SQL text. ADO.NET fortunately provides full support for stored procedures.

To demonstrate stored procedures, let's create a new one from our Northwind database. The stored procedure can be added via Visual Studio.NET or SQL Server Query Analyzer. Listing 6 shows that the SQL inside SelectCustByID.

Listing 6 SQL Server SelectCustByID Stored Procedure for Northwind Database

CREATE PROCEDURE dbo.SelectCustByID
(
@CustID varchar(7),
@CustCount int OUTPUT
}
AS
SELECT @CustCount=Count(*) FROM Customers WHERE ContactName Like @CustID

SELECT * FROM Customers WHERE ContactName Like @CustID

RETURN

The next step is to generate a demo for calling the procedure (see Listing 7).

Listing 7 SQL Provider Stored Procedure Demo

using System;
using System.Data;
using System.Data.SqlClient;


namespace ConnectedOpsDemo
{

class SqlCmdStoredProcDemo
{

[STAThread]
static void Main(string[] args)
{
   SqlConnection conn = null;
   SqlDataReader rdr = null;
   SqlCommand cmd = null;

   try
   {
     // open connection to the data source
     conn = new
     SqlConnection(
      "server=(local);database=Northwind;" + 
      "integrated security=true;");
     conn.Open();

     // set the command to execute a stored procedure named
     // SelectCustomersByID
     cmd = new SqlCommand("SelectCustByID", conn);
     cmd.CommandType = CommandType.StoredProcedure;

     // create a parameter for the @ContactName output parameter
     SqlParameter custcount = new
        SqlParameter("@CustCount",SqlDbType.Int);
     custcount.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(custcount);

     // create a parameter for the @Name input parameter
     SqlParameter custid = new
        SqlParameter("@CustID",SqlDbType.VarChar,7);
     custid.Value = "%D%";
     cmd.Parameters.Add(custid);

     // execute command to return a datareader that closes our
     // parent connection when it is closed
     rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);


     Console.WriteLine("\nSTORED PROC: {0} \nReturned the " +
      "following rows from ExecuteReader",
     cmd.CommandText);

     // read through all rows
     while (rdr.Read())
     {
        // display CustomerID (position 0),
        // ContactName (by name) columns
        // from the DataReader
        Console.WriteLine("CustomerID: {0} ContactName: {1}",
        rdr.GetString(0), rdr["ContactName"]);
     }
   }
   finally
   {
     // close the reader in the face of exceptions
     if (rdr != null)
     {
        if (!rdr.IsClosed)
          rdr.Close();
     }

     // wait till the reader is closed to
     // read the output parameter
     Console.WriteLine("\n\nSTORED PROC: {0} @CustCount: {1}" +
     "from ExecuteReader",
     cmd.CommandText, cmd.Parameters["@CustCount"].Value);

   }
}
}
}

The biggest difference between stored procedures and text commands is the value of the CommandType property of the Command object. The default is normally set to Text; here we set it to StoredProcedure. We also set the CommandText property to the name of the stored procedure instead of a SQL statement.

After we have outlined the name of the stored procedure to execute, we need to ensure that we have the appropriate parameters passed into and out of it. This is done using the Parameters collection of the Command object that holds instances of the SQLParameter class.

The SelectCustByID stored procedure takes an input parameter named @CustID and has a single output parameter named @CustCount. The @CustID parameter takes a SQL varchar value that is 7 characters wide. The @CustCount parameter is a SQL int. We create each parameter with the correct initialization information and then set the value before we add the parameter to the Parameters collection.

The ExecuteReader method is then called to get the result set back and allow the same iteration through the row values as before. An interesting quirk is the way we get the result value from the output param. We need to close down the DataReader before the value is passed back into the Parameters collection. Calling earlier, before the DataReader close, results in a null value.

The result set in Listing 8 shows we returned 27 rows, as reported by the output parameter.

Listing 8 SQL Provider Stored Procedure Demo Results

STORED PROC: SelectCustByID
Returned the following rows from ExecuteReader
CustomerID: ALFKI ContactName: Maria Anders
CustomerID: AROUT ContactName: Thomas Hardy
CustomerID: BERGS ContactName: Christina Berglund
CustomerID: BLONP ContactName: Frédérique Citeaux
CustomerID: COMMI ContactName: Pedro Afonso
CustomerID: DALEM ContactName: Dale Michalk
CustomerID: EASTC ContactName: Ann Devon
CustomerID: ERNSH ContactName: Roland Mendel
CustomerID: FISSA ContactName: Diego Roel
CustomerID: FURIB ContactName: Lino Rodriguez
CustomerID: GALED ContactName: Eduardo Saavedra
CustomerID: GODOS ContactName: José Pedro Freyre
CustomerID: GOURL ContactName: André Fonseca
CustomerID: GREAL ContactName: Howard Snyder
CustomerID: HILAA ContactName: Carlos Hernández
CustomerID: LACOR ContactName: Daniel Tonini
CustomerID: LINOD ContactName: Felipe Izquierdo
CustomerID: MAISD ContactName: Catherine Dewey
CustomerID: MORGK ContactName: Alexander Feuer
CustomerID: OCEAN ContactName: Yvonne Moncada
CustomerID: PARIS ContactName: Marie Bertrand
CustomerID: PERIC ContactName: Guillermo Fernández
CustomerID: PRINI ContactName: Isabel de Castro
CustomerID: QUEDE ContactName: Bernardo Batista
CustomerID: ROMEY ContactName: Alejandra Camino
CustomerID: SPECD ContactName: Dominique Perrier
CustomerID: TRADH ContactName: Anabela Domingues


STORED PROC: SelectCustByID @CustCount: 27 from ExecuteReader
  • + Share This
  • 🔖 Save To Your Account