下面實現的方法，可以把你從sql的add,delete,update,select的重復勞動解脫出來
1。實體類
2。訪問類
現在以下表為例
tblperson
(perid,pername,pergender,perold,pernation)
實體類
person
---------
id
name
gender
old
nation
訪問基類
dataoper
------------
shared delete
shared add
shared update
shared select
訪問類
personoper:dataoper
--------------------
shared getall
shared getpersonbyid
shared getpersonbyname
shared get......
將數據庫表的信息導入到db.xml文件中
根據db.xml文件生成實體類
例如上面的tblperson表對應的db.xml中的一個表如下
name dbname key seed type
--------------------------------------
id perid 1 1 integer
name pername 0 0 string
gender pergender 0 0 boolean
old perold 0 0 integer
nation pernation 0 0 string
將從數據庫返回的dataset轉換成實體類，如果用一般方式寫的話：
ds=cmd.execute("select * from tblperson where id=1")
dim p as new person
p.id=ctype(ds.tables(0).rows(0).item("perid"),integer)
p.name=...
p.gender=...
p.old=...
p.nation=...
一張表的轉換還好辦，那么如果又幾十張表效率顯得地下了
這里又另外一種通用的方法，利用.net提供的反射功能！
.net提供的反射功能可以在知道類方法或屬性的名稱的情況下，動態訪問類的方法。
知道怎么做了嗎？
前面我們有一個從數據庫對應的db.xml數據庫架構文件
這個文件和實體類的字段一一對應
所以我們只要遍歷這個架構文件，利用反射，就能輕松實現上述的賦值了
讓我們再看看訪問基類
dataoper
--------------
delete(o as object)
add(o as object)
update(o as object)
選擇add(o as object)為例說明
public sub add(o as object)
dim type as string=o.gettype.tostring ' 得到傳進來的對象的類型
... ' 對象類型的字符串和剛才那個xml架構文件中表的名稱一一映射
dim schematablename as string=getschematablename(type) ' 取得需要操作的表的名稱 及從 person->tblperson
' 打開數據庫架構文件
dim dsschema as new dataset("db.xml")
dim sql as string="insert into " & schematablename
dim r as datarow
for each r in dsschema.tables(schematablename)
' 在這里操作上面的sql語句，值的部分利用反射從o中取得，建議寫一個專門身成特定對象特定操作的sql語句的類
' 處理一些特殊情況，如主鍵，自動增加值字段等
' 如果在數據庫架構文件中存儲更多的架構信息，如最大值等，還可以完成一些數據驗證之類的操作
...
next
sqlhelper.excute(connectionstring,...text,sql)
end sub
delete/update的通用方法類似
這樣，如果你要添加一個person的話可以這樣
dim p as new person
p.name="hahasoft"
p.gender=1
p.old=20
...
personoper.add(p) ' 從dataoper繼承的方法,也可以這樣寫:dataoper.add(p)
如果要添加一個book 的話，可以這樣
dim b as new book
b.isbn=
b.name=
...
bookoper.add(b)
怎么樣，是不是很通用？
這樣寫成一個通用類，可以完成所有的實體類的add/update/delete操作
要注意的是，db.xml數據庫架構文件很重要
通用實體類操作完成了，下面是通用的 select 方法
以前在csdn上看到過這樣的文章:
personoper.keys("name")="hahasoft"
personoper.keys("gender")=1
dim p as new person=personoper.select()
這樣就能返回相應的person的實體類
也可以變相完成一些方法：如
getpersonbyid(id)
getpersonbyname()
......
后來苦想一陣，終于實現了.
注：(這里實現的單表的o-r映射，如果對有關聯的表的話，只要聲明一個如 aladdress as arraylist 的成員就可以了，當然還涉及一些數據
晚期填充以提高效率的技巧，在這里就不詳細介紹了)
數據庫表
tbl_teacher
數據字段名稱
類型
說明
teaid
int
自動編號
teacode
char(20)
教師員工號
teaname
nchar(10)
?
teagender
bit
?
teanation
nchar(6)
?
teaage
tinyint
?
tbl_student
數據字段名稱
類型
說明
stuid
int
自動編號
stucode
char(20)
學生證號
stuteachercode
char(20)
班主任的員工號
stuname
nchar(10)
姓名
stugender
bit
性別
stunation
nchar(6)
民族
stuage
tinyint
年齡
stuclassid
int
班級的ｉｄ
用以描述這兩張表的xml文件，該xml文件存放的是這兩種表的結構信息
假設該文件的名稱位? db.xml
tbl_teacher
name
dbname
type
seed
key
id
teaid
integer
1
1
code
teacode
string
0
0
name
teaname
string
0
0
gender
teagender
boolean
0
0
nation
teanation
string
0
0
age
teaage
integer
0
0
tbl_student
name
dbname
type
seed
key
id
stuid
integer
1
1
code
stucode
string
0
0
teachercode
stuteachercode
string
0
0
name
stuname
string
0
0
gender
stugender
boolean
0
0
nation
stunation
string
0
0
age
stuage
integer
0
0
classid
stuclassid
integer
0
0
必須的類
clssqlhelper:微軟的數據訪問輔助類
clssqlbuilder:根據配置文件生成相應sql語句的類 見輕松搞定數據訪問層[續1]
clsdataaccessoper:所以操作類的父類，提供通用的數據操作方法 見輕松搞定數據訪問層[續2]
clsdataaccess:暫時沒有什么用
數據類和訪問類
實體類clsteacher 的屬性(property),與數據庫字段一一對應
property id as integer
property code as string
property name as string
property gender as boolean
property nation as string
property age as integer
實體類clsstudent
property id as integer
property code as string
property teachercode as string
property name as string
property gender as boolean
property nation as string
property age as integer
property classid as integer
注意:上面兩個類的具體代碼就不用寫了吧
訪問類clsteacheroper 從 clsdataaccessoper 繼承
public class clsteacheroper
public function getall() as arraylist
return clsdataaccessoper.select((new clsteacher).gettype)
end function
public function getteacherbycode(code as string) as clsteacher
sdataaccessoper.selectkeys("code") = code
return clsdataaccessoper.select((new clsteacher).gettype).item(0)
end function
'以下方法類似，實現的是一些如 getxxx by yyy 的查詢
'據操作如添加/刪除/修改全部從 clsdataaccessoper 中繼承
'這里的查詢方法都可以用 clsdataaccessoper.selectkeys(“”)=… 實現
'只是為了提供更友好的接口,如果你時間緊迫可以把
' clsdataaccessoper.selectkeys/select 方法提交給邏輯層或表示層的程序員
end class
訪問類 clsstudentoper從 clsdataaccessoper 繼承
(同 clsteacheroper 類)
上篇<<輕松搞定數據訪問層>>文章有人說我代碼太少，看不懂。其實我只想說明一下道理。
現在，我貼出 clssqlbuilder clsdataaccessoper 這兩個核心類的全部代碼。這也只是想更明白的說清楚道理。這兩個類還是第一版本，代碼質量不是很高，一些異常沒有拋出.只是實現了一些功能。
順便提到的是，實體類可以通過數據庫腳本生成，這個工具當然是要自己寫的了。工具很簡單，兩三百行就可以搞定。還有那個數據庫結構信息的xml文件自己寫也很麻煩，所以也可以寫一個自動生成它的工具。
這都不是這個文章討論的重點，這里就不介紹怎樣才能生成那些模樣幾乎相同的代碼了。
這種數據訪問方法的優點在于：
如果你的數據庫有變動，比如說tbl_teahcer 表加了一個字段職稱(teatitle)，那么你只需要在 clsteacher 表里加一個屬性 property title，然后在 db.xml 文件中的 tblteacher 里加一條記錄 title|teatitle|string|0|0就可以了。
編碼速度快，除了這兩個核心類，數據實體類可以自動生成，訪問類也只是簡單的寫一些查詢方法.
不用寫sql語句。
使用方法舉例：
添加一個教師
dim newteacher as new clsteacher
with newteahcer
‘ 此處如果有id的賦值，將被忽略，因為是自動增加值。見 clssqlbuilder
.name=”haha”
.code=” 2001”
.gender=true
…
end with
clsteacheroper.add(newteacher)
添加一個學生
dim newstudent as new clsstudent
with newstudent
.name=”tom”
.gender=true
.teachercode=” 2001”
…
end with
clsstudentoper.add(newstudent)
更新刪除類似(這里就不舉例了)。
現在再就添加一個教師的程序流程大概介紹一下。
當執行 clsteacheroper.add(newteancher) 后，clsdataaccessoper.add 會把 newteacher繼續傳遞給 clssqlbuilder.add() 方法,在這個方法里,clssqlbuilder首先取得該對象的類型
在這里是 “clsteacher” 這個類型字符串和 db.xml 文件中 tblteacher 有一一對應的關系，這里是去掉前綴 cls,加上前綴 tbl.你也可以用其它更靈活的方法,如:把映射關系記錄到文件中.
然后遍歷db.xml文件中的tblteacher 表的所有行,其中用到反射方法,以 在知道對象屬性名稱的情況下取得該屬性的值(這其中我也波折了一下,開始用 invokemember調用,好麻煩.最后發現竟然有一個 callbyname的方法,用起來特簡單.其實它也是封裝了invokemember,).最終生成這樣的sql語句返回.
insert into tblteacher(name,code,gender…) values (‘haha’,’ 2001’ ,1…)
(有幾點要注意,在add/delete/update不同的操作方法里面,對數據表字段有不同的要求.比如在添加時,不能對自動編號id賦值,所以上面的sql語句就沒有id字段.這里我只用了seek和key的字段屬性,其實還可以添加其它屬性,如數字型的最大值,字符型驗證的正則表達式.以在clssqlbuilder中完成數據驗證操作)
最后clsdataaccessoper.add 方法用這個sql語句連接數據庫,進行操作.
clsdataaccessoper和clssqlbuilder我就不詳細解說了.代碼都貼出來了,自己看吧
' 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
