using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LYFZ.WanYuKeFuData.DAL
{
  public  class DatabaseBackup
    {
      public DatabaseBackup() { 
      
      }
        /// <summary>
        /// 获取数据库备份文件名
        /// </summary>
        /// <param name="prefixName"></param>
        /// <returns></returns>
        public static string GetDatabaseBackupName(string prefixName)
        {
            return "(" + prefixName.Trim() + ")" + DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒.bak");
        }
        /// <summary>
        /// 执行数据库备份
        /// </summary>
        /// <param name="saveFileFullName">保存备份文件完全路径名</param>
        /// <returns></returns>
        public static string ExecutedDatabaseBackup(string saveFileFullName, string backDataBaseName,string connString)
        {
            string msg = "";

            StringBuilder BackupSql = new StringBuilder();
            BackupSql.Append("backup database @databaseName to disk=@diskPath");
            List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();

            parameterlist.Add(new System.Data.SqlClient.SqlParameter("@diskPath", saveFileFullName));
            parameterlist.Add(new System.Data.SqlClient.SqlParameter("@databaseName", backDataBaseName));

            System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                LYFZ.Helper.SQLHelper.ExecuteSql(BackupSql.ToString(), connString, parameters);
                msg = "数据库备份成功！";
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                msg = ex.Message;
            }
            return msg;
        }
        /// <summary>
        /// 执行数据库还原
        /// </summary>
        /// <param name="restoreDataBaseName">数据库备份文件所在完全路径名</param>
        /// <returns></returns>
        public static string ExecutedDatabaseRestore(string restoreDataFilePath, string restoreDataBaseName)
        {
            string msg = "";
            StringBuilder RestoreSql = new StringBuilder();
            RestoreSql.Append("/* 结束所有对当前数据库的连接 */\r\n");
            RestoreSql.Append("if exists(select 1 from sys.sysprocesses where dbid=db_id(@databaseName)) begin\r\n");
            RestoreSql.Append("declare #cs_spid cursor -- 声明游标\r\n");
            RestoreSql.Append("for\r\n");
            RestoreSql.Append("select #cs_spid=convert(varchar,spid) from sys.sysprocesses where dbid=db_id(@databaseName)\r\n");
            RestoreSql.Append("open #cs_spid\r\n");
            RestoreSql.Append("declare @spid varchar(20)\r\n");
            RestoreSql.Append("fetch next from #cs_spid into @spid -- 赋值并前进到下一条\r\n");
            RestoreSql.Append("while(@@fetch_status=0) begin -- 在fetch失败前执行\r\n");
            RestoreSql.Append("exec ('kill '+@spid) -- 结束对操作库的连接(exec执行SQL语句1)\r\n");
            RestoreSql.Append("fetch next from #cs_spid into @spid\r\n");
            RestoreSql.Append("end\r\n");
            RestoreSql.Append("close #cs_spid\r\n");
            RestoreSql.Append("deallocate #cs_spid -- 释放游标\r\n");
            RestoreSql.Append("end\r\n");
            RestoreSql.Append("/* 还原数据库 */\r\n");
            RestoreSql.Append("restore database @databaseName from disk=@diskPath");
            List<System.Data.SqlClient.SqlParameter> parameterlist = new List<System.Data.SqlClient.SqlParameter>();

            parameterlist.Add(new System.Data.SqlClient.SqlParameter("@databaseName", restoreDataBaseName));
            parameterlist.Add(new System.Data.SqlClient.SqlParameter("@diskPath", restoreDataFilePath));

            System.Data.SqlClient.SqlParameter[] parameters = parameterlist.ToArray();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                LYFZ.Helper.SQLHelper.ExecuteSqlToMaster(RestoreSql.ToString(), parameters);
                msg = "数据库还原成功！";
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                msg = (ex.Message);
            }
            return msg;
        }
      /// <summary>
      /// 获取待备份的数据库集合
      /// </summary>
      /// <returns></returns>
        public static List<string> GetWaitDatabaseBackupList()
        {
            List<string> waitBakDataBaseNameList = new List<string>();
            string sql = "select name From sysdatabases order by dbid asc ";
            string[] ignoreDbNames=new string[]{"master","tempdb","model","msdb","ReportServer","ReportServerTempDB"};
           System.Data.DataTable tb= LYFZ.Helper.SQLHelper.MasterQuery(sql,null).Tables[0];
           foreach (System.Data.DataRow row in tb.Rows)
           { 
               string dbName=row["name"].ToString();
               if (!String.IsNullOrEmpty(dbName) && !ignoreDbNames.Any(n => n.ToLower() == dbName.ToLower()))
               {
                   waitBakDataBaseNameList.Add(dbName);
               }
           }
            return waitBakDataBaseNameList;
        }

    }
}