' clsSQLBuilder
‘ By YuHonglai
‘ www.hahaIT.com
‘ [email protected]
' 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
' clsDataAccessOper 该类是所有数据访问类的父类
' by YuJun
‘ www.hahaIT.com
‘ [email protected]
Public Class clsDataAccessOper
' 当Update,Delete,Add方法操作失败返回 False 时，记录出错的信息
Public Shared ModifyErrorString As String
Private Shared Keys As New Hashtable
' 数据库连接字符串
Public Shared Property ConnectionString() As String
Get
Return SqlHelper.cnnString.Trim
End Get
Set(ByVal Value As String)
SqlHelper.cnnString = Value.Trim
End Set
End Property
' Update 不更新主键，包括联合主键
Public Shared Function Update(ByVal o As Object) As Boolean
ModifyErrorString = ""
Try
If CType(SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Exists(o)), Int64) = 0 Then
Throw New Exception("该记录不存在!")
End If
Catch ex As Exception
Throw ex
End Try
Try
SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Update(o))
Catch ex As Exception
ModifyErrorString = ex.Message
Return False
End Try
Return True
End Function
' Delete 将忽略
Public Shared Function Delete(ByVal o As Object) As Boolean
ModifyErrorString = ""
Try
SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Delete(o))
Catch ex As Exception
ModifyErrorString = ex.Message
Return False
End Try
Return True
End Function
' Add 方法将忽略自动增加值的主键
Public Shared Function Add(ByVal o As Object) As Boolean
ModifyErrorString = ""
Try
SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Add(o))
Catch ex As Exception
ModifyErrorString = ex.Message
Return False
End Try
Return True
End Function
' 通用数据库查询方法
' 重载方法用于明确指定要操作的数据库表名称
' 否则会以 ReturnType 的类型描述得到要操作的数据库表的名称 eg: ReturnType="clsRooms" ,得道 TableName="tbl_Rooms"
' 该查询方法将查询条件添加到 Keys(HashTable) 中，然后调用 Select 方法返回 对象的集合
' 当Keys包含特殊键时，将要处理的是复杂类型的查询,见 SQLBuilder 的 ComplexSQL 说明
' 该方法可以拓展数据访问类的固定查询方法
Public Overloads Shared Function [Select](ByVal ReturnType As Type) As ArrayList
Dim tableName As String
tableName = ReturnType.Name
Dim i As Int16
i = tableName.IndexOf("cls") + 3
tableName = "tbl_" & tableName.Substring(i, tableName.Length - i)
Return [Select](ReturnType, tableName)
End Function
Public Overloads Shared Function [Select](ByVal ReturnType As Type, ByVal TableName As String) As ArrayList
Dim alOut As New ArrayList
Dim dsDB As New Data.DataSet
dsDB.ReadXml(clsPersistant.DBConfigPath)
Dim xxxH As New Hashtable
Dim eachRow As Data.DataRow
For Each eachRow In dsDB.Tables(TableName).Rows
If Keys.Contains(CType(eachRow.Item("name"), String).ToLower.Trim) Then
xxxH.Add(CType(eachRow.Item("dbname"), String).ToLower.Trim, Keys(CType(eachRow.Item("name"), String).Trim.ToLower))
End If
Next
' 检查 Keys 的合法性
Dim dsSelect As New Data.DataSet
If Keys.Count <> xxxH.Count Then
Keys.Clear()
Dim InvalidField As New Exception("没有您设置的字段:")
Throw InvalidField
Else
Keys.Clear()
Try
dsSelect = SqlHelper.ExecuteDataset(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Select(xxxH, TableName))
Catch ex As Exception
Throw ex
End Try
End If
Dim eachSelect As Data.DataRow
Dim fieldName As String
Dim DBfieldName As String
For Each eachSelect In dsSelect.Tables(0).Rows
Dim newObject As Object = System.Activator.CreateInstance(ReturnType)
For Each eachRow In dsDB.Tables(TableName).Rows
fieldName = CType(eachRow.Item("name"), String).Trim
DBfieldName = CType(eachRow.Item("dbname"), String).Trim
CallByName(newObject, fieldName, CallType.Set, CType(eachSelect.Item(DBfieldName), String).Trim)
Next
alOut.Add(newObject)
newObject = Nothing
Next
Return alOut
End Function
Public Shared WriteOnly Property SelectKeys(ByVal KeyName As String)
Set(ByVal Value As Object)
Keys.Add(KeyName.Trim.ToLower, Value)
End Set
End Property
' 下面4个方法用来移动记录
' 移动记录安主键的大小顺序移动，只能对有且仅有一个主键的表操作
' 对于组合主键，返回 Nothing
' 当记录移动到头或末尾时 返回 Noting,当表为空时，First,Last 均返回Nothing
Public Shared Function First(ByVal o As Object) As Object
Return Move("first", o)
End Function
Public Shared Function Last(ByVal o As Object) As Object
Return Move("last", o)
End Function
Public Shared Function Previous(ByVal o As Object) As Object
Return Move("previous", o)
End Function
Public Shared Function [Next](ByVal o As Object) As Object
Return Move("next", o)
End Function
' 返回一个表的主键的数量，keyName,keyDBName 记录的是最后一个主键
Private Shared Function getKey(ByRef keyName As String, ByRef keyDBName As String, ByVal TableName As String) As Int16
Dim keyNum As Int16 = 0
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
keyNum = keyNum + 1
keyName = CType(row.Item("name"), String).Trim
keyDBName = CType(row.Item("dbname"), String).Trim
Exit For
End If
Next
Return keyNum
End Function
' 为 First,Previous,Next,Last 提供通用函数
Private Shared Function Move(ByVal Type As String, ByVal o As Object) As Object
Dim moveSQL As String
Select Case Type.Trim.ToLower
Case "first"
moveSQL = SQLBuilder.First(o)
Case "last"
moveSQL = SQLBuilder.Last(o)
Case "previous"
moveSQL = SQLBuilder.Previous(o)
Case "next"
moveSQL = SQLBuilder.Next(o)
End Select
Dim typeString As String = o.GetType.ToString
Dim i As Int16
i = typeString.IndexOf("cls") + 3
typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)
Dim TableName As String = typeString
Dim keyName As String
Dim keyDBName As String
Dim tmpString As String
If getKey(keyName, keyDBName, TableName) = 1 Then
Keys.Clear()
Dim ds As New Data.DataSet
ds = SqlHelper.ExecuteDataset(SqlHelper.cnnString, CommandType.Text, moveSQL)
If ds.Tables(0).Rows.Count = 0 Then
Return Nothing
Else
tmpString = CType(ds.Tables(0).Rows(0).Item(keyDBName), String).Trim
Keys.Add(keyName.Trim.ToLower, tmpString)
Dim al As New ArrayList
al = [Select](o.GetType)
If al.Count = 1 Then
Return al.Item(0)
Else
Return Nothing
End If
End If
Else
Return Nothing
End If
End Function
End Class
本类阅读TOP10