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