一个连接数据库的Class类

option explicit'local variable(s) to hold property value(s)private lsserver as string 'local copyprivate lsdatabase as string'local variable(s) to hold property value(s)private lsusername as string 'local copyprivate lspassword as string 'local copyprivate lsselectsql as string 'local copy'local variable(s) to hold property value(s)private lstable as string 'local copypublic property let table(byval vdata as string)'used when assigning a value to the property, on the left side of an assignment.'syntax: x.table = 5 lstable = vdataend propertypublic property get table() as string'used when retrieving value of a property, on the right side of an assignment.'syntax: debug.print x.table table = lstableend propertypublic property let selectsql(byval vdata as string)'used when assigning a value to the property, on the left side of an assignment.'syntax: x.selectsql = 5 lsselectsql = vdataend propertypublic property get selectsql() as string'used when retrieving value of a property, on the right side of an assignment.'syntax: debug.print x.selectsql selectsql = lsselectsqlend propertypublic property let password(byval vdata as string)'used when assigning a value to the property, on the left side of an assignment.'syntax: x.password = 5 lspassword = vdataend propertypublic property get password() as string'used when retrieving value of a property, on the right side of an assignment.'syntax: debug.print x.password password = lspasswordend propertypublic property let username(byval vdata as string)'used when assigning a value to the property, on the left side of an assignment.'syntax: x.username = 5 lsusername = vdataend propertypublic property get username() as string'used when retrieving value of a property, on the right side of an assignment.'syntax: debug.print x.username username = lsusernameend propertypublic property let server(byval vdata as string)'used when assigning a value to the property, on the left side of an assignment.'syntax: x.server = 5 lsserver = vdataend propertypublic property get server() as string'used when retrieving value of a property, on the right side of an assignment.'syntax: debug.print x.server server = lsserverend propertypublic property let database(byval vdata as string)'used when assigning a value to the property, on the left side of an assignment.'syntax: x.database = 5 lsdatabase = vdataend propertypublic property get database() as string'used when retrieving value of a property, on the right side of an assignment.'syntax: debug.print x.database database = lsdatabaseend propertypublic function connectdatabasebyodbcdns(lsdns as string) as adodb.connectionon error goto errhandle dim conn as new adodb.connection dim strconn as string strconn = "dsn=" & lsdns & _ ";uid=" & lsusername & _ ";pwd=" & lspassword if conn.state = adstateopen then conn.close end if conn.open strconn conn.cursorlocation = aduseclient set connectdatabasebyodbcdns = conn exit functionerrhandle: err.raise 102, "dbconn.cls", "can't connect to the " & database & " database!"end functionpublic function connectdatabasebyodbc() as adodb.connectionon error goto errhandle dim conn as new adodb.connection dim strconn as string strconn = "provider=msdasql.1" & _ ";user id=" & lsusername & _ ";password=" & lspassword & _ ";persist security info=false" & _ ";initial catalog=" & lsdatabase & _ ";data source=" & lsserver if conn.state = adstateopen then conn.close end if conn.open strconn conn.cursorlocation = aduseclient set connectdatabasebyodbc = conn exit functionerrhandle: err.raise 108, "dbconn.cls", "can't connect to the " & database & " database!"end functionpublic function connectdatabasebyodbcprovider() as adodb.connectionon error goto errhandle dim conn as new adodb.connection dim strconn as string strconn = "driver={sql server};server=" & lsserver & _ ";database=" & lsdatabase & _ ";uid=" & lsusername & _ ";pwd=" & lspassword if conn.state = adstateopen then conn.close end if conn.open strconn conn.cursorlocation = aduseclient set connectdatabasebyodbcprovider = conn exit functionerrhandle: err.raise 101, "dbconn.cls", "can't connect to the " & database & " database!"end functionpublic function connectdatabasebyoledb(lsserver as string, lsusername as string, lspassword as string, lsdatabase as string) as adodb.connectionon error goto errhandle dim conn as new adodb.connection dim strconn as string strconn = "provider=sqloledb.1" & _ ";user id=" & lsusername & _ ";password=" & lspassword & _ ";persist security info=false" & _ ";initial catalog=" & lsdatabase & _ ";data source=" & lsserver if conn.state = adstateopen then conn.close end if conn.open strconn conn.cursorlocation = aduseclient set connectdatabasebyoledb = conn exit functionerrhandle: err.raise 100, "dbconn.cls", "can't connect to the " & database & " database!"end functionpublic function connectdatabasebyodbcdnsforcrystal(lsdns as string) as adodb.connectionon error goto errhandle dim conn as new adodb.connection dim strconn as string strconn = "dsn=" & lsdns & _ ";uid=" & lsusername & _ ";pwd=" & lspassword & _ ";dsq=" & lsdatabase if conn.state = adstateopen then conn.close end if conn.open strconn conn.cursorlocation = aduseclient set connectdatabasebyodbcdnsforcrystal = conn exit functionerrhandle: err.raise 104, "dbconn.cls", "can't connect to the " & database & " database for crystal!"end functionpublic function connectdatabasebyoledbforcrystal() as adodb.connectionon error goto errhandle dim conn as new adodb.connection dim strconn as string strconn = "driver={sql server}" & _ ";uid=" & lsusername & _ ";pwd=" & lspassword & _ ";server=" & lsserver & _ ";database=" & lsdatabase if conn.state = adstateopen then conn.close end if conn.open strconn conn.cursorlocation = aduseclient set connectdatabasebyoledbforcrystal = conn exit functionerrhandle: err.raise 105, "dbconn.cls", "can't connect to the " & database & " database for crystal!"end functionpublic function openrecordset(lsserver as string, lsusername as string, lspassword as string, lsdatabase as string, lstable as string, optional lsselectsql as string) as adodb.recordseton error goto errhandle dim rs as new adodb.recordset rs.cursortype = adopenstatic rs.locktype = adlockoptimistic if rs.state = adstateopen then rs.close end if if trim(lsselectsql) = "" then set rs = connectdatabasebyoledb(lsserver, lsusername, lspassword, lsdatabase).execute("select * from " & lstable) else set rs = connectdatabasebyoledb(lsserver, lsusername, lspassword, lsdatabase).execute(lsselectsql) end if set openrecordset = rs exit functionerrhandle: err.raise 110, "conndb.cls", "can't open the recordset"end functionpublic sub updaterecordset(lstable as string, lsupdatecondition as string)on error goto errhandle connectdatabasebyodbcprovider.begintrans connectdatabasebyodbcprovider.execute ("update " & lstable & " where " & lsupdatecondition) connectdatabasebyodbcprovider.committranserrhandle: connectdatabasebyodbcprovider.rollbacktrans err.raise 111, "conndb.cls", "can't update the recordset!"end subprivate sub class_initialize()' lsserver = "prc_msdnt"' 'lsdatabase = "northwind"' lsusername = "sa"' lspassword = "sa"' lsselectsql = ""' lstable = ""end sub