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