Exemple #1
0
        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() + ";");
        }
Exemple #2
0
        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};");
        }
Exemple #3
0
        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}");
        }
Exemple #5
0
        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};");
        }
Exemple #6
0
        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());
        }
Exemple #7
0
        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));
        }
Exemple #8
0
        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);
        }
Exemple #9
0
        public virtual string GenerateCount <T>(object @where, out IList <QueryInfo> parameters)
        {
            var tableName = DotEntityDb.GetTableNameForType <T>();

            return(GenerateCount(tableName, where, out parameters));
        }