protected void PerformStoredProcedureValidation <T>(TransactionTypes transactionType, QueryOptions queryOptions) where T : Cope <T>, IManageable, new()
{
TransactionTypes singleTransactionType;
switch (transactionType)
{
case TransactionTypes.InsertMassive:
singleTransactionType = TransactionTypes.Insert;
break;
case TransactionTypes.UpdateMassive:
singleTransactionType = TransactionTypes.Update;
break;
case TransactionTypes.DeleteMassive:
singleTransactionType = TransactionTypes.Delete;
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
string schema = Manager.ConnectionType == ConnectionTypes.MSSQL ? Cope <T> .ModelComposition.Schema : ConsolidationTools.GetInitialCatalog(queryOptions.ConnectionToUse, true);
if (!DoStoredProcedureExist(ConsolidationTools.GetInitialCatalog(queryOptions.ConnectionToUse), schema, $"{Manager.StoredProcedurePrefix}massive_operation", queryOptions.ConnectionToUse))
{
ExecuteScalar(GetTransactionTextForProcedure <T>(transactionType, false), queryOptions.ConnectionToUse, false);
}
if (!DoStoredProcedureExist(ConsolidationTools.GetInitialCatalog(queryOptions.ConnectionToUse), schema, $"{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{GetFriendlyTransactionSuffix(singleTransactionType)}", queryOptions.ConnectionToUse))
{
ExecuteScalar(GetTransactionTextForProcedure <T>(singleTransactionType, false), queryOptions.ConnectionToUse, false);
}
}
internal Guid GetTransactionTypeIdByEnum(TransactionTypes transactionType)
{
if (_transactionTypes == null)
{
_transactionTypes = ExecuteCommand(new GetAllTransactionTypes());
}
return(_transactionTypes.Single(t => t.Name == transactionType.ToString()).Id);
}
private Result <T> ExecuteMassiveOperation <T>(IEnumerable <T> list, QueryOptions queryOptions, TransactionTypes transactionType) where T : Cope <T>, IManageable, new()
{
bool throwIfError = false;
Start:
using (MySqlConnection connection = Connection.OpenMySqlConnection(queryOptions.ConnectionToUse))
{
if (connection.State != ConnectionState.Open)
{
throw new BadConnectionStateException();
}
_command = connection.CreateCommand();
_command.CommandType = CommandType.StoredProcedure;
_command.CommandText = $"`{Manager.StoredProcedurePrefix}massive_operation`";
try
{
switch (transactionType)
{
case TransactionTypes.InsertMassive:
SetMassiveOperationParameters(list, transactionType, queryOptions);
_command.ExecuteNonQuery();
break;
case TransactionTypes.UpdateMassive:
SetMassiveOperationParameters(list, transactionType, queryOptions);
_command.ExecuteNonQuery();
break;
case TransactionTypes.DeleteMassive:
SetMassiveOperationParameters(list, transactionType, queryOptions);
_command.ExecuteNonQuery();
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
}
catch (MySqlException mySqlException) when(mySqlException.Number == ERR_STORED_PROCEDURE_NOT_FOUND)
{
if (Manager.AutoCreateStoredProcedures && !throwIfError)
{
Logger.Warn(string.Format("Stored Procedure for {0} not found. Creating...", transactionType.ToString()));
PerformStoredProcedureValidation <T>(transactionType, queryOptions);
throwIfError = true;
goto Start;
}
Logger.Error(mySqlException);
throw;
}
}
return(new Result <T>(new Dictionary <dynamic, T>(), false, true));
}
public void UnPostAllLedgers(TransactionTypes trType)
{
Console.WriteLine("> Un Post" + trType.ToString());
string sqlCommand = "DELETE FROM [dbo].[ERP_Ledgers] WHERE TransactionType = {0} ";
sqlCommand = string.Format(sqlCommand, (int)trType);
erpNodeDBContext.Database.ExecuteSqlRaw(sqlCommand);
sqlCommand = "DELETE FROM [dbo].[ERP_Ledger_Transactions] WHERE TransactionType = {0}";
sqlCommand = string.Format(sqlCommand, (int)trType);
erpNodeDBContext.Database.ExecuteSqlRaw(sqlCommand);
erpNodeDBContext.SaveChanges();
}
protected string GetFriendlyTransactionSuffix(TransactionTypes transactionType)
{
switch (transactionType)
{
case TransactionTypes.Delete:
return(Manager.DeleteSuffix);
case TransactionTypes.Insert:
return(Manager.InsertSuffix);
case TransactionTypes.Update:
return(Manager.UpdateSuffix);
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
}
public Result <T> Evaluate <T>(object obj, Expression <Func <T, bool> > expression, TransactionTypes transactionType, ref DataCache <T> dataCache, QueryOptions queryOptions) where T : Cope <T>, IManageable, new()
{
Result <T> resultado = null;
bool hasCache = dataCache.Cache == null ? false : true;
switch (transactionType)
{
case TransactionTypes.Select:
EvaluateSelectQuery(expression, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.SelectAll:
EvaluateSelectAll((T)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.Delete:
EvaluateDelete((T)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.DeleteMassive:
EvaluateDeleteMassive((IEnumerable <T>)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.Insert:
EvaluateInsert((T)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.InsertMassive:
EvaluateInsertMassive((IEnumerable <T>)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.Update:
EvaluateUpdate((T)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
case TransactionTypes.UpdateMassive:
EvaluateUpdateMassive((IEnumerable <T>)obj, out resultado, hasCache, ref dataCache, queryOptions);
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
return(resultado);
}
protected Log NewLog(string TableName, TransactionTypes transactionType)
{
dynamic identityId = 0;
if (Manager.Identity != null)
{
identityId = Manager.Identity.Configuration.PrimaryKeyProperty.GetValue(Manager.Identity);
}
Log newLog = new Log
{
Id = Guid.NewGuid(),
IdentityId = identityId,
Transaccion = transactionType.ToString(),
TablaAfectada = TableName,
Parametros = GetStringParameters()
};
Logger.Info(string.Format("Created new log object for affected table {0}, transaction used {1}, with the following parameters: {2}", Cope <Log> .ModelComposition.TableName, newLog.Transaccion, newLog.Parametros));
return(newLog);
}
private Result <T> ExecuteProcedure <T>(T obj, QueryOptions queryOptions, TransactionTypes transactionType) where T : Cope <T>, IManageable, new()
{
Result <T> result = new Result <T>(new Dictionary <dynamic, T>(), false, true);
using (SqlConnection connection = Connection.OpenMsSqlConnection(queryOptions.ConnectionToUse))
{
if (connection.State != ConnectionState.Open)
{
throw new BadConnectionStateException();
}
_command = connection.CreateCommand();
_command.CommandType = CommandType.StoredProcedure;
_command.CommandText = string.Format("{0}.{1}{2}{3}", Cope <T> .ModelComposition.Schema, Manager.StoredProcedurePrefix, Cope <T> .ModelComposition.TableName, GetFriendlyTransactionSuffix(transactionType));
switch (transactionType)
{
case TransactionTypes.Delete:
SetParameters(obj, transactionType, true, queryOptions);
_command.ExecuteNonQuery();
break;
case TransactionTypes.Insert:
SetParameters(obj, transactionType, false, queryOptions);
_command.ExecuteNonQuery();
break;
case TransactionTypes.Update:
SetParameters(obj, transactionType, true, queryOptions);
_command.ExecuteNonQuery();
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
}
return(result);
}
internal static MassiveOperationParameter GenerateCompatibleMassiveOperationXML <T>(IEnumerable <T> list, TransactionTypes transactionType) where T : Cope <T>, IManageable, new()
{
MassiveOperationParameter massiveOperationParameter = new MassiveOperationParameter();
StringBuilder builder = new StringBuilder();
builder.Append("<columns>\n");
if (transactionType == TransactionTypes.DeleteMassive)
{
builder.Append(" <column>\n");
builder.Append($" <name>{Cope<T>.ModelComposition.PrimaryKeyProperty.Name}</name>\n");
builder.Append(" </column>\n");
}
else
{
foreach (KeyValuePair <string, PropertyInfo> property in Cope <T> .ModelComposition.FilteredProperties)
{
builder.Append(" <column>\n");
builder.Append($" <name>{property.Value.Name}</name>\n");
builder.Append(" </column>\n");
}
}
builder.Append("</columns>");
massiveOperationParameter.XmlNames = builder.ToString();
builder.Clear();
builder.Append("<objects>\n");
if (transactionType == TransactionTypes.DeleteMassive)
{
foreach (T obj in list)
{
builder.Append(" <object>\n");
// Si es Numero o Boolean no agrega comillas sencillas, de lo contrario se las pone.
if (long.TryParse(Cope <T> .ModelComposition.PrimaryKeyProperty.GetValue(obj).ToString(), out long n) || Cope <T> .ModelComposition.PrimaryKeyProperty.GetValue(obj) is bool)
{
builder.Append($" <{Cope<T>.ModelComposition.PrimaryKeyProperty.Name}>{Cope<T>.ModelComposition.PrimaryKeyProperty.GetValue(obj)}</{Cope<T>.ModelComposition.PrimaryKeyProperty.Name}>\n");
}
else
{
builder.Append($" <{Cope<T>.ModelComposition.PrimaryKeyProperty.Name}>'{Cope<T>.ModelComposition.PrimaryKeyProperty.GetValue(obj)}'</{Cope<T>.ModelComposition.PrimaryKeyProperty.Name}>\n");
}
builder.Append(" </object>\n");
}
}
else
{
foreach (T obj in list)
{
builder.Append(" <object>\n");
foreach (KeyValuePair <string, PropertyInfo> property in Cope <T> .ModelComposition.FilteredProperties)
{
if (property.Value.GetValue(obj) == null)
{
builder.Append($" <{property.Value.Name}>{DBNull.Value}</{property.Value.Name}>\n");
}
else
{
// Si es Numero o Boolean no agrega comillas sencillas, de lo contrario se las pone.
if (long.TryParse(property.Value.GetValue(obj).ToString(), out long n) || property.Value.GetValue(obj) is bool)
{
builder.Append($" <{property.Value.Name}>{property.Value.GetValue(obj)}</{property.Value.Name}>\n");
}
else
{
if (property.Value.GetValue(obj) is DateTime)
{
builder.Append($" <{property.Value.Name}>'{((DateTime)property.Value.GetValue(obj)).ToString("yyyy-MM-dd H:mm:ss")}'</{property.Value.Name}>\n");
}
else
{
builder.Append($" <{property.Value.Name}>'{property.Value.GetValue(obj)}'</{property.Value.Name}>\n");
}
}
}
}
builder.Append(" </object>\n");
}
}
builder.Append("</objects>");
massiveOperationParameter.XmlValues = builder.ToString();
switch (transactionType)
{
case TransactionTypes.InsertMassive:
massiveOperationParameter.ProcedureName = Manager.ConnectionType == ConnectionTypes.MySQL ? $"`{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{Manager.InsertSuffix}`" : $"[{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{Manager.InsertSuffix}]";
break;
case TransactionTypes.UpdateMassive:
massiveOperationParameter.ProcedureName = Manager.ConnectionType == ConnectionTypes.MySQL ? $"`{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{Manager.UpdateSuffix}`" : $"[{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{Manager.UpdateSuffix}]";
break;
case TransactionTypes.DeleteMassive:
massiveOperationParameter.ProcedureName = Manager.ConnectionType == ConnectionTypes.MySQL ? $"`{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{Manager.DeleteSuffix}`" : $"[{Manager.StoredProcedurePrefix}{Cope<T>.ModelComposition.TableName}{Manager.DeleteSuffix}]";
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con la funcion {nameof(GenerateCompatibleMassiveOperationXML)}.");
}
return(massiveOperationParameter);
}
public Result <T> ExecuteProcedure <T>(QueryOptions queryOptions, TransactionTypes transactionType, bool logTransaction, object obj, Expression <Func <T, bool> > expression) where T : Cope <T>, IManageable, new()
{
Result <T> result = null;
bool throwIfError = false;
Start:
try
{
Logger.Info(string.Format("Starting {0} execution for object {1} using connection {2}", transactionType.ToString(), typeof(T), queryOptions));
if (Manager.ConstantTableConsolidation)
{
if (!typeof(T).Equals(typeof(Log)))
{
PerformFullTableCheck(new T(), queryOptions.ConnectionToUse);
}
}
switch (transactionType)
{
case TransactionTypes.Select:
result = ExecuteSelect(expression, queryOptions, transactionType);
break;
case TransactionTypes.SelectAll:
result = ExecuteSelectAll((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.Delete:
result = ExecuteProcedure((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.DeleteMassive:
result = ExecuteMassiveOperation((IEnumerable <T>)obj, queryOptions, transactionType);
break;
case TransactionTypes.Insert:
result = ExecuteProcedure((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.InsertMassive:
result = ExecuteMassiveOperation((IEnumerable <T>)obj, queryOptions, transactionType);
break;
case TransactionTypes.Update:
result = ExecuteProcedure((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.UpdateMassive:
result = ExecuteMassiveOperation((IEnumerable <T>)obj, queryOptions, transactionType);
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
Logger.Info(string.Format("Execution {0} for object {1} using connection {2} has finished successfully.", transactionType.ToString(), typeof(T), queryOptions));
}
catch (MySqlException mySqlException) when(mySqlException.Number == ERR_STORED_PROCEDURE_NOT_FOUND)
{
if (Manager.AutoCreateStoredProcedures && !throwIfError)
{
Logger.Warn(string.Format("Stored Procedure for {0} not found. Creating...", transactionType.ToString()));
ExecuteScalar(GetTransactionTextForProcedure <T>(transactionType, false), queryOptions.ConnectionToUse, false);
throwIfError = true;
goto Start;
}
Logger.Error(mySqlException);
throw;
}
catch (MySqlException mySqlException) when(mySqlException.Number == ERR_TABLE_NOT_FOUND)
{
if (Manager.AutoCreateTables && !throwIfError)
{
Logger.Warn(string.Format("Table {0} not found. Creating...", Cope <T> .ModelComposition.TableName));
PerformFullTableCheck(new T(), queryOptions.ConnectionToUse);
throwIfError = true;
goto Start;
}
Logger.Error(mySqlException);
throw;
}
catch (MySqlException mySqlException) when(mySqlException.Number == ERR_INCORRECT_NUMBER_OF_ARGUMENTS || mySqlException.Number == ERR_UNKOWN_COLUMN || mySqlException.Number == ERR_UNKOWN_COLUMN || mySqlException.Number == ERR_NO_DEFAULT_VALUE_IN_FIELD)
{
if (Manager.AutoAlterTables && !throwIfError)
{
PerformFullTableCheck(new T(), queryOptions.ConnectionToUse);
ExecuteScalar(GetTransactionTextForProcedure <T>(transactionType, true), queryOptions.ConnectionToUse, false);
throwIfError = true;
goto Start;
}
Logger.Error(mySqlException);
throw;
}
catch (Exception exception)
{
Logger.Error(exception);
throw;
}
if (logTransaction)
{
LogTransaction(Cope <T> .ModelComposition.TableName, transactionType, queryOptions);
}
return(result);
}
public Result <T> ExecuteProcedure <T>(QueryOptions queryOptions, TransactionTypes transactionType, bool logTransaction, object obj, Expression <Func <T, bool> > expression) where T : Cope <T>, IManageable, new()
{
Result <T> result = null;
bool throwIfError = false;
Start:
try
{
Logger.Info(string.Format("Starting {0} execution for object {1} using connection {2}", transactionType.ToString(), typeof(T), queryOptions));
if (Manager.ConstantTableConsolidation)
{
if (!typeof(T).Equals(typeof(Log)))
{
PerformFullTableCheck(new T(), queryOptions.ConnectionToUse);
}
}
switch (transactionType)
{
case TransactionTypes.Select:
result = ExecuteSelect(expression, queryOptions, transactionType);
break;
case TransactionTypes.SelectAll:
result = ExecuteSelectAll((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.Delete:
result = ExecuteProcedure((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.DeleteMassive:
result = ExecuteMassiveOperation((IEnumerable <T>)obj, queryOptions, transactionType);
break;
case TransactionTypes.Insert:
result = ExecuteProcedure((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.InsertMassive:
result = ExecuteMassiveOperation((IEnumerable <T>)obj, queryOptions, transactionType);
break;
case TransactionTypes.Update:
result = ExecuteProcedure((T)obj, queryOptions, transactionType);
break;
case TransactionTypes.UpdateMassive:
result = ExecuteMassiveOperation((IEnumerable <T>)obj, queryOptions, transactionType);
break;
default:
throw new NotSupportedException($"El tipo de transaccion {transactionType.ToString()} no puede ser utilizado con esta funcion.");
}
Logger.Info(string.Format("Execution {0} for object {1} using connection {2} has finished successfully.", transactionType.ToString(), typeof(T), queryOptions));
}
catch (SqlException sqlException) when(sqlException.Number == ERR_STORED_PROCEDURE_NOT_FOUND)
{
if (Manager.AutoCreateStoredProcedures && !throwIfError)
{
Logger.Warn(string.Format("Stored Procedure for {0} not found. Creating...", transactionType.ToString()));
ExecuteScalar(GetTransactionTextForProcedure <T>(transactionType, false), queryOptions.ConnectionToUse, false);
throwIfError = true;
goto Start;
}
Logger.Error(sqlException);
throw;
}
catch (SqlException sqlException) when(sqlException.Number == ERR_OBJECT_NOT_FOUND)
{
if (Manager.AutoCreateTables && !throwIfError)
{
Logger.Warn(string.Format("Table {0} not found. Creating...", Cope <T> .ModelComposition.TableName));
PerformFullTableCheck(new T(), queryOptions.ConnectionToUse);
throwIfError = true;
goto Start;
}
Logger.Error(sqlException);
throw;
}
catch (SqlException sqlException) when(sqlException.Number == ERR_INCORRECT_NUMBER_OF_ARGUMENTS ||
sqlException.Number == ERR_CANNOT_INSERT_EXPLICIT_VALUE_FOR_IDENTITY ||
sqlException.Number == ERR_EXPECTED_PARAMETER_NOT_SUPPLIED ||
sqlException.Number == ERR_CANNOT_UPDATE_IDENTITY_VALUE ||
sqlException.Number == ERR_NOT_A_PARAMETER_FOR_PROCEDURE)
{
if (Manager.AutoAlterStoredProcedures && !throwIfError)
{
Logger.Warn(string.Format("Incorrect number of arguments or is identity explicit value related to the {0} stored procedure. Modifying...", transactionType.ToString()));
PerformFullTableCheck(new T(), queryOptions.ConnectionToUse);
ExecuteScalar(GetTransactionTextForProcedure <T>(transactionType, true), queryOptions.ConnectionToUse, false);
throwIfError = true;
goto Start;
}
Logger.Error(sqlException);
throw;
}
catch (Exception exception)
{
Logger.Error(exception);
throw;
}
if (logTransaction)
{
LogTransaction(Cope <T> .ModelComposition.TableName, transactionType, queryOptions);
}
return(result);
}
protected void SetMassiveOperationParameters <T>(IEnumerable <T> obj, TransactionTypes transactionType, QueryOptions queryOptions) where T : Cope <T>, IManageable, new()
{
Logger.Info(string.Format("Setting parameters in command based massive operation transaction type {1}.", typeof(T), transactionType.ToString()));
MassiveOperationParameter parameters = DataSerializer.GenerateCompatibleMassiveOperationXML(obj, transactionType);
_command.Parameters.Add(CreateDbParameter("_xmlValues", parameters.XmlValues));
_command.Parameters.Add(CreateDbParameter("_xmlNames", parameters.XmlNames));
_command.Parameters.Add(CreateDbParameter("_procedureName", parameters.ProcedureName));
}
protected void SetParameters <T>(T obj, TransactionTypes transactionType, bool considerPrimary, QueryOptions queryOptions) where T : Cope <T>, IManageable, new()
{
Logger.Info(string.Format("Setting parameters in command based on type {0} for transaction type {1}.", typeof(T), transactionType.ToString()));
if (transactionType == TransactionTypes.Delete)
{
_command.Parameters.Add(CreateDbParameter(string.Format("_{0}", Cope <T> .ModelComposition.PrimaryKeyProperty.Name), Cope <T> .ModelComposition.PrimaryKeyProperty.GetValue(obj)));
return;
}
foreach (KeyValuePair <string, PropertyInfo> property in Cope <T> .ModelComposition.FilteredProperties)
{
if (property.Value.Equals(Cope <T> .ModelComposition.PrimaryKeyProperty) && property.Value.PropertyType.Equals(typeof(int?)) && !considerPrimary)
{
continue;
}
_command.Parameters.Add(CreateDbParameter("_" + property.Value.Name, property.Value.GetValue(obj)));
}
}
protected string GetTransactionTextForProcedure <T>(TransactionTypes transactionType, bool doAlter) where T : Cope <T>, IManageable, new()
{
Logger.Info(string.Format("Getting {0} transaction for type {1}. DoAlter = {2}", transactionType.ToString(), typeof(T), doAlter));
switch (transactionType)
{
case TransactionTypes.Delete:
return(_creator.CreateDeleteStoredProcedure <T>(doAlter));
case TransactionTypes.DeleteMassive:
return(_creator.CreateMassiveOperationStoredProcedure <T>(doAlter));
case TransactionTypes.Insert:
return(_creator.CreateInsertStoredProcedure <T>(doAlter));
case TransactionTypes.InsertMassive:
return(_creator.CreateMassiveOperationStoredProcedure <T>(doAlter));
case TransactionTypes.Update:
return(_creator.CreateUpdateStoredProcedure <T>(doAlter));
case TransactionTypes.UpdateMassive:
return(_creator.CreateMassiveOperationStoredProcedure <T>(doAlter));
default:
ArgumentException argumentException = new ArgumentException("El tipo de transaccion no es valido para generar un nuevo procedimiento almacenado.");
Logger.Error(argumentException);
throw argumentException;
}
}
