| http://blog.chinaunix.net/u/23701/showart_425165.html SQL Server2005中的SMO编程 | |||
http://www.365master.com/manage/database/sqlserver/20080421/33267.shtml
深入讲解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 7D87EB54AFCA4D2SQLUser */ 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); } |
SQL Server2005中的SMO编程
最新推荐文章于 2025-08-09 18:20:04 发布
}
}
扫一扫
13
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
