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

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.Text;using System.IO;using SQLDMO;using System.Collections;namespace ConsoleApplication1 / / SQLDMO辅助类 / / / 使用前添加 Microsoft SQLDMO Object Library COM 引用。 / public class SqlDmoHelper #region DatabaseInfo / / 数据库信息 / public struct DatabaseInfo public string Name; public string Owner; public string PrimaryFilePath; 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() 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, Name, 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 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 Version get return string.Format(0.1, sqlServer.VersionMajor, sqlServer.VersionMinor); / / 获取详细版本信息 / public string VersionString get return sqlServer.VersionString; / / 获取服务器时间 / public string ServerTime get return sqlServer.ServerTime; / / 获取系统服务名称 / public string ServiceName get return sqlServer.ServiceName; / / 获取或设置系统服务是否自动启动 / public bool AutostartServer get return sqlServer.Registry.AutostartServer; set sqlServer.Registry.AutostartServer = value; / / 获取字符集设置 / public string CharacterSet get return sqlServer.Registry.CharacterSet; / / 获取服务器物理内存大小(MB) / public int PhysicalMemory get return sqlServer.Registry.PhysicalMemory; / / 获取服务器处理器(CPU)数量 / public int NumberOfProcessors get return sqlServer.Registry.NumberOfProcessors; #endregion #region Public Method / / 获取网络内所有可用的服务器 / / public static string ListAvailableSQLServers() NameList servers = new ApplicationClass().ListAvailableSQLServers(); if (servers.Count = 0) return new string0; ArrayList list = new ArrayList(servers.Count); foreach (object o in servers) list.Add(o); return (string)list.ToArray(typeof(string); / / 断开数据库所有连接 / / public void KillAllProcess(string dbName) QueryResults qr = sqlServer.EnumProcesses(-1); / 获取SPID和DBNAME字段列序号 int iColPIDNum = -1; int iColDbName = -1; for (int i = 1; i = qr.Columns; i+) string strName = qr.get_ColumnName(i); if (strName.ToUpper().Trim() = SPID) iColPIDNum = i; else if (strName.ToUpper().Trim() = DBNAME) iColDbName = i; if (iColPIDNum != -1 & iColDbName != -1) break; / 将指定数据库的连接全部断开 for (int i = 1; i = qr.Rows; i+) int lPID = qr.GetColumnLong(i, iColPIDNum); string strDBName = qr.GetColumnString(i, iColDbName); if (string.Compare(strDBName, test, true) = 0) sqlServer.KillProcess(lPID); / / 获取数据库信息 / / / public DatabaseInfo GetDatabaseInfo(string dbName) Database db = GetDatabase(dbName); if (db = null) throw new Exception(Database not exists!); DatabaseInfo 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 = primary.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.MaximumSize; 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 AttachDB(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, 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; /dbFile.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.FileGrowthType=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 = 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; oBackup.Database = dbName; oBackup.Files = bakFile; oBackup.BackupSetName = bakSetName; oBackup.BackupSetDescription = bakDescription; oBackup.Initialize = true; oBackup.SQLBackup(sqlServer); / / 恢复数据库 / / / / / 恢复前最好调用KillAllProcess关闭所有连接，否则恢复可能失败。 / / / / SqlDmoHelper dmo = new SqlDmoHelper(local), sa, 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.ReplaceDatabase = 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; else return (string)list.ToArray(typeof(string); / / 获取所有登录名 / / / / 管理工具 安全性-登录 / public string ListAllLogins() ArrayList list = new ArrayList(); foreach (Login d in sqlServer.Logins) list.Add(d.Name); if (list.Count = 0) return new string0; else return (string)list.ToArray(typeof(string); / / 获取全部数据表名称 / / / public string ListAllTables(string dbName) Database db = GetDatabase(dbName); if (db = null) throw new Exception(Database not exists!); ArrayList list = new ArrayList(); foreach (Table t in db.Tables) list.Add(t.Name); if (list.Count = 0) return new string0; else return (string)list.ToArray(typeof(string); / / 获取全部存储过程名称 / / / public string ListAllStoredProcedure(string dbName) Database db = GetDatabase(dbName); if (db = null) throw new Exception(Database not exists!); ArrayList list = new ArrayList(); foreach (StoredProcedure sp in db.StoredProcedures) list.Add(sp.Name); if (list.Count = 0) return new string0; else return (string)list.ToArray(typeof(string); / / 获取数据库对象 / / / / / 可以通过数据库对象获取数据库内表、存储过程、触发器、数据类型等信息。 / / / 显示数据库中所有表及其结构 / / SqlDmoHelper dmo = new SqlDmoHelper(local), sa, sa); / SQ