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 GenerateDelete <T>(Expression <Func <T, bool> > where, out IList <QueryInfo> parameters) where T : class
{
var tableName = DotEntityDb.GetTableNameForType <T>();
var parser = new ExpressionTreeParser();
var whereString = parser.GetWhereString(where).Trim();
parameters = parser.QueryInfoList;
return($"DELETE FROM {tableName.ToEnclosed()} WHERE {whereString};");
}
public virtual string GenerateUpdate <T>(T entity, out IList <QueryInfo> queryParameters) where T : class
{
var tableName = DotEntityDb.GetTableNameForType <T>();
var deserializer = DataDeserializer <T> .Instance;
var keyColumn = deserializer.GetKeyColumn();
var keyColumnValue = deserializer.GetPropertyAs <int>(entity, keyColumn);
dynamic data = new ExpandoObject();
var dataDictionary = (IDictionary <string, object>)data;
dataDictionary.Add(keyColumn, keyColumnValue);
return(GenerateUpdate(tableName, entity, dataDictionary, out queryParameters, keyColumn));
}
private static string GetAliasedPropertyName(MemberExpression memberExpression, Dictionary <string, string> aliases)
{
var propertyName = memberExpression.Member.Name;
if (aliases == null)
{
return(propertyName);
}
var tableName = DotEntityDb.GetTableNameForType(memberExpression.Expression.Type);
propertyName = memberExpression.Member.Name;
var prefix = tableName;
aliases?.TryGetValue(tableName, out prefix);
return($"{prefix}.{propertyName}");
}
public virtual string GenerateCount <T>(IList <Expression <Func <T, bool> > > @where, out IList <QueryInfo> parameters) where T : class
{
parameters = new List <QueryInfo>();
var tableName = DotEntityDb.GetTableNameForType <T>();
var whereString = "";
if (where != null)
{
var parser = new ExpressionTreeParser();
var whereStringBuilder = new List <string>();
foreach (var wh in where)
{
whereStringBuilder.Add(parser.GetWhereString(wh));
}
parameters = parser.QueryInfoList;
whereString = string.Join(" AND ", whereStringBuilder).Trim();
}
return($"SELECT COUNT(*) FROM {tableName.ToEnclosed()} WHERE {whereString};");
}
public virtual string GenerateBatchInsert <T>(T[] entities, out IList <QueryInfo> parameters) where T : class
{
Throw.IfEmptyBatch(entities.Length == 0);
var queryBuilder = new StringBuilder();
var tableName = DotEntityDb.GetTableNameForType <T>();
parameters = new List <QueryInfo>();
const string pattern = "@([a-zA-Z0-9_]+)";
var regEx = new Regex(pattern);
for (var i = 0; i < entities.Length; i++)
{
var e = entities[i];
var sql = GenerateInsert(tableName, e, out IList <QueryInfo> newParameters);
parameters = MergeParameters(parameters, newParameters);
if (i > 0)
{
sql = regEx.Replace(sql, "@${1}" + (i + 1));
}
queryBuilder.Append(sql + Environment.NewLine);
}
return(queryBuilder.ToString());
}
public virtual string GenerateInsert <T>(T entity, out IList <QueryInfo> parameters) where T : class
{
var tableName = DotEntityDb.GetTableNameForType <T>();
return(GenerateInsert(tableName, entity, out parameters));
}
public virtual string GenerateSelectWithTotalMatchingCount <T>(out IList <QueryInfo> parameters, List <Expression <Func <T, bool> > > @where = null, Dictionary <Expression <Func <T, object> >, RowOrder> orderBy = null,
int page = 1, int count = Int32.MaxValue) where T : class
{
parameters = new List <QueryInfo>();
var builder = new StringBuilder();
var tableName = DotEntityDb.GetTableNameForType <T>();
var whereString = "";
if (where != null)
{
var parser = new ExpressionTreeParser();
var whereStringBuilder = new List <string>();
foreach (var wh in where)
{
whereStringBuilder.Add(parser.GetWhereString(wh));
}
parameters = parser.QueryInfoList;
whereString = string.Join(" AND ", whereStringBuilder).Trim();
}
var orderByStringBuilder = new List <string>();
var orderByString = "";
if (orderBy != null)
{
var parser = new ExpressionTreeParser();
foreach (var ob in orderBy)
{
orderByStringBuilder.Add(parser.GetOrderByString(ob.Key) + (ob.Value == RowOrder.Descending ? " DESC" : ""));
}
orderByString = string.Join(", ", orderByStringBuilder).Trim(',');
}
var paginatedSelect = PaginateOrderByString(orderByString, page, count, out string newWhereString);
if (paginatedSelect != string.Empty)
{
paginatedSelect = "," + paginatedSelect;
orderByString = string.Empty;
}
// make the query now
builder.Append($"SELECT {QueryParserUtilities.GetSelectColumnString(new List<Type>() { typeof(T) })}{paginatedSelect} FROM ");
builder.Append(tableName.ToEnclosed());
if (!string.IsNullOrEmpty(whereString))
{
builder.Append(" WHERE " + whereString);
}
if (!string.IsNullOrEmpty(orderByString))
{
builder.Append(" ORDER BY " + orderByString);
}
var query = builder.ToString().Trim();
if (paginatedSelect != string.Empty)
{
//wrap everything
query = $"SELECT * FROM ({query}) AS __PAGINATEDRESULT__ WHERE {newWhereString};";
}
//and the count query
query = query + $"{Environment.NewLine}SELECT COUNT(*) FROM {tableName.ToEnclosed()}" + (string.IsNullOrEmpty(whereString)
? ""
: $" WHERE {whereString}") + ";";
return(query);
}
public virtual string GenerateCount <T>(object @where, out IList <QueryInfo> parameters)
{
var tableName = DotEntityDb.GetTableNameForType <T>();
return(GenerateCount(tableName, where, out parameters));
}