SQL Server編程系列(1):SMO介紹

續篇：SQL Server編程系列(2):SMO常用對象的有關操作

最近在項目中用到了有關SQL Server管理工作方面的編程實現，有了一些自己的心得體會，想在此跟大家分享一下，在工作中用到了SMO/SQL CLR/SSIS等方面的知識，在國內這方面的文章並不多見，有也是一些零星的應用，特別是SSIS部分國內外的文章大都是講解如何拖拽控制項的，在開發過程中周公除了參閱SQL Server協助文檔、MSDN及StackOverFlow等網站，這些網站基本上都是英文的，為了便於一些英文不好的開發人員學習，周公在自己的理解上加以整理成系列，不到之處請大家諒解。
SMO簡介
SMO是英文SQL Server Management Objects的縮寫，意思是SQL Server管理對象系列，包含了一些列的命名空間(namespace)、動態連結程式庫（dll)和類(class)。這些類偏重於SQL Server的管理，並且在底層是通過SQL Server資料庫提供者(System.Data.SqlClient)下的類來與SQL Server來進行互動的。可以通過編程的方式利用SMO來管理SQL Server7.0以上的版本（SQL Server 7.0/2000/2005/2008)，如果低於以上版本的SQL Server則無法利用SMO來管理（除了曆史原因遺留的系統，在現在的開發中那些不受支援的SQL Server算是和Windows95一樣的古董了）。同時，要使用SMO的話，必須安裝SQL Server Native Client，一般情況下當我們安裝.Net Framework2.0以上版本或者SQL Server2005以上版本時就會自動安裝上了。
在32位系統下如果安裝的是SQL Server2005並且沒有更改安裝路徑，則SMO程式集的路徑是：C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies，相應的，如果安裝的是SQL Server2008，則SMO程式集的路徑就是C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies，如果是在64位系統下安裝，則根據安裝的SQL Server的版本來判斷是在Program Files (x86)還是在Program Files下面的對應目錄下。
在SMO中有如下命名空間：Microsoft.SqlServer.Management.Common、Microsoft.SqlServer.Management.Nmo、Microsoft.SqlServer.Management.Smo、Microsoft.SqlServer.Management.Smo.Agent、Microsoft.SqlServer.Management.Smo.Broker、Microsoft.SqlServer.Management.Smo.Mail、Microsoft.SqlServer.Management.Smo.RegisteredServers、Microsoft.SqlServer.Management.Smo.Wmi、Microsoft.SqlServer.Management.Trace，關於這些命名空間在哪個dll中以及該命名空間下有哪些類，大家可以查閱SQL Server的協助文章或者查閱線上MSDN，例如查看命名空間下的類可以瀏覽：http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo(v=sql.100)
SMO體系架構
我們知道在SQL Server體系中處於頂層是SQL Server執行個體，每個執行個體會有多個資料庫，每個資料庫會有多個表、預存程序、函數、登入帳號等，每個表會有列、索引、主鍵等資訊，每個列會有列名、預設值、欄位大小等資訊，在SMO中有與之相對應的一套類體繫結構。 

在DatabaseCollection中每個元素都是一個Database類的執行個體，分別對應資料執行個體中的一個資料庫；在TableCollection中每個元素都是一個Table的執行個體，分別對應資料庫中的一個表；在ColumnCollection中每個元素都是一個Column類的執行個體，分別對應表中的每一列，以上這些類都位於Microsoft.SqlServer.Management.Smo命名空間下，在Microsoft.SqlServer.Smo.dll這個dll中。
當然，在Microsoft.SqlServer.Management.Smo命名空間中的類遠不止上面提到的這些，上面的表格只是做了一個簡單的類比。
SMO用法樣本
上面的文章只是做了一個簡單的介紹，也許通過上面枯燥的介紹大家沒什麼印象，下面通過一段簡單的代碼來簡單示範一下用法，首先要添加響應的引用。在VS2008中可以直接通過下面的方式添加引用：

