public virtual string Query(string query, object inParameters, out IList <QueryInfo> parameters)
{
var columnValueMap = QueryParserUtilities.ParseObjectKeyValues(inParameters);
parameters = ToQueryInfos(columnValueMap);
return(query);
}
public virtual string GenerateUpdate <T>(object item, Expression <Func <T, bool> > where, out IList <QueryInfo> queryParameters) where T : class
{
var tableName = DotEntityDb.GetTableNameForType <T>();
var builder = new StringBuilder();
// convert the query parms into a SQL string and dynamic property object
builder.Append("UPDATE ");
builder.Append(tableName.ToEnclosed());
builder.Append(" SET ");
Dictionary <string, object> updateValueMap = QueryParserUtilities.ParseObjectKeyValues(item);
var updateString = string.Join(",", updateValueMap.Select(x => $"{x.Key.ToEnclosed()} = @{x.Key}"));
builder.Append(updateString);
var parser = new ExpressionTreeParser();
var whereString = parser.GetWhereString(where);
queryParameters = parser.QueryInfoList;
if (!string.IsNullOrEmpty(whereString))
{
//update where string to handle common parameters
var commonKeys = updateValueMap.Keys.Intersect(queryParameters.Select(x => x.PropertyName));
whereString = commonKeys.Aggregate(whereString, (current, ck) => current.Replace($"@{ck}", $"@{ck}2"));
}
queryParameters = MergeParameters(ToQueryInfos(updateValueMap), queryParameters);
if (!string.IsNullOrEmpty(whereString))
{
builder.Append(" WHERE " + whereString);
}
return(builder.ToString().Trim() + ";");
}
public virtual string GenerateCount(string tableName, object @where, out IList <QueryInfo> parameters)
{
Dictionary <string, object> whereMap = QueryParserUtilities.ParseObjectKeyValues(where);
var whereString = string.Join(" AND ", whereMap.Select(x => $"{x.Key} = @{x.Key}"));
parameters = ToQueryInfos(whereMap);
return($"SELECT COUNT(*) FROM {tableName.ToEnclosed()} WHERE {whereString};");
}
public virtual string GenerateInsert(string tableName, object entity, out IList <QueryInfo> parameters)
{
Dictionary <string, object> columnValueMap = QueryParserUtilities.ParseObjectKeyValues(entity, exclude: "Id");
var insertColumns = columnValueMap.Keys.ToArray();
var joinInsertString = string.Join(",", insertColumns.Select(x => x.ToEnclosed()));
var joinValueString = "@" + string.Join(",@", insertColumns);;
parameters = ToQueryInfos(columnValueMap);
return($"INSERT INTO {tableName.ToEnclosed()} ({joinInsertString}) OUTPUT inserted.Id VALUES ({joinValueString});");
}
public virtual string GenerateUpdate(string tableName, object entity, object where, out IList <QueryInfo> queryParameters, params string[] exclude)
{
Dictionary <string, object> updateValueMap = QueryParserUtilities.ParseObjectKeyValues(entity, exclude);
Dictionary <string, object> whereMap = QueryParserUtilities.ParseObjectKeyValues(where);
queryParameters = ToQueryInfos(updateValueMap, whereMap);
var updateString = string.Join(",", updateValueMap.Select(x => $"{x.Key.ToEnclosed()} = @{x.Key}"));
//get the common keys
var commonKeys = updateValueMap.Keys.Intersect(whereMap.Keys);
var whereString = string.Join(" AND ", whereMap.Select(x =>
{
var prefix = commonKeys.Contains(x.Key) ? "2" : "";
return($"{x.Key.ToEnclosed()} = @{x.Key}{prefix}");
}));
return($"UPDATE {tableName.ToEnclosed()} SET {updateString} WHERE {whereString};");
}