using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Collections;

namespace DataManager
{
    /// <summary>
    /// DbOper類,主要應用SQLDMO實現對Microsoft SQL Server數據庫的備份和恢復
    /// </summary>
    public partial class DbOper
    {
        /// <summary>
        /// DbOper類的構造函數
        /// </summary>
        ProgressBar PBar = null;
        //private DbOper()
        //{

        //}
        /// <summary>
        /// 數據庫備份
        /// </summary>
        public void DbBackup(string uid, string pwd, string DataServer, string Dataname, string DataBak, ProgressBar pgbMain)
        {
            PBar = pgbMain;
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(DataServer, uid, pwd);
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = Dataname;
                oBackup.Files = DataBak;
                oBackup.BackupSetName = Dataname;
                oBackup.BackupSetDescription = "DateBase Buckup";
                oBackup.Initialize = true;
                SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
                oBackup.PercentComplete += pceh;
                oBackup.SQLBackup(oSQLServer);
            }
            catch
            {
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }
        /// <summary>
        /// 數據庫恢復
        /// </summary>
        public void DbRestore(string uid, string pwd, string DataServer, string Dataname, string DataBak, ProgressBar pgbMain)
        {
            PBar = pgbMain;
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(DataServer, uid, pwd);
                SQLDMO.QueryResults qr = oSQLServer.EnumProcesses(-1);
                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 (strDBName.ToUpper() == Dataname.ToUpper())
                        oSQLServer.KillProcess(lPID);
                }
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = Dataname;
                oRestore.Files = DataBak;
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
                oRestore.PercentComplete += pceh;
                oRestore.SQLRestore(oSQLServer);
            }
            catch (Exception err)
            {
                throw (new Exception("Restore DataBase Faile,Please close relating the BaseBase's Program！" + err.Message));
                ;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }
        private void Step(string message, int percent)
        {
            PBar.Value = percent;
        }
        public static void CombServer(ComboBox cboServers)
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();

            for (int i = 0; i < sqlServers.Count; i++)
            {
                object srv = sqlServers.Item(i + 1);
                if (srv != null)
                {
                    cboServers.Items.Add(srv);
                }
            }
            if (cboServers.Items.Count > 0)
                cboServers.SelectedIndex = 0;
            else
                cboServers.Text = "<No available SQL Servers>";
        }
        public static void CombDb(string txtUser, string txtPassword, ComboBox cboDatabase, ComboBox cboServers)
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
            srv.Connect(cboServers.SelectedItem.ToString(), txtUser, txtPassword);
            foreach (SQLDMO.Database db in srv.Databases)
            {
                if (db.Name != null)
                    cboDatabase.Items.Add(db.Name);
            }

        }
        public static void ListBoxTable(string txtUser, string txtPassword, ListBox lstObjects, ComboBox cboServers, ComboBox cboDatabase)
        {
            SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
            srv.Connect(cboServers.SelectedItem.ToString(), txtUser, txtPassword);
            for (int i = 0; i < srv.Databases.Count; i++)
            {
                if (srv.Databases.Item(i + 1, "dbo").Name == cboDatabase.SelectedItem.ToString())
                {
                    SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo");
                    lstObjects.Items.Clear();
                    for (int j = 0; j < db.StoredProcedures.Count; j++)
                    {
                        lstObjects.Items.Add(db.StoredProcedures.Item(j + 1, "dbo").Name);
                    }
                    break;
                }
            }
        }
        public ArrayList GetServerList()
        {
            ArrayList alServers = new ArrayList();
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            try
            {
                SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();
                for (int i = 1; i <= serverList.Count; i++)
                {
                    alServers.Add(serverList.Item(i));
                }
            }
            catch (Exception e)
            {
                throw (new Exception("Fech Database Server List Error：" + e.Message));
            }
            finally
            {
                sqlApp.Quit();
            }
            return alServers;
        }
        public ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
        {
            string ServerName = strServerName;
            string UserName = strUserName;
            string Password = strPwd;

            ArrayList alDbs = new ArrayList();
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                foreach (SQLDMO.Database db in svr.Databases)
                {
                    if (db.Name != null)
                        alDbs.Add(db.Name);
                }
            }
            catch (Exception e)
            {
                throw (new Exception("Connect DataBase Error!：" + e.Message));
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
            return alDbs;
        }
        public bool BackUPDB(string strDbName, string strFileName, ProgressBar pgbMain)
        {
            ////string ServerName = strServerName;
            ////string UserName = strUserName;
            ////string Password = strPwd;
            //ProgressBar PBar = pgbMain;
            //SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            //try
            //{
            //    svr.Connect(ServerName, UserName, Password);
            //    SQLDMO.Backup bak = new SQLDMO.BackupClass();
            //    bak.Action = 0;
            //    bak.Initialize = true;
            //    SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
            //    bak.PercentComplete += pceh;

            //    bak.Files = strFileName;
            //    bak.Database = strDbName;
            //    bak.SQLBackup(svr);
            return true;
            //}
            //catch (Exception err)
            //{
            //    throw (new Exception("備份數據庫失敗" + err.Message));
            //}
            //finally
            //{
            //    svr.DisConnect();
            //}
        }
    }
}