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); } }