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