dnn中NULL值的處理

空處理

每一個資料存取系統都有一個特殊的構造來處理那些沒有明確指定的欄位值。在大多數關聯式資料庫管理系統中，這個構造就是眾所周知的null值。

從應用程式的角度看，在表述層和資料存取層傳遞null值是一個架構上的挑戰。這是因為表述層必須從資料庫的特定資訊抽象出來；而且，當一個屬性值沒有明確指定的時候表述層也必須能夠表達說明。

事實上這相當複雜，.NET Framework的本身的資料類型不能自動的轉換從資料庫返回的null值（如果你試圖直接那樣賦值的話將會拋出一個異常）。另外，每一個資料存放區都有它自己的屬性來實現null。

唯一合理的解決方案就是建立一個抽象的傳輸服務，來編碼/解碼應用程式各層之間的null值。

 

乍一看，你也許會想到用vb.net中的“nothing”關鍵字可以很好的擔負起這個傳輸服務的任務。不幸的是，調查顯示，.NET Framework本身的資料類型處理“nothing”的時候沒有預想的那麼好。儘管分配為nothing的屬性不會拋出異常，實際上這個屬性的值將非常依賴於它的資料類型（String = Nothing, Date = Date.MinValue, Integer = 0, Boolean = False, 等等）並且內建的IsNothing()函數的結果還不是一致的（相容的）結果。

 

在DNN裡，我們建立了一個通用的類來處理null的問題，它統一管理應用程式各層的null問題。在應用程式中用一個常量來描述每種資料類型的null情況，再把這個常量轉化成各種資料存放區實現裡的實際的null值。這個類包含的各種方法將null轉換服務的物理細節從應用程式中抽象出來了。

* 記住，這個類僅僅用在資料庫欄位允許有null值的情況下。還要記住，這個類要求DAL和BLL層之間的資料類型一致（例如：一個BLL資訊類裡的屬性欄位的資料類型必須跟DAL 資料提供者傳遞過來的參數的資料類型一致）。

 

 

    Public Class Null

 

        ' define application encoded null values

        Public Shared ReadOnly Property NullInteger() As Integer

            Get

                Return -1

            End Get

        End Property

        Public Shared ReadOnly Property NullDate() As Date

            Get

                Return Date.MinValue

            End Get

        End Property

        Public Shared ReadOnly Property NullString() As String

            Get

                Return ""

            End Get

        End Property

        Public Shared ReadOnly Property NullBoolean() As Boolean

            Get

                Return False

            End Get

        End Property

 

        ' sets a field to an application encoded null value ( used in Presentation layer )

        Public Shared Function SetNull(ByVal objField As Object) As Object

            If TypeOf objField Is Integer Then

                SetNull = NullInteger

            ElseIf TypeOf objField Is Date Then

                SetNull = NullDate

            ElseIf TypeOf objField Is String Then

                SetNull = NullString

            ElseIf TypeOf objField Is Boolean Then

                SetNull = NullBoolean

            Else

                Throw New NullReferenceException()

            End If

        End Function

 

        ' sets a field to an application encoded null value ( used in BLL layer )

        Public Shared Function SetNull(ByVal objPropertyInfo As PropertyInfo) As Object

            Select Case objPropertyInfo.PropertyType.ToString

                Case "System.Int16", "System.Int32", "System.Int64", "System.Single", "System.Double", "System.Decimal"

                    SetNull = NullInteger

                Case "System.DateTime"

                    SetNull = NullDate

                Case "System.String", "System.Char"

                    SetNull = NullString

                Case "System.Boolean"

                    SetNull = NullBoolean

                Case Else

                    Throw New NullReferenceException()

            End Select

        End Function

 

        ' convert an application encoded null value to a database null value ( used in DAL )

        Public Shared Function GetNull(ByVal objField As Object, ByVal objDBNull As Object) As Object

            GetNull = objField

            If TypeOf objField Is Integer Then

                If objField = NullInteger Then

                    GetNull = objDBNull

                End If

            ElseIf TypeOf objField Is Date Then

                If objField = NullDate Then

                    GetNull = objDBNull

                End If

            ElseIf TypeOf objField Is String Then

                If objField = NullString Then

                    GetNull = objDBNull

                End If

            ElseIf TypeOf objField Is Boolean Then

                If objField = NullBoolean Then

                    GetNull = objDBNull

                End If

            Else

                Throw New NullReferenceException()

            End If

        End Function

 

        ' checks if a field contains an application encoded null value

        Public Shared Function IsNull(ByVal objField As Object) As Boolean

            If objField = SetNull(objField) Then

                IsNull = True

            Else

                IsNull = False

            End If

        End Function

 

    End Class

項目實際使用DA層

SqlDataProvider (具體實作類別)

 

在具體類中包含了下面的協助方法，這個方法用來獨立資料庫null的實現（這個例子中DBNull.Value 是針對SQL Server而言的）並且提供一個簡單的介面。

 

 

    ' general

    Private Function GetNull(ByVal Field As Object) As Object

        Return Null.GetNull(Field, DBNull.Value)

    End Function

 

每一個在基類裡表明必須繼承的方法在具體類裡都必須實現。注意上面的add/update方法裡描述的GetNull()函數的使用。

 

    ' announcements module

    Public Overrides Function GetAnnouncements(ByVal ModuleId As Integer) As IDataReader

        Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "GetAnnouncements", ModuleId), IDataReader)

    End Function

    Public Overrides Function GetAnnouncement(ByVal ItemId As Integer, ByVal ModuleId As Integer) As IDataReader

        Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "GetAnnouncement", ItemId, ModuleId), IDataReader)

    End Function

    Public Overrides Sub DeleteAnnouncement(ByVal ItemId As Integer)

        SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "DeleteAnnouncement", ItemId)

    End Sub

    Public Overrides Sub AddAnnouncement(ByVal ModuleId As Integer, ByVal UserName As String, ByVal Title As String, ByVal URL As String, ByVal Syndicate As Boolean, ByVal ExpireDate As Date, ByVal Description As String, ByVal ViewOrder As Integer)

        SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "AddAnnouncement", ModuleId, UserName, Title, URL, Syndicate, GetNull(ExpireDate), Description, GetNull(ViewOrder))

    End Sub

    Public Overrides Sub UpdateAnnouncement(ByVal ItemId As Integer, ByVal UserName As String, ByVal Title As String, ByVal URL As String, ByVal Syndicate As Boolean, ByVal ExpireDate As Date, ByVal Description As String, ByVal ViewOrder As Integer)

        SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner & ObjectQualifier & "UpdateAnnouncement", ItemId, UserName, Title, URL, Syndicate, GetNull(ExpireDate), Description, GetNull(ViewOrder))

    End Sub