Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 9, 2009

DAL: Access a DataReader using a SQL Statement

Filed under: C#,VB.NET @ 2:03 pm

This post provides an implementation of a method that accesses a DataReader from a SQL Server database using a SQL statement. 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.

File:Garden hose pistol.JPGIf 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.

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

In C#:

public static SqlDataReader ExecuteDataReader(
                    
string sqlStatement,
                     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.Text;
   cmd.CommandText = sqlStatement;

   // 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 sqlStatement 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.Text
    cmd.CommandText = sqlStatement

    ‘ 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 SQL statement. Since this code returns a single stream of data, it assumes that the SQL statement contains one Select statement.

The second parameter is a parameter array that allows defining any parameters required by the SQL statement.

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 SQL statement and the CommandText property of the command defines the text of the SQL statement.

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 define 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.

As an example, this is how you call this method.

In C#:

SqlDataReader reader = Dac.ExecuteDataReader(
     "SELECT CustomerId, LastName, FirstName FROM Customer", null);

while (reader.Read())
{
   // Do something with the data
}
reader.Close();

In VB:

Dim reader As SqlClient.SqlDataReader = Dac.ExecuteDataReader( _
         "SELECT CustomerId, LastName, FirstName FROM Customer", _
                                                         Nothing)

While reader.Read
    ‘ Do something with the data
End While

reader.Close

If your SQL statement has parameters, you can call it like this:

In C#:

SqlDataReader reader = Dac.ExecuteDataReader(
                           "SELECT CustomerId, LastName, " +
                           "FirstName FROM Customer " +
                           "WHERE CustomerId=@CustomerId",
                           new SqlParameter("@CustomerID", 3));

while (reader.Read())
{
   // Do something with the data
}
reader.Close();

In VB:

Dim reader As SqlClient.SqlDataReader = Dac.ExecuteDataReader( _
                 "SELECT CustomerId, LastName, " & _
                 "FirstName FROM Customer " & _
                 "WHERE CustomerId=@CustomerId", _
                 New SqlParameter("@CustomerID", 3))
 
While reader.Read
    ‘ Do something with the data
End While
reader.Close

NOTE: See this for an easier way to write a SQL statement in VB using XML literals.

Enjoy!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs