SMO相关操作资料

http://blog.csdn.net/zhoufoxcn/article/details/7593135

=========================================================================================

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常用对象的有关操作
/// 作者：周公
/// 创建日期：2012-05-23
/// 博客地址：http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com
/// 新浪微博地址：http://weibo.com/zhoufoxcn
class SMODemo02
{
/// <summary>
/// 显示数据库常见对象信息示例
/// </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);
}
//创建ServerConnection的实例
ServerConnection connection = new ServerConnection();
//指定连接字符串
connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";
//实例化Server
Server server = new Server(connection);
Console.WriteLine("ActiveDirectory:{0},InstanceName:{1}", server.ActiveDirectory, server.InstanceName);
//下面列出每个数据库的具体信息
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);
//列出数据库的数据文件文件组信息
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);
//列出每个文件组中的数据文件信息
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);
}
}
//列出数据库日志文件信息
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创建SQL登录
/// </summary>
public static void CreateLogin()
{
string loginName = "zhoufoxcn";//要创建的数据库登录名
string loginPassword = "C#.NET";//登录密码
//创建ServerConnection的实例
ServerConnection connection = new ServerConnection();
//指定连接字符串
connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";
//实例化Server
Server server = new Server(connection);
#region [创建数据库登录对象]
//检查在数据库是否已经存在该登录名
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认证
login.PasswordPolicyEnforced = true;
login.DefaultDatabase = "master";//默认数据库
login.Create(loginPassword);
#endregion
}
/// <summary>
/// 利用SMO创建数据库
/// </summary>
public static void CreateDatabase()
{
string databaseName = "SMODemo";
//创建ServerConnection的实例
ServerConnection connection = new ServerConnection();
//指定连接字符串
connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";
//实例化Server
Server server = new Server(connection);
#region [创建数据库对象]
//检查在数据库是否已经存在该数据库
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);
//指定数据库数据文件细节
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);
//指定数据库日志文件细节
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备份数据库
/// </summary>
public static void BackupDatabase()
{
string databaseName = "msdb";//备份的数据库名
string bkPath = @"C:\";//存放备份后的数据的文件夹
//创建ServerConnection的实例
ServerConnection connection = new ServerConnection();
//指定连接字符串
connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";
//实例化Server
Server server = new Server(connection);
#region [创建数据库备份对象]
Backup backup = new Backup();
backup.Action = BackupActionType.Database;//完全备份
backup.Database = databaseName;
backup.BackupSetDescription = "Full backup of master";
backup.BackupSetName = "master Backup";
//创建备份设备
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>
/// 备份数据库
/// </summary>
public static void RestoreDatabase()
{
string databaseName = "SMODemo";//备份的数据库名
string bkPath = @"C:\";//存放备份后的数据的文件夹
//创建ServerConnection的实例
ServerConnection connection = new ServerConnection();
//指定连接字符串
connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;";
//实例化Server
Server server = new Server(connection);
Restore restore = new Restore();
restore.NoRecovery = false;
restore.NoRewind = false;
restore.Action = RestoreActionType.Database;
restore.Database = databaseName;
//创建备份设备
BackupDeviceItem bkDeviceItem = new BackupDeviceItem();
bkDeviceItem.DeviceType = DeviceType.File;
bkDeviceItem.Name = bkPath + databaseName + ".bak";
//如果需要重新制定Restore后的数据库的物理文件位置，需要知道数据库文件的逻辑文件名
//可以RESTORE FILELISTONLY 来列出逻辑文件名，如果覆盖已有数据库可以通过SMO来获取
//因本处使用的是刚刚备份的msdb数据库来Restore，所以其分别为"MSDBData"和"MSDBLog"
//如果不指定Restore路径则默认恢复到数据库服务器存放数据的文件夹下
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);
}
}
}

http://zhoufoxcn.blog.51cto.com/792419/873470

====================================================================================================

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//连接本地数据库
Microsoft.SqlServer.Management.Common.ServerConnection conn = new ServerConnection("localhost", "sa", "pass");
//连接远程数据库
//Microsoft.SqlServer.Management.Common.ServerConnection conn = new ServerConnection("tcp:192.168.18.10,pass", "sa", "pass");
Server s = new Server(conn);
Console.WriteLine("DataBaseCount:" + s.Databases.Count);

//创建数据库
Database db1 = new Database(s, "NewDataBaseName");
//db1.Create();
Console.WriteLine("DataBaseCount:" + s.Databases.Count);

//创建表
Database db = s.Databases["NewDataBaseName"];//引用数据库
Table tb = new Table(db, "NewTableName");//表名
Column c = new Column(tb, "CustomerId");//字段CustomerId
c.Identity = true;
c.IdentitySeed = 1;
c.DataType = DataType.Int;
c.Nullable = false;
tb.Columns.Add(c);
c = new Column(tb, "CustomerName");//字段CustomerName
c.DataType = DataType.VarChar(20);
c.Nullable = true;
tb.Columns.Add(c);
//tb.Create();//创建表

//创建储存过程
StoredProcedure sp = new StoredProcedure(db, "NewProcedure");
StoredProcedureParameter spp1 = new StoredProcedureParameter(sp, "@addId", DataType.Int);
sp.TextMode = false;
sp.Parameters.Add(spp1);
sp.TextBody = "select * from newtableName where CustomerId=@addId";
//sp.Create();

//执行储存过程
db.ExecuteNonQuery("exec NewProcedure addId=1");

//删除数据库
Database db2 = s.Databases["NewDataBaseName"];
//db2.Drop();
Console.WriteLine("DataBaseCount:" + s.Databases.Count);

//删除表
Table tb1 = db.Tables["NewTableName"];
//tb1.Drop();

//删除储存过程
StoredProcedure sp2 = db.StoredProcedures["NewProcedure"];
//sp2.Drop();
}
}
}

其中要引用三个文件：
1.Microsoft.SqlServer.ConnectionInfo.dll
2.Microsoft.SqlServer.Smo.dll
3.Microsoft.SqlServer.WmiEnum.dll
可以从C:\Program Files\Microsoft SQL Server\90\sdk\Assemblies 这个文件夹里找到，如果你安装了sql的话。
还有SMO操作表的增删改查操作，待续中。。。。

http://www.cnblogs.com/coolsoft/archive/2010/08/18/1802690.html

====================================================================================================