快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

使用SQL Server管理对象框架对SQL Server进行编程

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

SQL Server管理对象(SMO)是用于管理SQL Server的出色工具。Darko Martinovic演示了如何在C#解决方案中使用SMO创建表,备份数据库等等。

SQL Server管理对象(SMO)框架是一组对象,旨在对Microsoft SQL Server和Microsoft Azure SQL数据库进行编程管理。本文讨论如何通过使用SMO操作SQL Server对象。您可以在许多情况下使用SMO,例如:

  • 您想开发一个自定义应用程序来安装或更新您的软件。这意味着向您的客户分发新版本的解决方案。该解决方案的一部分是进行数据库更改,在数据库更改过程开始之前保存数据库状态,如果数据库更改过程失败,则还原为原始状态。
  • 您想开发自定义解决方案,该解决方案逐步实现数据库更改。每个开发人员都应在标准数据库模型中提出一组DDL更改建议。
  • 您已经或想要开发一个自定义应用程序来监视SQL Server。

当然,还有比我在这里提到的场景更多的场景。例如,在Microsoft的文档中,列出了以下方案:

  • SMO应用程序可能用于将第三方硬件或软件无缝地包含到数据库管理应用程序中。
  • 您可能必须创建一个用于创建和监视索引效率的应用程序,等等。

从SQL Server 2017开始,SMO作为Microsoft.SqlServer.SqlManagementObjects NuGet包进行分发,以允许用户使用SMO开发应用程序。

在本文中,我将演示一些实际示例,这些示例将向您展示SMO的部分功能。解释有关SMO的所有内容可能会花费很多时间,因此无法写在一篇文章中。

  • 在第一个示例中,我将向您展示如何建立与SQL Server的连接。
  • 在第二个示例中,我将向您展示如何进行DDL更改,创建表并基于该新创建的表,创建用于重新创建表的T-SQL脚本。
  • 最后,我将向您展示如何以编程方式进行备份,还原和构建SMO库,这将在某些情况下为您提供帮助。在示例中,我将结合SMO和SQLCLR方法。

安装SMO NuGet软件包

为了使用SMO NuGet包,您必须启动Visual Studio社区版(或任何许可版本)。使用C#创建一个新项目(您可以使用任何.NET语言,但是这些示例在C#中),然后选择Console App(.NET Framework)。根据需要命名该项目;例如TestSmoConnection,如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

然后启动NuGet软件包管理器控制台。

使用SQL Server管理对象框架对SQL Server进行编程

Package Manager控制台中,粘贴以下说明,然后按Enter。

几个DLL将被加载到解决方案中。在下图中,我突出显示了将在示例中使用的四个DLL。

使用SQL Server管理对象框架对SQL Server进行编程

下表中介绍了突出显示的SMO组件

Microsoft.SqlServer.Management.Smo 包含用于以编程方式操作Microsoft SQL Server的实例类,实用工具类和枚举。
Microsoft.SqlServer.SqlEnum 包含SQL Server内部存在的各种枚举器的定义。例如,数据库恢复模型的定义如下面的代码片段所示

Microsoft.SqlServer.ConnectionInfo 包含实例类,实用工具类和枚举,这些实例类,实用工具类和枚举用于以编程方式操作连接对象
Microsoft.SqlServer.SmoExtended 包含一些用于备份,还原,设备等的基类。

第一个示例–连接到SQL Server

一旦创建了项目并添加了SMO程序集,接下来要做的就是添加名称空间引用,如下面的清单所示:

其余的源代码显示在下面的列表中。将此代码段复制并粘贴到Program.cs文件中。

如果需要,修改服务器连接。本示例在AdventureWorks2014数据库上运行,因此请确保连接到托管该数据库的实例。然后只需按F5键,控制台应用程序将启动并几乎立即完成。最后,包括Console.ReadLine(),以防止应用程序退出。此时,您可以查看应用程序的输出并调查建立了哪种连接。

在第一个示例的开头,通过创建类型为ServerConnection的对象来建立连接。在一个ServerConnection类用于建立与SQL Server实例的连接。有多种建立连接的方法。让我们探索其中的一些。

只需调用如下所示的代码即可建立与默认SQL实例的连接。

另一个选择是传递SQL Server名称以及用户名和密码,如下面的代码片段所示。

