' clsdataaccessoper 該類是所有數(shù)據(jù)訪問類的父類
' by yujun
‘ www.hahait.com
‘ [email protected]
public class clsdataaccessoper
' 當(dāng)update,delete,add方法操作失敗返回 false 時(shí)，記錄出錯(cuò)的信息
public shared modifyerrorstring as string
private shared keys as new hashtable
' 數(shù)據(jù)庫連接字符串
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 不更新主鍵，包括聯(lián)合主鍵
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
' 通用數(shù)據(jù)庫查詢方法
' 重載方法用于明確指定要操作的數(shù)據(jù)庫表名稱
' 否則會以 returntype 的類型描述得到要操作的數(shù)據(jù)庫表的名稱 eg: returntype="clsrooms" ,得道 tablename="tbl_rooms"
' 該查詢方法將查詢條件添加到 keys(hashtable) 中，然后調(diào)用 select 方法返回 對象的集合
' 當(dāng)keys包含特殊鍵時(shí)，將要處理的是復(fù)雜類型的查詢,見 sqlbuilder 的 complexsql 說明
' 該方法可以拓展數(shù)據(jù)訪問類的固定查詢方法
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("沒有您設(shè)置的字段:")
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個(gè)方法用來移動記錄
' 移動記錄安主鍵的大小順序移動，只能對有且僅有一個(gè)主鍵的表操作
' 對于組合主鍵，返回 nothing
' 當(dāng)記錄移動到頭或末尾時(shí) 返回 noting,當(dāng)表為空時(shí)，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
' 返回一個(gè)表的主鍵的數(shù)量，keyname,keydbname 記錄的是最后一個(gè)主鍵
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 提供通用函數(shù)
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
