public SqlBuiderResult Build()
{
if (string.IsNullOrEmpty(_tableName))
{
throw new Exception("TableName is Null.");
}
if (!_pir.Any())
{
throw new Exception("Value is Null.");
}
var res = new SqlBuiderResult();
var fieldSb = new StringBuilder();
var valueSb = new StringBuilder();
foreach (var item in _pir)
{
fieldSb.Append($"`{item.Key}`,");
var valfild = $"@INPARM_{item.Key}";
valueSb.Append(valfild + ",");
res.Params.Add(valfild, item.Value);
}
res.SqlString.AppendFormat("INSERT INTO {0} (", _tableName);
res.SqlString.AppendLine(fieldSb.ToString().TrimEnd(','));
res.SqlString.AppendLine(")");
res.SqlString.AppendLine("VALUE(");
res.SqlString.AppendLine(valueSb.ToString().TrimEnd(','));
res.SqlString.AppendLine(");");
return(res);
}
public void ConverterQueryParamGroups(List <DynamicQueryParamGroup> groups, SqlBuiderResult collection, QueryRelation relation, SqlFieldMappings mappings, string parentKey)
{
if (!groups.Any())
{
return;
}
var list = groups.ToList();
for (var i = 0; i < list.Count; i++)
{
var item = list[i];
CheckQueryParamGroup(item);
if (IsParam(item))
{
ConverterQueryParams(item.Params, collection, item.Relation, mappings, parentKey);
}
else
{
collection.SqlString.Append("(");
ConverterQueryParamGroups(item.ChildGroups, collection, item.Relation, mappings, parentKey);
collection.SqlString.Append(")");
}
if (i < list.Count - 1)
{
collection.SqlString.Append(relation == QueryRelation.And ? " AND " : " OR ");
}
}
}
public SqlBuiderResult BuildWhere()
{
var res = new SqlBuiderResult();
if (_dynamicQueryParamGroup == null)
{
return(new SqlBuiderResult());
}
_mysqlConverterHelper.ConverterQueryParamGroup(_dynamicQueryParamGroup, res, SqlFieldMappings, null);
return(res);
}
public void ConverterQueryParamGroup(DynamicQueryParamGroup group, SqlBuiderResult collection, SqlFieldMappings mappings, string
parentKey)
{
CheckQueryParamGroup(group);
if (IsParam(group))
{
ConverterQueryParams(group.Params, collection, group.Relation, mappings, parentKey);
}
else
{
ConverterQueryParamGroups(group.ChildGroups, collection, group.Relation, mappings, parentKey);
}
}
public SqlBuiderResult Build()
{
if (string.IsNullOrEmpty(_tableName))
{
throw new Exception("TableName is Null.");
}
var res = new SqlBuiderResult();
res.SqlString.AppendLine($"DELETE FROM `{_tableName}` WHERE 1=1 ");
var whereres = BuildWhere();
if (whereres.SqlString.Length > 0)
{
res.SqlString.AppendLine(" AND " + whereres.SqlString.ToString());
}
res.SqlString.Append(" ;");
foreach (var item in whereres.Params)
{
res.Params.Add(item.Key, item.Value);
}
return(res);
}
public SqlBuiderResult Build()
{
if (string.IsNullOrEmpty(_tableName))
{
throw new Exception("TableName is Null.");
}
if (!_pir.Any())
{
throw new Exception("Value is Null.");
}
var res = new SqlBuiderResult();
res.SqlString.AppendLine($"UPDATE {_tableName} SET");
var filedSb = new StringBuilder();
foreach (var item in _pir)
{
var pfield = $"@UPPARM_{item}";
filedSb.AppendFormat("`{0}` = {1},", item.Key, pfield);
}
res.SqlString.AppendLine(filedSb.ToString().TrimEnd(','));
res.SqlString.AppendLine($"WHERE 1=1");
var whereRes = BuildWhere();
if (whereRes.SqlString.Length > 0)
{
res.SqlString.AppendLine(" AND " + whereRes.SqlString.ToString());
}
res.SqlString.Append(" ;");
foreach (var item in whereRes.Params)
{
res.Params.Add(item.Key, item.Value);
}
return(res);
}
public void ConverterQueryParams(List <DynamicQueryParam> queryParams, SqlBuiderResult collection, QueryRelation relation, SqlFieldMappings mappings, string parentKey)
{
if (!queryParams.Any())
{
return;
}
var list = queryParams.ToList();
collection.SqlString.Append("(");
for (var i = 0; i < list.Count; i++)
{
var item = list[i];
var field = item.Operator == QueryOperation.Any ? "" : GetSqlKey(mappings, item, parentKey);
var param = $"@{__PAM_}{collection.Params.Count}";
var val = GetSqlValue(mappings, item, parentKey);
switch (item.Operator)
{
case QueryOperation.Equal:
collection.SqlString.Append($"{field} = {param}");
collection.Params.Add(param, val);
break;
case QueryOperation.LessThan:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} < {param}");
break;
case QueryOperation.LessThanOrEqual:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} <= {param}");
break;
case QueryOperation.GreaterThan:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} > {param}");
break;
case QueryOperation.GreaterThanOrEqual:
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} >= {param}");
break;
case QueryOperation.Contains:
collection.Params.Add(param, $"%{val}%");
collection.SqlString.Append($"{field} like {param}");
break;
case QueryOperation.StartsWith:
collection.Params.Add(param, $"{val}%");
collection.SqlString.Append($"{field} like {param}");
break;
case QueryOperation.EndsWith:
collection.Params.Add(param, $"%{val}");
collection.SqlString.Append($"{field} like {param}");
break;
case QueryOperation.DataTimeLessThanOrEqualThenDay:
if (DateTime.TryParse(val.ToString(), out DateTime date))
{
val = date.AddDays(1).AddMilliseconds(-1);
}
else
{
break;
}
collection.Params.Add(param, val);
collection.SqlString.Append($"{field} <= {param}");
break;
case QueryOperation.In:
//collection.Params.Add(param, val);
collection.SqlString.Append($"{field} IN ({val.ToString().Trim(',')})");
break;
case QueryOperation.Any:
var group = JsonConvert.DeserializeObject <DynamicQueryParamGroup>((val ?? "").ToString());
if ((group == null) || (group.ChildGroups == null))
{
break;
}
ConverterQueryParamGroup(group, collection, mappings, item.Field);
break;
default:
throw new ArgumentException($"{nameof(QueryOperation)}无效");
}
if (i < list.Count - 1)
{
collection.SqlString.Append(relation == QueryRelation.Or ? " OR " : " AND ");
}
}
collection.SqlString.Append(")");
}
