Ejemplo n.º 1
0
        /// <summary>
        /// get Update Sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static (string, List <object>) GetSqlUpdate <T>(IQueryable <T> query, DbContext context, Type type, Expression <Func <T, T> > expression) where T : class
        {
            (string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: true);

            var createUpdateBodyData = new BatchUpdateCreateBodyData(sql, context, innerParameters, query, type, tableAlias, expression);

            CreateUpdateBody(createUpdateBodyData, expression.Body);

            var sqlParameters = ReloadSqlParameters(context, createUpdateBodyData.SqlParameters); // Sqlite requires SqliteParameters
            var sqlColumns    = (createUpdateBodyData.DatabaseType == DbServer.SQLServer)
                ? createUpdateBodyData.UpdateColumnsSql
                : createUpdateBodyData.UpdateColumnsSql.Replace($"[{tableAlias}].", "");

            var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} SET {sqlColumns} {sql}";

            if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
            {
                string tableAliasPrefix = "[" + tableAlias + "].";
                resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C SET {sqlColumns.Replace(tableAliasPrefix, "")}";
            }
            if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
            {
                resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
            }

            var databaseType = SqlAdaptersMapping.GetDatabaseType(context);

            if (databaseType == DbServer.PostgreSQL)
            {
                resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);

                var npgsqlParameters = new List <object>();
                foreach (var param in sqlParameters)
                {
                    var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);

                    string paramName    = npgsqlParam.ParameterName.Replace("@", "");
                    var    propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
                    if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?))
                    {
                        npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
                    }

                    npgsqlParameters.Add(npgsqlParam);
                }
                sqlParameters = npgsqlParameters;
            }

            return(resultQuery, sqlParameters);
        }
Ejemplo n.º 2
0
        // SELECT [a].[Column1], [a].[Column2], .../r/n
        // FROM [Table] AS [a]/r/n
        // WHERE [a].[Column] = FilterValue
        // --
        // UPDATE [a] SET [UpdateColumns] = N'updateValues'
        // FROM [Table] AS [a]
        // WHERE [a].[Columns] = FilterValues
        public static (string, List <object>) GetSqlUpdate(IQueryable query, DbContext context, Type type, object updateValues, List <string> updateColumns)
        {
            var(sql, tableAlias, tableAliasSufixAs, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: true);
            var sqlParameters = new List <object>(innerParameters);

            string sqlSET = GetSqlSetSegment(context, updateValues.GetType(), updateValues, updateColumns, sqlParameters);

            sqlParameters = ReloadSqlParameters(context, sqlParameters); // Sqlite requires SqliteParameters

            var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} {sqlSET}{sql}";

            if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
            {
                resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C {sqlSET}";
            }
            if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
            {
                resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
            }

            var databaseType = SqlAdaptersMapping.GetDatabaseType(context);

            if (databaseType == DbServer.PostgreSQL)
            {
                resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);

                var npgsqlParameters = new List <object>();
                foreach (var param in sqlParameters)
                {
                    var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);

                    string paramName    = npgsqlParam.ParameterName.Replace("@", "");
                    var    propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
                    if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?)) // for JsonDocument works without fix
                    {
                        npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
                    }

                    npgsqlParameters.Add(npgsqlParam);
                }
                sqlParameters = npgsqlParameters;
            }

            return(resultQuery, sqlParameters);
        }
Ejemplo n.º 3
0
        // In comment are Examples of how SqlQuery is changed for Sql Batch

        // SELECT [a].[Column1], [a].[Column2], .../r/n
        // FROM [Table] AS [a]/r/n
        // WHERE [a].[Column] = FilterValue
        // --
        // DELETE [a]
        // FROM [Table] AS [a]
        // WHERE [a].[Columns] = FilterValues
        public static (string, List <object>) GetSqlDelete(IQueryable query, DbContext context)
        {
            var(sql, tableAlias, _, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: false);

            innerParameters = ReloadSqlParameters(context, innerParameters.ToList()); // Sqlite requires SqliteParameters
            var databaseType = SqlAdaptersMapping.GetDatabaseType(context);

            string resultQuery;

            if (databaseType == DbServer.SQLServer)
            {
                tableAlias = $"[{tableAlias}]";
                int outerQueryOrderByIndex = -1;
                var useUpdateableCte       = false;
                var lastOrderByIndex       = sql.LastIndexOf(Environment.NewLine + $"ORDER BY ", StringComparison.OrdinalIgnoreCase);
                if (lastOrderByIndex > -1)
                {
                    var subQueryEnd = sql.LastIndexOf($") AS {tableAlias}" + Environment.NewLine, StringComparison.OrdinalIgnoreCase);
                    if (subQueryEnd == -1 || lastOrderByIndex > subQueryEnd)
                    {
                        outerQueryOrderByIndex = lastOrderByIndex;

                        if (topStatement.Length > 0)
                        {
                            useUpdateableCte = true;
                        }
                        else
                        {
                            int offSetIndex = sql.LastIndexOf(Environment.NewLine + "OFFSET ", StringComparison.OrdinalIgnoreCase);
                            if (offSetIndex > outerQueryOrderByIndex)
                            {
                                useUpdateableCte = true;
                            }
                        }
                    }
                }

                if (useUpdateableCte)
                {
                    var cte = "cte" + Guid.NewGuid().ToString().Substring(0, 8); // 8 chars of Guid as tableNameSuffix to avoid same name collision with other tables
                    resultQuery = $"{leadingComments}WITH [{cte}] AS (SELECT {topStatement}* {sql}) DELETE FROM [{cte}]";
                }
                else
                {
                    if (outerQueryOrderByIndex > -1)
                    {
                        // ORDER BY is not allowed without TOP or OFFSET.
                        sql = sql.Substring(0, outerQueryOrderByIndex);
                    }

                    resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
                }
            }
            else
            {
                resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
            }

            if (databaseType == DbServer.PostgreSQL)
            {
                resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery, isDelete: true);

                var npgsqlParameters = new List <object>();
                foreach (var param in innerParameters)
                {
                    npgsqlParameters.Add(new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value));
                }
                innerParameters = npgsqlParameters;
            }

            return(resultQuery, new List <object>(innerParameters));
        }