SQLDMO类的使用

SQLDMO（SQL Distributed Management Objects，SQL分布式管理对象）封装 Microsoft SQL Server 2000 数据库中的对象。SQL-DMO 允许用支持自动化或 COM 的语言编写应用程序，以管理 SQL Server 安装的所有部分。SQL-DMO 是 SQL Server 2000 中的 SQL Server 企业管理器所使用的应用程序接口 (API)；因此使用 SQL-DMO 的应用程序可以执行 SQL Server 企业管理器执行的所有功能。

SQL-DMO 用于必须包含 SQL Server 管理的任何自动化或 COM 应用程序，例如：
1.封装 SQL Server 作为自己的数据存储并想尽量减少用户的 SQL Server 管理任务的应用程序。
2.在程序本身并入了专门的管理逻辑的应用程序。
3.想在自己的用户界面中集成 SQL Server 管理任务的应用程序。

SQLDMO对象来自SQLDMO.dll，SQLDMO.dll是随SQL Server2000一起发布的。SQLDMO.dll自身是一个COM对象，因此，在你的.NET项目里必须先引用它。

/* 版本:V0.1(C#2.0)
* 当使用SQL Server时,请引用 COM组件中的,SQLDMO.dll组件
* 当使用Access中,请浏览添加引用以下两个dll
* 引用C:/Program Files/Common Files/System/ado/msadox.dll,该DLL包含ADOX命名空间
* 引用C:/Program Files/Common Files/System/ado/msjro.dll,该DLL包含JRO命名空间
* *******************************************************************************/
using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;
using ADOX;//该命名空间包含创建ACCESS的类(方法)--解决方案 ==> 引用 ==> 添加引用 ==> 游览找到.dll
using JRO;//该命名空间包含压缩ACCESS的类(方法)

