深入講解SQL Server 2005資料庫中的SMO

1、資料庫的連線與關閉
     //方式一
            ServerConnection conn = new ServerConnection(”計算機的名字”);
            server canopus5 = new server(conn);
            ServerConnection conn2 = canopus5.ConnectionContext;//獲取這個連線的引用

     //方式2
            server server = new server(”localhost”);
            server.ConnectionContext.Connect();
            server.Initialize(false);
//表示不載入資料庫伺服器的屬性，如果是true，則強制加

載屬性
           
            Console.WriteLine(server.State.ToString());
     Console.WriteLine(serverConnection.ProcessID);//52
            Console.ReadLine();
            //關閉資料庫
            server.ConnectionContext.SqlConnectionObject.Close();

對資料庫連線的方式，具體的根據下面的程式，大家會有一個比較清晰的認識：
 //首先給大家介紹的預設連線的形式
            ServerConnection serverConnection = new ServerConnection();
            serverConnection.Connect();
            //資料庫，我們採用的混合模式
            Console.WriteLine(serverConnection.ConnectionString.ToString());
//預設是使用

Windows的認證模式，
            //所以下面的屬性是空的
            Console.WriteLine(serverConnection.ConnectAsUserName.ToString());
//這裡返回的是空的
            Console.WriteLine(serverConnection.ConnectAsUserPassword.ToString());
//返回的是空
            Console.ReadLine();
 //下面的形式比較靈活，使用Sql Server登入模式
 //也使用了WIndows的認證模式，這裡我們使用的登入使用者是通過模擬實現的，

也就是登入的使用者不是當前在Windows中執行的使用者
          ServerConnection serverConnection = new ServerConnection();
            SqlCommand cmd =
                new SqlCommand(”SELECT SUSER_NAME()”,
                serverConnection.SqlConnectionObject);
            serverConnection.ServerInstance = “7D87EB54AFCA4D2″;
//根據LoginSecure屬性來決定是什麼模式登入資料庫

//LoginSecure=false,表示使用SQL Server登入方式，需要提供sa和對應的密碼，

或者其他的使用者名稱和密碼
            /*
            serverConnection.LoginSecure = false;
            serverConnection.Login = “sa”;
            serverConnection.Password = “qeilf0327″;
 //這裡提供一種加密的方式
            SecureString spwd = new SecureString();
            spwd.AppendChar(’q');
            spwd.AppendChar(’e');
            spwd.AppendChar(’i');
            spwd.AppendChar(’l');
            spwd.AppendChar(’f');
            spwd.AppendChar(’0′);
            spwd.AppendChar(’3′);
            spwd.AppendChar(’2′);
            spwd.AppendChar(’7′);
            spwd.MakeReadOnly();
            Console.WriteLine(spwd.ToString());
//返回的字串System.Security.SecureString
            localhost =
                new ServerConnection(
                “localhost”, name, spwd);

            Console.WriteLine(localhost.Password);
            Console.ReadLine();
           */

            //根據LoginSecure屬性來決定是什麼模式登入資料庫
            //LoginSecure=true,表示使用Windows認證的方式，需要提供系統的使用者的
            serverConnection.LoginSecure = true;//這種方式為Windows認證的方式，
            //如果至此我們什麼都不設定的話，就是當前登入的使用者
            serverConnection.ConnectAsUser = true;//如果將這個屬性設定為true，

必須要相應設定下面的兩個屬性
            serverConnection.ConnectAsUserName = @”SQLUser”;
//SQLUser,是我在系統中建立的一個管理員使用者
            serverConnection.ConnectAsUserPassword = @”chenleiilf)#27″;
            /*下面的是我們的輸出
            LogonUser succedded
            7D87EB54AFCA4D2\SQLUser
            */

            serverConnection.Connect();
 //連線建立起來的時候，我們可以看看當前的狀態，並輸出當前登入的使用者
     if (serverConnection.IsOpen)
            {
                Console.WriteLine(serverConnection.TrueLogin);
            }
           
            Console.WriteLine(cmd.ExecuteScalar());//返回的結果是7D87EB54AFCA4D2

