簡單搞定數(shù)據(jù)訪問層[續(xù)1]
發(fā)表時(shí)間：2024-02-19 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]' clsSQLBuilder‘ By YuHonglai‘ www.hahaIT.com‘ hahasoft@msn.com ' Note:提供重載方法以指明表名稱，默認(rèn)情況下，是傳入?yún)?shù) o 的類型＋"tbl_",此時(shí)類名稱必須是 clsXXX 的形式.&#...
' clsSQLBuilder
‘ By YuHonglai
‘ www.hahaIT.com
‘ hahasoft@msn.com
' Note:提供重載方法以指明表名稱，默認(rèn)情況下，是傳入?yún)?shù) o 的類型＋"tbl_",此時(shí)類名稱必須是 clsXXX 的形式.
' 如：
' dim Rooms as new clsRooms
' SQLBuilder.Add(Rooms)
' 此時(shí)程序?qū)?clsRooms 轉(zhuǎn)換成 tbl_Rooms,以操作數(shù)據(jù)庫表 tbl_Rooms
' 如果類名稱和數(shù)據(jù)庫表名稱不具有上述對(duì)應(yīng)關(guān)系，請(qǐng)使用 Add(o,"TableName")形式的方法，以顯示指定要操作的數(shù)據(jù)庫表的名稱
Public Class SQLBuilder
' 當(dāng)要生成的SQL語句的 where 條件語句很復(fù)雜時(shí)，用該常量作為 Select 方法中 FindCondition(HashTable)
' Key,例如：要生成 where Birth<'2000-4-4' and Birth>'1980-1-1' 的復(fù)雜條件時(shí)，用以下方法:
' Dim h as new HashTable
' h.Add(ComplexSQL,"_Birth<'2000-4-4' and _Birth>'1980-1-1'")
' 注意，Birth是實(shí)體類的屬性名稱，前面必須有一個(gè)下劃線 "_"
' 處理時(shí)，程序?qū)⒂脤?shí)際數(shù)據(jù)庫字段名稱代替相應(yīng)的 _Birth
Public Const ComplexSQL As String = "@ComplexSQL"
' 根具實(shí)體類生成相應(yīng)的 Insert ...SQL 語句
' 如果與數(shù)據(jù)庫表名稱對(duì)應(yīng)的屬性時(shí) 關(guān)鍵字段而且是自動(dòng)增加值時(shí)(在DB.XML文件中seed的值為 1 )
' 那么該屬相將忽略,不會(huì)出現(xiàn)在返回的 Insert... SQL語句中
Public Overloads Shared Function Add(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return Add(o, typeString)
End Function
Public Overloads Shared Function Add(ByVal o As Object, ByVal TableName As String) As String
Try
Dim outSQL As String
Dim tmpString As String
outSQL = "insert into [" & TableName & "]("
tmpString = ""
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("seed") & "" = "0" Then
outSQL = outSQL & row.Item("dbname") & ","
tmpString = tmpString & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & ","
End If
Next
outSQL = outSQL.Substring(0, outSQL.Length - 1)
tmpString = tmpString.Substring(0, tmpString.Length - 1)
outSQL = outSQL & ") values (" & tmpString & ")"
For Each row In dsDB.Tables(TableName).Rows
If row.Item("seed") & "" <> "1" Then
' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""
tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""
If tmpString = "True" Then
tmpString = "1"
ElseIf tmpString = "False" Then
tmpString = "0"
End If
outSQL = outSQL.Replace("@" & row.Item("dbname"), tmpString)
End If
Next
Return outSQL.Trim
Catch ex As Exception
Throw ex
End Try
End Function
' 如 Add 方法，關(guān)鍵字段不會(huì)更新
' 而且關(guān)鍵字段會(huì)作為 update....where .... 的 where 的條件出現(xiàn)
Public Overloads Shared Function Update(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return Update(o, typeString)
End Function
Public Overloads Shared Function Update(ByVal o As Object, ByVal TableName As String) As String
Try
Dim outString As String = ""
Dim tmpString As String
outString = "update [" & TableName & "] set "
tmpString = ""
Dim whereString As String = ""
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
whereString = whereString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & " and "
Else
tmpString = tmpString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & ","
End If
Next
If whereString.Trim = "" Then
Throw New Exception("必須指定一個(gè)以上的主鍵!")
End If
tmpString = tmpString.Substring(0, tmpString.Length - 1)
whereString = whereString.Substring(0, whereString.Length - 4)
outString = outString & tmpString & " where " & whereString
For Each row In dsDB.Tables(TableName).Rows
' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""
tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""
If tmpString = "True" Then
tmpString = "1"
ElseIf tmpString = "False" Then
tmpString = "0"
End If
outString = outString.Replace("@" & row.Item("dbname"), tmpString)
Next
Return outString.Trim
Catch ex As Exception
Throw ex
End Try
End Function
' 更具對(duì)象的關(guān)鍵屬性(與數(shù)據(jù)庫表的關(guān)鍵字段對(duì)應(yīng))刪除指定的記錄
' 對(duì)象的其他屬性將被忽略
Public Overloads Shared Function Delete(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return Delete(o, typeString)
End Function
Public Overloads Shared Function Delete(ByVal o As Object, ByVal TableName As String) As String
Try
Dim outString As String = ""
Dim tmpString As String
outString = "delete from [" & TableName & "] where "
Dim whereString As String = ""
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
whereString = whereString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & " and "
End If
Next
If whereString.Trim = "" Then
Throw New Exception("必須指定一個(gè)以上的主鍵!")
End If
whereString = whereString.Substring(0, whereString.Length - 4)
outString = outString & tmpString & whereString
For Each row In dsDB.Tables(TableName).Rows
If row.Item("key") & "" = "1" Then
' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""
tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""
outString = outString.Replace("@" & row.Item("dbname"), tmpString)
End If
Next
Return outString.Trim
Catch ex As Exception
Throw ex
End Try
End Function
' 更具對(duì)象的關(guān)鍵屬性(與數(shù)據(jù)庫表的關(guān)鍵字段對(duì)應(yīng))判斷該對(duì)象是否存在于數(shù)據(jù)庫中
' 對(duì)象的其他屬性將被忽略
Public Overloads Shared Function Exists(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return Exists(o, typeString)
End Function
Public Overloads Shared Function Exists(ByVal o As Object, ByVal TableName As String) As String
Try
Dim outString As String
outString = "select count(*) from [" & TableName & "] where "
Dim tmpString As String
Dim whereString As String = ""
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
whereString = whereString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & " and "
End If
Next
If whereString.Trim = "" Then
Throw New Exception("必須指定一個(gè)以上的主鍵!")
End If
whereString = whereString.Substring(0, whereString.Length - 4)
outString = outString & tmpString & whereString
For Each row In dsDB.Tables(TableName).Rows
If row.Item("key") & "" = "1" Then
' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""
tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""
outString = outString.Replace("@" & row.Item("dbname"), tmpString)
End If
Next
Return outString.Trim
Catch ex As Exception
Throw ex
End Try
End Function
' 生成 First SQL語句
Public Overloads Shared Function First(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return First(typeString)
End Function
Public Overloads Shared Function First(ByVal TableName As String) As String
Dim MoudleSQL As String
MoudleSQL = "select * from [%TableName%] where [%key%] in(select Min([%key%]) from [%TableName%])"
Dim key As String
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
key = CType(row.Item("dbname"), String).Trim
Exit For
End If
Next
MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)
MoudleSQL = MoudleSQL.Replace("%key%", key)
Return MoudleSQL
End Function
Public Overloads Shared Function Last(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return Last(typeString)
End Function
Public Overloads Shared Function Last(ByVal TableName As String) As String
Dim MoudleSQL As String
MoudleSQL = "select * from [%TableName%] where [%key%] in(select Max([%key%]) from [%TableName%])"
Dim key As String
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
key = CType(row.Item("dbname"), String).Trim
Exit For
End If
Next
MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)
MoudleSQL = MoudleSQL.Replace("%key%", key)
Return MoudleSQL
End Function
Public Overloads Shared Function Previous(ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return Previous(o, typeString)
End Function
Public Overloads Shared Function Previous(ByVal o As Object, ByVal TableName As String) As String
Dim MoudleSQL As String
MoudleSQL = "select * from [%TableName%] where [%key%] in(select Max([%key%]) from [%TableName%] where [%key%]<%keyValue%)"
Dim key As String
Dim propertyName As String
Dim propertyValue As String
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
key = CType(row.Item("dbname"), String).Trim
propertyName = CType(row.Item("name"), String).Trim
Exit For
End If
Next
' propertyValue = o.GetType.InvokeMember(propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""
propertyValue = CallByName(o, propertyName, CallType.Get) & ""
MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)
MoudleSQL = MoudleSQL.Replace("%key%", key)
MoudleSQL = MoudleSQL.Replace("%keyValue%", propertyValue)
Return MoudleSQL
End Function
Public Overloads Shared Function [Next](ByVal o As Object) As String
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Return [Next](o, typeString)
End Function
Public Overloads Shared Function [Next](ByVal o As Object, ByVal TableName As String) As String
Dim MoudleSQL As String
MoudleSQL = "select * from [%TableName%] where [%key%] in(select Min([%key%]) from [%TableName%] where [%key%]>%keyValue%)"
Dim key As String
Dim propertyName As String
Dim propertyValue As String
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
key = CType(row.Item("dbname"), String).Trim
propertyName = CType(row.Item("name"), String).Trim
Exit For
End If
Next
' propertyValue = o.GetType.InvokeMember(propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""
propertyValue = CallByName(o, propertyName, CallType.Get) & ""
MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)
MoudleSQL = MoudleSQL.Replace("%key%", key)
MoudleSQL = MoudleSQL.Replace("%keyValue%", propertyValue)
Return MoudleSQL
End Function
' 見 public const ComplexSQL 的說明
Public Shared Function [Select](ByVal FindCondition As Hashtable, ByVal TableName As String) As String
Dim outSQL As String
If FindCondition.Contains(ComplexSQL) Then ' 處理復(fù)雜類型的 Where 從句
outSQL = "select * from [" & TableName & "] where " & FindCondition(ComplexSQL)
Dim row As Data.DataRow
Dim dsDB As New DataSet
dsDB.ReadXml(clsPersistant.DBConfigPath)
For Each row In dsDB.Tables(TableName).Rows
outSQL = outSQL.Replace("_" & CType(row.Item("name"), String).Trim, "[" & CType(row.Item("dbname"), String).Trim & "]")
Next
Else
outSQL = "select * from [" & TableName & "] where "
Dim whereString As String = ""
Dim eachKey As Object
For Each eachKey In FindCondition.Keys
whereString = whereString & CType(eachKey, String) & "=" & getS(getTypeByName(TableName, CType(eachKey, String))) & FindCondition(eachKey) & getS(getTypeByName(TableName, CType(eachKey, String))) & " and "
Next
If whereString.Length = 0 Then
whereString = "0=0"
Else
whereString = whereString.Substring(0, whereString.Length - 5)
End If
outSQL = outSQL & whereString
End If
Return outSQL
End Function
' 返回指定的字段(數(shù)據(jù)庫表字段的名稱)的數(shù)據(jù)類型名稱(VB數(shù)據(jù)類型)
Private Shared Function getTypeByName(ByVal TableName As String, ByVal n As String) As String
Dim outStr As String
Dim dsDB As New Data.DataSet
dsDB.ReadXml(clsPersistant.DBConfigPath)
Dim eachRow As DataRow
For Each eachRow In dsDB.Tables(TableName).Rows
If CType(eachRow.Item("dbname"), String).Trim.ToLower = n.Trim.ToLower Then
outStr = CType(eachRow.Item("type"), String).Trim.ToLower
Exit For
End If
Next
Return outStr
End Function
' 根具數(shù)據(jù)類型名稱，返回空或' SQL語句中數(shù)字和字符型是否被‘括起來
Private Shared Function getS(ByVal t As String) As String
Dim outString As String
t = t.ToLower.Trim
If t = "single" Or t = "int16" Or t = "int32" Or t = "int64" Or t = "double" Or t = "byte" Then
outString = ""
Return outString
ElseIf t = "date" Or t = "string" Then
outString = "'"
Return outString
End If
End Function
End Class
