public IList <TEntity> Consultar <TEntity>(string sql, TEntity entity, OptionsQueryZero options = null) where TEntity : class, ISelect, new()
{
try
{
SqlCommand command = Connection.CreateCommand();
command.Transaction = Transaction;
Statement statement = QueryZero.GetSelect(entity, options);
foreach (KeyValuePair <string, object> entry in statement.Values)
{
command.Parameters.AddWithValue(entry.Key, entry.Value ?? DBNull.Value);
}
//Se crea un data set para regresarlo
DataSet theDataSet = new DataSet();
command.CommandText = sql;//se cambia por el personalizado en lugar del generado
SqlDataAdapter theDataAdapter = new SqlDataAdapter(command);
//Se llena e dataset
theDataAdapter.Fill(theDataSet);
return(Zero.Utils.ZeroUtils.DataSetToList <TEntity>(theDataSet));
}
catch (Exception e)
{
Logger.Error(Codes.ERR_00_03, e);
throw new DalException(Codes.ERR_00_01, e);
}
}
/// <summary>
/// Método que busca el primer elemento de acuerdo a los parametros enviados en la entidad, y discrimina registros de acuerdo a las opciones configuradas.
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity">Entidad con los parámetros para el where</param>
/// <param name="options">Opciones de configuración para discriminar el where de la consulta</param>
/// <returns></returns>
public TEntity BuscarUno <TEntity>(TEntity entity, OptionsQueryZero options = null) where TEntity : class, ISelect, new()
{
try
{
return(Consultar(entity, options).FirstOrDefault());
}
catch (Exception e)
{
Logger.Error(Codes.ERR_00_01, e);
throw new DalException(Codes.ERR_00_01, e);
}
}
private static object GetValueForDataBase(object value, bool isWhere = false, OptionsQueryZero options = null)
{
if (options == null)
{
options = new OptionsQueryZero();
}
if (value == null)
{
return(isWhere ? "is null" : "null");
}
object valor = value;
string prefix = string.Empty;
Type propertyType = value.GetType();
//se asigna el prefijo solo si contiene valor
if (TiposWrapper.ContainsKey(propertyType))
{
prefix = TiposWrapper[propertyType];
}
if (value is bool)
{
valor = (bool)value ? "1" : "0";
}
if (value is string)
{
if (options.UseLikeForString)
{
valor = "%" + valor + "%";
}
}
if (value is DateTime)
{
if ((DateTime)value == DateTime.MinValue || (DateTime)value == DateTime.MaxValue)
{
return(isWhere ? "is null" : "null");
}
valor = ((DateTime)value).ToString("dd/MM/yyyy HH:mm:ss");
}
return(prefix + valor + prefix);
}
public IList <TEntity> ExecuteStoredProcedure <TEntity>(TEntity entity, OptionsQueryZero options = null) where TEntity : class, ISelect, new()
{
try
{
SqlCommand command = QueryZero.GetStoredProcedureCommand(entity, options);
command.Connection = GetConnection();
command.Transaction = Transaction;
//Se crea un data set para regresarlo
DataSet theDataSet = new DataSet();
SqlDataAdapter theDataAdapter = new SqlDataAdapter(command);
//Se llena e dataset
theDataAdapter.Fill(theDataSet);
return(Zero.Utils.ZeroUtils.DataSetToList <TEntity>(theDataSet));
}
catch (Exception e)
{
Logger.Error(Codes.ERR_00_03, e);
throw new DalException(Codes.ERR_00_16, e);
}
}
public static SqlCommand GetStoredProcedureCommand(ISelect entity, OptionsQueryZero options = null)
{
Table table = entity.GetType().GetCustomAttribute <Table>();
string nameTable = table == null?entity.GetType().Name : table.Name;
SqlCommand command = new SqlCommand
{
CommandText = nameTable,
CommandType = CommandType.StoredProcedure
};
PropertyInfo[] properties = entity.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
Column columna = property.GetCustomAttribute <Column>();
if (columna != null && columna.Exclude)
{
continue;
}
string nameColumn = columna == null ? property.Name : columna.Name;
object value = property.GetValue(entity, null);
if (value is DateTime && ((DateTime)value == DateTime.MinValue || (DateTime)value == DateTime.MaxValue))
{
value = null;
}
if (value is string)
{
if (string.IsNullOrEmpty((string)value))
{
value = null;
}
}
if (value is bool && options.ExcludeBool)
{
value = null;
}
if (value is int && (int)value == default(int) && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value is long && (long)value == default(long) && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value is decimal && (decimal)value == default(decimal) && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value is double && Math.Abs((double)value - default(double)) < 0.01 && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value != null)
{
command.Parameters.Add("@" + nameColumn, columna.SqlDataType, GetValue(property.GetValue(entity, null)).ToString().Length).Value = (string)GetValue(property.GetValue(entity, null));
}
}
return(command);
}
public static Statement GetSelect(ISelect entity, OptionsQueryZero options = null)
{
if (options == null)
{
options = new OptionsQueryZero();
}
Statement statementComplete = new Statement();
if (entity == null)
{
throw new ZeroException(ZeroCodes.ERR_00_01);
}
StringBuilder selectFields = new StringBuilder();
StringBuilder selecttWhere = new StringBuilder();
IDictionary <string, object> values = new Dictionary <string, object>();
//obtenemos valores de las anotaciones
Table table = entity.GetType().GetCustomAttribute <Table>();
string nameTable = table == null?entity.GetType().Name : table.Name;
//se deben obtener todas las propiedades, incluidas las de la super clase
PropertyInfo[] properties = entity.GetType().GetProperties();
bool isFirst = true;
bool isFirstFilter = true;
foreach (PropertyInfo property in properties)
{
Column columna = property.GetCustomAttribute <Column>();
if (columna != null && columna.Exclude)
{
continue;
}
string nameColumn = columna == null ? property.Name : columna.Name;
statementComplete.SelectColumns.Add(property.Name, nameColumn);
if (isFirst)
{
isFirst = false;
}
else
{
selectFields.Append(", ");
}
selectFields.Append(nameColumn);
object value = property.GetValue(entity, null);
bool useEqual = true;
if (value is DateTime && ((DateTime)value == DateTime.MinValue || (DateTime)value == DateTime.MaxValue))
{
value = null;
}
if (value is string)
{
if (string.IsNullOrEmpty((string)value))
{
value = null;
}
if (options.UseLikeForString)
{
useEqual = false;
}
}
if (value is bool && options.ExcludeBool)
{
value = null;
}
if (value is int && (int)value == default(int) && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value is long && (long)value == default(long) && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value is decimal && (decimal)value == default(decimal) && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value is double && Math.Abs((double)value - default(double)) < 0.01 && options.ExcludeNumericsDefaults)
{
value = null;
}
if (value != null)
{
if (isFirstFilter)
{
isFirstFilter = false;
}
else
{
selecttWhere.Append(" and ");
}
selecttWhere.Append(nameColumn);
selecttWhere.Append(useEqual ? " = " : " like ");
selecttWhere.Append("@");
selecttWhere.Append(nameColumn);
values.Add("@" + nameColumn, GetValue(property.GetValue(entity, null)));
}
}
StringBuilder select = new StringBuilder();
select.Append("Select ");
select.Append(selectFields);
select.Append(" From ");
select.Append(nameTable);
if (!isFirstFilter && !options.ExcludeWhere)
{
select.Append(" Where ");
select.Append(selecttWhere);
}
statementComplete.StatementQuery = select.ToString();
statementComplete.Values = values;
if (!string.IsNullOrEmpty(options.WhereComplementary) && !options.ExcludeWhere)
{
if (options.WhereComplementary.Trim().ToUpper().StartsWith("AND "))
{
options.WhereComplementary = options.WhereComplementary.Trim().Substring(3);
}
if (!isFirstFilter)
{
//si empieza con and
statementComplete.StatementQuery += " and ";
}
else
{
statementComplete.StatementQuery += " Where ";
}
statementComplete.StatementQuery += options.WhereComplementary;
}
return(statementComplete);
}