namespace xjy.JXCSYS
{
    /// <summary>
    /// 数据库恢复和备份
    /// </summary>
    public class SqlBackObject
    {
        public SqlBackObject()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        #region SQL数据库备份
        /// <summary>
        /// SQL数据库备份
        /// </summary>
        /// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
        /// <param name="LoginName">数据库登录名</param>
        /// <param name="LoginPass">数据库登录密码</param>
        /// <param name="DBName">数据库名</param>
        /// <param name="BackPath">备份到的路径</param>
        public static bool SQLBACK(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
        {
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(ServerIP, LoginName, LoginPass);
                oBackup.Database = DBName;
                oBackup.Files = BackPath;
                oBackup.BackupSetName = DBName;
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                return true;
            }
            catch (Exception e)
            {
                return false;
                throw new Exception(e.ToString());
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }
        #endregion

        #region SQL恢复数据库
        /// <summary>
        /// SQL恢复数据库
        /// </summary>
        /// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
        /// <param name="LoginName">数据库登录名</param>
        /// <param name="LoginPass">数据库登录密码</param>
        /// <param name="DBName">要还原的数据库名</param>
        /// <param name="BackPath">数据库备份的路径</param>

        public static bool SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
        {
            if (exepro(ServerIP, LoginName, LoginPass, DBName) != true)
            {
                return false;
            }
            else
            {
                SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
                try
                {
                    exepro(ServerIP, LoginName, LoginPass, DBName);
                    oSQLServer.LoginSecure = false;
                    oSQLServer.Connect(ServerIP, LoginName, LoginPass);
                    orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                    orestore.Database = DBName;
                    orestore.Files = BackPath;
                    orestore.FileNumber = 1;
                    orestore.ReplaceDatabase = true;
                    orestore.SQLRestore(oSQLServer);
                    return true;
                }
                catch (Exception e)
                {
                    return false;
                    throw new Exception(e.ToString());
                }
                finally
                {
                    oSQLServer.DisConnect();
                }
            }
        }

        #endregion

        #region 根据指定的文件名称创建Access数据库
        /// <summary>
        /// 根据指定的文件名称创建数据
        /// </summary>
        /// <param name="DBPath">绝对路径+文件名称</param>
        public static void CreateAccess(string DBPath)
        {
            if (File.Exists(DBPath))//检查数据库是否已存在
            {
                throw new Exception("目标数据库已存在,无法创建");
            }
            DBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath;
            //创建一个CatalogClass对象实例
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            //使用CatalogClass对象的Create方法创建ACCESS数据库
            cat.Create(DBPath);

        }
        #endregion

        #region 压缩Access数据库
        /// <summary>
        /// 压缩Access数据库
        /// </summary>
        /// <param name="DBPath">数据库绝对路径</param>
        public static void CompactAccess(string DBPath)
        {
            if (!File.Exists(DBPath))
            {
                throw new Exception("目标数据库不存在,无法压缩");
            }

            //声明临时数据库名称
            string temp = DateTime.Now.Year.ToString();
            temp += DateTime.Now.Month.ToString();
            temp += DateTime.Now.Day.ToString();
            temp += DateTime.Now.Hour.ToString();
            temp += DateTime.Now.Minute.ToString();
            temp += DateTime.Now.Second.ToString() + ".bak";
            temp = DBPath.Substring(0, DBPath.LastIndexOf("//") + 1) + temp;
            //定义临时数据库的连接字符串
            string temp2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + temp;
            //定义目标数据库的连接字符串
            string DBPath2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath;
            //创建一个JetEngineClass对象的实例
            JRO.JetEngineClass jt = new JRO.JetEngineClass();
            //使用JetEngineClass对象的CompactDatabase方法压缩修复数据库
            jt.CompactDatabase(DBPath2, temp2);
            //拷贝临时数据库到目标数据库(覆盖)
            File.Copy(temp, DBPath, true);
            //最后删除临时数据库
            File.Delete(temp);
        }
        #endregion

        #region 备份Access数据库
        /// <summary>
        /// 备份Access数据库
        /// </summary>
        /// <param name="srcPath">要备份的数据库绝对路径</param>
        /// <param name="aimPath">备份到的数据库绝对路径</param>
        /// <returns></returns>
        public static void Backup(string srcPath, string aimPath)
        {

            if (!File.Exists(srcPath))
            {
                throw new Exception("源数据库不存在,无法备份");
            }
            try
            {
                File.Copy(srcPath, aimPath, true);
            }
            catch (IOException ixp)
            {
                throw new Exception(ixp.ToString());
            }

        }

        #endregion

        #region 还原Access数据库
        /// <summary>
        /// 还原Access数据库
        /// </summary>
        /// <param name="bakPath">备份的数据库绝对路径</param>
        /// <param name="dbPath">要还原的数据库绝对路径</param>
        public static void RecoverAccess(string bakPath, string dbPath)
        {
            if (!File.Exists(bakPath))
            {
                throw new Exception("备份数据库不存在,无法还原");
            }
            try
            {
                File.Copy(bakPath, dbPath, true);
            }
            catch (IOException ixp)
            {
                throw new Exception(ixp.ToString());
            }
        }
        #endregion

        /// <summary>
        /// 切割字符串
        /// </summary>
        /// <param name="str"></param>
        /// <param name="bg"></param>
        /// <param name="ed"></param>
        /// <returns></returns>
        public static string StringCut(string str, string bg, string ed)
        {
            string sub;
            sub = str.Substring(str.IndexOf(bg) + bg.Length);
            sub = sub.Substring(0, sub.IndexOf(";"));
            return sub;
        }

        /// <summary>
        /// 杀死当前库的所有进程
        /// </summary>
        /// <returns></returns>
        private static bool exepro(string ServerIP, string LoginName, string LoginPass, string DBName)
        {
            SqlConnection conn1 = new SqlConnection("server=" + ServerIP + ";uid=" + LoginName + ";pwd=" + LoginPass
                + ";database=master");
            SqlCommand cmd = new SqlCommand("killspid", conn1);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@dbname", DBName);
            try
            {
                conn1.Open();
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn1.Close();
            }
        }

    }
}

 

 

CREATE PROCEDURE killspid
/*----------------------------------
杀死该库所有进程，放在系统数据库master
----------------------------------*/
(
@dbname varchar(20) --数据库的名称
)
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @spid int
SET @sql='declare getspid cursor for select spid from sys.sysprocesses where dbid=db_id('''+@dbname+''')'
EXECUTE (@sql)
OPEN getspid
FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status<>-1
BEGIN
EXECUTE('kill '+@spid)
FETCH NEXT FROM getspid INTO @spid
END
CLOSE getspid
DEALLOCATE getspid
END
GO

 

        private void Back()
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Back files (*.bak)|*.bak";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "备份文件保存路径";
            saveFileDialog.InitialDirectory = @"d:/";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                this.Refresh();
                string conn = Maticsoft.DBUtility.PubConstant.ConnectionString;
                string ServerIP = SqlBackObject.StringCut(conn, "server=", ";");
                string LoginName = SqlBackObject.StringCut(conn, "uid=", ";");
                string LoginPass = SqlBackObject.StringCut(conn, "pwd=", ";");
                string DBName = SqlBackObject.StringCut(conn, "database=", ";");
                string BackPath = saveFileDialog.FileName;
                if (SqlBackObject.SQLBACK(ServerIP, LoginName, LoginPass, DBName, BackPath))
                {
                    MessageBox.Show("数据库备份成功！/n", "备份成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("数据库备份失败！/n", "备份失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

 

        private void Restore()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Back files (*.bak)|*.bak";
            openFileDialog.FilterIndex = 0;
            openFileDialog.RestoreDirectory = true;
            openFileDialog.Title = "恢复文件保存路径";
            openFileDialog.InitialDirectory = @"d:/";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                this.Refresh();
                string conn = Maticsoft.DBUtility.PubConstant.ConnectionString;
                string ServerIP = SqlBackObject.StringCut(conn, "server=", ";");
                string LoginName = SqlBackObject.StringCut(conn, "uid=", ";");
                string LoginPass = SqlBackObject.StringCut(conn, "pwd=", ";");
                string DBName = SqlBackObject.StringCut(conn, "database=", ";");
                string BackPath = openFileDialog.FileName;
                if (SqlBackObject.SQLDbRestore(ServerIP, LoginName, LoginPass, DBName, BackPath))
                {
                    MessageBox.Show("数据库恢复成功！/n", "恢复成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("数据库恢复失败！/n", "恢复失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }