public void ConfigTerminal(Replica replica, Table table, List<Insert> valuesToInsert)
 {
     SqlDatabaseDA sqlDatabaseAccess = new SqlDatabaseDA(replica.StrTerminalUser, replica.StrTerminalPassword, replica.StrTerminalIPAddress, replica.IntTerminalPort.ToString(), replica.StrTerminalDatabase);
     sqlDatabaseAccess.CreateReplicaLogs();
     sqlDatabaseAccess.createTable(table);
     sqlDatabaseAccess.ExecuteMultipleInsert(valuesToInsert);
     sqlDatabaseAccess.CreateTrigger(table);
 }
 public List<Insert> GetConfigValues(Replica replica, Table table)
 {
     SqlDatabaseDA sqlDatabaseAccess = new SqlDatabaseDA(replica.StrSourceUser, replica.StrSourcePassword, replica.StrSourceIPAddress, replica.IntSourcePort.ToString(), replica.StrSourceDatabase);
     return sqlDatabaseAccess.GetCurrentRows(table);
 }
 /// <summary>
 /// Crea un trigger de borrado para una tabla insertada como parametro
 /// que llene la tabla log
 /// </summary>
 /// <param name="pTabla"></param>
 /// <returns></returns>
 /// 
 public string CreateDeleteTriggerSQL(Table oTable)
 {
     return this.CreateTrigger(oTable,"DELETE");
 }
 /// <summary>
 /// Crea un trigger de actualizacion para una tabla insertada como parametro
 /// que llene la tabla log
 /// </summary>
 /// <param name="pTabla"></param>
 /// <returns></returns>
 public string CreateInsertTriggerSQL(Table oTable)
 {
     return this.CreateTrigger(oTable,"INSERT");
 }
        public List<Insert> GetCurrentRows(Table table)
        {
            List<Insert> result = new List<Insert>();
            Insert oInsert = new Insert();
            string strListColumns = string.Join(",", table.ListColumns);

            string strQuery = "SELECT " + strListColumns + " FROM " + table.StrName;

            MySqlDataReader dtrResult = null;
            DataTable dtResult = new DataTable();

            try
            {

                this.OpenConnection();
                MySqlCommand cmdComando = new MySqlCommand(strQuery, this._oConnection);

                dtrResult = cmdComando.ExecuteReader();

                //Load the Results on the DataTable
                dtResult.Load(dtrResult);

                foreach (DataRow dtrFila in dtResult.Rows)
                {
                    oInsert = new Insert();

                    foreach (Column column in table.ListColumns)
                    {
                        oInsert.strTableName = table.StrName;
                        oInsert.listColumnNames.Add(column.StrName);
                        oInsert.listValues.Add(dtrFila[column.StrName].ToString().Trim());
                    }
                    //nchar is set but default on all engines is varchar
                    result.Add(oInsert);

                }

            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, strQuery);
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pNombreTabla"></param>
        /// <returns></returns>
        public Table getTableStructure(string strNombreBase, string pNombreTabla)
        {
            Table oTable = new Table();
            oTable.StrName = pNombreTabla;
            string querry = "use " + strNombreBase + "; describe " + pNombreTabla;

            try
            {

                this.OpenConnection();
                MySqlCommand cmdComando = new MySqlCommand(querry, this._oConnection);
                MySqlDataReader lector = cmdComando.ExecuteReader();

                while (lector.Read())
                {
                    Column oColumn = new Column();
                    oColumn.StrName = lector.GetString(0);
                    oColumn.StrType = lector.GetString(1);

                    //nchar is set but default on all engines is varchar
            //                    if (oColumn.StrType.Equals("nchar") || oColumn.StrType.Equals("varchar"))
            //                    {
            //                        oColumn.StrType = "varchar(" + dtrFila["PRECISION"].ToString() + ")";
            //                    }

                    if (oColumn.StrType.Contains("int"))
                    {
                        oColumn.StrType = "int";
                    }

                    if (oColumn.StrType.Contains("float"))
                    {
                        oColumn.StrType = "float";
                    }

                    if (oColumn.StrType.Contains("smallint"))
                    {
                        oColumn.StrType = "smallint";
                    }

                    if (!lector.GetString(2).Equals("NO"))
                    {
                        oColumn.BoolNull = true;
                    }
                    oTable.ListColumns.Add(oColumn);
                }
            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, querry);
            }
            finally
            {
                this.CloseConnection();
            }
            return oTable;
        }
 /// <summary>
 /// Crea un trigger de actualizacion para una tabla insertada como parametro
 /// que llene la tabla log
 /// </summary>
 /// <param name="pTabla"></param>
 /// <returns></returns>
 public string CreateUpdateTriggerSQL(Table oTable)
 {
     return this.CreateTrigger(oTable,"UPDATE");
 }
        /// <summary>
        /// This method will retrieve all the Tables for and specific DB on MySQL, this cannot be a SP because is on client side
        /// </summary>
        /// <param name="Database"></param>
        /// <returns></returns>
        public List<Table> GetAllTables(string Database)
        {
            List<Table> listResult = new List<Table>();
            Table oTable = new Table();

            string strQuery = string.Empty;
            MySqlDataReader dtrResult = null;
            DataTable dtResult = new DataTable();

            try
            {
                this.OpenConnection();

                strQuery = "show tables FROM "+ Database;
                MySqlCommand cmdComando = new MySqlCommand(strQuery, this._oConnection);
                //cmdComando.CommandType = CommandType.StoredProcedure;

                //cmdComando.Parameters.Add("@intResult", SqlDbType.Int).Direction = ParameterDirection.Output;

                dtrResult = cmdComando.ExecuteReader();

                //Load the Results on the DataTable
                dtResult.Load(dtrResult);

                foreach (DataRow dtrFila in dtResult.Rows)
                {

                   oTable = new Table();
                   oTable.StrName = dtrFila["Tables_in_"+Database].ToString();
                   listResult.Add(oTable);
                }
            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, strQuery);
            }
            finally
            {
                this.CloseConnection();
            }
            return listResult;
        }
 /// <summary>
 /// General Trigger Creator
 /// </summary>
 /// <param name="oTable"></param>
 /// <param name="triggerEvent"></param>
 /// <returns></returns>
 public string CreateTrigger(Table oTable, string triggerEvent)
 {
     string querry = "CREATE TRIGGER " + oTable.StrName + "_" + triggerEvent + " AFTER " + triggerEvent + " ON " + oTable.StrName + " FOR EACH ROW BEGIN ";
     querry += "DECLARE original_query VARCHAR(1024); ";
     //querry += "IF (ENABLE_TRIGGER_" + oTable.StrName + " = 1) THEN ";
     querry += "SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); ";
     querry += "INSERT INTO replicalog values (null, '" + oTable.StrName + "', NOW(), " + "original_query" + ",0); ";
     //querry += "END IF; ";
     querry += "END";
     return querry;
 }
        /// <summary>
        /// Method to create Trigger
        /// </summary>
        /// <param name="insert"></param>
        /// <returns></returns>
        public Boolean CreateTriggerUpdate(Table oTable)
        {
            Boolean result = false;
            string strQuery = this.CreateUpdateTriggerSQL(oTable);

            MySqlDataReader dtrResult = null;
            DataTable dtResult = new DataTable();

            try
            {

                this.OpenConnection();
                MySqlCommand cmdComando = new MySqlCommand(strQuery, this._oConnection);

                //cmdComando.Parameters.AddWithValue("@ENABLE_TRIGGER_" + oTable.StrName, "@ENABLE_TRIGGER_" + oTable.StrName);

                cmdComando.ExecuteNonQuery();
                cmdComando.ExecuteNonQuery();
                result = true;
                //Load the Results on the DataTable
            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, strQuery);
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        public Boolean createTable(Table table)
        {
            Boolean resultado = false;
            string strQuery = table.ToString();

            DataTable dtResult = new DataTable();

            try
            {

                this.OpenConnection();
                MySqlCommand cmdComando = new MySqlCommand(strQuery, this._oConnection);

                cmdComando.ExecuteNonQuery();
                resultado = true;
                //Load the Results on the DataTable
            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, strQuery);
            }
            finally
            {
                this.CloseConnection();
            }
            return resultado;
        }
        /// <summary>
        /// Crea una tabla a la base de datos.
        /// </summary>
        /// <param name="pNombreTabla"></param>
        /// <param name="pColumnas"></param>
        /// <param name="pTipo"></param>
        /// <returns></returns>
        //public string crearTabla(string pNombre, List<string> pColumnas, List<string> pTipo)
        public string createSQLTable(Table oTable)
        {
            string strQuerry = "create table " + oTable.StrName + " (";

            int counterColumns = oTable.ListColumns.Count();

            foreach (Column Ocolumn in oTable.ListColumns)
            {
                strQuerry += Ocolumn.StrName + " " + Ocolumn.StrType;

                if (Ocolumn.BoolNull)
                {
                    strQuerry += " NOT NULL ";
                }

                //Delimiter , for more than 1 colum
                counterColumns--;
                if (counterColumns > 0)
                {
                    strQuerry += ", ";
                }

            }

            //strQuerry += "primary key(" + pColumnas[0] + ")
            strQuerry += ");";
            return strQuerry;
        }
        public Table getTableStructure(string strNombreBase, string pNombreTabla)
        {
            Table oTable = new Table();
            oTable.StrName = pNombreTabla;
            string strQuery = "sp_columns";

            SqlDataReader dtrResult = null;
            DataTable dtResult = new DataTable();

            try
            {

                this.OpenConnection();
                SqlCommand cmdComando = new SqlCommand(strQuery, this._oConnection);
                cmdComando.CommandType = CommandType.StoredProcedure;
                cmdComando.Parameters.AddWithValue("@table_name", pNombreTabla);
                dtrResult = cmdComando.ExecuteReader();

                //Load the Results on the DataTable
                dtResult.Load(dtrResult);

                foreach (DataRow dtrFila in dtResult.Rows)
                {
                    Column oColumn = new Column();
                    oColumn.StrName = dtrFila["COLUMN_NAME"].ToString();
                    oColumn.StrType = dtrFila["TYPE_NAME"].ToString();

                    //nchar is set but default on all engines is varchar
                    if (oColumn.StrType.Equals("nchar") || oColumn.StrType.Equals("varchar") || oColumn.StrType.Equals("char"))
                    {
                        oColumn.StrType = "varchar("+dtrFila["PRECISION"].ToString()+")";
                    }

                    if (oColumn.StrType.Contains("int"))
                    {
                        oColumn.StrType = "int";
                    }

                    if (oColumn.StrType.Contains("float"))
                    {
                        oColumn.StrType = "float";
                    }

                    if (oColumn.StrType.Contains("smallint"))
                    {
                        oColumn.StrType = "smallint";
                    }

                    //Identity is hidden on column
                    if (oColumn.StrType.Contains("identity")){
                        oColumn.StrType = oColumn.StrType.Remove(oColumn.StrType.Length - 8);
                    }
                    if (dtrFila["IS_NULLABLE"].ToString().Equals("NO"))
                    {
                        oColumn.BoolNull = true;
                    }
                    oTable.ListColumns.Add(oColumn);

                }

            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, strQuery);
            }
            finally
            {
                this.CloseConnection();
            }
            return oTable;
        }
        public Boolean CreateTrigger(Table table)
        {
            bool result = false;
            string strQuery = string.Empty;

            try
            {

                strQuery = " CREATE TRIGGER " + table.StrName + "_Trigger " +
                                  " ON " + table.StrName +
                                  " AFTER INSERT, UPDATE, DELETE " +
                                  " AS " +
                                  " BEGIN " +
                                  " " +
                                  "" +
                                  "" +
                                  "     DECLARE @LASTCOMMAND  NVARCHAR(MAX);" +
                                  "     DECLARE @SQLBuffer NVARCHAR(4000);" +
                                  "" +
                                  "     DECLARE @ReplicaLog TABLE (" +
                                  "             EventType NVARCHAR(30)," +
                                  "             Parameters INT," +
                                  "             EventInfo NVARCHAR(4000)" +
                                  "     )" +
                                  "     INSERT @ReplicaLog" +
                                  "     EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'" +
                                  "" +
                                  "     SELECT @LASTCOMMAND = EventInfo " +
                                  "     FROM @ReplicaLog" +
                                  "" +
                                  "" +
                                  "     INSERT INTO ReplicaLog " +
                                  "     ( " +
                                  "         ReplicaTable " +
                                  "         ,ReplicaDatetime " +
                                  "         ,ReplicaTransaction " +
                                  "         ,IsSynchronized " +
                                  "     ) " +
                                  "     VALUES " +
                                  "     ( " +
                                  "         '" + table.StrName + "'" +
                                  "         ,GETDATE() " +
                                  "         ,@LASTCOMMAND" +
                                  "         ,0 " +
                                  "     ) " +
                                  " " +
                                  " END";

                this.OpenConnection();
                SqlCommand cmdComando = new SqlCommand(strQuery, this._oConnection);

                cmdComando.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                this._oLogErrors.GuardarLog(IConstantes.TIPOCAPA.ACCESODATOS, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message, strQuery);
                result = false;
            }
            finally
            {
                this.CloseConnection();
            }

            return result;
        }
        /// <summary>
        /// This will configure the Initial Replica requirements on the client
        /// </summary>
        /// <param name="replica"></param>
        public void InitialReplicaClientConfig(Replica replica)
        {
            //Source Config
            Table table = new Table();
            SqlDatabaseBL sqlDatabaseBL = new SqlDatabaseBL();
            MysqlDatabaseBL mysqlDatabaseBL = new MysqlDatabaseBL();
            List<Insert> valuesToInsert = new List<Insert>();
            if (replica.StrSourceEngine.Contains("SQL Server"))
            {

                table = sqlDatabaseBL.ConfigSource(replica);
                valuesToInsert = sqlDatabaseBL.GetConfigValues(replica, table);
            }
            if (replica.StrSourceEngine.Contains("MySQL"))
            {
                table = mysqlDatabaseBL.ConfigSource(replica);
                valuesToInsert = mysqlDatabaseBL.GetConfigValues(replica, table);

            }
            //Terminal Config
            if (replica.StrTerminalEngine.Contains("SQL Server"))
            {
                sqlDatabaseBL.ConfigTerminal(replica, table, valuesToInsert);

            }
            if (replica.StrTerminalEngine.Contains("MySQL"))
            {
                mysqlDatabaseBL.ConfigTerminal(replica, table, valuesToInsert);
            }
        }