' 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
