Ӧо
APPLICATION RESEARCH OF COMPUTERS
2000 Vol.17 No.3 P.106-107




VBSQL Serverݿ⼼
־ȫ
ժҪVBADOӿڷSQL ServerݿϵͳĻADOӿڵĹɡܡ̷ԼVisual BasicSQL ServerӦϵͳĿձ塣
ؼʣSQL Server ADOӿ VB
SQL Server΢ƳСݿϵͳĿǰõݿϵͳ֮һSQL ServerݿӦó࣬WebݿӦϵͳһѴڵûݿ⡣άݿĹSQL ServerṩSQL Enterprise ManagerУṩһֶƵݿߣͨӦóݿ⼰豸û˵ġ
VBSQL ServerݿϵͳʹRDO (ԶݶRemote Data Object)ADO (ActiveXݶActiveX Data Objects)ȷRDOλODBC API֮ϵһģͲ㣬ODBC APIODBCԼݿʵ֣RDOĳС(Լ250 KB)١RDO߱ODBCֱִдODBC APIRDOVB 4.0/VB 5.0ҵУMSRDO32.DLL̬ӿʵ֡RDOۺDAO/JetVBSQL/DBLibODBCŵĶģͣODBC APIӦò㣬Ϊں̨()ݿڵǰУSQL ServerOracleرƵġRDOȫVB֮УֱӷSQL Server洢̡ȫ֧T-SQLT-SQLԼڿСVisual Database Toolsļɻȡ΢ٶVBSQL/DBLibODBC APIһı̷ʽҲΪϲRDOӦý𽥼١
ADOǻȫµOLE DBOLE DBɶԵʼıļļݱȸָͨͳһĽӿڽдȡActiveXؼ(Windows 98ActiveX 5.0)RDOActiveXΪADOӿĿǰADOӿڵݿӦó򻹱Ƚ٣Ϊ˱ĽҪܻActiveXADOSQL Server 6.5ݿļͷ
1SQL Serverװ豸
SQL ServerWindows NTWebݿϵͳװNTϡʹNT 4.0İ氲װSQL Server 6.5ҪװΪMaster̴120MBΪ863ġЭΪName Pipe (ܵЭ)ʽΪNTʱԶSQL Serverȡ
SQL ServerװɺȿSQL ServerĹSQL Enterprise Managerݿ豸һӦϵͳ봴豸(GX_DATA)־豸(DATA_LOG)豸СɸݴŵƱVBʱݿ豸ͬ⣬Դʱ豸(TMPDBF)豸(GX_DATABF)ȣЩ豸СΪʱ豸һΪ豸1/4豸ж(ÿһ)ÿܼһΪ56豸Сɸÿ
豸ȻҲNTĹվֱʹóڶ̨վͬʱʹͬ豸ʱҪжϸ豸Ƿڣʱϳһ÷
豸ɺ󣬾ͿNTеĸվϿSQL Server 6.5ݿϵͳڸվݾNTϣļʽ豸УݵİȫԷǳߡ
2VBSQL Serverݿӿ
VBSQL ServerӿڿɷΪࣺ豸ݿӿڣ豸SQL ServerṩSQL Enterprise ManagerУҲͨVBơVBݿϵͳҪʵSQL ServerݿӣADOӿڳú£
(1)ȡǰĹݿ
ڹһ㶼Masterɣִй֮ǰñ浱ǰ⣬Ա֮лԭ
Public Function SQLGetCurrentDatabaseName(Cn As 
ADODB.Connection) As String
Dim sSQL As String
Dim RS As New ADODB.Recordset
On Error GoTo errSQLGetCurrentDatabaseName
sSQL="select CurrentDB=DB_NAME ( )"
RS.Open sSQL, Cn
SQLGetCurrentDatabaseName=Trim $ (RS! CurrentDB)
RS.Close
Exit Function
errSQLGetCurrentDatabaseName:
SQLGetCurrentDatabaseName=" "
End Function
(2)ȡSQL ServerװĿ¼µDATAĿ¼·
ȡSQL Server豸ļȱʡĿ¼D:\MSSQL DATA\
Public Function SQLGetDataPath(Cn As ADODB.Connection)
As String
Dim sSQL As String
Dim RS As New ADODB.Recordset
Dim sFullPath As String
On Error GoTo errSQLGetDataPath
sSQL="select phyname from master..sysdevices where
name='master' "
RS.Open sSQL, Cn
sFullPath = RS! phyname
RS.Close
SQLGetDataPath=Left $ (sFullPath, Len(sFullPath) -10)
 'MASTER.DATĴС
