public IEnumerable <T> ExecuteList <T>(string sql, IDictionary <string, object> parameters) where T : class, new()
{
Type TypeT = typeof(T);
ConstructorInfo ctor = TypeT.GetConstructor(Type.EmptyTypes);
if (ctor == null)
{
throw new InvalidOperationException($"Type {TypeT.Name} no contiene un constructor por default");
}
using (SqlConnection _conn = new SqlFactoryConnection().Connection())
{
using (SqlCommand Command = new SqlCommand(sql, _conn))
{
Command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
Command.Parameters.Clear();
foreach (KeyValuePair <string, object> kvp in parameters)
{
Command.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value != null ? kvp.Value : DBNull.Value));
}
}
using (SqlDataReader dr = Command.ExecuteReader())
{
T obj = new T();
while (dr.Read())
{
obj = DelegadoAccion <T>(dr);
yield return(obj);
}
}
}
}
}
public object ExecuteProcedure(string procedureName, ExecuteType executeType, IDictionary <string, object> parameters)
{
using (SqlConnection _conn = new SqlFactoryConnection().Connection())
{
try
{
object returnObject = null;
SqlCommand Command = new SqlCommand(procedureName, _conn)
{
CommandType = CommandType.StoredProcedure,
};
if (parameters != null)
{
Command.Parameters.Clear();
foreach (KeyValuePair <string, object> kvp in parameters)
{
Command.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
}
}
switch (executeType)
{
case ExecuteType.ExecuteNonQuery:
returnObject = Command.ExecuteNonQuery();
break;
case ExecuteType.ExecuteScalar:
returnObject = Command.ExecuteScalar();
break;
}
return(returnObject);
}
catch (Exception ex)
{
Logger?.LogError("Error de ejecución en modo sincrono ExecuteProcedure");
throw new ArgumentException(ex.Message, ex);
}
finally
{
if (_conn.State == ConnectionState.Open)
{
Logger?.LogInformation("Cerrando conexión en modo sincrono ExecuteProcedure");
_conn.Close();
}
}
}
}
public int ExecuteNonQuery(string procedureName, Dictionary <string, object> parameters)
{
int returnObject = 0;
using (SqlConnection _conn = new SqlFactoryConnection().Connection())
{
using (SqlCommand Command = new SqlCommand(procedureName, _conn))
{
try
{
Command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
Command.Parameters.Clear();
foreach (KeyValuePair <string, object> kvp in parameters)
{
Command.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
}
}
returnObject = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
Logger?.LogError($"Error en ejecución del siguiente Procedimiento {procedureName} se produjo el siguiente error: {ex.Message}");
throw new ArgumentException(ex.Message, ex);
}
finally
{
if (_conn.State == ConnectionState.Open)
{
Logger?.LogInformation("Cerrando conexión en modo sincrono ExecuteNonQuery");
_conn.Close();
}
}
}
return(returnObject);
}
}
