轻松搞定数据访问层[续2]
' clsDataAccessOper 该类是所有数据访问类的父类
' by YuJun
‘ 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
