轻松搞定数据访问层[续2]

' clsDataAccessOper 该类是所有数据访问类的父类

' by YuJun

‘ www.hahaIT.com

‘ hahasoft@msn.com

 

Public Class clsDataAccessOper

 

    ' 当Update,Delete,Add方法操作失败返回 False 时，记录出错的信息

    Public Shared ModifyErrorString As String

 

    Private Shared Keys As New Hashtable

 

    ' 数据库连接字符串

    Public Shared Property ConnectionString() As String

        Get

            Return SqlHelper.cnnString.Trim

        End Get

        Set(ByVal Value As String)

            SqlHelper.cnnString = Value.Trim

        End Set

    End Property

 

    ' Update 不更新主键，包括联合主键

    Public Shared Function Update(ByVal o As Object) As Boolean

        ModifyErrorString = ""

        Try

            If CType(SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Exists(o)), Int64) = 0 Then

                Throw New Exception("该记录不存在!")

            End If

        Catch ex As Exception

            Throw ex

        End Try

 

        Try

            SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Update(o))

        Catch ex As Exception

            ModifyErrorString = ex.Message

            Return False

        End Try

        Return True

    End Function

 

    ' Delete 将忽略

    Public Shared Function Delete(ByVal o As Object) As Boolean

        ModifyErrorString = ""

        Try

            SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Delete(o))

        Catch ex As Exception

            ModifyErrorString = ex.Message

            Return False

        End Try

        Return True

    End Function

 

    ' Add 方法将忽略自动增加值的主键

    Public Shared Function Add(ByVal o As Object) As Boolean

        ModifyErrorString = ""

        Try

            SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Add(o))

        Catch ex As Exception

            ModifyErrorString = ex.Message

            Return False

        End Try

        Return True

    End Function

 

    ' 通用数据库查询方法

    ' 重载方法用于明确指定要操作的数据库表名称

    ' 否则会以 ReturnType 的类型描述得到要操作的数据库表的名称 eg: ReturnType="clsRooms" ,得道 TableName="tbl_Rooms"

 

    ' 该查询方法将查询条件添加到 Keys(HashTable) 中，然后调用 Select 方法返回 对象的集合

    ' 当Keys包含特殊键时，将要处理的是复杂类型的查询,见 SQLBuilder 的 ComplexSQL 说明

    ' 该方法可以拓展数据访问类的固定查询方法

 

    Public Overloads Shared Function [Select](ByVal ReturnType As Type) As ArrayList

        Dim tableName As String

        tableName = ReturnType.Name

        Dim i As Int16

        i = tableName.IndexOf("cls") + 3

        tableName = "tbl_" & tableName.Substring(i, tableName.Length - i)

        Return [Select](ReturnType, tableName)

    End Function

 

    Public Overloads Shared Function [Select](ByVal ReturnType As Type, ByVal TableName As String) As ArrayList

        Dim alOut As New ArrayList

 

        Dim dsDB As New Data.DataSet

        dsDB.ReadXml(clsPersistant.DBConfigPath)

        Dim xxxH As New Hashtable

        Dim eachRow As Data.DataRow

        For Each eachRow In dsDB.Tables(TableName).Rows

            If Keys.Contains(CType(eachRow.Item("name"), String).ToLower.Trim) Then

                xxxH.Add(CType(eachRow.Item("dbname"), String).ToLower.Trim, Keys(CType(eachRow.Item("name"), String).Trim.ToLower))

            End If

        Next

 

        ' 检查 Keys 的合法性

        Dim dsSelect As New Data.DataSet

        If Keys.Count <> xxxH.Count Then

            Keys.Clear()

            Dim InvalidField As New Exception("没有您设置的字段:")

            Throw InvalidField

        Else

            Keys.Clear()

            Try

                dsSelect = SqlHelper.ExecuteDataset(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Select(xxxH, TableName))

            Catch ex As Exception

                Throw ex

            End Try

        End If

 

        Dim eachSelect As Data.DataRow

        Dim fieldName As String

        Dim DBfieldName As String

 

        For Each eachSelect In dsSelect.Tables(0).Rows

            Dim newObject As Object = System.Activator.CreateInstance(ReturnType)

            For Each eachRow In dsDB.Tables(TableName).Rows

                fieldName = CType(eachRow.Item("name"), String).Trim

                DBfieldName = CType(eachRow.Item("dbname"), String).Trim

                CallByName(newObject, fieldName, CallType.Set, CType(eachSelect.Item(DBfieldName), String).Trim)

            Next

            alOut.Add(newObject)

            newObject = Nothing

        Next

        Return alOut

    End Function

 

    Public Shared WriteOnly Property SelectKeys(ByVal KeyName As String)

        Set(ByVal Value As Object)

            Keys.Add(KeyName.Trim.ToLower, Value)

        End Set

    End Property

 

    ' 下面4个方法用来移动记录

    ' 移动记录安主键的大小顺序移动，只能对有且仅有一个主键的表操作

    ' 对于组合主键，返回 Nothing

    ' 当记录移动到头或末尾时 返回 Noting,当表为空时，First,Last 均返回Nothing

    Public Shared Function First(ByVal o As Object) As Object

        Return Move("first", o)

    End Function

 

    Public Shared Function Last(ByVal o As Object) As Object

        Return Move("last", o)

    End Function

 

    Public Shared Function Previous(ByVal o As Object) As Object

        Return Move("previous", o)

    End Function

 

    Public Shared Function [Next](ByVal o As Object) As Object

        Return Move("next", o)

    End Function

 

    ' 返回一个表的主键的数量，keyName,keyDBName 记录的是最后一个主键

    Private Shared Function getKey(ByRef keyName As String, ByRef keyDBName As String, ByVal TableName As String) As Int16

        Dim keyNum As Int16 = 0

        Dim dsDB As New DataSet

        dsDB.ReadXml(clsPersistant.DBConfigPath)

        Dim row As Data.DataRow

        For Each row In dsDB.Tables(TableName).Rows

            If row.Item("key") = "1" Then

                keyNum = keyNum + 1

                keyName = CType(row.Item("name"), String).Trim

                keyDBName = CType(row.Item("dbname"), String).Trim

                Exit For

            End If

        Next

        Return keyNum

    End Function

 

    ' 为 First,Previous,Next,Last 提供通用函数

    Private Shared Function Move(ByVal Type As String, ByVal o As Object) As Object

        Dim moveSQL As String

        Select Case Type.Trim.ToLower

            Case "first"

                moveSQL = SQLBuilder.First(o)

            Case "last"

                moveSQL = SQLBuilder.Last(o)

            Case "previous"

                moveSQL = SQLBuilder.Previous(o)

            Case "next"

                moveSQL = SQLBuilder.Next(o)

        End Select

 

        Dim typeString As String = o.GetType.ToString

        Dim i As Int16

        i = typeString.IndexOf("cls") + 3

        typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

        Dim TableName As String = typeString

 

        Dim keyName As String

        Dim keyDBName As String

        Dim tmpString As String

        If getKey(keyName, keyDBName, TableName) = 1 Then

            Keys.Clear()

            Dim ds As New Data.DataSet

            ds = SqlHelper.ExecuteDataset(SqlHelper.cnnString, CommandType.Text, moveSQL)

            If ds.Tables(0).Rows.Count = 0 Then

                Return Nothing

            Else

                tmpString = CType(ds.Tables(0).Rows(0).Item(keyDBName), String).Trim

                Keys.Add(keyName.Trim.ToLower, tmpString)

                Dim al As New ArrayList

                al = [Select](o.GetType)

                If al.Count = 1 Then

                    Return al.Item(0)

                Else

                    Return Nothing

                End If

            End If

        Else

            Return Nothing

        End If

    End Function

 

End Class