在C#中运用SQLDMO操作SQL Server 2000

在C#中运用SQLDMO能完成常用的SQL Server 2000管理工作。

SQLDMO(SQL Distributed Management Objects，SQL分布式管理对象)封装了SQL Server 2000数据库中的对象。

SQLDMO是SQL Server 2000中企业管理器所使用的应用程序接口，所以它可以执行很多功能，包括对数据库的创建、备份、恢复、删除、分离和和收缩以及各种对象信息的查询等等。

(SQLDMO.DLL放在C:/Program Files/Microsoft SQL Server/80/Tools/Binn目录下，使用前请添加该文件的引用。)

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

using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.IO;

/// <summary>/// SQLDMO辅助类/// </summary>/// <remarks>/// 使用前请添加 "SQLDMO.DLL"的引用。/// </remarks>public class SqlDmoHelper{private SQLDMO.SQLServer sqlServer;SQLDMO.Application sqlApp;private string server;//服务器名 (如：localhost)private string loginname;//登录名 (如：sa)private string password;//密码 (如：sa)

    public SqlDmoHelper(string server, string loginname, string password){this.server = server;this.loginname = loginname;this.password = password;

        sqlApp = new SQLDMO.Application();sqlServer = new SQLDMO.SQLServerClass();sqlServer.Connect(server, loginname, password);}

    #region DatabaseInfo/// <summary>/// 数据库信息/// </summary>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:{0}/r/n" +"Owner:{1}/r/n" +"PrimaryFilePath:{2}/r/n" +"CreateDate:{3}/r/n" +"Size:{4}MB/r/n" +"SpaceAvailable:{5}MB/r/n" +"PrimaryName:{6}/r/n" +"PrimaryFilename:{7}/r/n" +"PrimarySize:{8}MB/r/n" +"PrimaryMaxSize:{9}MB/r/n" +"LogName:{10}/r/n" +"LogFilename:{11}/r/n" +"LogSize:{12}MB/r/n" +"LogMaxSize:{13}MB";

            return string.Format(s, Name, Owner, PrimaryFilePath, CreateDate, Size,SpaceAvailable, PrimaryName, PrimaryFilename, PrimarySize,PrimaryMaxSize, LogName, LogFilename, LogSize, LogMaxSize);}}#endregion

    #region Property/// <summary>/// 获取主要版本信息/// </summary>public string Version{get{return string.Format("{0}.{1}", sqlServer.VersionMajor, sqlServer.VersionMinor);}}/// <summary>/// 获取详细版本信息/// </summary>public string VersionString{get{return sqlServer.VersionString;}}/// <summary>/// 获取服务器时间/// </summary>public string ServerTime{get{return sqlServer.ServerTime;}}/// <summary>/// 获取或设置系统服务是否自动启动/// </summary>public bool AutostartServer{get{return sqlServer.Registry.AutostartServer;}set{sqlServer.Registry.AutostartServer = value;}}/// <summary>/// 获取字符集设置/// </summary>public string CharacterSet{get{return sqlServer.Registry.CharacterSet;}}/// <summary>/// 获取服务器物理内存大小(MB)/// </summary>public int PhysicalMemory{get{return sqlServer.Registry.PhysicalMemory;}}/// <summary>/// 获取服务器处理器(cpu)的数量/// </summary>public int NumberOfProcessors{get{return sqlServer.Registry.NumberOfProcessors;}}#endregion

    #region Public Method/// <summary>/// 获取网络内所有可用的服务器/// </summary>/// <returns>string[]</returns>public static string[] GetListAvailableSQLServers(){SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();SQLDMO.NameList servers = sqlApp.ListAvailableSQLServers();if (servers.Count <= 0)return new string[0];ArrayList list = new ArrayList(servers.Count);foreach (object server in servers){list.Add(server);}return (string[])list.ToArray(typeof(string));}

    /// <summary>/// 杀死正在使用指定的数据库进程/// </summary>/// <param name="dbName">数据库名</param>/// <returns>是否杀死进程</returns>public bool KillAllProcess(string dbName){bool flag = false;try{SQLDMO.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 IPID = qr.GetColumnLong(i, iColPIDNum);string strDBName = qr.GetColumnString(i, iColDbName);

                if (string.Compare(strDBName, dbName, true) == 0){sqlServer.KillProcess(IPID);}}flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 获取数据库实体信息/// </summary>/// <param name="dbName">数据库名</param>/// <returns>数据库实体信息</returns>public DatabaseInfo GetDatabaseInfo(string dbName){SQLDMO.Database db = GetDBObj(dbName);if (db == null){throw new Exception("Database not exists!"); }DatabaseInfo info = new DatabaseInfo();info.Name = db.Name;info.Owner = db.Owner;info.PrimaryFilename = db.PrimaryFilePath;info.CreateDate = db.CreateDate;info.Size = db.Size;info.SpaceAvailable = db.SpaceAvailableInMB;

        SQLDMO.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;

        SQLDMO._LogFile log = db.TransactionLog.LogFiles.Item(1);info.LogName = log.Name;info.LogFilename = log.PhysicalName;info.LogSize = log.Size;info.LogMaxSize = log.MaximumSize;return info;}

    /// <summary>/// 分离数据库/// </summary>/// <param name="dbName">数据库名</param>/// <returns>分离是否成功</returns>/// <remarks>/// 分离前要调用KillAllProcess关闭所有连接，否则分离可能失败。/// </remarks>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.DetachDB("test");/// </code>/// </example>public bool DetachDB(string dbName){bool flag = false;try{KillAllProcess(dbName);sqlServer.DetachDB(dbName, true);flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 附加数据库/// </summary>/// <param name="dbName">数据库名</param>/// <param name="dbFile">dbFile</param>/// <returns>附加是否成功</returns>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.AttachDB("test", @"d:/temp/database/test_data.mdf");/// </code>/// </example>public bool AttachDB(string dbName, string dbFile){bool flag = false;try{sqlServer.AttachDB(dbName,dbFile);flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 物理删除数据库/// </summary>/// <param name="dbName">数据库名</param>/// <returns>删除是否成功</returns>/// <remarks>/// 删除前要调用KillAllProcess关闭所有连接，否则删除可能失败。/// </remarks>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.DeleteDB("test");/// </code>/// </example>public bool DeleteDB(string dbName){bool flag = false;try{KillAllProcess(dbName); //先杀死正在使用中的数据库进程sqlServer.KillDatabase(dbName);flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 创建数据库/// </summary>/// <param name="dbName">数据库名称</param>/// <param name="path">数据文件保存路径</param>/// <param name="primaryFileName">数据库文件名(不含路径)</param>/// <param name="logFileName">日志文件名(不含路径)</param>/// <returns>创建是否成功</returns>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.CreateDB("test1", @"d:/temp/database", "abc.mdf", "abc1.ldf");/// </code>/// </example>public bool CreateDB(string dbName, string path, string primaryFileName, string logFileName){bool flag = false;try{//创建数据库文件SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();dbFile.Name = dbName + "_Data";dbFile.PhysicalName = Path.Combine(path, primaryFileName);dbFile.PrimaryFile = true;dbFile.Size = 2;//初始化大小(MB)dbFile.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;dbFile.FileGrowth = 1;//设置增长幅度

            //创建日志文件SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();logFile.Name = dbName + "_Log";logFile.PhysicalName = Path.Combine(path, logFileName);logFile.Size = 3;logFile.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;logFile.FileGrowth = 1;

            //创建数据库SQLDMO.Database db = new SQLDMO.Database();db.Name = dbName;db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);db.TransactionLog.LogFiles.Add(logFile);

            //建立数据库连接，并添加数据库到服务器sqlServer.Databases.Add(db);flag = true;}catch (Exception ex){flag = false; ;throw ex;}return flag;}

    /// <summary>/// 创建数据库/// </summary>/// <param name="dbName">数据库名</param>/// <returns></returns>public string CreateDB(string dbName){//函数返回结果string rvCDB = "";//新建数据库(包括路径)string dbPath = sqlServer.Registry.SQLDataRoot + "//DATA//" + dbName;bool DBExist = false;foreach (SQLDMO.Database db in sqlServer.Databases){if (db.Name == dbName){DBExist = true;}}try{if (DBExist){rvCDB = "此数据库存名已存在请选择其它名称!";sqlServer.Close();sqlApp.Quit();return rvCDB;}else{rvCDB = "创建数据库成功!";}SQLDMO.Database nDB = new SQLDMO.Database();SQLDMO.DBFile nDBFile = new SQLDMO.DBFile();SQLDMO.LogFile nLogFile = new SQLDMO.LogFile();

            nDB.Name = dbName;//数据库名nDBFile.Name = dbName + "file";//数据库文件名nDBFile.PhysicalName = dbPath + "_Data.mdf";//数据库文件在硬盘上存储的实际名称nDBFile.PrimaryFile = true;nDBFile.FileGrowthType = 0;nDBFile.FileGrowth = 1;nDB.FileGroups.Item("primary").DBFiles.Add(nDBFile);

            nLogFile.Name = dbName + "log";//日志文件名nLogFile.PhysicalName = dbPath + "_Log.ldf";//日志文件在硬盘上存储的实际名称nDB.TransactionLog.LogFiles.Add(nLogFile);sqlServer.Databases.Add(nDB);sqlServer.Close();sqlApp.Quit();}catch (Exception ex){throw new Exception("创建数据库失败! 错误信息：" + ex.Message);}return rvCDB;}

    /// <summary>/// 备份数据库/// </summary>/// <param name="dbName">要备份的数据库名</param>/// <param name="bakFile">备份文件名(全路径)</param>/// <param name="bakSetName">设置的备份文件名</param>/// <param name="bakDesc">备份说明</param>/// <returns>备份是否成功</returns>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.BackupDB("test", @"d:/temp/database/test.bak", "手动备份1", "备份说明...");/// </code>/// </example>public bool BackupDB(string dbName, string bakFilePath, string bakSetName, string bakDesc){bool flag = false;try{SQLDMO.Backup oBackup = new SQLDMO.Backup();oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;oBackup.Database = dbName;oBackup.Files = bakFilePath;oBackup.BackupSetName = bakSetName;oBackup.BackupSetDescription = bakDesc;oBackup.Initialize = true;oBackup.SQLBackup(sqlServer);flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 恢复(还原)数据库/// </summary>/// <param name="dbName">数据库名</param>/// <param name="bakFilePath">数据库备份文件的完整路径名</param>/// <returns>恢复(还原)是否成功</returns>/// <remarks>/// 恢复前最好调用KillAllProcess关闭所有连接，否则恢复可能失败。/// </remarks>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.RestoreDB("test", @"d:/temp/database/test.bak");/// </code>/// </example>public bool RestoreDB(string dbName, string bakFilePath){bool flag = false;try{KillAllProcess(dbName); //先杀死正在使用中的数据库进程SQLDMO.Restore oRestore = new SQLDMO.Restore();oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;oRestore.Database = dbName;oRestore.Files = bakFilePath;oRestore.FileNumber = 1;oRestore.ReplaceDatabase = true;oRestore.SQLRestore(sqlServer);flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 收缩数据库/// </summary>/// <param name="dbName">数据库名</param>/// <returns>收缩是否成功</returns>public bool ShrinkDB(string dbName){bool flag = false;try{SQLDMO.Database db = new SQLDMO.Database();if (db == null)throw new Exception("Database not exists!");db.Shrink(0, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default);flag = true;}catch (Exception ex){flag = false;throw ex;}return flag;}

    /// <summary>/// 获取所有的数据库名/// </summary>/// <returns>string[]</returns>public string[] GetAllDatabasesList(){ArrayList list = new ArrayList();foreach (SQLDMO.Database d in sqlServer.Databases){list.Add(d.Name);}

        if (list.Count == 0)return new string[0];elsereturn (string[])list.ToArray(typeof(string));}

    /// <summary>/// 获取所有登录用户/// </summary>/// <returns>string[]</returns>/// <remarks>/// 管理工具 "安全性->登录"/// </remarks>public string[] GetAllLoginsList(){ArrayList list = new ArrayList();foreach (SQLDMO.Login d in sqlServer.Logins){list.Add(d.Name);}

        if (list.Count == 0)return new string[0];elsereturn (string[])list.ToArray(typeof(string));}

    /// <summary>/// 获取所有数据表名称/// </summary>/// <param name="dbName">数据库名</param>/// <returns>string[]</returns>public string[] GetAllTablesList(string dbName){SQLDMO.Database db = GetDBObj(dbName);if (db == null)throw new Exception("Data not exists!");ArrayList list = new ArrayList();foreach (SQLDMO.Table t in db.Tables){list.Add(t.Name);}

        if (list.Count == 0)return new string[0];elsereturn (string[])list.ToArray(typeof(string));}

    /// <summary>/// 获取数据库的所有存储过程名/// </summary>/// <param name="dbName">数据库名</param>/// <returns>string[]</returns>public string[] GetAllStoredProceduresList(string dbName){SQLDMO.Database db = GetDBObj(dbName);if (db == null)throw new Exception("Data not exists!");ArrayList list = new ArrayList();foreach (SQLDMO.StoredProcedure sp in db.StoredProcedures){list.Add(sp.Name);}

        if (list.Count == 0)return new string[0];elsereturn (string[])list.ToArray(typeof(string));}

    /// <summary>/// 获取数据库对象/// </summary>/// <param name="dbName">数据库名</param>/// <returns>SQLDMO.Database</returns>/// <remarks>/// 可以通过数据库对象获取数据库内表、存储过程、触发器、数据类型等信息。/// </remarks>/// <example>/// 显示数据库中所有表及其结构/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// SQLDMO.Database db = dmo.GetDBObj("test");/// foreach(SQLDMO.Table t in db.Tables)/// {///     Console.WriteLine("Table:{0}", t.Name);///     for (int i = 1; i <= t.Columns.Count; i++) // SQLDMO所有索引序号从1开始///     {///       SQLDMO._Column col = t.Columns.Item(i);///       Console.WriteLine(" Column:{0} DataType:{1}", col.Name, col.Datatype);///     } ///     Console.WriteLine("---------------");/// }/// </code>/// </example>public SQLDMO.Database GetDBObj(string dbName){foreach (SQLDMO.Database db in sqlServer.Databases){if (string.Compare(db.Name, dbName, true) == 0)return db;}return null;}#endregion}