public void Should_return_empty_if_sqlWhereClause_is_null() { _where.Add(null); string result = _builder.ToString(); result.ShouldBeEqualTo(""); }
public void WhereTest() { var Fmt = this.GetSqlFormatter(); var Where = new Where(); Where.Add(new ComparisonCondition("col1", ComparisonOperators.GreaterOrEqual, 32)); Where.Add(new ComparisonCondition("col2", ComparisonOperators.InsensitiveLike, "%part%")); Where.Add(new SqlCondition("col3 IS NOT NULL")); Assert.AreEqual("(col1>=32 AND col2 LIKE '%part%' AND col3 IS NOT NULL)", Fmt.SqlText(Where)); }
/// <summary> /// errors are thrown on empty filed name, /// if value1 or value2 is null /// /// the expression will be skipped of both values are string.empty.No error thrown. /// </summary> /// <param name="fieldName"></param> /// <param name="value1"></param> /// <param name="value2"></param> /// <returns></returns> public IQueryBuilder AddBetween(string fieldName, string value1, string value2) { Contract.Requires <ArgumentNullException>(!string.IsNullOrWhiteSpace(fieldName)); Contract.Requires <ArgumentNullException>(value1 != null || value2 != null); string betweenExpression = "{0} BETWEEN {1} AND {2}"; var wi = new WhereItem(); // if there is no property name we can't really do anything. if (string.IsNullOrWhiteSpace(value1) && string.IsNullOrWhiteSpace(value2)) { return(this); } //put the property name in there then send in the other spaces var expression = string.Format(betweenExpression, fieldName, "{0}", "{1}"); wi.WhereExpression = expression; var prms = new List <object>() { value1, value2 }; wi.SqlParams.AddRange(prms); Where.Add(wi); return(this); }
private void Files_SelectedIndexChanged(object sender, EventArgs e) { string status = string.Empty; Where.Nodes.Clear(); _checked.Clear(); if (Files.SelectedItem != null) { if (src.DuplicatesGroupByName.ContainsKey((string)Files.SelectedItem)) { foreach (long index in src.DuplicatesGroupByName[(string)Files.SelectedItem]) { foreach (DuplicateFileInfo s in src.DuplicatesByGroup[index]) { if (s.FileName == Files.Text) { status = (string)Files.SelectedItem + " " + s.Length.ToFileSizeString(); Where.Add(s.FullPath); foreach (DuplicateFileInfo o in src.DuplicatesByGroup[index]) { Where.Add(o.FullPath); } } } } } Where.ExpandAll(); } OnItemStatusUpdate?.Invoke(this, status); }
public void SetWhere(LambdaExpression whereExpression) { if (whereExpression != null) { Where.Add(new QueryWhere(whereExpression)); } }
public void SetWhere(string whereSql) { if (whereSql.NotNull()) { Where.Add(new QueryWhere(whereSql)); } }
public void SetWhere(LambdaExpression expression, QueryOperator queryOperator, INetSqlQueryable subQueryable) { if (subQueryable != null) { Where.Add(new QueryWhere(expression, queryOperator, subQueryable)); } }
public QuerySQLHelper AddWhere(ColumnRelevanceMapper column) { if (column != null) { Where.Add(column); } return(this); }
public IQueryBuilder AddWhere(string sqlParam, string expression) { Where.Add(new WhereItem() { SqlParam = sqlParam, WhereExpression = expression }); return(this); }
public IQueryBuilder AddBetween(DateSearch dateSearch) { var cmd = new DateSearchCommand(); var wi = cmd.CreateQuery(dateSearch); Where.Add(wi); return(this); }
public void Add(ICondition condition) { if (_conditions == null) { Where.Add(condition); } else { _conditions.Add(condition); } }
/// <summary> /// Prepara o comando antes de executar um select /// </summary> /// <param name="command">comando que será preparado antes da execução</param> /// <param name="where">Filtro where adicional</param> public override void PrepareReader(Command command, Where where) { base.PrepareReader(command, where); string selectFields = ""; string fieldEmpresa = GetField("GUIDEmpresa"); if(!String.IsNullOrEmpty(fieldEmpresa)) { selectFields += fieldEmpresa; string parameterName = Parameter.FixParameterName(fieldEmpresa, "base"); where.Add($"{fieldEmpresa.Split(new string[] { " AS " })[0]} = {parameterName}", new Parameter { ParameterName = parameterName, Value = Settings.EmpresaAtual.ID }); } selectFields += GetField("GUID"); selectFields += GetField("EGUID"); selectFields += GetField("DataHoraCadastro"); selectFields += GetField("DataHoraAlteracao"); selectFields += GetField("GUIDUsuarioCadastro"); selectFields += GetField("GUIDUsuarioAlteracao"); string s = where.FirstOrDefault(w => w.StartsWith("GUID", StringComparison.InvariantCultureIgnoreCase)); if(s != null) { string tableName = DbContext.FindTableDefinition(this).TableName; where.Remove(s); where.Add(tableName + "." + s); } command.CommandText = command.CommandText.Replace("{selectFields}", selectFields + " {selectFields}"); }
public IQueryBuilder AddLike(string fieldName, string likeVal, bool excludeIfValIsNullOrWhiteSpace = true) { if (string.IsNullOrWhiteSpace(likeVal)) { System.Diagnostics.Trace.WriteLine(string.Format("{0} was not added to the like statement because the value was null", fieldName)); return(this); } var wi = new WhereItem() { WhereExpression = LikeExp(fieldName), SqlParam = LikeSqlParam(likeVal) }; Where.Add(wi); return(this); }
private Where BuildWhereFromIds(Type type, object[] ids) { IList <PropertyInfo> keyColumns = GetKeyColumns(type).Select(c => c.Value).ToList(); Where where = new Where { ReflectedType = (type) }; for (int i = 0; i < ids.Length; i++) { where.Add(keyColumns[i].Name, Where.EQ, ids[i]); } return(where); }
/// <summary> /// adds to the where expression. /// </summary> /// <param name="sqlField">e.g.. FirstName</param> /// <param name="operationSymbol"> greater than less than equal too</param> /// <param name="paramValue">"Fred"</param> /// <param name="skipOnEmptyParamValue"></param> /// <returns></returns> public IQueryBuilder AddWhere(string sqlField, string operationSymbol, string paramValue, bool skipOnEmptyParamValue = true) { //don't add this to the list. if (skipOnEmptyParamValue && string.IsNullOrWhiteSpace(paramValue)) { return(this); } var expression = string.Format(@" {0} {1} ", sqlField, operationSymbol) + " @{0} "; Where.Add(new WhereItem() { SqlParam = paramValue, WhereExpression = expression }); return(this); }
protected void FillWhere(params NameValueCollection[] collections) { foreach (var collection in collections) { foreach (var item in collection) { var key = item.ToString(); if (Where.Any(p => p.Key.ToLower() == key.ToLower()) || string.IsNullOrEmpty(collection[key]) || key.ToLower() == "page" || key.ToLower() == "rows" || key.ToLower() == "order" || key.ToLower() == "sort") { continue; } Where.Add(key, collection[key].Trim()); } } }
private Where BuildWhereFromIds(object entity) { Type type = entity.GetType(); List <PropertyInfo> keyColumns = GetKeyColumns(type).Select(c => c.Value).ToList(); Where where = new Where { ReflectedType = (type) }; for (int i = 0; i < keyColumns.Count; i++) { where.Add(keyColumns[i].Name, Where.EQ, GetValue(keyColumns[i], entity)); } return(where); }
/// <summary> /// 添加Where /// </summary> /// <param name="key"></param> /// <param name="value"></param> /// <returns></returns> public void Add(string key, object value) { if (Where == null) { Where = new Dictionary <string, object>(); } if (Where.ContainsKey(key)) { Where.Remove(key); } if (string.IsNullOrEmpty(key) || value == null || (value is string && string.IsNullOrEmpty(value.ToString()))) { return; } Where.Add(key, value); }
/// <summary> /// Instancia um objeto e atribui as chaves como compos de condição /// </summary> /// <param name="pTableAdapter">Um table adapter</param> /// <param name="pKeysAsWhere"></param> public TableQuery(TableAdapter pTableAdapter, bool pKeysAsWhere = false) : this() { Fields.AddRange(pTableAdapter.Collumns); DbName = pTableAdapter.DBName; TableName = pTableAdapter.TableName; SchemaTable = pTableAdapter.SchemaTable; WithoutNotLock = pTableAdapter.WithoutNotLock; if (!pKeysAsWhere) { return; } foreach (var myItem in pTableAdapter.KeyFields) { Where.Add(new QueryParam(myItem)); } }
/// <summary> /// Retorna os lançamentos realizados no caixa informado para o usuário informado. /// <para>Se não for informado o caixa ou o usuário, será buscado todos os lançamentos.</para> /// </summary> /// <param name="caixa">Caixa para pesquisar os lançamentos. Se nulo ou vazio irá trazer todos os caixas</param> /// <param name="usuario">Usuário para filtrar os registros somente por ele. Se nulo ou vazio irá trazer para todos os usuários</param> /// <param name="status">Status do lançamento. Se informado um valor inválido, não irá filtrar pelo status.</param> /// <returns></returns> public IList<IContaCorrenteCaixa> Find(ICaixa caixa, IUsuario usuario, StatusLancamento status) { Where w = new Where(); if(!caixa.IsNullOrEmpty()) w.Add("cxa_lancccaixa.guidcaixa", caixa.GUID); if(!usuario.IsNullOrEmpty()) w.Add("cxa_lancccaixa.guidusuariooperador", usuario.GUID); if(status.IsValid()) w.Add("cxa_lancccaixa.statuslancamento", (int)status); return new Data.FrenteCaixa.Lancamento.LancamentoCaixa().Find<IContaCorrenteCaixa>(w); }
private void sfModelo_OnSelectedValueChanged(object sender, ComponentModel.SearchField.SelectedItemArgs e) { sfAnoFabricacao.Enabled = true; Where w = new Where(); w.Add("GUIDModelo", e.GUID); sfAnoFabricacao.DataSource = new AnoFabricacao().GetDisplayValues(w); }
/// <summary> /// Este método vai ser util para filtrar os cadastros de Exportação, pois o mesmo /// não possui uma espcialização e utiliza a mesma FieldDefinition da Leiaute Base. /// Então aqui ele vai filtrar somente os especificos de cada leiaute. /// </summary> protected override void PrepareReader(Command command, Where where) { base.PrepareReader(command, where); where.Add("Importacao", Importacao); }
/// <summary> /// Pesquisar todos os movimentos utilizando a SearchWindow /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnPesquisarMovimento_Click(object sender, EventArgs e) { IParentModel tipoMovimento = null; // Aqui verifica o tipo de movimento que o usuário quer pesquisar if (rbtDAV.Checked) tipoMovimento = new DAV(); else if (rbtDAVOS.Checked) tipoMovimento = new Data.Faturamento.Lancamento.Movimento.DAV.OS.DAV(); else tipoMovimento = new PreVenda(); //Filtro para trazer somente os movimentos que estão com o status: Aberto Where where = new Where(); where.Add("fat_lan.status = @status", new Parameter { ParameterName = "@status", Value = (int)Enuns.Faturamento.Lancamento.Status.Aberto }); // Fazer a Pesquisa e pegar o GUID movimento selecionado SearchWindowResult result = SearchWindow.Show(tipoMovimento, where); if (!result.GUID.IsNullOrEmpty()) CarregarMovimento(result.GUID); }
/// <summary> /// <inheritdoc/> /// </summary> protected override void Prepare() { CheckKeyHasValue(Name); CheckOptionsProperties(); Schema = DefaultWhereNull(Schema, () => "dbo"); DbTable = Root !.DbTables !.Where(x => x.Name == Name && x.Schema == Schema).SingleOrDefault(); if (DbTable == null) { throw new CodeGenException(this, nameof(Name), $"Specified Schema.Table '{Schema}.{Name}' not found in database."); } Alias = DefaultWhereNull(Alias, () => new string(StringConversion.ToSentenceCase(Name) !.Split(' ').Select(x => x.Substring(0, 1).ToLower(System.Globalization.CultureInfo.InvariantCulture).ToCharArray()[0]).ToArray())); ViewName = DefaultWhereNull(ViewName, () => "vw" + Name); ViewSchema = DefaultWhereNull(ViewSchema, () => Schema); if (!string.IsNullOrEmpty(Permission) && Permission.Split(".", StringSplitOptions.RemoveEmptyEntries).Length == 1) { Permission += ".Read"; } ColumnNameIsDeleted = DefaultWhereNull(ColumnNameIsDeleted, () => Root !.ColumnNameIsDeleted); ColumnNameTenantId = DefaultWhereNull(ColumnNameTenantId, () => Root !.ColumnNameTenantId); ColumnNameOrgUnitId = DefaultWhereNull(ColumnNameOrgUnitId, () => Root !.ColumnNameOrgUnitId); ColumnNameRowVersion = DefaultWhereNull(ColumnNameRowVersion, () => Root !.ColumnNameRowVersion); ColumnNameCreatedBy = DefaultWhereNull(ColumnNameCreatedBy, () => Root !.ColumnNameCreatedBy); ColumnNameCreatedDate = DefaultWhereNull(ColumnNameCreatedDate, () => Root !.ColumnNameCreatedDate); ColumnNameUpdatedBy = DefaultWhereNull(ColumnNameUpdatedBy, () => Root !.ColumnNameUpdatedBy); ColumnNameUpdatedDate = DefaultWhereNull(ColumnNameUpdatedDate, () => Root !.ColumnNameUpdatedDate); ColumnNameDeletedBy = DefaultWhereNull(ColumnNameDeletedBy, () => Root !.ColumnNameDeletedBy); ColumnNameDeletedDate = DefaultWhereNull(ColumnNameDeletedDate, () => Root !.ColumnNameDeletedDate); PrepareJoins(); if (Order != null && Order.Count > 0) { foreach (var order in Order) { order.Prepare(Root !, this); } } foreach (var c in DbTable.Columns) { if (c.IsPrimaryKey) { var cc = new QueryColumnConfig { Name = c.Name, DbColumn = c }; cc.Prepare(Root !, this); PrimaryKeyColumns.Add(cc); } if ((ExcludeColumns == null || !ExcludeColumns.Contains(c.Name !)) && (IncludeColumns == null || IncludeColumns.Contains(c.Name !))) { var cc = new QueryColumnConfig { Name = c.Name, DbColumn = c }; var ca = AliasColumns?.Where(x => x.StartsWith(c.Name + "^", StringComparison.Ordinal)).FirstOrDefault(); if (ca != null) { var parts = ca.Split("^", StringSplitOptions.RemoveEmptyEntries); if (parts.Length == 2) { cc.NameAlias = parts[1]; } } cc.Prepare(Root !, this); Columns.Add(cc); } } if (Where == null) { Where = new List <QueryWhereConfig>(); } if (ColumnTenantId != null) { Where.Add(new QueryWhereConfig { Statement = $"[{Alias}].[{ColumnTenantId.Name}] = dbo.fnGetTenantId(NULL)" }); } if (ColumnIsDeleted != null) { Where.Add(new QueryWhereConfig { Statement = $"([{Alias}].[{ColumnIsDeleted.Name}] IS NULL OR [{Alias}].[{ColumnIsDeleted.Name}] = 0)" }); } if (!string.IsNullOrEmpty(Permission)) { Where.Add(new QueryWhereConfig { Statement = $"{Root!.GetUserPermissionSql}(NULL, NULL, '{Permission.ToUpperInvariant()}', NULL) = 1" });
/// <summary> /// Prepara o comando antes de executar um select /// </summary> /// <param name="command">comando que será preparado antes da execução</param> /// <param name="where">Filtro where adicional</param> protected override void PrepareReader(Command command, Where where) { base.PrepareReader(command, where); where.Add("fat_Lan.Tipo", (int)Tipo); }
/// <summary> /// Executas as validações impostas pela classe /// </summary> /// <param name="updating">Determina se é update ou insert</param> public override void Validate(bool updating) { base.Validate(updating); Where where = new Where(); object value = null; foreach (IRegraFiltro item in Filtros) { //Pesquisar todos os filtros da regra de análise de crédito value = item.Cliente.IsNullOrEmpty() ? null : (object)item.Cliente.GUID; where.Add(string.Format("cad_AnaliseCreditoRegraFiltro.GUIDCliente {0} @p1", value == null ? "is" : "="), new Parameter { ParameterName = "@p1", Value = value }); //Procurar as regras IList<IRegra> list = new Data.Regra.AnaliseCredito.Regra().Find<IRegra>(where); if (list.Count(w => w.GUID != GUID) > 0) throw new FilterAlreadyExists(Convert.ToString(list[0].Descricao)); } }
/// <summary> /// Adds the specified filtering field. /// </summary> /// <param name="field">The filtering field.</param> /// <param name="value">The filtering value.</param> /// <param name="operation">The filtering operation.</param> public void Add(string field, object value, FilterOperation operation = FilterOperation.Equal) { var filterItem = new FilterItem(field, value, operation); Where.Add(filterItem); }
/// <summary> /// Método para efetuar pesquisa de acordo com o digitado /// </summary> /// <returns>retorna uma lista de objetos</returns> private IList<object[]> Pesquisa() { try { Wait.Show(); IList<object[]> result = null; if(_dataSource.IsDynamicPaging) { #region sql Where where = new Where(); string nome = ""; string value = ""; string condicao = ""; foreach(Control item in grid.Controls .Cast<Control>() .Where(w => w is TextBoxFilter && !String.IsNullOrEmpty(w.Text))) { TextBoxFilter filter = item as TextBoxFilter; nome = filter.SourceColumn; value = item.Text; Parameter param = PrepareValueToFilter(nome, value, out condicao); where.Add(condicao, param); } _dataSource.ApplyFilters(where); result = DataSource.Values; #endregion } else { #region linq //Começa do valor 1 pois a coluna zero sempre será o GUID int indice = 1; result = DataSource.Values; foreach(TextBoxFilter text in grid.Controls.OfType<TextBoxFilter>()) { if(text.Text.Length > 0) result = (from j in result where VerificaPesquisa(j, text.Text, indice) select j).ToList(); indice++; } #endregion } return result; } finally { Wait.Close(); } }
public PageList <T> AddWhere(String fieldName, String condition, Object value) { Where.Add(fieldName, condition, value); return(this); }
protected override void PrepareReader(Command command, Where where) { base.PrepareReader(command, where); //------------------------------------------------------------------------- // Filtrar pelo tipo de item especial //------------------------------------------------------------------------- where.Add("cad_ItemEsp.Tipo", (int)Tipo); }
/// <summary> /// Pesquisa os voucher disponíveis /// </summary> /// <param name="sender">Objeto que disparou o evento</param> /// <param name="e">Argumento do evento gerado</param> private void btnPesquisar_Click(object sender, EventArgs e) { Where where = new Where(); where.Add("fat_lan.status = @status", new Parameter { ParameterName = "@status", Value = (int)Enuns.Faturamento.Lancamento.Status.Aberto }); IDevolucao devolucao = new Devolucao(); //Procura somente os vouchers no qual o status está aberto SearchWindowResult result = SearchWindow.Show(devolucao, where); //Se encontrou carrega os valores do número e valor do voucher if (!result.GUID.IsNullOrEmpty()) { devolucao = Data.Abstract.Faturamento.Lancamento.LancamentoBase.Create(result.GUID, false) as IDevolucao; txtVoucher.Text = devolucao.EGUID.ToString(); mnValor.Value = devolucao.ValorTotalLiquido; mnValor.Enabled = false; } }
public override void Validate(bool updating) { base.Validate(updating); #region vars Where where = new Where(); object value = null; #endregion foreach (IDescontoRegraFiltro filtro in Filtros) { //------------------------------------------------------------------------- // Pesquisar por todos os campos do fitro //------------------------------------------------------------------------- value = filtro.Cliente.IsNullOrEmpty() ? null : (object)filtro.Cliente.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDCliente = @p1 OR cad_DescontoRegraFiltro.GUIDCliente IS NULL)", value), new Parameter { ParameterName = "@p1", Value = value }); value = filtro.CondicaoPagamento.IsNullOrEmpty() ? null : (object)filtro.CondicaoPagamento.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDCondPgto = @p2 OR cad_DescontoRegraFiltro.GUIDCondPgto IS NULL)", value ), new Parameter { ParameterName = "@p2", Value = value }); value = filtro.Estado.IsNullOrEmpty() ? null : (object)filtro.Estado.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDEstado = @p3 OR cad_DescontoRegraFiltro.GUIDEstado IS NULL)", value ), new Parameter { ParameterName = "@p3", Value = value }); value = filtro.Fabricante.IsNullOrEmpty() ? null : (object)filtro.Fabricante.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDFabricante = @p4 OR cad_DescontoRegraFiltro.GUIDFabricante IS NULL)", value), new Parameter { ParameterName = "@p4", Value = value }); value = filtro.GrupoItem.IsNullOrEmpty() ? null : (object)filtro.GrupoItem.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDGrupoItem = @p5 OR cad_DescontoRegraFiltro.GUIDGrupoItem IS NULL)", value ), new Parameter { ParameterName = "@p5", Value = value }); value = filtro.Item.IsNullOrEmpty() ? null : (object)filtro.Item.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDItem = @p6 OR cad_DescontoRegraFiltro.GUIDItem IS NULL)", value ), new Parameter { ParameterName = "@p6", Value = value }); value = filtro.Quantidade; where.Add(String.Format("(cad_DescontoRegraFiltro.Quantidade = @p7 OR cad_DescontoRegraFiltro.Quantidade IS NULL)", value ), new Parameter { ParameterName = "@p7", Value = value }); value = filtro.Vendedor.IsNullOrEmpty() ? null : (object)filtro.Vendedor.GUID; where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDVendedor = @p8 OR cad_DescontoRegraFiltro.GUIDVendedor IS NULL)", value ), new Parameter { ParameterName = "@p8", Value = value }); IList<IDescontoRegra> regras = new DescontoRegra().Find<IDescontoRegra>(where); //------------------------------------------------------------------------- // Localizar a lista de regras //------------------------------------------------------------------------- if (regras.Count(w => w.GUID != GUID) > 0) { throw new Exceptions.Regra.FilterAlreadyExists(Convert.ToString(regras[0].Descricao)); } } }
public void Add(ICondition condition) { Where.Add(condition); }