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