' 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