' clsSQLBuilder
‘ By YuHonglai
' 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 = ""
Di