using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;
using System.Collections;


    /// <summary>
    /// Summary description for conn
    /// </summary>
    public class conn
    {
        public conn()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public static OleDbConnection connection()
        {
            System.Web.UI.Page pg = new System.Web.UI.Page();
            String strConnection = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + pg.Server.MapPath("data/test.mdb");
            OleDbConnection myconn = new OleDbConnection(strConnection);
            //myconn.Open();
            return myconn;
        }

        /// <summary>
        /// 连接mssql2000
        /// </summary>
        /// <returns></returns>
        public static SqlConnection sqlconn()
        {
            System.Web.UI.Page pg = new System.Web.UI.Page();
            SqlConnection myconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["wormbookConnectionString"].ToString());
            //myconn.Open();
            return myconn;
        }

        /// <summary>
        /// 结束mssql2000数据库连接进程
        /// </summary>
        /// <returns></returns>
        public static SqlConnection killconn()
        {
            System.Web.UI.Page pg = new System.Web.UI.Page();
            SqlConnection myconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["killConnectionString"].ToString());
            //myconn.Open();
            return myconn;
        }

     }

public class DbOper
{
    /// <summary> 
    /// DbOper类的构造函数 
    /// </summary> 

    public DbOper()
    {

    }
    protected ArrayList Lbkid = new ArrayList();
    protected ArrayList Lbkname = new ArrayList();
    protected ArrayList Lbkpath = new ArrayList();
    protected ArrayList Lbktime = new ArrayList();
    protected ArrayList Lrstime = new ArrayList();
    /// <summary> 
    /// 数据库备份 
    /// </summary> 
    /// server:服务器地址 ad:sql账户 pw:sql密码 db:要备份数据库名称 bkpath:备份路径 bkname:备份的文件名 des:描述
    public void DbBackup(string server, string ad, string pw, string db, string bkpath, string bkname, string des)
    {
        SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
        SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
        try
        {
            oSQLServer.LoginSecure = false;
            //oSQLServer.Connect("localhost", "sa", "1234");
            oSQLServer.Connect(server, ad, pw);
            oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
            //oBackup.Database = "Northwind";
            oBackup.Database = db;
            //oBackup.Files = @"d:\Northwind.bak";
            oBackup.Files = bkpath;
            //oBackup.BackupSetName = "Northwind";
            oBackup.BackupSetName = bkname;
            //oBackup.BackupSetDescription = "数据库备份";
            oBackup.BackupSetDescription = des;
            oBackup.Initialize = true;
            oBackup.SQLBackup(oSQLServer);
        }
        catch (System.Exception ee)
        {
            throw;
        }
        finally
        {
            oSQLServer.DisConnect();
        }
    }

    /// <summary> 
    /// 数据库恢复 
    /// </summary> 
    /// server:服务器地址 ad:sql账户 pw:sql密码 resname:要还原数据库名称 respath:要还原的备份数据库的路径
    public void DbRestore(string server, string ad, string pw, string resname, string respath)
    {
        //if (exepro() != true)//执行存储过程
        //{
            
        //}
        //else
        //{
            tempdb();    //缓存备份数据库列表
            exepro();   //还原数据库
            
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            //SqlConnection one = conn.sqlconn();
            try
            {

                //one.Open();
                oSQLServer.LoginSecure = false;
                //oSQLServer.Connect("localhost", "sa", "1234");
                oSQLServer.Connect(server, ad, pw);
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                //oRestore.Database = "Northwind";
                oRestore.Database = resname;
                //oRestore.Files = @"d:\Northwind.bak";
                oRestore.Files = respath;
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);
                
            }
            catch
            {
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
                //one.Close();
            }
            Excudb();   //导入回原来的备份数据库列表
        //}
    }



    public void exepro()
    {
      SqlConnection one = conn.sqlconn();
      SqlCommand Cmd = new SqlCommand();
      string sql;
      try
      {
          one.Open();
          //获取所有用户进程 
          //使用master数据表
          sql = "select * from db_backup";
          Cmd.CommandText = sql;
          Cmd.Connection = one;

          sql = "use [master]";
          Cmd.CommandText = sql;
          Cmd.Connection = one;
          Cmd.ExecuteNonQuery();

          //用户conn连接进程都是在master系统数据库中，把master的进程全部读取出来
          sql = "select   spid   from   master..sysprocesses   where   dbid=db_id( 'wormbook ')";
          SqlDataAdapter Da = new SqlDataAdapter(sql, one);
          DataTable spidTable = new DataTable();
          Da.Fill(spidTable);

          Cmd = new SqlCommand();
          Cmd.CommandType = CommandType.Text;
          Cmd.Connection = one;

          for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++)
          {
              Cmd.CommandText = "kill   " + spidTable.Rows[iRow][0].ToString();   //强行关闭用户进程 
              Cmd.ExecuteNonQuery();
          }
          Da.Dispose();
      }
      catch { }
      finally 
      {
          Cmd.Dispose();
          one.Close();
      }
    }

    //暂存备份数据库的列表，以便数据库还原后再导入
    public void tempdb()
    {
        SqlConnection one = conn.sqlconn();
        SqlCommand Cmd = new SqlCommand("select * from db_backup",one);

        try
        {
            one.Open();
            SqlDataReader rs = Cmd.ExecuteReader();
            while (rs.Read())
            {
                Lbkid.Add(rs["id"]);
                Lbkname.Add(rs["bkname"]);
                Lbkpath.Add(rs["bkpath"]);
                Lbktime.Add(rs["bktime"]);
                Lrstime.Add(rs["rstime"]);
            }
            rs.Dispose();
        }
        catch { }
        finally 
        {
            Cmd.Dispose();
            one.Close();
        }
    }


    //还原数据库后导入原来的数据备份的列表
    public void Excudb()
    {
        SqlConnection one = conn.sqlconn();
        SqlCommand Cmd = new SqlCommand();
        string sql;
        try
        {
            one.Open();
            int i;
            Cmd.CommandText = "delete from db_backup";
            Cmd.Connection = one;
            Cmd.ExecuteNonQuery();
            for (i = 0; i < Lbkname.Count; i++)
            {
                sql = "insert into db_backup(id,bkname,bkpath,bktime,rstime) values(" + Convert.ToInt16(Lbkid[i])+ ",'" + Lbkname[i].ToString() + "','" + Lbkpath[i].ToString() + "','" + Lbktime[i].ToString() + "','" + Lrstime[i].ToString() + "')";
                Cmd.Connection = one;
                Cmd.CommandText = sql;
                //System.Web.HttpContext.Current.Response.Write(i + sql + "<br>");
                int t=Cmd.ExecuteNonQuery();
                //System.Web.HttpContext.Current.Response.Write("影响行数:"+t+"<br>");
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            Lbkid.Clear();
            Lbkname.Clear();
            Lbkpath.Clear();
            Lbktime.Clear();
            Lrstime.Clear();
            Cmd.Dispose();
            one.Close();
        }
    }

}

