原文: SQL Server編程系列(2):SMO常用對(duì)象的有關(guān)操作

在上一篇周公簡(jiǎn)單講述了SMO的一些基本概念，實(shí)際上SMO體系結(jié)構(gòu)遠(yuǎn)不止周公在上一篇中講述的那么簡(jiǎn)單，下圖是MSDN上給出的一個(gè)完整的結(jié)構(gòu)圖：

上圖是一個(gè)完整的涉及到各種關(guān)系的結(jié)構(gòu)圖。不過它們之間的層次結(jié)構(gòu)關(guān)系周公已經(jīng)在上一篇做了簡(jiǎn)單概述。
在本篇中周公還是在上一篇的基礎(chǔ)上再做稍微深入一點(diǎn)的介紹，在本篇中主要講述如何獲取數(shù)據(jù)庫常見對(duì)象信息，并且如何利用SMO來進(jìn)行一些日常操作：如創(chuàng)建Login，創(chuàng)建數(shù)據(jù)庫、備份數(shù)據(jù)庫和還原數(shù)據(jù)庫。執(zhí)行上面這些操作的SQL語句也許我們已經(jīng)寫過，下面我們來看看利用SMO來操作的代碼將如何寫。為了便于理解，在代碼中的注釋比較詳盡，所以本篇中代碼之外的講解就不多了。 另外，需要注意的是SMO的dll的版本隨著升級(jí)變化比較大，這一點(diǎn)不同.NET Framework，沒有考慮向下兼容。
代碼如下：

    using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SqlServer.Management.Smo.RegisteredServers;//在microsoft.sqlserver.smo.dll中

using Microsoft.SqlServer.Management.Smo;//需添加microsoft.sqlserver.smo.dll的引用

using Microsoft.SqlServer.Management.Common;//需添加microsoft.sqlserver.connectioninfo.dll的引用



namespace SSISStudy

{

    /// <summary>

    /// SQL Server編程些列文章(2):SMO常用對(duì)象的有關(guān)操作

    /// 作者：周公 

    /// 創(chuàng)建日期：2012-05-23 

    /// 博客地址：http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com 

    /// 新浪微博地址：http://weibo.com/zhoufoxcn  

    class SMODemo02

    {

        /// <summary>

        /// 顯示數(shù)據(jù)庫常見對(duì)象信息示例，本方法中的代碼只針對(duì)9.0版本的SMO dll(SQL2005附帶程序集）

        /// </summary>

        public static void ShowSMOObjects()

        {

            Console.WriteLine("Server Group Information");

            foreach (ServerGroup serverGroup in SmoApplication.SqlServerRegistrations.ServerGroups)

            {

                Console.WriteLine("Group Name:{0},Path:{1},ServerType:{2},State:{3},Urn:{4}", serverGroup.Name, serverGroup.Path, serverGroup.ServerType, serverGroup.State, serverGroup.Urn);

            }



            Console.WriteLine("Registered Server Information");

            foreach (RegisteredServer regServer in SmoApplication.SqlServerRegistrations.RegisteredServers)

            {

                Console.WriteLine("Server Name:{0},Login:{1},State:{2},Urn:{3}", regServer.Name, regServer.Login, regServer.State, regServer.Urn);

            }



            //創(chuàng)建ServerConnection的實(shí)例

            ServerConnection connection = new ServerConnection();

            //指定連接字符串

            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";

            //實(shí)例化Server

            Server server = new Server(connection);

            Console.WriteLine("ActiveDirectory:{0},InstanceName:{1}", server.ActiveDirectory, server.InstanceName);

            //下面列出每個(gè)數(shù)據(jù)庫的具體信息

            foreach (Database db in server.Databases)

            {

                Console.WriteLine("Database Name:{0},ActiveDirectory:{1},ActiveConnections:{2},DataSpaceUsage:{3},PrimaryFilePath:{4}", db.Name, db.ActiveDirectory, db.ActiveConnections, db.DataSpaceUsage, db.PrimaryFilePath);

                //列出數(shù)據(jù)庫的數(shù)據(jù)文件文件組信息

                foreach (FileGroup fileGroup in db.FileGroups)

                {

                    Console.WriteLine("\tFileGroup Name:{0},Size:{1},State:{2},Urn:{3}", fileGroup.Name, fileGroup.Size, fileGroup.State, fileGroup.Urn);

                    //列出每個(gè)文件組中的數(shù)據(jù)文件信息

                    foreach (DataFile dataFile in fileGroup.Files)

                    {

                        Console.WriteLine("\t\tDataFile Name:{0},Size:{1},State:{2},Urn:{3},FileName:{4}", dataFile.Name, dataFile.Size, dataFile.State, dataFile.Urn, dataFile.FileName);

                    }

                }

                //列出數(shù)據(jù)庫日志文件信息

                foreach (LogFile logFile in db.LogFiles)

                {

                    Console.WriteLine("\tLogFile Name:{0},Size:{1},State:{2},Urn:{3},FileName:{4}", logFile.Name, logFile.Size, logFile.State,logFile.Urn,logFile.FileName);

                }

            }

        }

