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