封裝SQLDMO操作的類，能完成常用的SQL Server 2000管理工作。
使用前請添加 "Microsoft SQLDMO Object Library" COM 引用。
有部分代碼借鑒網絡資料，再次向原作者表示感謝。
/* *********************************************** Rainsoft Development Library for Microsoft.NET* Author: Q.yuhen (qyuhen@hotmail.com)********************************************** */ using System; using System.Collections; using System.Runtime.InteropServices; using System.IO; using SQLDMO;namespace Rainsoft.Data {/// <summary>/// SQLDMO輔助類/// </summary>/// <remarks>/// 使用前添加 "Microsoft SQLDMO Object Library" COM 引用。/// </remarks>public class SqlDmoHelper{#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);}}#endregionprivate 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/// <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 string ServiceName{get{return sqlServer.ServiceName;}}/// <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></returns>public static string[] ListAvailableSQLServers(){NameList servers = new ApplicationClass().ListAvailableSQLServers();if (servers.Count <= 0) return new string[0];ArrayList list = new ArrayList(servers.Count);foreach (object o in servers) list.Add(o);return (string[])list.ToArray(typeof(string));}/// <summary>/// 斷開數據庫所有連接/// </summary>/// <param name="dbName"></param>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); } }/// <summary>/// 獲取數據庫信息/// </summary>/// <param name="dbName"></param>/// <returns></returns>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;}/// <summary>/// 分離數據庫/// </summary>/// <param name="dbName"></param>/// <remarks>/// 分離前最好調用KillAllProcess關閉所有連接，否則分離可能失敗。/// </remarks>public void DetachDB(string dbName){sqlServer.DetachDB(dbName, true);}/// <summary>/// 附加數據庫/// </summary>/// <param name="dbName"></param>/// <param name="dbFile"></param>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.AttachDB("test", @"d:\temp\database\test_data.mdf");/// </code>/// </example>public void AttachDB(string dbName, string dbFile){sqlServer.AttachDB(dbName, dbFile);}/// <summary>/// 刪除數據庫(文件也將被刪除)/// </summary>/// <param name="dbName"></param>public void KillDB(string dbName){sqlServer.KillDatabase(dbName);}/// <summary>/// 創建數據庫/// </summary>/// <param name="dbName">數據庫名稱</param>/// <param name="path">數據文件保存路徑</param>/// <param name="primaryFilename">數據庫文件名(不含路徑)</param>/// <param name="logFilename">日志文件名(不含路徑)</param>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.CreateDB("test1", @"d:\temp\database", "abc.mdf", "abc1.ldf");/// </code>/// </example>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);}/// <summary>/// 備份數據庫/// </summary>/// <param name="dbName"></param>/// <param name="bakFile"></param>/// <param name="bakSetName"></param>/// <param name="bakDescription"></param>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.BackupDB("test", @"d:\temp\database\test.bak", "手動備份1", "備份說明...");/// </code>/// </example>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);}/// <summary>/// 恢復數據庫/// </summary>/// <param name="dbName"></param>/// <param name="bakFile"></param>/// <remarks>/// 恢復前最好調用KillAllProcess關閉所有連接，否則恢復可能失敗。/// </remarks>/// <example>/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// dmo.RestoreDB("test", @"d:\temp\database\test.bak");/// </code>/// </example>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);}/// <summary>/// 收縮數據庫/// </summary>/// <param name="dbName"></param>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);}/// <summary>/// 獲取所有的數據庫名/// </summary>/// <returns></returns>public string[] ListAllDatabase(){ArrayList list = new ArrayList();foreach(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></returns>/// <remarks>/// 管理工具 "安全性->登錄"/// </remarks>public string[] ListAllLogins(){ArrayList list = new ArrayList();foreach(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></returns>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 string[0];elsereturn (string[])list.ToArray(typeof(string));}/// <summary>/// 獲取全部存儲過程名稱/// </summary>/// <param name="dbName"></param>/// <returns></returns>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 string[0];elsereturn (string[])list.ToArray(typeof(string));}/// <summary>/// 獲取數據庫對象/// </summary>/// <param name="dbName"></param>/// <returns></returns>/// <remarks>/// 可以通過數據庫對象獲取數據庫內表、存儲過程、觸發器、數據類型等信息。/// </remarks>/// <example>/// 顯示數據庫中所有表及其結構/// <code>/// SqlDmoHelper dmo = new SqlDmoHelper("(local)", "sa", "sa");/// SQLDMO.Database db = dmo.GetDatabase("test");/// foreach(SQLDMO.Table t in db.Tables)/// {/// Console.WriteLine("Table:{0}", t.Name);/// for (int i = 1; i &lt;= 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 Database GetDatabase(string dbName){foreach(Database d in sqlServer.Databases){if (string.Compare(d.Name, dbName, true) == 0)return d;}return null;}#endregion} }

總結

以上是生活随笔為你收集整理的封装SQLDMO操作的类的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。