\Administrator
            Console.ReadLine();
       

2、連線資料庫，獲取資料庫的列表
using microsoft.SqlServer.Management.Smo;

     //Server server = new server(args[0]);
            //Server server = new server(”localhost”);//本地的
            //Server server = new server();//預設的，只有一個例項的時候
            server server = new server(”7D87EB54AFCA4D2″);//計算機的名字
            foreach (Database database in server.Databases)
            {
                Console.WriteLine(database.Name);
            }
            Console.ReadLine();
     //補充另外一種方法，來獲取伺服器中的所有資料庫
            SqlConnectionStringBuilder sb =
                new SqlConnectionStringBuilder();
            sb.DataSource = “localhost”;
            sb.IntegratedSecurity = true;
            SqlConnection conn =
                new SqlConnection(sb.ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                 @”SELECT dtb.name AS [Name] FROM
                   master.sys.databases AS dtb
                   ORDER BY [Name] ASC”,
                   conn);
            SqlDataReader rdr = cmd.ExecuteReader();
            while(rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
     //獲取資料下面的表
            server server = new server();
            Console.WriteLine(server.Information.Product
              + ” ” + server.Information.ProductLevel
              + ” ” + server.Information.VersionString);
            foreach (Database db in server.Databases)
            {
                Console.WriteLine(”Database: ” + db.Name);
                foreach (Table tb in db.Tables)
                {
                    Console.WriteLine(”  table: ” + tb.Name);
                }
            }
       
3、連線資料庫，獲取資料庫物件的URN，如，表，資料庫，儲存過程，程式集等

using microsoft.SqlServer.Management.Common;
using microsoft.SqlServer.Management.Smo;
        static void Main(string[] args)
        {
            server localhost = new server(”localhost”);
            Database firstDatabase = localhost.Databases[0];
            table table = firstDatabase.Tables[0];
            Console.WriteLine(firstDatabase.Name.ToString());
            Console.WriteLine(table.Name.ToString());
            Urn firstUrn = firstDatabase.Urn;
            //Server[@Name=’7D87EB54AFCA4D2′]/Database[@Name=’AdventureWorks’]
            Console.WriteLine(firstUrn.ToString());

            Urn firstTable = table.Urn;
            /*Server[@Name=’7D87EB54AFCA4D2′]
/Database[@Name=’AdventureWorks’]/Table

[@Name=’AWBuildVersion’ and @Schema=’dbo’]
            */
            Console.WriteLine(firstTable.ToString());
            //看看儲存過程，URN，樣子是怎樣的
            if (firstDatabase.StoredProcedures.Count != 0)
            {
                StoredProcedure sp = firstDatabase.StoredProcedures[0];
                Console.WriteLine(sp.Urn.ToString());
            }
            //觸發器
            if (firstDatabase.Triggers.Count != 0)
            {
                DatabaseDdlTrigger tigger = firstDatabase.Triggers[0];
                Console.WriteLine(tigger.Urn.ToString());
            }
            //檢視
            if (firstDatabase.Views.Count != 0)
            {
                View view = firstDatabase.Views[0];
                Console.WriteLine(view.Urn.ToString());
            }
            //使用者
            if(firstDatabase.Users.Count!=0)
            {
                foreach (User user in firstDatabase.Users)
                {
                    Console.WriteLine(user.Urn.ToString());
                }
            }

            Console.WriteLine(firstDatabase.UserName.ToString());//dbo

            Console.WriteLine(firstDatabase.ActiveConnections.ToString());//0
            Console.WriteLine(firstDatabase.ActiveDirectory.Urn.ToString());
            if(firstDatabase.Assemblies.Count!=0)
            {
                foreach(SqlAssembly assembly in firstDatabase.Assemblies)
                {
                    Console.WriteLine(assembly.Urn.ToString());
                }
               
            }
            localhost.ConnectionContext.Disconnect();
            //
            server localhost1 = new server(”localhost”);
            Database tryAgain = localhost1.GetSmoObject(firstUrn) as Database;
            Console.WriteLine(firstDatabase.Name.ToString());
            Console.ReadLine();
        }
4、建立和刪除表，資料庫
            server localhost = new server();
            //必須輸入本機的名字，7D87EB54AFCA4D2，localhost似乎不行
            Database NorthWind = localhost.GetSmoObject(
                “Server[@Name=’7D87EB54AFCA4D2′]/Database[@Name=’NorthWind’]”) as

Database;
            table t = new table(NorthWind, “MyTable”);
            t.Columns.Add(new Column(t, “ID”, DataType.Int));
            t.Columns.Add(new Column(t, “Description”, DataType.NVarChar(200)));
            t.Create();
            Console.WriteLine(”Successfully!!”);

     //一種比較容易理解的方法
        static void AddTable(string serverName, string databaseName, string tableName,

string columnName)
        {
            server server = new server(serverName);
            Database database = server.Databases[databaseName];
            table table = new table(database, tableName);
            Column column = new Column(table, “ID”, DataType.Int);
            column.Identity = true;
            table.Columns.Add(column);
            Index primary = new Index(table, “PK_” + tableName);
            primary.IndexKeyType = IndexKeyType.DriPrimaryKey;
            primary.IndexedColumns.Add(new IndexedColumn(primary, “ID”));
            table.Indexes.Add(primary);
            column = new Column(table, columnName, DataType.VarChar(256));
            table.Columns.Add(column);
            table.Create();
        }

     //刪除資料庫中的表
            table Test456 = canopus5.GetSmoObject(
                @”Server[@Name=’7D87EB54AFCA4D2′]
                /Database[@Name=’NorthWind’]
                /Table[@Name=’MyTable’ and @Schema=’dbo’]”)
                as table;
            Test456.Drop();
5、查詢等操作
 //學習會使用SMO的連線物件，與SqlCommand結合進行查詢，執行SQL語句
     ServerConnection serverConnection = new ServerConnection();
            serverConnection.Connect();
            SqlCommand cmd = new SqlCommand(
                “Select * from pubs..authors”, serverConnection.SqlConnectionObject);
6、指令碼的操作

using microsoft.SqlServer.Management.Smo;
using system.Collections.Specialized;//StringCollection

            server server = new server();
            Database database = server.Databases[”northwind”];
            string tableName = “mytest”;
            string columnName = “name”;
            table table = new table(database, tableName);
            Column column = new Column(table, “ID”, DataType.Int);
            column.Identity = true;
            table.Columns.Add(column);
            Index primary = new Index(table, “PK_” + tableName);
            primary.IndexKeyType = IndexKeyType.DriPrimaryKey;//需要新增引用

microsoft.SqlServer.SqlEnum檔案
            primary.IndexedColumns.Add(new IndexedColumn(primary, “ID”));
            table.Indexes.Add(primary);
            column = new Column(table, columnName, DataType.VarChar(256));
            table.Columns.Add(column);
            table.Create();
            ScriptingOptions so = new ScriptingOptions();
            //so.ScriptDrops = true;
            StringCollection sc = new StringCollection();
            //customer.Script(
            sc = table.Script(so);
            foreach (String s in sc)
            {
                Console.WriteLine(s);
            }
            Console.ReadLine();
        }
        /*
         * 下面是輸出的內容，就是建立的指令碼
        SET ANSI_NULLS ON
        SET QUOTED_IDENTIFIER ON
        CREATE table [dbo].[mytest](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [name] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
        ) ON [PRIMARY]
        
        */
    }
}

7、配置管理
using microsoft.SqlServer.Management.Smo;
using microsoft.SqlServer.Management.Smo.Wmi;
            //可以獲取，本地計算機的資訊，下面演示的服務的列表
            ManagedComputer mc = new ManagedComputer();
            Console.WriteLine(mc.Name);
            foreach (Service s in mc.Services)
            {
                Console.WriteLine(s.Name);
            }