Apply SQLDMO in VB.NET
SQLDMO (SQL Distributed Management Objects, SQL Distributed Management Object) encapsulates objects in the Microsoft SQL Server 2000 database. SQL-DMO allows you to write an application with a language that supports automation or COM to manage all parts of SQL Server installation. SQL-DMO is the application interface (API) used by SQL Server Enterprise Manager in SQL Server 2000; therefore uses SQL-DMO applications to perform all features performed by SQL Server Enterprise Manager.
SQL-DMO is used to include any automation or COM applications managed by SQL Server, for example:
1. Packaging SQL Server as its own data store and wants to minimize the application of the user's SQL Server management task.
2. In the program itself, it is incorporated into a dedicated management logic.
3. I want to integrate the application of the SQL Server management task in my user interface.
SQLDMO objects come from SqldMo.dll, SqldMo.dll is released with SQL Server2000. SqldMo.dll itself is a COM object, so you must first reference it in your .NET project.
Get a list of SQL servers in your network:
'Get list of SQL servers
'Must install SQL Server 2000 SP2 and above
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
Get a list of all databases of the specified SQL server:
'Get a list of all databases of the specified SQL server
DIM SQLAPP AS New SqldMo.application ()
Dim Osterver As New SqldMo.sql Server ()
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
Get all tables, views, and stored procedures:
DIM I as Short
Dim Osterver As New SqldMo.sql Server ()
Oserver.connect ("(local)", "sa", "sa")
DIM DB As New SqldMo.database ()
For i = 1 to iserver.databases.count
If oserver.databases.item (i, "dbo"). Name = "northwind" TEN EXIT for
NEXT
I> iserver.databases.count kiln eXIT SUB
DB = oserver.databases.Item (i, "dbo")
Listbox1.Items.clear ()
'Get all stored procedures
For i = 1 to db.StoredProcedures.countListBox1.Items.Add (DB.StoredProcedures.Item (i, "dbo"). Name)
NEXT
'Get all tables
For i = 1 to db.tables.count
Listbox1.items.add (db.tables.Item (i, "dbo"). Name)
NEXT
'Get all views
For i = 1 to db.views.count
Listbox1.items.add (DB.Views.Item (i, "dbo"). Name)
NEXT
Use SQLDMO to implement database backup with progress bar:
'Add progress bar ProgressBar1 control
'̉ Reference Microsoft Sqldmo Object Library
'statement
Public WitHevents BKPS as SqldMo.Backup
'Database Backup Operation
Private sub btnbackup_click (byvale as system.object, byval e as system.eventargs) Handles btnbackup.click
Dim OSQLServer As New SqldMo.sqlServer ()
OSQLServer.loginsecure = false
OSQLServer.connect ("(local)", "sa", "sa") 'connection server
Me.cursor = Windows.Forms.cursors.Waitcursor
BKPS = CREATEOBJECT ("SqldMo.Backup")
Bkps.Database = "northwind" "Specifies the database that needs to be backed up
Bkps.Action = 0
Bkps.files = "f: /northwind.bak" "Specifies the backup file
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 ("Database Backup Complete", MSGBoxStyle.information, System Messages)
End Sub
'Display progress
PRIVATE SUB BKPS_PERCENTCOMPLETE (Byval Message As String, BYVAL Percent As Integer) Handles Bkps.PercentComplete
Progressbar1.value = progressbar1.maximum * (percent / 100)
End Sub
'********************************************************** ************* Author: Li Honggen
'Mail: lihonggen0@gci-corp.com
'Column: http://www.9cbs.net/develop/author/netauthor/lihonggen0/0/
'If you need to quote, please refer to the place! 9CBS Forum VB Welcome!
'********************************************************** *************
Code for C # operation:
http://www.csharphelp.com/archives2/archive342.html