为了查看建立了什么样的连接(注意:控制台应用程序应该仍在运行),启动SSMS(SQL Server管理服务器)并打开Activity Monitor。您可以通过右键单击服务器名称来找到“ 活动监视器”。假设您以用户sa身份连接,并且通过使用活动监视器,可以在“ 进程”部分中轻松找到您的连接。正如您在下图中看到的,根本没有魔术。幕后是.Net SqlClient数据提供程序。SMO基于System.DataSystem.Data.SqlClient构建命名空间。对于SMO,不是使用.NET代码编写T-SQL,而是使用点表示法。

使用SQL Server管理对象框架对SQL Server进行编程

ServerConnection对象最有趣的属性称为LoginSecure,它将在集成安全性和SQL Server安全性之间切换。为了通过使用SQL Server身份验证建立与服务器的连接,请将此属性设置为false。以下代码检查该值以确定哪种登录方式:

使用SMO对象

仅在特别引用时才加载SMO对象。创建对象时,仅部分加载对象属性。其余对象和属性将在直接引用时加载。

为了说明,请先按任意键退出第一个演示。然后将光标置于突出显示的行,如下图所示,然后按CTRL + F10或从上下文菜单中选择“运行到光标”。控制台应用程序将再次启动,并在突出显示的行上停止。

使用SQL Server管理对象框架对SQL Server进行编程

右键单击服务器变量,然后从上下文菜单中选择“ 快速监视 ”。您会注意到一个延迟,因为我们是第一次访问服务器属性,并且为了确定属性值,SMO必须在后台执行T-SQL。通过单击加号扩展服务器属性后,“ 快速监视”窗口将如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

例如,如果您想访问server.BackupDirectory属性…

…以下T-SQL在后台执行,可以通过启动SQL Profiler轻松证明。

在继续本文的其余部分之前,请停止执行代码。

创建对象

让我们再举一个例子。该示例的源代码位于SmoIntroduction项目内的CreateTable.cs中,您可以在此处下载该演示的目的是说明创建表并生成T-SQL脚本以重新创建新创建的表是多么容易。在本示例的最后,您将获得表和T-SQL脚本。

在项目中,有一个名为app.config的配置文件。打开app.config并调整连接字符串设置,使其指向包含AdventureWorks2014的SQL Server实例。确保将SmoIntroduction设置为启动项目。项目中的启动对象应为SmoIntroduction.CreateTable

使用SQL Server管理对象框架对SQL Server进行编程

F5后,执行结果将是表的创建。表名被硬编码到解决方案中。结果,新创建的具有主键和聚簇索引的表将显示在下图中。

使用SQL Server管理对象框架对SQL Server进行编程

T-SQL脚本显示在记事本窗口中,如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

停止执行并打开CreateTable.cs文件以查看创建表所需的代码。该对象表示一个SQL Server表。有三种可能的构造函数可用于创建表对象的实例。在示例中,我使用了其中必须指定表和架构名称以及数据库名称的数据库,如下面的代码片段所示。

要将列添加到表中,将使用表名,列名和数据类型创建一个列对象。您可以添加其他属性,例如默认值。

您可以添加约束,键和索引。以下代码显示了如何添加主键索引。

如果运行的是SQL Server 2016 SP1或更高版本,则还可以创建内存优化表。要创建内存优化表,请右键单击项目,然后将启动对象更改为SmoIntroduction.CreateMOTable。该SmoIntroduction项目也应设置为启动项目。

使用SQL Server管理对象框架对SQL Server进行编程

创建内存优化表时,必须指定两个属性,如下面的代码片段所示。这个示例的源代码位于CreateMOTable.cs里面SmoIntroduction项目。

表属性DurabilityDurabilityType的类型它是一个枚举器。您可能会猜到,枚举器值可以是SchemaOnlySchemaAndData

使用内存优化表时,有两种类型的索引:哈希索引和范围索引。要创建范围索引,您可以使用如下所示的代码段。

为了创建哈希索引,您必须将IndexType属性更改为IndexTypeNonClusteredHashIndex,并指定BucketCount属性,如下面的代码片段所示。

结果如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

当然,您可能知道,任何将包含内存优化表的数据库都必须具有一个MEMORY_OPTIMIZED_DATA文件组,该文件组至少具有一个容器,该容器存储SQL Server恢复内存优化表所需的检查点文件。

您可以使用SMO创建文件组,如下面的代码片段所示。注意,它通过如在第三个参数文件组的构造的值FileGroupType.MemoryOptimizedDataFileGroup

结果如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

创建用于内存优化数据的容器(数据文件)的代码相似。您可以使用SMO(通过使用如下所示的代码段)来执行此操作。

(注意:描述如何使用内存优化表超出了本文的范围。如果您想了解有关内存优化表的更多信息,请阅读Kalen Delaney的SQL Server内部:内存中OLTP(第二版)可以在这里找到。)

将表放置到位后,将使用表对象的Script方法生成表创建脚本。

在此示例中,函数MakeOptions()用于精确控制要编写的内容。部分功能如下所示。

使用SMO对象库的关键概念是唯一资源名称(URN)。URN使用类似于XPath的语法。XPath是用于指定对象的层次结构路径,其中每个级别都有限定符和功能。在SMO中,URN具有两个元素,即路径和属性命名,功能有限。路径用于指定对象的位置,而属性命名允许一定程度的过滤。

我们的表格的URN的示例是。

URN物体可以通过引用其URN属性进行检索。

可以通过Scripter类完成在SMO中编写数据库和对象属性脚本的另一种方法。所述的编剧类也使用的URN作为通过对象引用到脚本编写者对象的方法的参数。下面的代码片段显示了使用URN的示例。请注意,该代码将table.URN传递给Scripter对象。

使用SMO进行管理

最后,让我们看一下最复杂的示例。

在某些情况下,您必须对DDL进行大量更改,这是将新版本的软件应用于客户端的一部分。在这种情况下,您必须完成几个任务。

  • 首先,您必须检查数据库是否在线。
  • 然后,您必须在DDL更改开始过程之前保存数据库状态。不幸的是,这不能通过获取数据库快照来完成,因为数据库快照仅限于企业版。最通用的解决方案是进行仅复制备份。但是,在进行仅复制备份之前(注意仅复制备份是独立于常规SQL Server备份顺序的SQL Server备份。通常,进行备份会更改数据库并影响以后的备份还原方式。但是,偶尔出于特殊目的进行备份会很有用,而不会影响数据库的总体备份和还原过程。),则必须确保有足够的磁盘空间来保存仅复制备份文件。
  • 然后,您必须将数据库置于“ 受限用户访问”模式,以便在DDL更改过程开始时阻止与数据库的连接。
  • 之后,可以安全地开始DDL更改过程。
  • 如果DDL更改过程未成功完成,则必须通过使用刚开始时执行的备份来启动还原过程来还原数据库状态。
  • 最后,您必须将数据库放回多用户访问模式。

这是一个非常简化的方案,其中SMO和SQLCLR可以为最大100GB的数据库提供帮助,并可以与SQL Server Standard Edition一起使用。

在解释如何完成上面列出的某些任务之前,让我们看一下本文随附的源代码是如何组织的。

在解决方案中,您可以找到四个项目,如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

下表说明了每个解决方案项目的工作。

数据库助手 因此具有DLL的SMO库。在该库中,您可以找到许多使用SQL Server数据库的方法。
超级英雄 因此,SQLCLR项目具有可以在数据库服务器上发布的DLL。生成的程序集包含确定特定驱动器上的可用磁盘空间,列出特定目录中的文件以及删除特定目录中的文件的方法。
测试仪 一个控制台应用程序,可能对进行某些测试很有用。
Smo介绍 一个控制台应用程序是我们的第一个示例

在此阶段,您已经启动了Visual Studio,并打开了解决方案文件dbHelper.sln。如果不是这样,请这样做。下一步是在数据库服务器上发布SQLCLR项目。

将SMO与SQLCLR一起使用

在解决方案中,有一个名为IOHELPER的SQLCLR项目。因为这是有关SMO的文章,所以我将仅简要介绍IOHELPER。编译IOHELPER项目的结果是一个程序集,如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

程序集SimpleTalk.SQLCLR.IoHelper应该部署到数据库服务器。它是一个UNSAFE程序集,应使用非对称密钥签名。如发布下面的清单所示,您应该在发布程序集之前更改PreDeployment.sql文件中的密钥路径和密码。

发布程序集后,数据库模型将通过两个存储过程和一个功能进行扩展,如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

IOHELPERFreeSpace确定特定驱动器上的可用空间。确定特定驱动器上的可用空间的逻辑是在DriveHelper.cs中完成的。

顾名思义IOHELPER.DeleteFiles删除文件系统上的文件。它带有两个必填的输入参数,@ path和@ filter。源代码位于DeleteFiles.cs中

最后,IOHELPER.FileHelper将为我们提供有关指定目录中文件的信息。.NET代码位于FileHelper.cs中。为了列出指定目录中的所有文件,使用DirectoryInfo类。

如下代码片段所示,SQLCLR对象可以与SMO一起使用。

测试解决方案

将SQLCLR项目发布到数据库后,该测试功能了。

为了测试解决方案,在项目Tester上单击鼠标右键,然后从上下文菜单中选择Set as a Startup Project。Tester项目按照其名称的含义进行操作。

在项目中,只有一个名为Program.cs的程序文件和一个名为app.config的配置文件。打开app.config并调整连接字符串设置,以指向包含AdventureWorks2014或其他数据库的SQL Server实例。为了明确起见,您可以使用您选择的数据库执行其余测试。

Program类的最开始会检查数据库的状态。为此,它仅调用IsTheDatabaseOnLine,它只是数据库属性Status的包装。

DBGeneral是在DBHelper项目中找到的静态类,其中包含一些静态方法。其中之一是IsTheDatabaseOnLine。该方法使用SMO类数据库的称为状态的属性来确定数据库的状态。该数据库类,顾名思义,代表一个SQL Server数据库。

找出SMO在后台执行什么T-SQL命令是很有趣的。在代码中,您将找到每个等效的T-SQL静态方法。

第一次测试的结果显示在控制台窗口中,如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

下一个测试演示了如何将数据库置于受限用户访问模式,然后又恢复为多用户访问模式。为此,代码将调用PutDbInUserMode,如下面的清单所示。

顾名思义,枚举器DatabaseUserAccess具有三个值SingleRestrictedMultiple。此处的第二个参数是boolean,它确定是否终止当前用户进程。如果数据库已经处于指定模式,则什么都不会发生。否则,将UserAccess属性设置为新值。之后,您应该调用数据库对象的AlterRefresh方法,如下面的代码片段所示。

有趣的是,SMO在后台执行了哪个T-SQL命令。SMO仅对执行锁定的用户进程感兴趣。因此,执行以下T-SQL以找出会话ID。然后,SMO使用一个简单的KILL <session_id>

为了将数据库置于“ 受限用户访问模式”,请在后台执行以下T-SQL命令。

控制台应用程序的输出如下图所示。

使用SQL Server管理对象框架对SQL Server进行编程

下一个测试显示了如何执行仅复制备份。SMO类包括两类:实例类和实用程序类。实例类表示SQL Server对象,例如服务器,数据库,表,触发器和存储过程。另一方面,名称Utility可能会造成混淆,并且有人可能认为该类未实例化并且是静态的。事实并非如此,这些类使用new运算符来创建对象。实用程序一词表示它们用于完成某些任务。

在SMO中,Backup类和Restore类是实用程序类,它们提供工具来完成备份和还原数据库的特定任务。一个备份对象代表所需的,而不是在服务器实例上一个Microsoft SQL Server对象特定备份任务。

如下面的代码片段所示,只有少数参数传递给BackupDatabase方法。该方法只是围绕SMO中定义的BackupRestore类的包装。

方法的实现部分显示在下面的代码段中。

如我先前所写,该测试将执行仅复制备份。为了避免用户在确定备份文件名时的输入,使用了命名约定。每个备份文件都有以下命名约定。

备份文件名在数据库级别被写为扩展属性LastBackup

备份文件名如下图所示,作为扩展属性。

使用SQL Server管理对象框架对SQL Server进行编程

备份文件保存在server.BackupDirectory中,本文前面已对此进行了介绍。该功能默认情况下会启用压缩功能,并且有一个选项可以验证备份文件。因此,如果备份文件未通过验证,则该方法返回False。下面的代码段显示了验证备份文件的过程。

您可以自己探索几个测试。简而言之,它们包括缩小数据库日志,更改数据库恢复模型,确定日志大小等。最后两个测试确定服务器驱动器上的可用磁盘空间以及删除旧的备份文件。

为了确定可用磁盘空间(:可以通过执行T-SQL EXEC master..xp_fixeddrives来确定可用磁盘空间,但是它有很多限制),不需要访问ADO.NET方法。而是可以在数据库对象的上下文中调用ExecuteWithResults。该方法返回一个DataSet类型的对象,如以下代码片段所示。

摘要

最后,我要说的不是结论,而是SMO是一个很好的框架,它将在许多情况下为您提供帮助。与SQLCLR结合使用时,它可以帮助您开发非常强大的解决方案。您可以使用标准的点表示法来开发解决方案,而不是在.NET代码内使用T-SQL语法。

解决方案源代码可以在GitHub上找到。

于2019年12月2日更新

我想告诉读者,在这段时间内,我添加了更多有关使用SMO的示例。您可以在下面的图片中看到它们的候选列表。正如读者在评论中建议的那样,我将继续添加新示例。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638