        /// <summary>

        /// 利用SMO創(chuàng)建SQL登錄

        /// </summary>

        public static void CreateLogin()

        {

            string loginName = "zhoufoxcn";//要?jiǎng)?chuàng)建的數(shù)據(jù)庫登錄名

            string loginPassword = "C#.NET";//登錄密碼



            //創(chuàng)建ServerConnection的實(shí)例

            ServerConnection connection = new ServerConnection();

            //指定連接字符串

            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";

            //實(shí)例化Server

            Server server = new Server(connection);



            #region [創(chuàng)建數(shù)據(jù)庫登錄對(duì)象]

            //檢查在數(shù)據(jù)庫是否已經(jīng)存在該登錄名

            var queryLogin = from Login temp in server.Logins

                             where string.Equals(temp.Name, loginName, StringComparison.CurrentCultureIgnoreCase)

                             select temp;

            Login login = queryLogin.FirstOrDefault<Login>();

            //如果存在就刪除

            if (login != null)

            {

                login.Drop();

            }

            login = new Login(server, loginName);

            login.LoginType = LoginType.SqlLogin;//指定登錄方式為SQL認(rèn)證

            login.PasswordPolicyEnforced = true;

            login.DefaultDatabase = "master";//默認(rèn)數(shù)據(jù)庫

            login.Create(loginPassword);

            #endregion

        }

        /// <summary>

        /// 利用SMO創(chuàng)建數(shù)據(jù)庫

        /// </summary>

        public static void CreateDatabase()

        {

            string databaseName = "SMODemo";

            //創(chuàng)建ServerConnection的實(shí)例

            ServerConnection connection = new ServerConnection();

            //指定連接字符串

            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";

            //實(shí)例化Server

            Server server = new Server(connection);



            #region [創(chuàng)建數(shù)據(jù)庫對(duì)象]

            //檢查在數(shù)據(jù)庫是否已經(jīng)存在該數(shù)據(jù)庫

            var queryDatabase = from Database temp in server.Databases

                                where string.Equals(temp.Name, databaseName, StringComparison.CurrentCultureIgnoreCase)

                                select temp;

            Database database = queryDatabase.FirstOrDefault<Database>();

            //如果存在就刪除

            if (database != null)

            {

                database.Drop();

            }



            database = new Database(server, databaseName);

            //指定數(shù)據(jù)庫數(shù)據(jù)文件細(xì)節(jié)

            FileGroup fileGroup = new FileGroup { Name = "PRIMARY", Parent = database, IsDefault = true };

            DataFile dataFile = new DataFile

            {

                Name = databaseName + "_data",

                Parent = fileGroup,

                FileName = @"F:\SQLData2005\" + databaseName + ".mdf"

            };

            fileGroup.Files.Add(dataFile);

            //指定數(shù)據(jù)庫日志文件細(xì)節(jié)

            LogFile logFile = new LogFile

            {

                Name = databaseName + "_log",

                Parent = database,

                FileName = @"F:\SQLData2005\" + databaseName + ".ldf"

            };



            database.FileGroups.Add(fileGroup);

            database.LogFiles.Add(logFile);



            database.Create();

            #endregion

        }

        /// <summary>

        /// 利用SMO備份數(shù)據(jù)庫

        /// </summary>

        public static void BackupDatabase()

        {

            string databaseName = "msdb";//備份的數(shù)據(jù)庫名

            string bkPath = @"C:\";//存放備份后的數(shù)據(jù)的文件夾

            //創(chuàng)建ServerConnection的實(shí)例

            ServerConnection connection = new ServerConnection();

            //指定連接字符串

            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";

            //實(shí)例化Server

            Server server = new Server(connection);



            #region [創(chuàng)建數(shù)據(jù)庫備份對(duì)象]

            Backup backup = new Backup();

            backup.Action = BackupActionType.Database;//完全備份

            backup.Database = databaseName;

            backup.BackupSetDescription = "Full backup of master";

            backup.BackupSetName = "master Backup";

            //創(chuàng)建備份設(shè)備

            BackupDeviceItem bkDeviceItem = new BackupDeviceItem();

            bkDeviceItem.DeviceType = DeviceType.File;

            bkDeviceItem.Name = bkPath+databaseName+".bak";



            backup.Devices.Add(bkDeviceItem);

            backup.Incremental = false;

            backup.LogTruncation = BackupTruncateLogType.Truncate;

            backup.SqlBackup(server);

            #endregion

        }



        /// <summary>

        /// 備份數(shù)據(jù)庫

        /// </summary>

        public static void RestoreDatabase()

        {

            string databaseName = "SMODemo";//備份的數(shù)據(jù)庫名

            string bkPath = @"C:\";//存放備份后的數(shù)據(jù)的文件夾

            //創(chuàng)建ServerConnection的實(shí)例

            ServerConnection connection = new ServerConnection();

            //指定連接字符串

            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";

            //實(shí)例化Server

            Server server = new Server(connection);



            Restore restore = new Restore();

            restore.NoRecovery = false;

            restore.NoRewind = false;

            restore.Action = RestoreActionType.Database;

            restore.Database = databaseName;



            //創(chuàng)建備份設(shè)備

            BackupDeviceItem bkDeviceItem = new BackupDeviceItem();

            bkDeviceItem.DeviceType = DeviceType.File;

            bkDeviceItem.Name = bkPath + databaseName + ".bak";



            //如果需要重新制定Restore后的數(shù)據(jù)庫的物理文件位置，需要知道數(shù)據(jù)庫文件的邏輯文件名

            //可以RESTORE FILELISTONLY 來列出邏輯文件名，如果覆蓋已有數(shù)據(jù)庫可以通過SMO來獲取

            //因本處使用的是剛剛備份的msdb數(shù)據(jù)庫來Restore，所以其分別為"MSDBData"和"MSDBLog"

            //如果不指定Restore路徑則默認(rèn)恢復(fù)到數(shù)據(jù)庫服務(wù)器存放數(shù)據(jù)的文件夾下

            RelocateFile relocateDataFile = new RelocateFile { LogicalFileName = "MSDBData", PhysicalFileName = bkPath + databaseName + ".mdf" };//(databaseName + "_data", bkPath + databaseName + ".mdf");

            RelocateFile relocateLogFile = new RelocateFile { LogicalFileName = "MSDBLog", PhysicalFileName = bkPath + databaseName + ".ldf" };//(databaseName + "_log", bkPath + databaseName + ".ldf");



            restore.Devices.Add(bkDeviceItem);

            restore.RelocateFiles.Add(relocateDataFile);

            restore.RelocateFiles.Add(relocateLogFile);

            restore.SqlRestore(server);

        }

    }

}
  

執(zhí)行結(jié)果在這里就不貼圖了，反正是是在周公家里的中文環(huán)境和辦公室英文環(huán)境中測(cè)試通過。預(yù)先透漏一下，下一篇將講述如何獲取SQL Server的對(duì)象的創(chuàng)建SQL語句，比如表、存儲(chǔ)過程、函數(shù)等。

2012-05-23
周公

SQL Server編程系列(2):SMO常用對(duì)象的有關(guān)操作