SQL Server Management Objects的研究與應(yīng)用

[摘 要] 通過(guò)SMO，用戶(hù)可以將對(duì)于數(shù)據(jù)庫(kù)的維護(hù)和狀態(tài)跟蹤集成到自己的應(yīng)用和解決方案中，本文就如何使用SMO來(lái)管理SQL Server進(jìn)行詳細(xì)的探討。

[關(guān)鍵詞] SMO;SQL;Server;管理

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2010 . 04 . 018

[中圖分類(lèi)號(hào)]F239.1 [文獻(xiàn)標(biāo)識(shí)碼]A [文章編號(hào)]1673 - 0194(2010)04 - 0050- 05

微軟將SQL Server管理對(duì)象(SQL Server Management Objects，SMO)定義為“為可編程管理微軟SQL Server而設(shè)計(jì)的對(duì)象”，是專(zhuān)為對(duì)管理 Microsoft SQL Server 所涉及的各個(gè)方面進(jìn)行編程而設(shè)計(jì)的對(duì)象集合，是針對(duì) Microsoft SQL Server 的編程管理設(shè)計(jì)的對(duì)象，是為了方便數(shù)據(jù)庫(kù)管理員和數(shù)據(jù)庫(kù)開(kāi)發(fā)人員更好地控制SQL Server的運(yùn)行和維護(hù)。它被建立在.Net Framework 2.0之上，提高了性能，加強(qiáng)了控制，并且更易于使用。SMO為開(kāi)發(fā)人員提供了更全面地管理SQL Server的各種元素的能力，如表、列、索引、存儲(chǔ)過(guò)程、觸發(fā)器、Service Broker、 快照數(shù)據(jù)庫(kù)、備份和恢復(fù)、文件和文件組等。方便用戶(hù)構(gòu)建自己的數(shù)據(jù)庫(kù)維護(hù)程序。通過(guò)SMO，用戶(hù)可以將對(duì)于數(shù)據(jù)庫(kù)的維護(hù)和狀態(tài)跟蹤集成到自己的應(yīng)用和解決方案中。

本文將以青島市機(jī)關(guān)事務(wù)管理局自主研發(fā)的計(jì)算機(jī)輔助審計(jì)系統(tǒng)中的具體示例，演示如何使用SMO來(lái)引用 SQL對(duì)象及其屬性的方法。

一、使用SMO的基礎(chǔ)要求

要使用SMO，我們需要如下的.NET庫(kù):

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoEnum

Microsoft.SqlServer.SqlEnum

如果我們沒(méi)有在Visual Studio的標(biāo)準(zhǔn).NET引用列表中看到，那么可以在C:\\Program Files\\Microsoft SQL Server\\90\\SDK\\Assemblies目錄中找到這些庫(kù)。如果沒(méi)有這些組件，可以使用SQL Server客戶(hù)端利用SQL Server 2005的任何版本來(lái)安裝。

在每個(gè)引用SMO對(duì)象的代碼文件中，我們都要使用imports(VB.NET)來(lái)導(dǎo)入以下的命名空間:

Imports Microsoft.SqlServer.Management

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common

二、具體應(yīng)用SMO管理SQL Server的方法

青島市機(jī)關(guān)事務(wù)管理局研發(fā)的計(jì)算機(jī)輔助審計(jì)系統(tǒng)，可以自動(dòng)搜索局域網(wǎng)內(nèi)的SQL Server服務(wù)器及實(shí)例，包括新建數(shù)據(jù)庫(kù)，引入數(shù)據(jù)庫(kù)，采集數(shù)據(jù)，增加、修改、刪除表格，以及數(shù)據(jù)查詢(xún)、數(shù)據(jù)分析等功能。主要界面如圖1所示。

其中數(shù)據(jù)庫(kù)操作界面如圖2所示。

以下分別介紹各個(gè)功能的主要實(shí)現(xiàn)方法。

1. 搜索局域網(wǎng)內(nèi)的服務(wù)器

