C#使用SQLDMO操作数据库.doc

1、SQL-DMO 的全称是 SQL Distributed Management Objects。它是一个集合，包含一组有双重接口的 COM。通过 SQL-DMO 我们可以控制操作 SQL Server 的数据库引擎和复制管理。由于 SQL-DMO 是一组 COM，所以任何可以使用 COM 的开发工具都可以使用它，包括VB、Delphi、VC、BCB 、ASP 等等几乎包括所有的 Windows 平台下的开发工具。用他操作管理数据库很爽。using System;using System.Collections.Generic;using System.Linq;using System.Tex

2、t;using System.IO;using SQLDMO;using System.Collections;namespace ConsoleApplication1/ / SQLDMO 辅助类/ / / 使用前添加 “Microsoft SQLDMO Object Library“ COM 引用。/ public class SqlDmoHelper#region DatabaseInfo/ / 数据库信息/ public struct DatabaseInfopublic string Name;public string Owner;public string PrimaryFile

3、Path;public string CreateDate;public int Size;public float SpaceAvailable;public string PrimaryName;public string PrimaryFilename;public int PrimarySize;public int PrimaryMaxSize;public string LogName;public string LogFilename;public int LogSize;public int LogMaxSize;public override string ToString(

4、)string s = “Name:0rn“ +“Owner:1rn“ +“PrimaryFilePath:2rn“ +“CreateDate:3rn“ +“Size:4MBrn“ +“SpaceAvailable:5MBrn“ +“PrimaryName:6rn“ +“PrimaryFilename:7rn“ +“PrimarySize:8MBrn“ +“PrimaryMaxSize:9MBrn“ +“LogName:10rn“ +“LogFilename:11rn“ +“LogSize:12MBrn“ +“LogMaxSize:13MB“;return string.Format(s, N

5、ame, Owner, PrimaryFilePath, CreateDate, Size,SpaceAvailable, PrimaryName, PrimaryFilename, PrimarySize,PrimaryMaxSize, LogName, LogFilename, LogSize, LogMaxSize);#endregion 保钓军团private SQLServer2 sqlServer;private string server;private string login;private string password;public SqlDmoHelper(string

6、 server, string login, string password)this.server = server;this.login = login;this.password = password;sqlServer = new SQLServer2Class();sqlServer.Connect(server, login, password);public void Close()sqlServer.Close();#region Property/ / 获取主要版本号/ public string Versiongetreturn string.Format(“0.1“, s

7、qlServer.VersionMajor, sqlServer.VersionMinor);/ / 获取详细版本信息/ public string VersionStringgetreturn sqlServer.VersionString;/ / 获取服务器时间/ public string ServerTimegetreturn sqlServer.ServerTime;/ / 获取系统服务名称/ public string ServiceNamegetreturn sqlServer.ServiceName;/ / 获取或设置系统服务是否自动启动/ public bool Autost

8、artServergetreturn sqlServer.Registry.AutostartServer;setsqlServer.Registry.AutostartServer = value;/ / 获取字符集设置/ public string CharacterSetgetreturn sqlServer.Registry.CharacterSet;/ / 获取服务器物理内存大小(MB)/ public int PhysicalMemorygetreturn sqlServer.Registry.PhysicalMemory;/ / 获取服务器处理器(CPU)数量/ public i

9、nt NumberOfProcessorsgetreturn sqlServer.Registry.NumberOfProcessors;#endregion#region Public Method/ / 获取网络内所有可用的服务器/ / public static string ListAvailableSQLServers()NameList servers = new ApplicationClass().ListAvailableSQLServers();if (servers.Count / 断开数据库所有连接/ / public void KillAllProcess(strin

10、g dbName)QueryResults qr = sqlServer.EnumProcesses(-1);/ 获取 SPID 和 DBNAME 字段列序号int iColPIDNum = -1;int iColDbName = -1;for (int i = 1; i / 获取数据库信息/ / / public DatabaseInfo GetDatabaseInfo(string dbName)Database db = GetDatabase(dbName);if (db = null) throw new Exception(“Database not exists!“);Datab

11、aseInfo info = new DatabaseInfo();info.Name = db.Name;info.Owner = db.Owner;info.PrimaryFilePath = db.PrimaryFilePath;info.CreateDate = db.CreateDate;info.Size = db.Size;info.SpaceAvailable = db.SpaceAvailableInMB;DBFile primary = db.FileGroups.Item(“PRIMARY“).DBFiles.Item(1);info.PrimaryName = prim

12、ary.Name;info.PrimaryFilename = primary.PhysicalName.Trim();info.PrimarySize = primary.Size;info.PrimaryMaxSize = primary.MaximumSize;_LogFile log = db.TransactionLog.LogFiles.Item(1);info.LogName = log.Name;info.LogFilename = log.PhysicalName.Trim();info.LogSize = log.Size;info.LogMaxSize = log.Max

13、imumSize;return info;/ / 分离数据库/ / / / 分离前最好调用 KillAllProcess 关闭所有连接，否则分离可能失败。/ public void DetachDB(string dbName)sqlServer.DetachDB(dbName, true);/ / 附加数据库/ / / / / / SqlDmoHelper dmo = new SqlDmoHelper(“(local)“, “sa“, “sa“);/ dmo.AttachDB(“test“, “d:tempdatabasetest_data.mdf“);/ / public void Att

14、achDB(string dbName, string dbFile)sqlServer.AttachDB(dbName, dbFile);/ / 删除数据库(文件也将被删除)/ / public void KillDB(string dbName)sqlServer.KillDatabase(dbName);/ / 创建数据库/ / 数据库名称/ 数据文件保存路径/ 数据库文件名( 不含路径)/ 日志文件名( 不含路径)/ / / SqlDmoHelper dmo = new SqlDmoHelper(“(local)“, “sa“, “sa“);/ dmo.CreateDB(“test1“

15、, “d:tempdatabase“, “abc.mdf“, “abc1.ldf“);/ / public void CreateDB(string dbName, string path, string primaryFilename, string logFilename)/ 创建数据库文件DBFile dbFile = new DBFileClass();dbFile.Name = dbName + “_Data“;dbFile.PhysicalName = Path.Combine(path, primaryFilename);dbFile.PrimaryFile = true;/db

16、File.Size = 2; / 设置初始化大小(MB)/dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; / 设置文件增长方式/dbFile.FileGrowth=1; / 设置增长幅度/ 创建日志文件_LogFile logFile = new LogFileClass();logFile.Name = dbName + “_Log“;logFile.PhysicalName = Path.Combine(path, logFilename);/logFile.Size = 3;/logFile.FileGrowthTy

17、pe=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;/logFile.FileGrowth=1;/ 创建数据库Database db = new DatabaseClass();db.Name = dbName;db.FileGroups.Item(“PRIMARY“).DBFiles.Add(dbFile);db.TransactionLog.LogFiles.Add(logFile);/ 建立数据库联接，并添加数据库到服务器sqlServer.Databases.Add(db);/ / 备份数据库/ / / / / / / / SqlDmoHelper dmo =

18、new SqlDmoHelper(“(local)“, “sa“, “sa“);/ dmo.BackupDB(“test“, “d:tempdatabasetest.bak“, “手动备份 1“, “备份说明.“);/ / public void BackupDB(string dbName, string bakFile, string bakSetName, string bakDescription)Backup oBackup = new BackupClass();oBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;oB

19、ackup.Database = dbName;oBackup.Files = bakFile;oBackup.BackupSetName = bakSetName;oBackup.BackupSetDescription = bakDescription;oBackup.Initialize = true;oBackup.SQLBackup(sqlServer);/ / 恢复数据库/ / / / / 恢复前最好调用 KillAllProcess 关闭所有连接，否则恢复可能失败。/ / / / SqlDmoHelper dmo = new SqlDmoHelper(“(local)“, “sa

20、“, “sa“);/ dmo.RestoreDB(“test“, “d:tempdatabasetest.bak“);/ / public void RestoreDB(string dbName, string bakFile)Restore oRestore = new RestoreClass();oRestore.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;oRestore.Database = dbName;oRestore.Files = bakFile;oRestore.FileNumber = 1;oRestore.R

21、eplaceDatabase = true;oRestore.SQLRestore(sqlServer);/ / 收缩数据库/ / public void ShrinkDB(string dbName)Database db = GetDatabase(dbName);if (db = null) throw new Exception(“Database not exists!“);db.Shrink(0, SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default);/ / 获取所有的数据库名/ / public string ListAllDatabase()ArrayList list = new ArrayList();foreach (Database d in sqlServer.Databases)list.Add(d.Name);if (list.Count = 0)return new string0;elsereturn (string)list.ToArray(typeof(string);/ / 获取所有登录名/ / / / 管理工具 “安全性-登录“