打包还原数据库代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】打包还原数据库
作者:Dezai.CN / 发布于2012/12/19/ 470
在打包程序中新添加一个类项目，把class1.cs删掉，添加一个安装程序类文件，把下面代码写到这个类文件中即可
    /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="path">当前安装目录路径</param>
        public void ReplaceDataBase(string path)
        {
            string server = this.Context.Parameters["server"];//服务器名称
            string uid = this.Context.Parameters["user"];//SQlServer用户名
            string pwd = this.Context.Parameters["pwd"];//密码
            string ConnectionString = "server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=master";//连接数据库字符串
#region
            string  DataBaseOfBackupName = @"XX.bak";//备份文件名称
            string  DataBaseName = @"xxx";//还原时候的新建的数据库名称DBase
            string BackupFile =path + @DataBaseOfBackupName; //备份文件的完整的物理路径            
            DataTable dtFiles=new DataTable();//DataBaseName新建的数据库文件物理路径
        DataTable dtLogyc = new DataTable();//备份文件的文件信息，主要获取数据文件的逻辑名称，日志文件的逻辑名称
       DataSet dsTemp=dsGetTempInfo(BackupFile,ConnectionString);
       dtFiles = dsTemp.Tables[2];
           string NewDBpath=CreateDataBaseFile(dtFiles);//新创建的数据库文件的物理路径
       dtLogyc = dsTemp.Tables[1];
       string bakLogycName_mdf = dtLogyc.Rows[0][0].ToString();//备份文件逻辑文件名称
           string bakLogycName_log = dtLogyc.Rows[1][0].ToString();//备份文件逻辑文件名称日志名称
       //执行还原数据库的主要sql脚本语句。
       string sql = " RESTORE FILELISTONLY FROM DISK ='" + BackupFile
           + "' RESTORE DATABASE " + DataBaseName + " FROM DISK = '" + BackupFile + "' " +
           "WITH MOVE '" + bakLogycName_mdf + "' TO '" + NewDBpath + DataBaseName+".mdf',MOVE '" +
             bakLogycName_log + "' TO '" + NewDBpath + DataBaseName+"_log.ldf ',REPLACE";       
             SqlConnection   Conn = new SqlConnection(ConnectionString);
             try
             {
                 Conn.Open();
                 SqlCommand Comm = new SqlCommand();
                 Comm.Connection = Conn;
                 Comm.CommandText = sql;//
             Comm.Parameters.Add(new SqlParameter(@"DataBaseName", SqlDbType.NVarChar));
                 Comm.Parameters[@"DataBaseName"].Value = DataBaseName;
                 Comm.Parameters.Add(new SqlParameter(@"BackupFile", SqlDbType.NVarChar));
                 Comm.Parameters[@"BackupFile"].Value = BackupFile;
                 Comm.CommandType = CommandType.Text;
                 Comm.ExecuteNonQuery();
                 MessageBox.Show("还原数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
             }
#endregion
          catch (Exception ex)
             {
                 MessageBox.Show(ex.Message + ";" + sql, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
             }
            finally
            {
                Conn.Close();
            }
        }
 
    /// <summary>
    ///通过在master数据库中创建数据表'YtTemp_tb'来
        /// 获取备份文件中的信息
        /// </summary>
        /// <param name="bakPath">备份文件路劲包括文件名</param>
        /// <param name="conStr">连接字符串。</param>
        /// <returns></returns>
        public DataSet dsGetTempInfo(string bakPath, string conStr)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("USE [master]");
            // sql.AppendLine("GO");
            sql.AppendLine("if(exists(select *from sysobjects where name='YtTemp_tb'))");
            sql.AppendLine("begin");
            sql.AppendLine("drop table YtTemp_tb");
            sql.AppendLine("create table YtTemp_tb (LogicalName varchar(255), PhysicalName varchar(255),Type varchar(20),");
            sql.AppendLine(" FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20), Fileld VARCHAR(20), ");
            sql.AppendLine("CreateLSN VARCHAR(20), DropLSN VARCHAR(20), Uniqueld uniqueidentifier, ReadOnlyLSN VARCHAR(20),");
            sql.AppendLine(" ReadWriteLSN VARCHAR(20), BackupSizeInBytes VARCHAR(255), SourceBlockSize VARCHAR(20), FileGroupld VARCHAR(20),");
            sql.AppendLine(" LogGroupGUID VARCHAR(20), DifferentialBaseLSN VARCHAR(20), DifferentialBaseGUID uniqueidentifier, IsReadOnly VARCHAR(20), ");
            sql.AppendLine("IsPresent VARCHAR(20)) SELECT * FROM YtTemp_tb insert into YtTemp_tb ");
            sql.AppendLine("execute('RESTORE FILELISTONLY FROM DISK =''" + bakPath + "''') SELECT * FROM YtTemp_tb ");
            sql.AppendLine("select name, filename, * from dbo.sysfiles");
            sql.AppendLine("end");
            sql.AppendLine("else");
            sql.AppendLine("begin");
            sql.AppendLine("create table YtTemp_tb (LogicalName varchar(255), PhysicalName varchar(255),Type varchar(20),");
            sql.AppendLine(" FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20), Fileld VARCHAR(20), ");
            sql.AppendLine("CreateLSN VARCHAR(20), DropLSN VARCHAR(20), Uniqueld uniqueidentifier, ReadOnlyLSN VARCHAR(20),");
            sql.AppendLine(" ReadWriteLSN VARCHAR(20), BackupSizeInBytes VARCHAR(255), SourceBlockSize VARCHAR(20), FileGroupld VARCHAR(20),");
            sql.AppendLine(" LogGroupGUID VARCHAR(20), DifferentialBaseLSN VARCHAR(20), DifferentialBaseGUID uniqueidentifier, IsReadOnly VARCHAR(20),");
            sql.AppendLine("IsPresent VARCHAR(20)) SELECT * FROM YtTemp_tb insert into YtTemp_tb ");
            sql.AppendLine("execute('RESTORE FILELISTONLY FROM DISK =''" + bakPath + "''') SELECT * FROM YtTemp_tb ");
            sql.AppendLine("select name, filename, * from dbo.sysfiles");
            sql.AppendLine("end");
            //sql.AppendLine("create table #temp0 (LogicalName varchar(255), PhysicalName varchar(255),Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20), Fileld VARCHAR(20), CreateLSN VARCHAR(20), DropLSN VARCHAR(20), Uniqueld uniqueidentifier, ReadOnlyLSN VARCHAR(20), ReadWriteLSN VARCHAR(20), BackupSizeInBytes VARCHAR(255), SourceBlockSize VARCHAR(20), FileGroupld VARCHAR(20), LogGroupGUID VARCHAR(20), DifferentialBaseLSN VARCHAR(20), DifferentialBaseGUID uniqueidentifier, IsReadOnly VARCHAR(20), IsPresent VARCHAR(20)) SELECT * FROM #temp0 insert into #temp0 execute('RESTORE FILELISTONLY FROM DISK =''" + bakPath + "''') SELECT * FROM #temp0 ");//创建临时表，可以使用这个方法，不过如果创建失败的时候有点问题，所以我就没有使用临时表了。
            DataSet ds = new DataSet();
            SqlConnection con = new SqlConnection(conStr);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(sql.ToString(), con);
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return ds;
        }
 
 
/// <summary>
        /// 创建一个数据文件。获取创建的这个数据文件的路劲。
        /// </summary>
        /// <param name="dtFiles">新生成的数据库文件的路径信息</param>
        /// <returns></returns>
        public string CreateDataBaseFile(DataTable dtFiles)
        {
            string path1 = string.Empty;// @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data";
            try
            {
                int  temp = dtFiles.Rows[0][1].ToString().LastIndexOf(@"\");
                 path1 = dtFiles.Rows[0][1].ToString().Substring(0, temp+1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return path1;
        }
 
 
private void InstallDrivers(string path)
        {
        System.Reflection.Assembly Asm = System.Reflection.Assembly.GetExecutingAssembly();//获取当前程序集信息
            System.IO.FileInfo fileinfo = new System.IO.FileInfo(Asm.Location);//获取当前程序集位置
            string baseDir = fileinfo.DirectoryName;//获取文件夹名称
        result = MessageBox.Show("是否还原数据库？", "爱心提示", System.Windows.Forms.MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
            if (result == DialogResult.Cancel)
            {
                throw new Exception("还原数据库时用户中止！");
            }
            else if (result == DialogResult.Yes)
            {
                string dbPath = baseDir + @"\DataBase\";//@"\DataBase\" 打包项目中数据备份文件所在的目录文件夹

                ReplaceDataBase(dbPath);
            }
        }
 
//调用方法。
public override void Install(IDictionary stateSaver)
        {
            base.Install(stateSaver);
            //string IsInstallDriver = this.Context.Parameters["IsInstallDriver"].ToString();
            string IsInstallDriver = this.Context.Parameters["IsInstallDriver"].ToString();//获取安装程序的目标路径
            if (IsInstallDriver == "1")//如果选择安装，则
            {
                InstallDrivers(IsInstallDriver);
            }
     }
试试其它关键字
　还原数据库　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)