Deborah's Developer MindScape

This post provides an implementation of a method that accesses a DataReader using a stored procedure in a SQL Server database. Your application can then use the DataReader to read the data from your database or bind directly to an ASP.NET control such as a grid.

If you are not familiar with a DataReader, it is basically like a hose with a nozzle. When you open the connection to the database, it is like turning on the spigot connecting the hose to the water source. The water is flowing, but you don’t get any water out because the nozzle is not open. 

Each time you read something from the DataReader, it is like one squirt of the nozzle: you only get one piece of data. You repeat this process until you get all of the data you need. Then you close the connection, basically turning off the spigot.

DataReaders are used most often in ASP.NET WebPages because of their performance and the ability to bind ASP.NET controls to DataReaders.

NOTE: This post assumes you already have a stored procedure defined to select the required data from your database. For more information on stored procedures, see this link.

The code is first shown in both VB and C#. It is then described in detail below.

In C#:

public static SqlDataReader ExecuteDataReader(
                     string storedProcedureName,
                     params SqlParameter[] arrParam)
{
   SqlDataReader reader;

   // Open the connection
   SqlConnection cnn = new SqlConnection(
              Properties.Settings.Default.ARConnectionString);
   cnn.Open();

   // Define the command
   SqlCommand cmd = new SqlCommand();
   cmd.Connection = cnn;
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = storedProcedureName;

   // Handle the parameters
   if (arrParam != null)
   {
      foreach (SqlParameter param  in arrParam)
         cmd.Parameters.Add(param);
   }

   // Execute the reader
   reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

   return reader;
}

In VB:

Public Shared Function ExecuteDataReader( _
        ByVal storedProcedureName As String, _
        ByVal ParamArray arrParam() As SqlParameter) As SqlDataReader

    Dim reader As SqlDataReader

    ' Open the connection
    Dim cnn As New SqlConnection(My.Settings.ARConnectionString)
    cnn.Open()

    ' Define the command
    Dim cmd As New SqlCommand
    cmd.Connection = cnn
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = storedProcedureName

    ' Handle the parameters
    If arrParam IsNot Nothing Then
        For Each param As SqlParameter In arrParam
            cmd.Parameters.Add(param)
        Next
    End If

    ' Execute the reader
    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

    Return reader
End Function

The ExecuteDataReader function is defined to be public and static (shared in VB). It is public so it can be called from any other code. It is static/shared because it does not use or retain any state. This allows the function to be called without creating an instance of the class containing the function.

The function has two parameters. The first is the name of a stored procedure. Since this code returns a single stream of data, it assumes that the stored procedure contains one Select statement. Here is an example:

CREATE PROCEDURE dbo.CustomerRetrieveAll
AS
    SELECT    
                CustomerId,
                LastName,
                FirstName
    FROM       
                Customer
    ORDER BY
                LastName + ', ' + FirstName

The second parameter is a parameter array that allows defining any parameters required by the stored procedure. In the CustomerRetrieveAll example, there are no parameters. But your stored procedure may have parameters to filter the data.

NOTE: A parameter array permits a variable number of arguments to be passed to a method. So this allows any number of parameters.

The first line of code defines a variable for the DataReader. It is used as the return value from the function.

The code defines the connection. The connection string itself is stored in the configuration file using the Settings feature in Visual Studio. (See this for information on settings in C# and this for information on settings in VB.) The connection is then opened.

NOTE: This code can not use a Using statement because a using statement closes the connection at the end of the statement. Closing the connection prevents the calling code from accessing data from the DataReader. (Following the earlier metaphor, closing the connection in this function is like turning off the spigot before getting any water out.)

Next, the code defines the command. In this case, the command is a stored procedure and the CommandText property of the command defines the stored procedure name.

If parameters were passed in, the parameters are added to the command’s Parameters.

The code then calls the ExecuteReader method of the command to create the DataReader. The CloseConnection parameter of the ExecuteReader method ensures that the connection is closed when the DataReader object is closed.

The resulting DataReader is then returned.

The code to call this method is shown below.

In C#:

using (SqlDataReader reader =
          Dac.ExecuteDataReader("CustomerRetrieveAll", null)) 
{
    while (reader.Read())
   {
      // Do something with the data
   }
}

In VB:

Using reader As SqlClient.SqlDataReader = _
           Dac.ExecuteDataReader("CustomerRetrieveAll", nothing)
   While reader.Read 
      ' Do something with the data
   End While
End Using

By using a using statement, you ensure that the reader is closed when you are finished with it.

Enjoy!