//方式一
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);
}