輕鬆搞定資料訪問層[續2]

來源:互聯網
上載者:User
訪問|資料 ' 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



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。