' 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
