/// <summary> /// 生成Select查询SQL /// </summary> /// <param name="tableName"></param> /// <param name="parameters"></param> /// <param name="countSql">查询总数的SQL,只需要传入新建的StringBuilder对象即可</param> public string BuildSelectSQL(string tableName, string parameters, StringBuilder countSql = null, Pagination pagination = null) { if (string.IsNullOrEmpty(parameters)) { return("SELECT * FROM " + tableName); } var condition = string.Empty; var jToken = JObject.Parse(parameters); var param = ParameterParser.GetOperationParameter(jToken); var selectSQL = GetSelectSQL(param, tableName); var hasGroupBy = false; if (param.WhereParameter != null) { condition += " WHERE " + param.WhereParameter; } if (param.GroupByParameter != null) { hasGroupBy = true; condition += " GROUP BY " + string.Join(",", param.GroupByParameter.Select(e => "`" + e + "`")); if (param.HavingParameter != null) { condition += " HAVING " + param.HavingParameter; } } var countStr = condition; if (param.OrderParameter != null) { condition += " " + param.OrderParameter; } if (param.PaginationParameter != null) { condition += " " + param.PaginationParameter; if (pagination != null) { pagination.index = param.PaginationParameter.Index; pagination.size = param.PaginationParameter.Size; } if (countSql != null) { if (hasGroupBy) { countSql.Clear().Append("SELECT COUNT(*) FROM (").Append(selectSQL).Append(countStr).Append(") AS TMPTABLE;"); } else { countSql.Clear().Append("SELECT COUNT(*) FROM ").Append(tableName).Append(countStr); } } } return(selectSQL + condition); }
/// <summary> /// 生成Update查询SQL /// </summary> public string BuildUpdateSQL(string tableName, string where, string parameters, out object parsedData) { var condition = string.Empty; if (!string.IsNullOrWhiteSpace(where)) { var jToken = JObject.Parse(where); var param = ParameterParser.GetOperationParameter(jToken); if (param.WhereParameter != null) { condition += " WHERE " + param.WhereParameter; } } var obj = JsonConvert.DeserializeObject(parameters) as JObject; if (obj == null) { throw new ParameterParseException("Unable to deserialize the parameters."); } var data = new ExpandoObject() as IDictionary <string, Object>; foreach (var item in obj) { data.Add(item.Key, (item.Value as JValue).Value); } var dynamicParameter = new DynamicParameters(data); List <string> paramNames = GetParamNames(dynamicParameter); var b = new StringBuilder(); b.Append("UPDATE `").Append(tableName).Append("` SET "); b.Append(string.Join(",", paramNames.Select(p => "`" + p + "`= @" + p))); b.Append(condition); parsedData = data; return(b.ToString()); }
/// <summary> /// 生成Delete查询SQL /// </summary> public string BuildDeleteSQL(string tableName, string parameters) { if (string.IsNullOrEmpty(parameters)) { return("DELETE FROM " + tableName); } var condition = string.Empty; var jToken = JObject.Parse(parameters); var param = ParameterParser.GetOperationParameter(jToken); if (param.WhereParameter != null) { condition += " WHERE " + param.WhereParameter; } var b = new StringBuilder(); b.Append("DELETE FROM ").Append(tableName).Append(condition); return(b.ToString()); }