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

' by YuJun

‘ www.hahaIT.com

‘ hahasoftmsn.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