namespace tonton.dal
'//----------------------------------------
'//《数据链接层》 之 《数据连接与命令行》
'//----------------------------------------
'//作者：张少棠 （tonton）
'//时间：2005年8月29日
'//邮编：[email protected]
'//主页：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
新闻热点
疑难解答
图片精选
