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
Else
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
Else
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:
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
Else
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:
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).
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
Else
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
Else
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.
Conclusion
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.
