ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 深入讲解SQL Server 2005数据库中的SMO

深入讲解SQL Server 2005数据库中的SMO

原创 Linux操作系统 作者:iSQlServer 时间:2009-02-11 16:42:39 0 删除 编辑
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);
            }

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-548732/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2087817