SQLDMO(SQL Distributed Management Objects,SQL分布式管理对象)封装 Microsoft SQL Server 2000 数据库中的对象。SQL-DMO 允许用支持自动化或 COM 的语言编写应用程序,以管理 SQL Server 安装的所有部分。 SQL-DMO 是 SQL Server 2000 中的 SQL Server 企业管理器所使用的应用程序接口 (API);因此使用 SQL-DMO 的应用程序可以执行 SQL Server 企业管理器执行的所有功能。
SQL-DMO 用于必须包含 SQL Server 管理的任何自动化或 COM 应用程序,例如:
1. 封装 SQL Server 作为自己的数据存储并想尽量减少用户的 SQL Server 管理任务的应用程序。
2. 在程序本身并入了专门的管理逻辑的应用程序。
3. 想在自己的用户界面中集成 SQL Server 管理任务的应用程序。
SQLDMO对象来自SQLDMO.dll,SQLDMO.dll是随SQL Server2000一起发布的。SQLDMO.dll自身是一个COM对象,因此,在你的.NET项目里必须先引用它。
得到网络中的SQL服务器的列表:
'得到SQL服务器的列表 '必须安装SQL SERVER 2000 SP2 及以上版本
Dim I As Short Dim sqlApp As New SQLDMO.Application() Dim ServerName As SQLDMO.NameList ServerName = sqlApp.ListAvailableSQLServers For i = 1 To ServerName.Count cbServer.Items.Add(ServerName.Item(i)) Next
得到指定SQL服务器所有数据库的列表:
'得到指定SQL服务器所有数据库的列表
Dim sqlApp As New SQLDMO.Application() Dim oServer As New SQLDMO.SQLServer() oServer.Connect("(local)","sa","sa") cboDatabase.Items.Clear() Dim db As SQLDMO.Database For Each db In oServer.Databases Me.cboDatabase.Items.Add(db.Name) Next
得到所有的表、视图、存储过程:
Dim I As Short Dim oServer As New SQLDMO.SQLServer() oServer.Connect("(local)","sa") Dim db As New SQLDMO.Database()
For I = 1 To oServer.Databases.Count If oServer.Databases.Item(I,"dbo").Name = "Northwind" Then Exit For Next If I > oServer.Databases.Count Then Exit Sub
db = oServer.Databases.Item(I,"dbo") ListBox1.Items.Clear() '得到所有的存储过程 For I = 1 To db.StoredProcedures.Count ListBox1.Items.Add(db.StoredProcedures.Item(I,"dbo").Name) Next '得到所有的表 For I = 1 To db.Tables.Count ListBox1.Items.Add(db.Tables.Item(I,"dbo").Name) Next ' 得到所有的视图 For I = 1 To db.Views.Count ListBox1.Items.Add(db.Views.Item(I,"dbo").Name) Next
利用SQLDMO实现带进度条的数据库备份:
'添加进度条ProgressBar1控件 '引用Microsoft SQLDMO Object Library '声明
Public WithEvents bkps As SQLDMO.Backup
'数据库备份操作 Private Sub btnBackUp_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnBackUp.Click Dim oSQLServer As New SQLDMO.SQLServer() oSQLServer.LoginSecure = False oSQLServer.Connect("(local)","sa") '连接服务器 Me.Cursor = Windows.Forms.Cursors.WaitCursor bkps = CreateObject("SQLDMO.Backup") bkps.Database = "Northwind" '指定需备份的数据库 bkps.Action = 0 bkps.Files = "f:/Northwind.bak" '指定备份文件 bkps.Initialize = True ProgressBar1.Value = 0 ProgressBar1.Maximum = 100 Me.Cursor = Windows.Forms.Cursors.Default() Application.DoEvents() Dim mouseCur As Cursor Me.Cursor = Windows.Forms.Cursors.WaitCursor bkps.SQLBackup(oSQLServer) ProgressBar1.Value = 100 Application.DoEvents() bkps = Nothing Me.Cursor = Windows.Forms.Cursors.Default() MsgBox("数据库备份完成",MsgBoxStyle.Information,"系统消息") End Sub
'显示进度
Private Sub bkps_PercentComplete(ByVal Message As String,ByVal Percent As Integer) Handles bkps.PercentComplete ProgressBar1.Value = ProgressBar1.Maximum * (Percent / 100) End Sub
自动发布MS SQL SERVER数据库
当您的MIS系统开发好以后,您如何尽快的分发您的数据库呢?
下面我将提供VB代码的具体实现:
Option Explicit
'define the sql connect Dim oSQLServer As SQLDMO.SQLServer Dim oCurrentDB As SQLDMO.Database Dim oCurrentTable As SQLDMO.Table Dim oTestIdx As SQLDMO.Index
'sqlDmo is Connected Yes or No Public sName As String Private IsConnected As Boolean
'connect to local database server Public Function ConnectDmo() As Boolean On Error GoTo HandleError 'If we're connected,then disconnect and clear lists. If IsConnected = True Then oSQLServer.DisConnect IsConnected = False End If 'Begin connect to sqlserver or msde 'Attempt a connection,then fill the properties stuff. oSQLServer.ApplicationName = "SQL-DMO Index Test" oSQLServer.LoginSecure = True
'connect oSQLServer.Connect "(local)",""
IsConnected = True ConnectDmo = True
HandleError: 'connect failth If IsConnected = False Then IsConnected = False ConnectDmo = False End If
End Function
'add a exists database to server Public Function AddDataBase(ByVal dbName As String,ByVal DBPath As String,ByVal rstr As String) As Boolean Dim rstring 'rstring = oSQLServer.AttachDBWithSingleFile(dbName,DBPath) rstring = oSQLServer.AttachDB(dbName,DBPath) AddDataBase = True
rstr = rstring End Function
'delete the exists database Public Function DelDataBase(ByVal dbName As String) As Boolean Dim rstring rstring = oSQLServer.DetachDB(dbName) DelDataBase = True End Function
Private Sub UserControl_Initialize() On Error GoTo merror Set oSQLServer = New SQLDMO.SQLServer oSQLServer.LoginTimeout = 15 oSQLServer.ODBCPrefix = False Name = "msdeconn1" merror: End Sub
Public Function isDBexists(ByVal dbName As String) As Boolean Dim oDB As SQLDMO.Database Dim rc As Boolean 'reconnect to database oSQLServer.DisConnect oSQLServer.ReConnect
rc = False For Each oDB In oSQLServer.Databases If oDB.SystemObject = False Then If Trim(UCase(oDB.Name)) = Trim(UCase(dbName)) Then rc = True End If End If Next oDB
'set the return value isDBexists = rc
End Function
Private Sub UserControl_Terminate() 'end connect the database oSQLServer.Close End Sub
Public Function startServer() oSQLServer.Start True End Function
Public Sub stopserver() oSQLServer.Stop End Sub
Public Property Get Name() As Variant Name = sName End Property
Public Property Let Name(ByVal vNewValue As Variant)sName = vNewValue (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|