When integrating T-SQL with the CLR, remember to declare variables, parameters, and return values of data types exposed through the System.Data.SqlTypes namespace. Doing so guarantees a behavior more similar to T-SQL.
As described in the previous section, the outcome of performing arithmetic, bitwise, and logical comparisons between two variables when one or both values is NULL can be inconsistent. The ANSI_NULLS option in T-SQL proves how different the results can be; and as you saw in the simple Visual Basic .NET example, not using the SqlTypes data types leads to the same confusion.
Fortunately, there is the SqlBoolean data type. Exposed as part of the SqlTypes namespace, the SqlBoolean data type can represent three distinct states—true, false, and unknown. In addition, the comparison of two SqlTypes data types always returns a SqlBoolean, which again ensures consistent behavior.
The SqlBoolean data type exposes three important properties:
- IsTrue: Indicates whether the comparison produces a TRUE value.
- IsFalse: The outcome when the comparison is FALSE.
- IsNull: Returns true when the comparison between the variables produces an unknown or NULL result.
Keeping these concepts in mind, look at the Visual Basic .NET code behind the SqlBooleans button on the switchboard form.
Private Sub SqlBooleans()
Dim intX As SqlInt32
Dim intY As SqlInt32
Dim blnResult As SqlBoolean
intX = 5
intY = SqlInt32.Null
blnResult = (intX = intY)
MsgBox("intX IS NULL - " & intX.IsNull & vbTab _
& "intY IS NULL - " & intY.IsNull & vbTab _
& "intX = intY - " _
The code makes the following comparison:
blnResult = (intX = intY)
The code compares intX—a SqlInt32 assigned the value 5—with intY, another SqlInt32 explicitly assigned a NULL value. The result is a SqlBoolean data type with properties that contain the outcome of the comparison— blnResult.IsTrue.
Figure 1 shows the Msgbox that displays the outcome of this routine.
Figure 1. SqlBooleans provide consistency when you work with NULL values.
WARNING! Remember that a SqlBoolean data type represents three states— IsTrue, IsFalse, and IsNull. IsNull returns TRUE only when both sides of the comparison are unknown.
Frank Tillinghast is a senior consultant with MTOW Software Solutions. He is a Microsoft Certified Solution Developer and has been developing applications for over fifteen years. Most of his time is spent consulting for companies nationwide with troubled projects or mentoring projects to successful completion. When he is not developing software or mentoring clients, Frank is teaching other developers. He has taught thousands of application developers how to create business solutions with Visual Studio .NET. VB.NET, ASP.NET, Visual C#, and SQL Server.