DAL: Access a DataReader using a Stored Procedure
Thu, Jul 9 2009 10:57
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.
public static SqlDataReader ExecuteDataReader(
params SqlParameter arrParam)
// Open the connection
SqlConnection cnn = new SqlConnection(
// 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)
// Execute the reader
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
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)
' 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
' Execute the reader
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
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
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.
using (SqlDataReader reader =
// Do something with the data
Using reader As SqlClient.SqlDataReader = _
' Do something with the data
By using a using statement, you ensure that the reader is closed when you are finished with it.