public static string parseWheres(this object wheres, out List <SqlParameter> list) { string returnValue = ""; list = new List <SqlParameter>(); if (wheres == null) { return(returnValue); } //单条件 if (wheres is Wheres) { Wheres wh = (Wheres)wheres; list.Add(new SqlParameter("@" + wh.key, wh.value)); return(string.Format(" {0} {1} {2} @{1} ", wh.relation, wh.key, wh.operation)); } //多条件 List <Wheres> li = (List <Wheres>)wheres; foreach (Wheres whs in li) { returnValue += string.Format(" {0} {1} {2} @{1} ", whs.relation, whs.key, whs.operation); list.Add(new SqlParameter("@" + whs.key, whs.value)); } return(returnValue); }
/// <summary> /// Instancie o objeto e clona /// </summary> /// <param name="pTableQuery">Objeto table query</param> public TableQuery(TableQuery pTableQuery) : this() { DbName = pTableQuery.DbName; TableName = pTableQuery.TableName; FieldName = pTableQuery.FieldName; Alias = pTableQuery.Alias; SchemaTable = pTableQuery.SchemaTable; Top = pTableQuery.Top; Distinct = pTableQuery.Distinct; Fields = new TableAdapterFieldCollection(pTableQuery.Fields); foreach (OrderBy myItem in pTableQuery.OrderBy) { OrderBy.Add(new OrderBy(myItem)); } pTableQuery.Wheres.ForEach( w => Wheres.Add(new WhereCollection(w))); if (pTableQuery.Joins != null) { pTableQuery.Joins.ForEach( j => Joins.Add(new Join(j))); } if (pTableQuery.Union != eUnionType.eutNone) { pTableQuery.InternalQuery.ForEach( c => InternalQuery.Add(new TableQuery(c))); } }
public string[] AdicionarCondicaoEntre(string campo, object inicio, object fim) { var parametros = new[] { string.Empty, string.Empty }; var parametroInicio = "_p" + _proximoParametro.ToString() + "a"; var parametroFim = "_p" + _proximoParametro.ToString() + "b"; if ((inicio != null) && (fim != null)) { parametros[0] = parametroInicio; parametros[1] = parametroFim; Wheres.Add("([" + campo + "]between @" + parametroInicio + " and @" + parametroFim + ")"); } else if (inicio != null) { parametros[0] = parametroInicio; Wheres.Add("([" + campo + "]>=@" + parametroInicio + ")"); } else { parametros[1] = parametroFim; Wheres.Add("([" + campo + "]<=@" + parametroFim + ")"); } _proximoParametro++; return(parametros); }
public override (string Sql, List <object> Values, object ClassObject) ToSqlBinding() { StringBuilder strBuilder = new StringBuilder(); strBuilder.Append($"UPDATE {Table}"); strBuilder.Append(" SET "); int i = 0; for (i = 0; i < Columns.Count; i++) { if (i > 0) { strBuilder.Append(","); } strBuilder.AppendFormat("{0}={1}", Columns[i], $"@p{i}"); } strBuilder.Append(" WHERE "); foreach (var where in Wheres) { strBuilder.AppendFormat("{0}={1}", where.Key, $"@p{i++}"); } List <object> valueAndWhere = Values; valueAndWhere.AddRange(Wheres.Select(x => x.Value)); return(strBuilder.ToString(), valueAndWhere, ParameterObjectBuilder.CreateObjectWithValues(Values)); }
public IEnumerable <T> Get() { if (Repository.SoftDelete & !WhithDeleted) { this.WhereNull("deleted_at"); } string orderByColumn = string.IsNullOrEmpty(OrderByColumn) ? Repository.PrimareKey : OrderByColumn; string wheres = Wheres.Count == 0 ? string.Empty : " WHERE"; DbParameter[] parameters = new DbParameter[ParametersCount]; ParametersCount = 0; while (Wheres.Count > 0) { var where = Wheres.Dequeue(); if (where is ISqlWhereComparer) { parameters[ParametersCount++] = ((ISqlWhereComparer) where).Parameter; } wheres = string.Concat(wheres, where, Wheres.Count > 0 ? where.LogicOperator.ToString() : string.Empty ); } string sintax = string.Format("SELECT * FROM {0}{1} ORDER BY {2} {3}", Repository.TableName, wheres, orderByColumn, OrderByMode); return(Repository.ExecuteSelectQuery(sintax, parameters)); }
public string[] AdicionarCondicaoEntre(string campo, object inicio, object fim) { var parametros = new[] { string.Empty, string.Empty }; var parametroInicio = string.Concat("_p", _proximoParametro.ToString(), "a"); var parametroFim = string.Concat("_p", _proximoParametro.ToString(), "b"); if ((inicio != null) && (fim != null)) { parametros[0] = parametroInicio; parametros[1] = parametroFim; Wheres.Add(string.Concat("([", campo, "]between @", parametroInicio, " and @", parametroFim, ")")); } else if (inicio != null) { parametros[0] = parametroInicio; Wheres.Add(string.Concat("([", campo, "]>=@", parametroInicio, ")")); } else { parametros[1] = parametroFim; Wheres.Add(string.Concat("([", campo, "]<=@", parametroFim, ")")); } _proximoParametro++; return(parametros); }
/// <summary> /// Get the current query value bindings including the where clauses. /// </summary> /// <returns>A collection of columns and values.</returns> private Dictionary <string, dynamic> PrepareBindings(Dictionary <string, dynamic> values = null) { Dictionary <string, dynamic> bindings = values is null ? new Dictionary <string, dynamic>() : values; Wheres.ForEach(w => bindings.Add(w.Column, w.Value)); return(bindings); }
/// <summary> /// Add a another condition to the WHERE Clause that need to be true /// </summary> /// <param name="where">The WHERE Clause to add</param> public virtual Where And(Where where) { if (where == null) { throw new System.ArgumentNullException(); } Wheres.Add(where); return(this); }
/// <summary> /// 查询username是否存在 /// </summary> /// <param name="username"></param> /// <returns></returns> public bool QueryExitByUsername(string username) { List <Wheres> list = new List <Wheres>(); Wheres wh = new Wheres(); wh.setField("username", "=", username, ""); list.Add(wh); return(_dao.QuerySingleByWheres(wh) != null); }
/// <summary> /// 查询openId是否存在 /// </summary> /// <param name="openId"></param> /// <returns></returns> public bool QueryExitByOpenId(string openId) { List <Wheres> list = new List <Wheres>(); Wheres wh = new Wheres(); wh.setField("openId", "=", openId, ""); list.Add(wh); return(_dao.QuerySingleByWheres(wh) != null); }
/// <summary> /// Add a another condition to the WHERE Clause that can be true instead /// </summary> /// <param name="where">The WHERE Clause to add</param> public virtual Where Or(Where where) { if (where == null) { throw new System.ArgumentNullException(); } where.IsAnd = false; Wheres.Add(where); return(this); }
/// <summary> /// 对空值、NULL值,不添加任何语句 /// </summary> protected void AddParam(string alias, string colName, string namedParam, object value, string expression) { if (value == null || string.Empty.Equals(value))//by crabo 2016.09.26 { return; } if (namedParam == null)//使用自增的动态参数名? { namedParam = colName + Parameters.Count.ToString(); } if (this.NamedQuery != null)//sp? { if (Parameters.ContainsKey(namedParam)) { throw new ArgumentException("试图重复添加同一参数名:" + namedParam); } else { Parameters.Add(namedParam, value); } return; } if (value != null) { if (Parameters.ContainsKey(namedParam)) { throw new ArgumentException("试图重复添加同一参数名:" + namedParam); } else { Parameters.Add(namedParam, value); } } else if (expression.IndexOf(StatementParser.PREFIX + namedParam) > 0 || //参数名在语句中已存在 expression.IndexOf(StatementParser.PREFIX + "{2}") > 0)//参数占位符{2}在语句中已存在 { expression = "and {0}{1} is null"; } if (!string.IsNullOrEmpty(alias)) { alias += "."; } if ((this.CustomSQL == null || this.CustomSQL.IndexOf(StatementParser.PREFIX + namedParam) < 0) && //not in sql text !Wheres.ContainsKey(namedParam)) //not in where { this.Wheres.Add(namedParam, string.Format(expression, alias, colName, namedParam)); //add to where } }
public string AdicionarCondicao(string campo, int operador, object valor) { var parametro = "_p" + _proximoParametro.ToString(); if ((valor == null) || (valor == DBNull.Value)) { Wheres.Add("([" + campo + "]IS NULL)"); } else { Wheres.Add("([" + campo + "]" + ConsultarSinalDoOperador(operador) + "@" + parametro + ")"); } _proximoParametro++; return(parametro); }
public void AdicionarCondicaoApenasCampoNaoNulo(string campo) { Wheres.Add(string.Concat("([", campo, "]IS NOT NULL)")); }
private void AddWhereComparer(string columnName, string @operator, object value, LogicOperator logicOperator = LogicOperator.AND) { ParametersCount++; Wheres.Enqueue(new MySqlWhereComparer(Wheres.Count, columnName, value, @operator, logicOperator)); }
public SqlBuilderUpdate Where(string name, object value) { Wheres.Add(Provider.CreateTag(name), value); return(this); }
public void AdicionarCondicaoApenasCampoNaoNulo(string campo) { Wheres.Add("([" + campo + "]IS NOT NULL)"); }
public void AdicionarCondicaoPersonalizada(string condicao) { Wheres.Add("(" + condicao + ")"); }
/// <summary> /// Add a basic where clause with a custom operator to the query. /// </summary> /// <param name="column">The column to which the clause applies.</param> /// <param name="ope">The operator for the where clause.</param> /// <param name="value">The value of the clause.</param> /// <param name="boolean">The boolean that applies for the where clause.</param> /// <returns>The current query.</returns> public Builder <T> Where(string column, string ope, dynamic value, string boolean = "AND") { Wheres.Add(new WhereClause(column, ope, value, boolean)); return(this); }
public void AdicionarCondicaoPersonalizada(string condicao) { Wheres.Add(string.Concat("(", condicao, ")")); }
public IResponseQuery <T> WhereNotNull(string columnName, LogicOperator logicOperator = LogicOperator.AND) { Wheres.Enqueue(new MySqlWhereNotNull(columnName, logicOperator)); return(this); }