但是在VS2010中就沒有這麼方便了，它使用了過濾屬性（而且還無法禁用掉或者進行設定），使得無法添加這些程式集，如：

我看到有人在Stack Overflow提了和我疑惑相同的問題，別人給了一個答案就是在VS2010中安裝Muse.VSExtensions來解決，安裝地址：http://visualstudiogallery.msdn.microsoft.com/36a6eb45-a7b1-47c3-9e85-09f0aef6e879/，我嘗試了一下不是很理想，不過Muse.VSExtensions還提供了其它的功能（比如移除未使用的程式集）還是不錯的。
注意在編譯下面的代碼之前需要添加對microsoft.sqlserver.connectioninfo.dll和microsoft.sqlserver.smo.dll的引用。
代碼如下：

using System;using Microsoft.SqlServer.Management.Common;//需添加microsoft.sqlserver.connectioninfo.dll的引用using Microsoft.SqlServer.Management.Smo;//需添加microsoft.sqlserver.smo.dll的引用namespace SSISStudy{    /// <summary>    /// SQL Server編程些列文章(1):SMO介紹的配套代碼    /// 作者：周公     /// 建立日期：2012-05-17     /// 部落格地址：http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com     /// 新浪微博地址：http://weibo.com/zhoufoxcn      /// </summary>    class SMODemo01    {        /// <summary>        /// 顯示資料庫的概要情況        /// </summary>        public static void ShowServerInfo()        {            //建立ServerConnection的執行個體            ServerConnection connection = new ServerConnection();            //指定連接字串            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";            //執行個體化Server            Server server = new Server(connection);            Console.WriteLine("ActiveDirectory:{0}", server.ActiveDirectory);            Console.WriteLine("InstanceName:{0}",server.InstanceName);            //列出當前SQL Server執行個體中的登入使用者            foreach (Login login in server.Logins)            {                Console.WriteLine("Name:{0},CreateDate:{1},DefaultDatabase:{2}", login.Name, login.CreateDate, login.DefaultDatabase);            }            //通過索引方式找到master資料庫            Database db = server.Databases["master"];            Console.WriteLine("Database:{0}", db.Name);            Console.WriteLine("CreateDate:{0},Dbo:{1}", db.CreateDate, db.Owner);            Console.WriteLine("DataSpaceUsage:{0},SpaceAvailable:{1}", db.DataSpaceUsage, db.SpaceAvailable);            Console.WriteLine("PrimaryFilePath:{0}", db.PrimaryFilePath);            //列出master資料庫下每個表的概要資訊            foreach (Table table in db.Tables)            {                Console.WriteLine("\tTable:{0}，DataSpaceUsed:{1},CreateDate:{2}", table.Name, table.DataSpaceUsed, table.CreateDate);                //urnCollection.Add(table.Urn);                //列出每個表中每個列的概要資訊                foreach (Column column in table.Columns)                {                    Console.WriteLine("\t\tColumn:{0}，DataType:{1}，Nullable:{2}", column.Name, column.DataType, column.Nullable);                }            }            //列出master資料庫中的角色資訊            foreach(DatabaseRole dbRole in db.Roles)            {                Console.WriteLine("DB Role {0} Created at {1}", dbRole.Name, dbRole.CreateDate);            }            Console.ReadLine();        }    }}

程式的執行效果如下：

通過上面的代碼可以擷取很多有關資料庫的資訊，而在代碼中我們沒有寫一行SQL語句，而在之前周公曾寫過一篇部落格《在.NET中根據SQL Server系統資料表擷取資料庫管理資訊》，在其中為了尋找這些資訊周公是查了很多資料才知道SQL語句的寫法，使用SMO之後連SQL語句都免了，可見利用SMO來管理資料庫的方便性。在後續的篇幅中周公將講述如何利用SMO來擷取SQL Server中資料庫的建立語句及如何利用SMO來建立Job。
周公
2012-05-17

續篇：SQL Server編程系列(2):SMO常用對象的有關操作