using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using System.Text.RegularExpressions;

namespace AnywhereTS.DBSupport
{
    public interface IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> : IDisposable
        where DBParameter : DbParameter, new()
        where DBCommand : DbCommand, new()
        where DBConnection : DbConnection, new()
        where DBDataAdapter : DbDataAdapter, new()
    {
        bool ConnectionIsOpen { get; }
        void CreateConnection(out Exception ErrorInfo);
        void OpenConnection(out Exception ErrorInfo);
        void CloseConnection(out Exception ErrorInfo);

        DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
        void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
        List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);

        DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
        public bool RunScript(string strFile, MsSqlConnector con, out Exception ErrorInfo);
    }
    public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
        IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
        where DBParameter : DbParameter, new()
        where DBCommand : DbCommand, new()
        where DBConnection : DbConnection, new()
        where DBDataAdapter : DbDataAdapter, new()
    {

        public DBConnector(string DBServerAddress, string DBServerInstance, string DBDatabase)
        {
            this.DBServerAddress = DBServerAddress;
            this.DBServerInstance = DBServerInstance;
            this.DBDatabase = DBDatabase;
        }

        protected DBConnection connection;
        protected string DBServerAddress = "";
        protected string DBServerInstance = "";
        protected string DBDatabase = "";
        private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
        #region IDBConnector members
        public DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                return sqlComm;
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            } 
        }
        private bool _ConnectionIsOpen;
        public virtual bool ConnectionIsOpen { get { return _ConnectionIsOpen; } protected set { _ConnectionIsOpen = value; } }
        public virtual void CreateConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                string connetionString = null;
                connetionString = string.Format(@"Data Source={0}\{1};Initial Catalog={2};Integrated Security=SSPI", DBServerAddress, DBServerInstance, DBDatabase);
                connection = new DBConnection();
                connection.ConnectionString = connetionString;
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            } 
        }
        public virtual void OpenConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                //this.CloseConnection(out ErrorInfo);
                connection.Open();
                this.ConnectionIsOpen = true;
            }
            //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo; 
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            } 
        }
        public virtual void CloseConnection(out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                if (this.ConnectionIsOpen)
                    connection.Close();
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
                }
                ErrorInfo = ex; throw ErrorInfo;
            } 
        }

        public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute query.", new Exception("A connection to the database has not, yet, been established.")); }
            try
            {
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                DbDataReader r = sqlComm.ExecuteReader();
                return r;
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            } 
        }
        public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            if (!this.ConnectionIsOpen) { ErrorInfo = new Exception("Cannot execute non-query.", new Exception("A connection to the database has not, yet, been established.")); }
            try
            {
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                sqlComm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }    
        }
        public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                List<string> ColumnNames = new List<string>();
                DBDataAdapter da = new DBDataAdapter();
                command = this.SafeSqlLiteral(command);
                DBCommand sqlComm = new DBCommand();
                sqlComm.CommandText = command;
                sqlComm.Connection = connection;
                foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
                da.SelectCommand = sqlComm;
                DataTable dt = new DataTable();
                da.Fill(dt);
                DataRow row = dt.Rows[0];
                for (int ordinal = 0; ordinal < dt.Columns.Count; ordinal++)
                {
                    string value = row[ordinal].ToString();
                    string column_name = dt.Columns[ordinal].ColumnName;
                    ColumnNames.Add(column_name);
                }
                return ColumnNames;
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
                }
                ErrorInfo = ex; throw ErrorInfo;
            }            
        }
        public string[] ParseScriptToCommands(string strScript)
        {
            string[] commands;
            commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
            return commands;
        }
        public virtual bool RunScript(string strFile, out Exception ErrorInfo)
        {
            ErrorInfo = null;
            try
            {
                string[] strCommands;
                strCommands = ParseScriptToCommands(strFile);
                if (this.ConnectionIsOpen)
                {
                    foreach (string strCmd in strCommands)
                    {
                        if (strCmd.Length > 0)
                        {
                            // Substitute database directory with the decided one.
                            DBCommand command = this.CreateCommandInstance(strCmd, new List<DBParameter>(), out ErrorInfo);
                            command.ExecuteNonQuery();
                        }
                    }
                    return true;
                }
                else
                {
                    Logging.ATSAdminLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
                    return false;
                }
            }
            catch (SqlException ex)
            {
                using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
                }
                ErrorInfo = ex;
            }
            catch (Exception ex)
            {
                using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
                {
                    Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
                }
                ErrorInfo = ex;
            }
            return false;
        }
        #endregion

        #region IDisposable Members
        public virtual void Dispose()
        {
            try
            {
                Exception ErrorInfo;
                CloseConnection(out ErrorInfo);
            }
            catch
            {
            }
        }
        #endregion
    }
}