Handling Null fields with three-tier architecture

I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: -

Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'"
Dim message As String = ""
Dim Found As Boolean = False
Try
Using connection As New SqlConnection(GDB_AppSettings.Connection)
Dim command As New SqlCommand(queryString, connection)
connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

If reader.Read() Then
Found = True
_USRid = reader("USRid")
_USRPassword = reader("USRPassword")
_USREmail = reader("USREmail")
_USRTitle = reader("USRTitle")
_USRName = reader("USRName")
_USRRole = reader("USRRole")
If IsDBNull(reader("USRAgreedConditions")) = False Then
_USRAgreedConditions = reader("USRAgreedConditions")
End If
End If
reader.Close()
End Using
Catch ex As Exception
If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then
Return "Cannot open database to logon"
Else
Return Err.Description
End If
End Try

the new version is much more elegant: -

Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()

Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid)
If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible
Return "User not found"
End If

Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0)
_USRid = userid
_USRPassword = password
_USREmail = trUser.USREmail
_USRTitle = trUser.USRTitle
_USRName = trUser.USRName
_USRRole = trUser.USRRole
_USRAgreedConditions = trUser.USRAgreedConditions

However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: -

System.Data.StrongTypingException was unhandled by user code
Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull."
Source="App_Code.wt2vzoc1"
...

There is no point in writing: -
If Not IsDBNull(trUser.USRAgreedConditions) Then
_USRAgreedConditions = trUser.USRAgreedConditions
End If
because the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property.

It seems that my only options are: -
1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture
2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?
3. Code a Try block within the business layer: -
Try
_USRAgreedConditions = trUser.USRAgreedConditions
Catch exAs Exception
_USRAgreedConditions =Nothing
EndTry

This seems to work OK, but seems less elegant than the original code in the old version: -
If IsDBNull(reader("USRAgreedConditions")) = False Then
_USRAgreedConditions = reader("USRAgreedConditions")
End If

Is there a better way?

Hi Robert my friend,

There is a better way. Change your query to list all of the fields within the table that you want and set the USRAgreedConditions field to a specific valid date if it is NULL via the ISNULL() function. The SQL below will return today's date in this field where it is NULL but you can change it to use any valid date value that you wish.

SELECT USRid, USRPassword, URSEmail, USRTitle, USRName, USRRole,

ISNULL(USRAgreedConditions, GetDate()) AS USRAgreedConditions

FROM NZGDB_User

For this reason, it is not good practice to rely solely on date fields for business logic. If this field being null indicates that the conditions have not been agreed yet, it would be better to have an additional BIT field that indicates Yes or No and then the USRAgreedConditions field would simply be a field containing the date information for those that are set to 1 (Yes).

Kind regards

Scotty

|||

Thanks Scotty, that's certainly a neater solution, and I'll progressively use it. Unfortunately my code is now riddled with Try/End try blocks to handle Null fields, so it will take some time to get rid of them all.

Your solution will certainly work in most, perhaps all, of my situations. However I wonder if there isn't a lurking problem. The point of NULL is to distinguish "No value" from "Default value". Sometimes you need to make the distinction in your code, and there is a risk that whatever value you choose for VALUE in
ISNULL(USRAgreedConditions, VALUE) AS USRAgreedConditions
will coincide with a real value. Getdate() won't do, and neither will 0 (which becomes 1/1/1900) as either could be a valid date in this application. In this case I can use Getdate()+20 and test for a future date (which is otherwise logically impossible), but I have to be careful to remember to check that my logic doesn't write these artificial values back to the database. I tried using
DIM USRAgreeConditions as NULLABLE (OF Datetime)
but found myself getting into a lot of trouble with errors popping up elsewhere.

It's a pity that the designers of the new NULLABLE feature didn't make the handling of all potentially-null fields consistent. Thus I find that there is little trouble with String fields (CHAR, VARCHAR, etc in the database), as I can simply set the column's Nullvalue property to Empty. It's just silly that I can't do this for GUID and Date (and any other type) fields. There are also some idiocies in the way that operator usage changes with NULLABLE fields. Enough raving!

Thanks for your help.

Regards, Robert

|||

Hi Robert,

You can also try this within the query. In addition to generating a valid date for your column, have another column that inidcates whether or not it was generated so that you can check this extra column in the code to determine how to process the date field: -

SELECT ISNULL(USRAgreedConditions, GetDate()) AS USRAgreedConditions,
CASE WHEN USRAgreedConditions IS NULL THEN 'Yes' ELSE '' END AS IsNullDate
FROM TableName

Kind regards

Scotty

|||

Thanks Scotty, yes that's exactly what I want. Something like this (returning the default value of Nothing and an indicator of whether it's a real null or not is what Microsoft should be doing automatically: in the meantime I guess this is the best we can do.

Thanks again, Robert