SMO提供SmoApplication.EnumAvailableSqlServers方法來(lái)實(shí)現(xiàn)獲取網(wǎng)絡(luò)上檢測(cè)到的所有可用的SQL Server實(shí)例列表。它有3個(gè)重載形式:第一個(gè)重載形式?jīng)]有參數(shù)，它將掃描網(wǎng)絡(luò)上所有可用的SQL Server實(shí)例。第二個(gè)重載形式有一個(gè)Boolean類(lèi)型參數(shù)，如果這個(gè)參數(shù)為true，只得到本機(jī)的SQL Server實(shí)例;如果為1，則只得到網(wǎng)絡(luò)上的SQL Server實(shí)例。第三個(gè)重載形式有一個(gè)String類(lèi)型參數(shù)，可以指定得到哪一臺(tái)機(jī)器上的SQL Sever實(shí)例。具體方法如下:

Dim objServers As DataTable

Dim strServer As String

'**********************************************

' 產(chǎn)生網(wǎng)絡(luò)上的SQL Server實(shí)例列表

'**********************************************

objServers = SmoApplication.EnumAvailableSqlServers ( )

For Each objRow As DataRow In objServers.Rows

strServer = CStr(objRow(\"Server\"))

If Not TypeOf objRow(\"Instance\") Is DBNull _

AndAlso CStr(objRow(\"Instance\")).Length > 0 Then

strServer += \"\\\" CStr(objRow(\"Instance\"))

End If

ComboSQLServerName.Items.Add(strServer)

Next

2. 搜索制定SQL Server上的數(shù)據(jù)庫(kù)名稱(chēng)

frmmain.srv = New Microsoft.SqlServer.Management.Smo.Server(ComboSQLServerName.Text)

'frmmain.srv為選中的SQL Server實(shí)例，下面遍歷其中的數(shù)據(jù)庫(kù)，并將名稱(chēng)加載到comboBox選擇框中

Dim db As Microsoft.SqlServer.Management.Smo.Database

For Each db In frmmain.srv.Databases

ComboDName.Items.Add(db.Name)

Next

3. 新建數(shù)據(jù)庫(kù)(界面見(jiàn)圖3)。

Dim m_dbname， m_path As String

m_dbname = Txtdatabase.Text‘取得要生成的數(shù)據(jù)庫(kù)報(bào)表

m_path = Txtpath.Text‘取得路徑名稱(chēng)

Dim srv As Microsoft.SqlServer.Management.Smo.Server(ComboSQLServerName.Text)

srv = New Microsoft.SqlServer.Management.Smo.Server‘連接到默認(rèn)的路徑

Dim db As Microsoft.SqlServer.Management.Smo.Database

db = New Microsoft.SqlServer.Management.Smo.Database(srv， m_dbname)

db.DatabaseOptions.AutoClose = True

db.DatabaseOptions.AutoShrink = True

db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

db.DatabaseOptions.CloseCursorsOnCommitEnabled = True

Dim objFg = New Microsoft.SqlServer.Management.Smo.FileGroup(db， \"PRIMARY\")

Dim objDf = New Microsoft.SqlServer.Management.Smo.DataFile(objFg， m_dbname \"_Data\"， m_path \"\\\" m_dbname \".mdf\")‘指定MDF文件

objDf.GrowthType = FileGrowthType.Percent

objDf.Growth = 10

objDf.Size = 4000

objFg.Files.Add(objDf)

db.FileGroups.Add(objFg)

Dim objLf = New Microsoft.SqlServer.Management.Smo.LogFile(db， m_dbname \"_Log\"， m_path \"\\\" m_dbname \"_Log.ldf\") ‘指定LDF文件

objLf.GrowthType = FileGrowthType.Percent

objLf.Growth = 10

objLf.Size = 1000

db.LogFiles.Add(objLf)

Try

db.Create()‘生成數(shù)據(jù)庫(kù)

MsgBox(\"創(chuàng)建數(shù)據(jù)庫(kù)成功\")

Catch smoex As SmoException

MsgBox(smoex.Message)

End Try

4. 修改數(shù)據(jù)庫(kù)名稱(chēng)

Dim NewFileName As String

NewFileName = InputBox(vbCrLf \"原數(shù)據(jù)庫(kù)名為:\" ComboDName.Text vbCrLf vbCrLf vbCrLf \"請(qǐng)輸入新數(shù)據(jù)庫(kù)名:\"， \"數(shù)據(jù)庫(kù)改名\"， \"\")

If Trim(NewFileName) <> \"\" Then

If MsgBox(\"修改數(shù)據(jù)庫(kù)名會(huì)導(dǎo)致其他用戶(hù)不能訪(fǎng)問(wèn)該數(shù)據(jù)庫(kù)，要繼續(xù)嗎?\"， vbOKCancel + vbSystemModal + vbQuestion， \"詢(xún)問(wèn)\") = vbOK Then

frmmain.srv = New Microsoft.SqlServer.Management.Smo.Server(ComboSQLServerName.Text)

frmmain.CurrData = New Microsoft.SqlServer.Management.Smo.Database(frmmain.srv， ComboDName.Text)

frmmain.CurrData.Rename(NewFileName)

ComboDName.Items.Remove(ComboDName.SelectedItem)

ComboDName.Text = NewFileName

MsgBox(\"數(shù)據(jù)庫(kù)名稱(chēng)已更改，請(qǐng)重新搜索數(shù)據(jù)庫(kù)...\")

End If

End If

5. 刪除數(shù)據(jù)庫(kù)

If MsgBox(\"刪除數(shù)據(jù)庫(kù)\" ComboDName.Text \"會(huì)導(dǎo)致其他用戶(hù)不能訪(fǎng)問(wèn)該數(shù)據(jù)庫(kù)，要繼續(xù)嗎?\"， vbOKCancel + vbSystemModal + vbQuestion， \"詢(xún)問(wèn)\") = vbOK Then

frmmain.srv = New Microsoft.SqlServer.Management.Smo.Server(ComboSQLServerName.Text)

frmmain.srv.Databases(ComboDName.Text).Drop( )

ComboDName.Items.Remove(ComboDName.SelectedItem)

MsgBox(\"數(shù)據(jù)庫(kù)已刪除，請(qǐng)重新搜索數(shù)據(jù)庫(kù)...\")

End If

6. 導(dǎo)入數(shù)據(jù)庫(kù)

Dim owner As String

Dim logstr As String

Dim datastr As String

owner = \"dbo\"

Dim sc As StringCollection

sc = New StringCollection

datastr = Txtmdf.Text‘指定MDF文件

logstr = Txtldf.Text ‘指定LDF文件

sc.Add(datastr)

sc.Add(logstr)

frmmain.srv.AttachDatabase(Txtdbname.Text， sc， owner， AttachOptions.None)

MsgBox(\"數(shù)據(jù)庫(kù)導(dǎo)入成功，請(qǐng)重新搜索數(shù)據(jù)庫(kù)...\")

7.登錄到指定的數(shù)據(jù)庫(kù)中，并列出其中的表

Frmmsgbox.LabelInfor.Text = \"正在列舉數(shù)據(jù)庫(kù)\" LabelSqlDataName.Text \" 中的數(shù)據(jù)表...\"

FrmMsgbox.Show( )

frmmain.CurrData = frmmain.srv.Databases(ComboDName.Text)‘CurrData為選中的數(shù)據(jù)庫(kù)

List1.Items.Clear( )

Dim tb As Table

For Each tb In CurrData.Tables

List1.Items.Add(tb.Name)

Next

8. 列出指定表中的字段

Frmmsgbox.LabelInfor.Text = \"正在列舉表\" List1.Text \" 中的信息，請(qǐng)等待...\"

Frmmsgbox.Show( )

List2.Items.Clear( )

Dim tc As Column

For Each tc In CurrData.Tables(List1.Text).Columns

List2.Items.Add(tc.Name)

Next

GetData(\"select * from \" List1.Text)‘GetData為自定義函數(shù)，用于讀取表中的數(shù)據(jù)

DataGridView1.Refresh( )

Frmmsgbox.Close( )

9.讀取表中的數(shù)據(jù)，GetData函數(shù)的實(shí)現(xiàn)

Dim connectionString As String = \"Integrated Security=SSPI;Persist Security Info=False;\" \"Initial Catalog=\" CurrData.Name \";Data Source=\" srv.Name

Dim dataAdapter = New SqlDataAdapter(selectCommand， connectionString)

Dim commandBuilder As New SqlCommandBuilder(dataAdapter)

Dim table As New DataTable( )

table.Locale = System.Globalization.CultureInfo.InvariantCulture

dataAdapter.Fill(table)

bindingSource1.DataSource = table

Me.DataGridView1.DataSource = Me.bindingSource1

Me.dataGridView1.AutoResizeColumns( DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)

10. 新建表(界面見(jiàn)圖4)

Dim tb As Table

Dim i As Integer

Dim type As DataType

Dim columnname， stype， datasize As String

Dim scolumn As Column

Dim row As DataGridViewRow

tb = New Table(frmmain.CurrData， TextBox1.Text)

Dim mycol As Column

For Each row In DataGridView1.Rows

If Not (row.Cells.Item(\"列名\").Value = Nothing) Then

columnname = row.Cells.Item(\"列名\").Value.ToString.ToLower

datasize = row.Cells.Item(\"長(zhǎng)度\").Value

stype = row.Cells.Item(\"數(shù)據(jù)類(lèi)型\").Value.ToString.ToLower

Select Case stype

Case \"bigint\"

type = DataType.BigInt

Case \"int\"

type = DataType.Int

Case \"smallint\"

type = DataType.SmallInt

Case \"tinyint\"

type = DataType.TinyInt

Case \"bit\"

type = DataType.Bit

Case \"decimal\"

type = DataType.Decimal(0， 18)

Case \"numeric\"

type = DataType.Numeric(0， 18)

Case \"money\"

type = DataType.Money

Case \"smallmoney\"

type = DataType.SmallMoney

Case \"float\"

type = DataType.Float

Case \"real\"

type = DataType.Real

Case \"datetime\"

type = DataType.DateTime

Case \"smalldatetime\"

type = DataType.SmallDateTime

Case \"char\"

type = DataType.Char(datasize)

Case \"varchar\"

type = DataType.VarChar(datasize)

Case \"text\"

type = DataType.Text

Case \"nchar\"

type = DataType.NChar(datasize)

Case \"nvarchar\"

type = DataType.NVarChar(datasize)

Case \"ntext\"

type = DataType.NText

Case \"binary\"

type = DataType.Binary(datasize)

Case \"varbinary\"

type = DataType.VarBinary(datasize)

Case \"image\"

type = DataType.Image

Case \"sql_variant\"

type = DataType.Variant

Case \"timestamp\"

type = DataType.Timestamp

Case \"uniqueidentifier\"

type = DataType.UniqueIdentifier

End Select

scolumn = New Column(tb， columnname， type)

tb.Columns.Add(scolumn)

End If

Next

tb.Create( )‘生成表

11. 刪除表

If MsgBox(\"刪除表\" List1.Text \"會(huì)導(dǎo)致其他用戶(hù)不能訪(fǎng)問(wèn)該數(shù)據(jù)庫(kù)，要繼續(xù)嗎?\"， vbOKCancel + vbSystemModal + vbQuestion， \"詢(xún)問(wèn)\") = vbOK Then

CurrData.Tables(List1.Text).Drop( )

MsgBox(\"刪除表\" List1.Text \"成功!\"， vbOKOnly + vbInformation， \"信息\")

End If

12. 表更名

Dim NewFileName As String

NewFileName = InputBox(vbCrLf \"原數(shù)據(jù)表名為:\" List1.Text vbCrLf vbCrLf vbCrLf \"請(qǐng)輸入新表名:\"， \"表改名\"， \"\")

If Trim(NewFileName) <> \"\" Then

If MsgBox(\"表改名\" List1.Text \"會(huì)導(dǎo)致其他用戶(hù)不能訪(fǎng)問(wèn)該數(shù)據(jù)庫(kù)，要繼續(xù)嗎?\"， vbOKCancel + vbSystemModal + vbQuestion， \"詢(xún)問(wèn)\") = vbOK Then

CurrData.Tables(List1.Text).Rename(NewFileName)

MsgBox(\"表\" List1.Text \"改名成功!新表名為\" NewFileName， vbOKOnly + vbInformation， \"信息\")

OpenDataList( )

End If

End If

以上程序在Visual Studio 2005和SQL Server環(huán)境下測(cè)試通過(guò)，并在青島市機(jī)關(guān)事務(wù)管理局計(jì)算機(jī)輔助審計(jì)中取得優(yōu)良的效果。為了簡(jiǎn)化，以上過(guò)程取消了容錯(cuò)處理。

主要參考文獻(xiàn)

[1] [美]Evangelos Petroutsos. Visual Basic 2005:從入門(mén)到精通[M]. 王軍， 譯. 北京:電子工業(yè)出版社，2007.

[2] [美]Roger Jennings. Visual Basic 2005數(shù)據(jù)庫(kù)專(zhuān)家編程[M]. 沈曉春，譯. 北京:清華大學(xué)出版社，2006.