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