Exit Function
errSQLGetDataPath:
SQLGetDataPath=" "
End Function
(3)һݿ
Public Function SQLCreateDatabase65 (Cn As ADODB.Connection,
sDBName As String, sDataDeviceName As String, nDataSize
As Integer, Optional sLogDeviceName, Optional nLogSize) As
Boolean
Dim sSQL As String
On Error GoTo errSQLCreateDatabase65
Dim sDB As String
sDB =SQLGetCurrentDatabaseName(Cn)
sSQL = "USE master" 
Cn.Execute sSQL
sSQL ="CREATE DATABASE" & sDBName
sSQL = sSQL &" ON " & sDataDeviceName & "=" & nDataSize
If Not IsMissing(sLogDeviceName) And Not IsMissing(nLogSize)
Then
sSQL = sSQL & "LOG ON" & sLogDeviceName & "="& nLogSize
End If
Cn.Execute sSQL
sSQL = "USE" & sDB
Cn.Execute sSQL
SQLCreateDatabase65 = True
Exit Function
errSQLCreateDatabase65:
On Error Resume Next
sSQL = "USE " & sDB
Cn.Execute sSQL
SQLCreateDatabase65 = False
End Function
(4)жһݿǷ
Public Function SQLExistDatabase(Cn As ADODB.Connection, 
sDBName As String) As Boolean 
Dim sSQL As String
Dim RS As New ADODB.Recordset
Dim bTmp As Boolean
on Error GoTo errSQLExistDatabase
sSQL = "select CntDB = count ( * ) " 
sSQL = sSQL & "From master.dbo.sysdatabases" 
sSQL = sSQL & "Where name = ' "& sDBName & " ' " 
RS.Open sSQL, Cn
If RS! CntDB = 0 Then bTmp = False Else bTmp = True
RS.Close
SQLExistDatabase = bTmp
Exit Function
errSQLExistDatabase:
SQLExistDatabase = False
Exit Function
End Function
(5)ɾһݿ
Public Function SQLDropDatabase (Cn As ADODB.Connection, 
sDBName As String) As Boolean
Dim sSQL As String
On Error GoTo errSQLDropDatabase
If Not SQLExistDatabase(Cn, sDBName) Then 
SQLDropDatabase = True
Exit Function
End If
Dim sDB As String
sDB = SQLGetCurrentDatabaseName(Cn)
sSQL = "Use master"
Cn.Execute sSQL
sSQL = "DROP DATABASE " & sDBName
Cn.Execute sSQL
sSQL = "USE " & sDB
Cn.Execute sSQL
SQLDropDatabase = True
Exit Function
errSQLDropDatabase:
On Error Resume Next
sSQL = "USE " & sDB
Cn.Execute sSQL
SQLDropDatabase = False
End Function
3Ӧ˵
ADOǿWebݿӦóǿĶ󷽷VB 4.0ҵ棬SQL Server 6.5ͨVB 4.0ADOӿڳɹؿ˻SQL Server6.5ϵͳĲݿ⣬֧NTûԶ̲ûѯݣʵӦЧϺáƪޣĽ˳VBADOӿڷSQL ServerĽӿںVBSQL Serverݿ豸ӿڷ溯Լݿʽӿںϵ
־ȫ(ɶѧԺ ɶ 610059)
ο
1 . Visual Basic 4.0ʵü뷽. ɶӿƼѧ磬1996
2 Ա̼ά. 19995
ոڣ199999
