IDC资讯中心
namespace tonton.dal
//—————————————-
//《数据链接层》 之 《数据连接与命令行》
//—————————————-
//作者：张少棠 （tonton）
//时间：2005年8月29日
//邮编：tonton@yeah.net
//主页：http://www.tonton.cn
//博客：http://blog.tonton.cn
//—————————————-
//—————————————-
//例子：
//—————————————-
dim conn as new tonton.dal.connection
dim cmd as tonton.dal.command
try
//打开access数据库，也可以用连接字符串并采用open方法，
//conn.open(“provider=microsoft.jet.oledb.4.0;data source=” & server.mappath(“db.mdb”))
//当然，如果知道是用access，你会用上面这个麻烦的方法吗？
//如果是sql server的话，可以用 conn.opensqlserver 方法。
conn.openaccess(server.mappath(“db.mdb”))
//添加记录
cmd = conn.execute(“insert into [item]([value]) values (?)”)
cmd.addparam(“添加的内容”)
cmd.update()
//删除记录
cmd = conn.execute(“delete from [item] where id=?”)
cmd.addparam(6) //要删除的记录号
cmd.update()
//更新记录
cmd = conn.execute(“update [item] set [value]=? where id=?”)
cmd.addparam(“新的内容”)
cmd.addparam(5)
cmd.update()
//读取记录
cmd = conn.execute(“select * from [item]”)
if cmd.read then
response.write(cmd(“value”))
else
response.write(“ok”)
end if
catch ex as exception
//出错处理
response.write(ex)
finally
关闭连接
conn.close()
cmd = nothing
end try
//—————————————-
//例子结束
//—————————————-
//—————————————-
//类定义开始
//—————————————-
数据连接类型枚举
public enum connectiontype as integer
oledb = 1
sqlserver = 2
oracle = 3
mysql = 4
end enum
连接字符串构造器类
public class connectstringbuilder
public shared function jetoledb(byval databasepath as string, optional byval password as string = “”) as string
if databasepath <> “” then
jetoledb = “provider=microsoft.jet.oledb.4.0;data source=” & databasepath & “;”
if password <> “” then
jetoledb &= “user id=admin;password=;jet oledb:database password=” & password
end if
end if
end function
public shared function sqloledb(optional byval hostname as string = “localhost”, optional byval catalog as string = “”, optional byval username as string = “sa”, optional byval password as string = “”) as string
sqloledb = “provider=sqloledb.1;persist security info=false;data source=” & hostname & “;password=” & password & “;user id=” & username & “;”
if catalog <> “” then sqloledb &= “initial catalog=” & catalog & “;”
end function
public shared function sqlclient(optional byval hostname as string = “localhost”, optional byval catalog as string = “”, optional byval username as string = “sa”, optional byval password as string = “”) as string
sqlclient = “persist security info=false;data source=” & hostname & “;password=” & password & “;user id=” & username & “;”
if catalog <> “” then sqlclient &= “initial catalog=” & catalog & “;”
end function
public shared function dsn(byval dsnname as string) as string
return “dsn=” & dsnname
end function
end class
连接对象类
public class connection
private _dbconn as idbconnection
private _connstr as string
private _dbtype as connectiontype = connectiontype.oledb
public sub new(optional byval connecttype as connectiontype = connectiontype.oledb)
_dbtype = connecttype
end sub
public sub new(byref connect as idbconnection)
if typeof connect is sqlclient.sqlconnection then
_dbtype = connectiontype.sqlserver
elseif typeof connect is oledb.oledbconnection then
_dbtype = connectiontype.oledb
end if
end sub
public sub new(byval connstring as string, optional byval connecttype as connectiontype = connectiontype.oledb)
_dbtype = connecttype
me.connectstring = connstring
end sub
设置/返回连接字符串，设置的同时生成新的连接对象实例
public property connectstring() as string
get
return _connstr
end get
set(byval value as string)
_connstr = value
try
_dbconn.close()
catch ex as exception
finally
if value <> “” then
select case _dbtype
case connectiontype.oledb
_dbconn = new oledb.oledbconnection(_connstr)
case connectiontype.sqlserver
_dbconn = new sqlclient.sqlconnection(_connstr)
case else
_dbconn = nothing
end select
else
_dbconn = nothing
end if
end try
end set
end property
设置/返回连接类型
public property connecttype() as connectiontype
get
return _dbtype
end get
set(byval value as connectiontype)
_dbtype = value
me.connectstring = _connstr
end set
end property
protected overrides sub finalize()
try
_dbconn.close()
_dbconn.dispose()
catch ex as exception
finally
mybase.finalize()
end try
end sub
返回连接对象
public readonly property connection() as idbconnection
get
return _dbconn
end get
end property
打开一个数据库连接，要指定连接类型（默认为oledb）
public function open(optional byval connstring as string = “”, optional byval conntype as connectiontype = connectiontype.oledb) as boolean
try
if connstring <> “” then
_connstr = connstring
_dbtype = conntype
end if
_dbconn.open()
return true
catch ex as exception
throw ex
return false
end try
end function
打开一个access连接
public function openaccess(byval dbpath as string, optional byval password as string = “”) as boolean
if dbpath <> “” then
try
_connstr = “provider=microsoft.jet.oledb.4.0;data source=” & dbpath & “;”
if password <> “” then
_connstr &= “user id=admin;password=;jet oledb:database password=” & password
end if
_dbtype = connectiontype.oledb
_dbconn = new oledb.oledbconnection(_connstr)
return true
catch ex as exception
throw ex
return false
end try
end if
end function
打开一个sql server连接
public function opensqlserver(optional byval hostname as string = “localhost”, optional byval catalog as string = “”, optional byval username as string = “sa”, optional byval password as string = “”) as boolean
try
_connstr = “persist security info=false;data source=” & hostname & “;password=” & password & “;user id=” & username & “;”
if catalog <> “” then _connstr &= “initial catalog=” & catalog & “;”
_dbtype = connectiontype.sqlserver
_dbconn = new sqlclient.sqlconnection(_connstr)
return true
catch ex as exception
throw ex
return false
end try
end function
关闭数据连接
public sub close()
try
_dbconn.close()
_dbconn = nothing
catch ex as exception
end try
end sub
执行一个sql语句，生成或不生成一个command对象
public function execute(byval sql as string, optional byval nonquery as boolean = false) as command
execute = new command(_dbconn, sql)
if nonquery then execute.update()
end function
生成一个dataset，不再需要手工生成dataadapter对象了。
public readonly property datasource(byval sql as string, optional byval name as string = “”, optional byval schema as boolean = false) as dataset
get
dim ds as idataadapter
select case _dbtype
case connectiontype.oledb
ds = new oledb.oledbdataadapter(sql, _dbconn)
case connectiontype.sqlserver
ds = new sqlclient.sqldataadapter(sql, _dbconn)
end select
if name = “” then
datasource = new dataset
else
datasource = new dataset(name)
end if
if schema then ds.fillschema(datasource, schematype.source)
ds.fill(datasource)
end get
end property
end class
命令行类
public class command
private _cmd as idbcommand
private _sql as string
private _conn as idbconnection
private _rdr as idatareader
private _dbtype as connectiontype
public sub new(byref connect as idbconnection)
_conn = connect
if typeof connect is oledb.oledbconnection then
_dbtype = connectiontype.oledb
elseif typeof connect is sqlclient.sqlconnection then
_dbtype = connectiontype.sqlserver
end if
end sub
public sub new(byref connect as idbconnection, byval sql as string)
_conn = connect
if typeof connect is oledb.oledbconnection then
_dbtype = connectiontype.oledb
elseif typeof connect is sqlclient.sqlconnection then
_dbtype = connectiontype.sqlserver
end if
me.sql = sql
end sub
protected overrides sub finalize()
try
_cmd.dispose()
_rdr.dispose()
_conn.dispose()
catch ex as exception
finally
mybase.finalize()
end try
end sub
设置或返回连接对象
public property connect() as idbconnection
get
return _conn
end get
set(byval value as idbconnection)
_conn = value
end set
end property
读取或设置sql语句
public property sql() as string
get
return _sql
end get
set(byval value as string)
_sql = value
if _dbtype = connectiontype.sqlserver then
_cmd = new sqlclient.sqlcommand(sql, _conn)
else
_cmd = new oledb.oledbcommand(_sql, _conn)
end if
end set
end property
读取下一记录行，如果记录集没有打开，则自动打开。
public function read(optional byval behavior as system.data.commandbehavior = commandbehavior.default) as boolean
if _rdr is nothing then
try
_rdr = _cmd.executereader(behavior)
return _rdr.read
catch ex as exception
return false
end try
return false
else
return _rdr.read
end if
end function
//返回字段数
public function fieldcount() as integer
try
return _rdr.fieldcount
catch ex as exception
return 0
end try
end function
//执行命令
public function update() as boolean
try
if _conn.state <> connectionstate.open then
_conn.open()
end if
_cmd.executenonquery()
return true
catch ex as exception
throw ex
return false
end try
end function
//读取字段或添加sql的命名参数
default public property item(byval name as string)
get
return _rdr(name)
end get
set(byval value)
addparam(value, name)
end set
end property
//读取字段
default public readonly property item(byval index as integer)
get
return _rdr(index)
end get
end property
//添加参数
public function addparam(byref value as object, optional byval name as string = “”)
if _dbtype = connectiontype.oledb then
return ctype(_cmd, oledb.oledbcommand).parameters.add(name, value)
else
return _cmd.parameters.add(new sqlclient.sqlparameter(“@” & name, value))
end if
end function
end class
end namespace
