示例#1
0
        public static SqlCommand CreateSelectCommand
        (
            [NotNull] this SqlConnection connection,
            [NotNull] SqlFourPartName tableName,
            [NotNull] string name,
            [CanBeNull] IImmutableDictionary <SqlServerColumn, SoftString> columnMappings,
            [CanBeNull] IImmutableDictionary <string, object> @where)
        {
            where = where ?? ImmutableDictionary <string, object> .Empty;

            var sql = new StringBuilder();

            var table = tableName.Render(connection);

            sql.Append($"SELECT *").AppendLine();
            sql.Append($"FROM {table}").AppendLine();
            sql.Append(where.Aggregate(
                           $"WHERE [{columnMappings.MapOrDefault(SqlServerColumn.Name)}] = @{columnMappings.MapOrDefault(SqlServerColumn.Name)}",
                           (current, next) => $"{current} AND {connection.CreateIdentifier(next.Key)} = @{next.Key}")
                       );

            var command = connection.CreateCommand();

            command.CommandText = sql.ToString();

            // --- add parameters & values

            command.AddParameters(where.Add(columnMappings.MapOrDefault(SqlServerColumn.Name), name));

            return(command);
        }
示例#2
0
        ////public static SqlCommand CreateDeleteCommand(SqlConnection connection, IIdentifier id, IImmutableDictionary<string, object> where)
        ////{
        ////    /*

        ////    DELETE FROM [dbo].[Setting] WHERE [Name] LIKE 'baz%' AND [Environment] = 'boz'

        ////    */

        ////    var sql = new StringBuilder();

        ////    var dbProviderFactory = DbProviderFactories.GetFactory(connection);
        ////    using (var commandBuilder = dbProviderFactory.CreateCommandBuilder())
        ////    {
        ////        string Sanitize(string identifier) => commandBuilder.QuoteIdentifier(identifier);

        ////        var table = $"{Sanitize(_tableMetadata.SchemaName)}.{Sanitize(_tableMetadata.TableName)}";

        ////        sql.Append($"DELETE FROM {table}").AppendLine();
        ////        sql.Append(where.Keys.Aggregate(
        ////            $"WHERE ([{EntityProperty.Name}] = @{EntityProperty.Name} OR [{EntityProperty.Name}] LIKE @{EntityProperty.Name} + N'[[]%]')",
        ////            (result, next) => $"{result} AND {Sanitize(next)} = @{next} ")
        ////        );
        ////    }

        ////    var command = connection.CreateCommand();
        ////    command.CommandType = CommandType.Text;
        ////    command.CommandText = sql.ToString();

        ////    // --- add parameters & values

        ////    (command, _tableMetadata).AddParameter(
        ////        ImmutableDictionary<string, object>.Empty
        ////            .Add(EntityProperty.Name, id.ToString())
        ////            .AddRange(where)
        ////    );

        ////    return command;
        ////}

        public static SqlCommand CreateUpdateCommand
        (
            [NotNull] this SqlConnection connection,
            [NotNull] SqlFourPartName tableName,
            [NotNull] string name,
            [CanBeNull] IImmutableDictionary <SqlServerColumn, SoftString> columnMappings,
            [CanBeNull] IImmutableDictionary <string, object> @where,
            [CanBeNull] object value)
        {
            where = where ?? ImmutableDictionary <string, object> .Empty;

            /*
             *
             * UPDATE [Setting]
             *      SET [Value] = 'Hallo update!'
             *      WHERE [Name]='baz' AND [Environment] = 'boz'
             * IF @@ROWCOUNT = 0
             *      INSERT INTO [Setting]([Name], [Value], [Environment])
             *      VALUES ('baz', 'Hallo insert!', 'boz')
             *
             */

            var sql = new StringBuilder();

            var table = tableName.Render(connection);

            sql.Append($"UPDATE {table}").AppendLine();
            sql.Append($"SET [{columnMappings.MapOrDefault(SqlServerColumn.Value)}] = @{columnMappings.MapOrDefault(SqlServerColumn.Value)}").AppendLine();

            sql.Append(where.Aggregate(
                           $"WHERE [{columnMappings.MapOrDefault(SqlServerColumn.Name)}] = @{columnMappings.MapOrDefault(SqlServerColumn.Name)}",
                           (result, next) => $"{result} AND {connection.CreateIdentifier(next.Key)} = @{next.Key} ")
                       ).AppendLine();

            sql.Append($"IF @@ROWCOUNT = 0").AppendLine();

            var columns = where.Keys.Select(key => connection.CreateIdentifier(key)).Aggregate(
                $"[{columnMappings.MapOrDefault(SqlServerColumn.Name)}], [{columnMappings.MapOrDefault(SqlServerColumn.Value)}]",
                (result, next) => $"{result}, {next}"
                );

            sql.Append($"INSERT INTO {table}({columns})").AppendLine();

            var parameterNames = where.Keys.Aggregate(
                $"@{columnMappings.MapOrDefault(SqlServerColumn.Name)}, @{columnMappings.MapOrDefault(SqlServerColumn.Value)}",
                (result, next) => $"{result}, @{next}"
                );

            sql.Append($"VALUES ({parameterNames})");

            var command = connection.CreateCommand();

            command.CommandType = CommandType.Text;
            command.CommandText = sql.ToString();

            // --- add parameters

            command.Parameters.AddWithValue($"@{columnMappings.MapOrDefault(SqlServerColumn.Name)}", name);
            command.Parameters.AddWithValue($"@{columnMappings.MapOrDefault(SqlServerColumn.Value)}", value);
            //command.Parameters.Add($"@{sqlServer.ColumnMapping.Value}", SqlDbType.NVarChar, 200).Value = setting.Value;

            command.AddParameters(where);

            return(command);
        }