http://tech.it168.com/msoft/2007-10-28/200710281939625.shtml
SQL Server2005之SMO实战
[
收藏此页] [
打印]
作者：
IT168 极地圣火 2007-10-29
<script>init_Nav();</script>
源代码下载
【IT168 专稿】
在SQL Server2005以前的版本中，SQL分布式管理对象(SQL-DMO)为我们提供了非常有效的方法来通过编程的方式管理SQL Server。SQL-DMO支持基于COM的接口，开发人员可以通过这个接口来发现和操作SQL Server对象。而SQL Server2005在这方面做了更大的改进，它提供了一种新的管理框架，叫做SQL Server管理对象(简称为SMO)，这套组件具有更强大的管理能力，而且它被建立在.Net Framework2.0之上。当然，我们仍然可以使用SQL-DMO来管理SQL Server2005，但SMO为我们提供了更多的功能，也支持很多SQL Server2005的新特性，它的性能也比SQL-DMO更优化。
微软将SMO定义为“为可编程管理微软SQL Server而设计的对象”。SMO给了开发人员更全面地管理SQL Server的各种元素的能力，如表、列、索引、存储过程、触发器、Service Broker、快照数据库、备份和恢复、文件和文件组等。本文为了介绍SMO的使用，讨论了SMI的一些主要的特性。本文首先讨论了一些和SMO相关的编程主题，然后分析了一个SMO的应用程序例子。在这个例子中将演示如何使用SMO来引用 SQL对象和它们的属性，以及如何为它们产生T-SQL脚本。
一、SMO基础
SMO由.NET Framework2.0写成。所以我们必须使用Visual Studio2005或其后继版本中使用SMO。我们可以在Windows2003、Windows XP、Windows2000和Windows NT(SP5及以上版本)。别外，我们可以使用SMO连接SQL Server7、SQL Server2000和Sql Server2005数据库，但SMO并不支持更底版本的数据库，如sql server6.5。虽然SMO可以降级使用，但很明显，如果使用较低级的数据库，它的更多的特性就无法使用了。要使用SMO，我们需要如下的.NET库：
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
如果我们没有在Visual Studio的标准.NET引用列表中看到，我们可以在C:/Program Files/Microsoft SQL Server/90/SDK/Assemblies目录中找到这些库。如果我们没有这些组件，可以使用SQL Server客户端利用SQL Server2005的任何版本来安装它们。
在每个引用SMO对象的代码文件中，我们都要使用imports(VB.NET)来导入以下的命名空间：
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
为了增强可读性，最好同时引用Microsoft.SqlServer.Management.Smo 和 the Microsoft.SqlServer.Management。这主要是因此这们包含了一些名子相同的类，这些类可能会和定义在Windows Forms应用程序中的其他类产生歧义。在这种情况下，我们可以使用SMO命名空间作为前缀以消除歧义，如Dim objView as Smo.View。
1
二、使用SMO连接SQL Server2005
SMO提供了非常具有弹性的连接SQL Server的方式，它可以连接本地或远程服务器，并可以使用SQL或Windows验证两种登录方式。最简单的一种连接方式用是使用Windows验证登录到本地SQL Server的默认实例。为了达到这个目的，我们可以使用SMO的Server类的默认构造方法，代码如下：
Imports
Microsoft.SqlServer.Management
Imports
Microsoft.SqlServer.Management.Smo
Imports
Microsoft.SqlServer.Management.Common
Dim objServer as
Server
Dim objSvrConn as
ServerConnection
'****************************************************
'
establish connection to default
'
instance of local SQL Server using
'
Windows authentication
'
****************************************************
objServer = New Server()
如果使用Server类使用Windows验证来连接一个指定的SQL Server的一个实例，无论这个服务器是本地的，还是远程的，都可以使用Server类构造方法来接收实例名，代码如下：
'****************************************************
' establish connection to remote
' instance of SQL Server using
' Windows authentication
'****************************************************
objServer = New Server("ServerName/InstanceName")
对于如有类型的连接，如使用SQL验证方式，或使用服务器名、用户名和密码进行连接的ServerConnection对象都可以通过Server的构造方法中传入，代码如下：
'****************************************************
'
establish connection to SQL
'
Server using SQL authentication
'
using the ServerConnection class
'
****************************************************
objSvrConn = new
ServerConnection()
objSvrConn.ServerInstance = "ServerName/InstanceName"
objSvrConn.Login = "SMOSampleUser"
objSvrConn.Password = "SMOSamplePassword"
objServer = New
Server(objSvrConn)
For Each objDB as Database in
objServer.Databases
Debug.WriteLine(objDB.Name)
Next
读者在使用SMO连接数据库时应注意，为了更有效地改善SMO应用程序的性能。连接到SQL Server时并不马上建立连接，直到我们第一次使用这个连接时才真正连接到SQL Server数据库.。这就意味着在第一次使用连接之前，无论我们使用的连接参数是否正确，都不会得到任何的异常。如上面的代码在建立Server对象时，如果连接参数错误，并不会抛出任何异常，直接调用objServer.Databases时才会抛出异常。
在有些情况下，我们的SMO应用程序需要向用户提供一些可用的SQL Server实例列表。这就需要使用SMO提供的方法SmoApplication.EnumAvailableSqlServers来实现，这个方法返回了它能在网络上检测到的所有可用的SQL Server实例列表。这个方法有三个重载形式，第一个重载形式没有参数，它将扫描网络上所有可用的SQL Server实例。第二个重载形式有一个Boolean类型参数，如果这个参数为true,只得到本机的SQL Server实例，如果为false，则只得到网络上的SQL Server实例。第三个重载形式可以指定得到哪一台机器上的SQL Sever实例。机器名可通过方法参数指定。这个方法将数据以DataTable形式返回。下面的代码将演示了EnumAvailableSQLServers方法的第二种重载形式，并设置了参数为false（只得到网络上可用的SQL Server实例）。
响应延迟可能是限制EnumAvailableSqlServers方法得到足够的SQL Server实例的另一个原因。在这种情况下，我们可以通过多次调用EnumAvailableSqlServers来发现更多在响应较慢机器上的SQL Server实例。
Imports
Microsoft.SqlServer.Management
Imports
Microsoft.SqlServer.Management.Smo
Imports
Microsoft.SqlServer.Management.Common
Dim dtServers as
DataTable
Dim strServer As String
'****************************************************
'
build a list of all SQL Server instances
'
on the network
'
****************************************************
objServers = SmoApplication.EnumAvailableSqlServers(False
)
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
Debug.WriteLine(
"SQL Server: " &
strServer)
Next
在上面的循环开始部分，要注意返回的服务器列表包括一个服务器名和一个实例名。当将一个服务名传递给Server类的构造方法时，同时还要传递一个合适的实例名。在上面代码的循环中演示了如何从DataTable中取得相应的服务器名和实例名。
使用EnumAvailableSqlServers方法也不一定可以得到网络中的所有可用的SQL Server实例。其中最主要的原因就是安全限制。现在安全问题已经成为大多数组织非常关心的问题，其中包括微软，因此，大多数PC桌面系统都有防火墙来阻止网络广播探测。.如果在我们的网络中有类似的防火墙或网络中的广播探测由于其他原因被阻止。那么EnumAvailableSqlServers将无法得到很全的可用SQL Server实例列表。如果限制非常严格，也许我们只能得到本机的SQL Server实例列表。
响应延迟可能是限制EnumAvailableSqlServers方法得到足够的SQL Server实例的另一个原因。在这种情况下，我们可以通过多次调用EnumAvailableSqlServers来发现更多在响应较慢机器上的SQL Server实例。
三、从SMO中获得对象列表
在许多nt况，SMO提供了两种方法从SMO中获得对象列表。如为了得到数据库中表对象的列表，我们可以采取两种方式：
1. 通过一个属性得到对象组，并接受一个对象集合。
2. 调用EnumObjects方法，这个方法返回一个包含相应信息的DataTable。
一般情况下，通过调用调用集合属性可以获得更多有用的结果，因为我们可以单独获得某个对象更多的信息。如我们得到一个表对象的集合，我们可以在集合中查询每个表对象的其他属性，如列信息，索引或触发器等。
相对比，使用EnumObjects虽然不能获得那么多的信息，但是它使用起来更方便。如果我们想使用网络或其他的数据感知控件时，可以考虑使用EnumObjects。下面的代码演示了使用上述两种方法从一个数据库中得到相应的表对象的步骤：
四、SMO类的层次
大多数SMO对象都可以从名子判断它用来做什么。在SMO类的最高层是连接方法。在创建一个连接后，我们将寻找方法来管理服务层属性，如获得数据库列表和登录用户列表。以及中断SQL进程等。每一个Server类包含一个数据库类的集合，而每一个数据库类包含了表、存储过程、视图等数据库元素的集合。在大多数情况下，SMO类的层次有些类似我们经常使用的SQL企业管理器的树结构。
在许多nt况，SMO提供了两种方法从SMO中获得对象列表。如为了得到数据库中表对象的列表，我们可以采取两种方式：
1. 通过一个属性得到对象组，并接受一个对象集合。
2. 调用EnumObjects方法，这个方法返回一个包含相应信息的DataTable。
一般情况下，通过调用调用集合属性可以获得更多有用的结果，因为我们可以单独获得某个对象更多的信息。如我们得到一个表对象的集合，我们可以在集合中查询每个表对象的其他属性，如列信息，索引或触发器等。
Imports
Microsoft.SqlServer.Management
Imports
Microsoft.SqlServer.Management.Smo
Imports
Microsoft.SqlServer.Management.Common
Dim objServer as
Server
Dim objDatabase As
Database
objServer = New
Server()
objDatabase = objServer.Databases("SMOSample"
)
'****************************************************
'
Use Tables property to get list of tables
'
****************************************************
Debug.WriteLine("Tables in Database " &
objDatabase.Name)
For Each objTable As Table in
objDatabase.Tables
Debug.WriteLine("Table: " &
strServer)
Next
'****************************************************
'
Use EnumObjects method to get list of tables
'
****************************************************
Debug.WriteLine("Tables in Database " &
objDatabase.Name)
For Each objRow As DataRow In
_
m_objDatabase.EnumObjects(DatabaseObjectTypes.Table, _
Smo.SortOrder.Name).Rows
Debug.WriteLine("Table " & CStr(objRow("Name"
)))
Next
大多数SMO对象都可以从名子判断它用来做什么。在SMO类的最高层是连接方法。在创建一个连接后，我们将寻找方法来管理服务层属性，如获得数据库列表和登录用户列表。以及中断SQL进程等。每一个Server类包含一个数据库类的集合，而每一个数据库类包含了表、存储过程、视图等数据库元素的集合。在大多数情况下，SMO类的层次有些类似我们经常使用的SQL企业管理器的树结构。
五、SMO应用实例
本文提供的可下载的例子岩石了如何使用SMO进行编程来实现类似SQL Server Management Studio一样的导航树。当我们运行这个例子程序时，我们将看到一个空的窗口。单击Connect按钮打开SQL连接对话框后，如图1如示。这个例子应用程序支持上面描述的所有的连接方式。
本文提供的可下载的例子岩石了如何使用SMO进行编程来实现类似SQL Server Management Studio一样的导航树。当我们运行这个例子程序时，我们将看到一个空的窗口。单击Connect按钮打开SQL连接对话框后，如图1如示。这个例子应用程序支持上面描述的所有的连接方式。
图1 连接数据库的对话框，在这个对话框中支持所有的连接方式
当SQL Connection窗口 每一次打开时，它使用了SMO的EnumAvailableSqlServers方法列出了在网络中可找到的所有SQL Server实例。然后将这些实例名加到combobox控件中。这些实现代码类似本文上面所述的代码。
选择一个SQL实例后，单击Connect按钮建立个Server对象，连接到服务器，并得到这个实例的所有数据库的列表，并加到如图2的Combobox中。
我们从列表中选择一个数据库，然后单击OK按钮。关闭SQL Connection对话框后，主窗口将数据库中的表、列、keys、约束、索引、视图和存储过程等信息显示出来，如图3示。
图3 数据库导航界面
选择一个SQL实例后，单击Connect按钮建立个Server对象，连接到服务器，并得到这个实例的所有数据库的列表，并加到如图2的Combobox中。
For Each objDB As Database In Me.SMOServer.Databases
Me.comboDatabase.Items.Add(objDB.Name)
Next我们从列表中选择一个数据库，然后单击OK按钮。关闭SQL Connection对话框后，主窗口将数据库中的表、列、keys、约束、索引、视图和存储过程等信息显示出来，如图3示。
图3 数据库导航界面
六、SMO
的性能如何
当我们会议为什么使用SMO代替DMO时，一个主要的原因就是增加性能。外加上可以延迟连接到SQL Server，以及限制从对象中得到信息的数量，SMO的性能已经得到了非常大的提高。SMO当建立对象时，仅仅装载非常少的属性。换句话说，这些对象仅仅部分初始化。如果我们以后尝试读取未初始化的属性，SMO会自动请求服务器来获得属性细节。当然，如果错误地使用这些属性，我们可能会迅速丢失通过部分初始化而获得的性能。
本文提供的例子程序充分演示了这种性能丢失的情况。在应用程序中并没有在设计时就显示系统对象，而是在程序运行时通过每个IsSystemObject属性来确定每个对象是否为系统对象。不幸的是，这个属性并不是SMO的默认装载的属性，而在运行时装载这个属性一般需要花很长的时间。而每次调用对象的IsSystemObject都要单独访问SQL Server 以获得每个对象的属性值，因此，程序的性能将会随着对象的多少而显著下降。
使用SMO的默认方法，我们可以通过简单地调用对象的Script方法来为一个对象产生脚本。默认的脚本非常简单，只包括了产生对象的代码，如下面是一个使用默认方法产生的表对象的脚本，脚本代码如下：
Script方法产生脚本虽然方便，但它往往不能满足我们的要求。如果要产生更多、更丰富的脚本，还需要我们建立其他相关的对象。如建立表的脚本还需要包括索引、许可、约束等。为了改变SMO默认产生脚本的行为，我们可以使用一个ScriptingOptions对象，并传递相关的用于产生脚本的参数，如下面的代码所示：
我们可以在MSDN中找到完整的关于ScriptingOptions类文档。下面的代码显示了使用ScriptingOptions进行设置后产生的脚本。
当我们会议为什么使用SMO代替DMO时，一个主要的原因就是增加性能。外加上可以延迟连接到SQL Server，以及限制从对象中得到信息的数量，SMO的性能已经得到了非常大的提高。SMO当建立对象时，仅仅装载非常少的属性。换句话说，这些对象仅仅部分初始化。如果我们以后尝试读取未初始化的属性，SMO会自动请求服务器来获得属性细节。当然，如果错误地使用这些属性，我们可能会迅速丢失通过部分初始化而获得的性能。
本文提供的例子程序充分演示了这种性能丢失的情况。在应用程序中并没有在设计时就显示系统对象，而是在程序运行时通过每个IsSystemObject属性来确定每个对象是否为系统对象。不幸的是，这个属性并不是SMO的默认装载的属性，而在运行时装载这个属性一般需要花很长的时间。而每次调用对象的IsSystemObject都要单独访问SQL Server 以获得每个对象的属性值，因此，程序的性能将会随着对象的多少而显著下降。
大家不用担心，微软早就为我们考虑到了这一点。虽然IsSystemObject不是系统默认初始化的属性，但我们可以改变这一切，也就是说将IsSystemObject设为默认的属性。在Server类中提供了一个SetDefaultInitFields方法来设置默认属性。下面的代码演示了如何使用SetDefaultInitFields方法来修改Table、View和StoredProcedure类的IsSystemObject属性。要注意的是我们必须为每个对象单独设置IsSystemObject方法。由于本例要使用tables、views和stored procedures的IsSystemObject属性，因此，我们需要调用三次SetDefaultInitFields方法，代码如下：
m_objServer.SetDefaultInitFields(GetType(Table), "IsSystemObject")
m_objServer.SetDefaultInitFields(GetType(Smo.View), "IsSystemObject")
m_objServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")
在加入上面的代码后，例子应用程序装载数据库时更快了。
七、默认的约束和主键
不象核对位于SMO层次的表层的约束，默认的约束是表列的属性。在本例中加了一个DefaultConstraint对象的列表，这些DefaultConstraint来源于每一个表的列。在向树加入表列之后，加入了DefaultConstraints列表。代码如下：
Dim aobjTableDefaultCons As New List(Of DefaultConstraint)
Dim aobjTableDefaultCons As New List(Of DefaultConstraint)
For Each objTableColumn As Column In objSMOTable.Columns
If Not IsNothing(objTableColumn.DefaultConstraint) Then
aobjTableDefaultCons.Add(objTableColumn.DefaultConstraint)
End If
. . .
Next
在本例中处理主键和约束类似，主键主不是SMO表类的属性，我们可以通过对表的索引和查询每个索引的IndexKeyType属性来发现表的主键。如果一个索引的IndexKeyType设置为DriPrimaryKey，那么这个索引就是主键，或主键的一部分。下面的代码演示了如何判断一个索引是否为主键：
For Each objIndex As Index In objSMOTable.Indexes
. . .
If objIndex.IndexKeyType = IndexKeyType.DriPrimaryKey Then
. . .
' This index is the primary key for the table
. . .
End If
. . .
Next
八、使用SMO对象产生脚本
现在现在，本文一直关注如何使用SMO来发现并使用SQL对象，而SMO的功能还不仅仅如些，它还可以根据我们定义的很多SQL Server对象来产生相应的T-SQL脚本。在大多数企业，数据库管理员使用SQL Server Management Studio来产生脚本，以便他们可以将一个数据库的结构复制到另一个数据库上。为了演示SMO的脚本产生能力，本例程序的表、视图、存储过程的树结点都可以通过上下文菜单来产生相应的脚本。在产生完脚本后，程序将这些脚本复制到剪切版上。
使用SMO的默认方法，我们可以通过简单地调用对象的Script方法来为一个对象产生脚本。默认的脚本非常简单，只包括了产生对象的代码，如下面是一个使用默认方法产生的表对象的脚本，脚本代码如下：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PeopleIKnow]
(
[FriendID] [int] IDENTITY(1,1) NOT NULL
,
[FirstName] [varchar](50
)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[LastName] [varchar](50
)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Nickname] [varchar](50
)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Friend] [int] NULL
,
[College] [int] NULL
)
ON [PRIMARY]
GO
读者应注意一点：从Script方法输出的 脚本不包含上面显示的GO表达式。而取而代之的是Script方法返回了一个StringCollection，在这个集合中每个字符串包含了上面的一个表达式。而本程序为了使脚本更可用，在每个表达式之间加了GO语句。这类似于SQL Server Management Studio。
Script方法产生脚本虽然方便，但它往往不能满足我们的要求。如果要产生更多、更丰富的脚本，还需要我们建立其他相关的对象。如建立表的脚本还需要包括索引、许可、约束等。为了改变SMO默认产生脚本的行为，我们可以使用一个ScriptingOptions对象，并传递相关的用于产生脚本的参数，如下面的代码所示：
Dim strc As
StringCollection
Dim objScriptingOptions As
ScriptingOptions
. . .
objScriptingOptions = New
ScriptingOptions
objScriptingOptions.ClusteredIndexes = True
objScriptingOptions.NonClusteredIndexes
= True
objScriptingOptions.DriAll
= True
objScriptingOptions.Indexes
= True
objScriptingOptions.IncludeDatabaseContext
= True
objScriptingOptions.Permissions
= True
. . .
strc
= objTable.Script(objScriptingOptions)
USE [SMOSample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PeopleIKnow]
(
[FriendID] [int] IDENTITY(1,1) NOT NULL
,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Nickname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Friend] [int] NULL CONSTRAINT [DF_PeopleIKnow_StillMyFriend] DEFAULT ((1
)),
[College] [int] NULL
,
CONSTRAINT [PK_PeopleIKnow] PRIMARY KEY CLUSTERED
(
[FriendID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_PeopleIKnow] ON [dbo].[PeopleIKnow]
(
[FirstName] ASC
,
[LastName] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
GRANT DELETE ON [dbo].[PeopleIKnow] TO [SMOSampleUser]
GO
GRANT INSERT ON [dbo].[PeopleIKnow] TO [SMOSampleUser]
GO
GRANT SELECT ON [dbo].[PeopleIKnow] TO [SMOSampleUser]
GO
GRANT UPDATE ON [dbo].[PeopleIKnow] TO [SMOSampleUser]
GO
ALTER TABLE [dbo].[PeopleIKnow] WITH CHECK ADD
CONSTRAINT [FK_PeopleIKnow_Colleges] FOREIGN KEY([College]
)
REFERENCES [Colleges] ([CollegeID]
)
GO
ALTER TABLE [dbo].[PeopleIKnow] CHECK CONSTRAINT [FK_PeopleIKnow_Colleges]
GO
也许我们会发现SQL Server Management Studio是非常有用的，但它却缺少一些对我们在企业中管理SQL Server更实用的工具。如有很多人抱怨SQL Server Management Studio在产生建立对象脚本时并不能同时产生DROP和CREATE表达式。而大多数开发人员需要将数据库的结构迁移到其他的数据库中，但在这些数据库的某些对象可能存在，因此，如果不同时使用DROP和CREATE，就可能会造成不小麻烦。但当我们从本文中学习了SMO后，就可以在自己的应用程序中定制这些功能了。
1002
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
