轻松搞定数据访问层[续1]
' clsSQLBuilder
‘ By YuHonglai
‘ 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
