When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Book Reviews
Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
Web Hosts
Author an Article
spgif spgif

ASP ASP.NET ASP FAQs Feedback topnav-right
Print this Page!
Published: Wednesday, August 10, 2005

Streamlining Your Data Access Layer with Helper Methods, Part 2

By Scott Mitchell

  • Read Part 1

  • In Part 1 we discussed how to reduce the total number of lines of code in a typical data access layer by utilizing Microsoft's free Data Access Application Block along with a number of helper methods. We also looked at our first class of helper methods, showing how to remove the need to explicitly specify the connection string when calling the DAAB (this was pertinent to version 1 and 2 of the DAAB). In this second and final part we'll look at the remaining three classes of helper methods.

    Working with NULL-able Parameters

    When working with NULL-able database fields, oftentimes when adding or updating a record I want to use NULL values if the end user entering the data provides a certain value. As discussed earlier, I may prompt the user to provide their name and address in a Web Form, and the address is optional. If the user does not provide her address I may want to store a NULL value in the corresponding database field(s) rather than storing blank strings. With the DAAB, I have to write about five or six lines of code to create the parameter object explicitly and set the Value of the parameter based on whether or not the value constitutes a NULL value in the database. For example, my code might look like:

    'Create the parameter explicitly
    Dim AddressParam as New SqlParameter
    AddressParam.ParameterName = "@Address"
    If addressValueProvidedByUser.Length = 0 Then
      'Insert a NULL value into the database
      AddressParam.Value = DBNull.Value
      AddressParam.Value = addressValueProvidedByUser
    End If
    DALHelpers.ExecuteNonQuery("UpdateContact", ..., AddressParam, ...)

    What I'd like to be able to remove are those lines that do the check. To accomplish this I use a helper method that accepts as input the parameter name and value and returns a SqlParameter assigned NULL if needed.

    Public Shared GetNULLableStringParameter(ByVal paramName as String, _
                     ByVal paramValue as String) as SqlParameter
      Dim myParam as New SqlParameter
      myParam.ParameterName = paramName
      If paramValue.Length = 0 Then
        'Insert a NULL value into the database
        myParam.Value = DBNull.Value
        myParam.Value = paramValue
      End If
      Return myParam
    End Sub

    I can then change my call to DALHelpers.ExecuteNonQuery() to include the parameter object returned by GetNULLableStringParameter() like so:

    DALHelpers.ExecuteNonQuery("UpdateContact", ..., DALHelpers.GetNULLableStringParameter("@Address", addressValueProvidedByUser), ...)

    If you are using the Enterprise Library version of the DAAB you should be passing in IDataParameter instances rather than using SqlParameter, since the Enterprise Library version aims to work with any data provider, not just the SqlClient provider.

    Retrieving Typed Scalar Values

    The DAAB provides an ExecuteScalar() method to retrieve a scalar value from a SELECT query or stored procedure, but returns a value of type Object. Granted, you can simply cast this to the appropriate type you want, but I decided to create a number of strongly-typed ExecuteScalar() methods in the DALHelpers class. A small smattering of them can be seen below:

    Public Shared Function ExecuteInteger(ByVal sprocName As String, _
                     ByVal ParamArray commandParameters() As SqlParameter) As Integer
       Dim result as Object = DALHelpers.ExecuteScalar(sprocName, commandParameters)
       Return GetNULLableInteger(result)
    End Function
    Public Shared Function ExecuteString(ByVal sprocName As String, _
                     ByVal ParamArray commandParameters() As SqlParameter) As String
       Dim result as Object = DALHelpers.ExecuteScalar(sprocName, commandParameters)
       If result.Equals(DBNull.Value) Then
         Return String.Empty
         Return result.ToString()
       End If
    End Function
    Public Shared Function ExecuteDateTime(ByVal sprocName As String, _
                     ByVal ParamArray commandParameters() As SqlParameter) As DateTime
       Dim result as Object = DALHelpers.ExecuteScalar(sprocName, commandParameters)
       Return GetNULLableDateTime(result)
    End Function

    As you can see, each method has the same pattern - I get back an Object by calling DALHelpers.ExecuteScalar. (DALHelpers.ExecuteScalar() is not a method we examined directly, but mimics the syntax and semantics of DALHelpers.ExecuteNonQuery(), which we looked at earlier.) The GetNULLableInteger() and GetNULLableDateTime() methods, which we'll examine in the next section in more detail, return pre-defined values if the returned value is NULL. For ExecuteString() an empty string is returned if the result is NULL.

    Feigning NULL-able Types

    If you still find yourself in the 1.x world (like myself at the time of this writing!), then you have to take steps to feign the nullable types .NET 2.0 provides out of the box. The idea behind nullable types is giving a value type - integers, DateTimes, doubles, decimals, Booleans, and so on - a way to indicate that they have a NULL value. One way this is typically faked in .NET 1.x is by deciding that a particular value of that type will represent the NULL value. I have decided that Integer.MinValue (the smallest integer value for the platform your application is running on) is the reserved NULL value for integers; for DateTimes I use DateTime.MinValue.

    By providing a pre-defined value to represent NULLs, I need to do two things in my code:

    1. When reading in data from a database and assigning to a value type variable, I need to see if the value from the database is NULL and, if so, give the variable its appropriate NULL value (i.e., Integer.MinValue in the case of an integer variable).
    2. When working with the data in the presentation layer, I need to check to see if I am dealing with a NULL value and alter the display as needed.
    To accomplish the first step, my DALHelpers class has a slew of GetNULLableIntegerType() methods that handle the necessary logic, as shown below:

    Public Shared Function GetNULLableInteger(ByVal val As Object) as Integer
       If val.Equals(DBNull.Value) Then
         Return Integer.MinValue
         Return Convert.ToInt32(val)
       End If
    End Sub
    Public Shared Sub GetNULLableDateTime(ByVal val As Object) As DateTime   
       If val.Equals(DBNull.Value) Then
         Return DateTime.MinValue
         Return Convert.ToDateTime(val)
       End If
    End Sub

    Regarding the second task, handling NULL values in the presentation layer, typically I am just interested in displaying values differently based on if the value is NULL or not. For example, when displaying a person's name and age values I might want to display a blank string if the age value is NULL in the database, otherwise I want to display the person's age. To accomplish this I typically add a series of helper methods to the ASP.NET Web application project that simply take in a value of the appropriate type and return a string. The string returned is either the value passed in, if it's not the NULL value, and an empty string if it is.


    In this article we examined various techniques for streamlining the data access layer in an ASP.NET application. The first and most important step is to start using the Data Access Application Block (DAAB). This free library from Microsoft will greatly reduce the total number of lines of code you write to access data. However, even with the cost savings of the DAAB, there are a number of tasks that still, in my experience, get performed enough that they warrant their own, more specialized helper methods. In this article we took a look at some of the methods in my toolbox, and discussed how I use them. Hopefully you can use these ideas to further streamline your data-driven ASP.NET applications.

    Happy Programming!

  • By Scott Mitchell

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article