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