- 1PS提示：因為圖層已鎖定，無法編輯圖層的處理方法
- 2Adobe Illustrator CS5 序列號大全
- 3picacg蘋果版怎么找不到|picacg ios版是不是下架了介紹
- 4ACDsee注冊碼免費分享（含ACDsee18、ACDsee10等版本）
- 5眼萌app怎么色弱測試 眼萌app色弱測試圖文說明教程
- 6蘋果iphone自動時間不準確的多種校正方法
- 7百度安全衛士好用么_百度安全衛士技巧說明
- 8PDF瀏覽器能看3D文件嗎？PDF瀏覽器看3D文件圖文詳細教程
- 9Potato(馬鈴薯聊天)怎么注冊不了|Potato不能注冊處理方法介紹
- 10Potato(土豆聊天)怎么換頭像|Potato app更改頭像方法介紹
- 11ipad版office如何激活? office365激活的圖文說明教程
- 12最新的Adobe Illustrator CS4序列號大全
[摘要]' clsSQLBuilder‘ By YuHonglai‘ www.hahaIT.com‘ hahasoft@msn.com ' Note:提供重載方法以指明表名稱，默認情況下，是傳入參數 o 的類型＋"tbl_",此時類名稱必須是 clsXXX 的形式.&#...
' clsSQLBuilder‘ By YuHonglai
‘ www.hahaIT.com
‘ hahasoft@msn.com
' Note:提供重載方法以指明表名稱，默認情況下，是傳入參數 o 的類型＋"tbl_",此時類名稱必須是 clsXXX 的形式.
' 如：
' dim Rooms as new clsRooms
' SQLBuilder.Add(Rooms)
' 此時程序將把 clsRooms 轉換成 tbl_Rooms,以操作數據庫表 tbl_Rooms
' 如果類名稱和數據庫表名稱不具有上述對應關系，請使用 Add(o,"TableName")形式的方法，以顯示指定要操作的數據庫表的名稱
Public Class SQLBuilder
' 當要生成的SQL語句的 where 條件語句很復雜時，用該常量作為 Select 方法中 FindCondition(HashTable)
' Key,例如：要生成 where Birth<'2000-4-4' and Birth>'1980-1-1' 的復雜條件時，用以下方法:
' Dim h as new HashTable
' h.Add(ComplexSQL,"_Birth<'2000-4-4' and _Birth>'1980-1-1'")
' 注意，Birth是實體類的屬性名稱，前面必須有一個下劃線 "_"
' 處理時，程序將用實際數據庫字段名稱代替相應的 _Birth
Public Const ComplexSQL As String = "@ComplexSQL"
' 根具實體類生成相應的 Insert ...SQL 語句
' 如果與數據庫表名稱對應的屬性時 關鍵字段而且是自動增加值時(在DB.XML文件中seed的值為 1 )
' 那么該屬相將忽略,不會出現在返回的 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 方法，關鍵字段不會更新
' 而且關鍵字段會作為 update....where .... 的 where 的條件出現
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("必須指定一個以上的主鍵!")
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
' 更具對象的關鍵屬性(與數據庫表的關鍵字段對應)刪除指定的記錄
' 對象的其他屬性將被忽略
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("必須指定一個以上的主鍵!")
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
' 更具對象的關鍵屬性(與數據庫表的關鍵字段對應)判斷該對象是否存在于數據庫中
' 對象的其他屬性將被忽略
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("必須指定一個以上的主鍵!")
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 ' 處理復雜類型的 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
' 返回指定的字段(數據庫表字段的名稱)的數據類型名稱(VB數據類型)
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
' 根具數據類型名稱，返回空或' SQL語句中數字和字符型是否被‘括起來
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
推薦資訊 總人氣榜
最新教程 本月人氣
- 1JDBC 示例（轉）
- 2Microsoft IIS 真的如此「不安全」嗎？(2)
- 3Beginner with c# 2
- 4用Visual Basic也可以完成“P2P”
- 5用PHP取Select影響行數的方法
- 6Microsoft IIS 真的如此「不安全」嗎？(3)
- 7Java優化性能技巧集錦（2）
- 8測試已經無誤，重新公布的更改后的分頁類，附點說明
- 9Microsoft IIS 真的如此「不安全」嗎？(4)
- 10來遲了，人不多：為什么開發人員對XML感到激動
- 11PHP 開發人員：充實您的 XML 工具箱
- 12在WinForm中使用Web Services 來完成 軟件 自動升級( Auto Update ) (C#)
