public PreparedCommand PrepareDelete(DocumentMap mapping, Where where, CommandParameterValues parameters, DeleteOptions options = null) { options ??= DeleteOptions.Default; var actualTableName = options.TableName ?? mapping.TableName; var actualSchemaName = options.SchemaName ?? configuration.GetSchemaNameOrDefault(mapping); if (!mapping.RelatedDocumentsMappings.Any()) { return(new PreparedCommand($"DELETE FROM [{actualSchemaName}].[{actualTableName}]{options.Hint??""} {where.GenerateSql()}", parameters, RetriableOperation.Delete, mapping, options.CommandTimeout)); } var statement = new StringBuilder(); statement.AppendLine("DECLARE @Ids as TABLE (Id nvarchar(400))"); statement.AppendLine(); statement.AppendLine("INSERT INTO @Ids"); statement.AppendLine($"SELECT [{mapping.IdColumn.ColumnName}]"); statement.AppendLine($"FROM [{actualSchemaName}].[{actualTableName}] WITH (ROWLOCK)"); statement.AppendLine(where.GenerateSql()); statement.AppendLine(); statement.AppendLine($"DELETE FROM [{actualSchemaName}].[{actualTableName}] WITH (ROWLOCK) WHERE [{mapping.IdColumn.ColumnName}] in (SELECT Id FROM @Ids)"); foreach (var relMap in mapping.RelatedDocumentsMappings.Select(m => (tableName: m.TableName, schema: configuration.GetSchemaNameOrDefault(m), idColumnName: m.IdColumnName)).Distinct()) { statement.AppendLine($"DELETE FROM [{relMap.schema}].[{relMap.tableName}] WITH (ROWLOCK) WHERE [{relMap.idColumnName}] in (SELECT Id FROM @Ids)"); } return(new PreparedCommand(statement.ToString(), parameters, RetriableOperation.Delete, mapping, options.CommandTimeout)); }
public override IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults) { if (clauses.Count == 0) { throw new InvalidOperationException("Must have at least one 'ON' clause per join"); } intermediateJoins.Add(new Join(clauses.ToList(), joinSource, type)); clauses = new List <JoinClause>(); joinSource = source; type = joinType; var commandParameterValues = new CommandParameterValues(ParamValues, parameterValues); var combinedParameters = new Parameters(Params, parameters); var combinedParameterDefaults = new ParameterDefaults(ParamDefaults, parameterDefaults); ParamValues.Clear(); ParamValues.AddRange(commandParameterValues); Params.Clear(); Params.AddRange(combinedParameters); ParamDefaults.Clear(); ParamDefaults.AddRange(combinedParameterDefaults); return(this); }
public void ShouldBulkInsert() { using var writer = Store.BeginWriteTransaction(); var idMetaData = new SqlMetaData("Id", SqlDbType.NVarChar, 50); var nameMetadata = new SqlMetaData("Name", SqlDbType.NVarChar, 50); var referencesMetadata = new SqlMetaData("References", SqlDbType.NVarChar, 50); var records = new List <SqlDataRecord>(); for (var i = 0; i < 100000; i++) { var record = new SqlDataRecord(idMetaData, nameMetadata, referencesMetadata); record.SetString(0, "MyId-" + i); record.SetString(1, "Name for " + i); record.SetString(2, "Some-Other-Doc-" + i); records.Add(record); } var parameters = new CommandParameterValues(); parameters.AddTable("bulkInsertData", new TableValuedParameter("TestSchema.SomeTableInsertData", records)); writer.ExecuteNonQuery("insert into TestSchema.SomeTable ([Id], [Name], [References]) select [Id], [Name], [References] from @bulkInsertData", parameters); var count = writer.ExecuteScalar <int>("select count(*) from TestSchema.SomeTable"); count.Should().Be(100000); writer.Commit(); }
void AppendRelatedDocumentStatementsForInsert( StringBuilder sb, CommandParameterValues parameters, DocumentMap mapping, IReadOnlyList <object> documents) { var relatedDocumentData = GetRelatedDocumentTableData(mapping, documents); foreach (var data in relatedDocumentData.Where(g => g.Related.Length > 0)) { var relatedVariablePrefix = $"{data.TableName.ToLower()}_"; sb.AppendLine($"INSERT INTO [{data.SchemaName}].[{data.TableName}] ([{data.IdColumnName}], [{data.IdTableColumnName}], [{data.RelatedDocumentIdColumnName}], [{data.RelatedDocumentTableColumnName}]) VALUES"); var related = data.Related; for (var x = 0; x < related.Length; x++) { var parentIdVariable = related[x].parentIdVariable; var relatedDocumentId = related[x].relatedDocumentId; var relatedTableName = related[x].relatedTableName; var relatedVariableName = relatedVariablePrefix + x; parameters.Add(relatedVariableName, relatedDocumentId); if (x > 0) { sb.Append(","); } sb.AppendLine($"(@{parentIdVariable}, '{mapping.TableName}', @{relatedVariableName}, '{relatedTableName}')"); } } }
string AppendRelatedDocumentStatementsForUpdate( string statement, CommandParameterValues parameters, DocumentMap mapping, object document) { var relatedDocumentData = GetRelatedDocumentTableData(mapping, new[] { document }); if (relatedDocumentData.Count == 0) { return(statement); } var sb = new StringBuilder(); sb.AppendLine(statement); sb.AppendLine(); if (relatedDocumentData.Any(d => d.Related.Any())) { sb.AppendLine("DECLARE @references as TABLE (Reference nvarchar(400), ReferenceTable nvarchar(400))"); } foreach (var data in relatedDocumentData) { if (data.Related.Any()) { var relatedVariablePrefix = $"{data.TableName.ToLower()}_"; sb.AppendLine(); sb.AppendLine("DELETE FROM @references"); sb.AppendLine(); var valueBlocks = data.Related.Select((r, idx) => $"(@{relatedVariablePrefix}{idx}, '{r.relatedTableName}')"); sb.Append("INSERT INTO @references VALUES "); sb.AppendLine(string.Join(", ", valueBlocks)); sb.AppendLine(); sb.AppendLine($"DELETE FROM [{data.SchemaName}].[{data.TableName}] WHERE [{data.IdColumnName}] = @{IdVariableName}"); sb.AppendLine($" AND [{data.RelatedDocumentIdColumnName}] not in (SELECT Reference FROM @references)"); sb.AppendLine(); sb.AppendLine($"INSERT INTO [{data.SchemaName}].[{data.TableName}] ([{data.IdColumnName}], [{data.IdTableColumnName}], [{data.RelatedDocumentIdColumnName}], [{data.RelatedDocumentTableColumnName}])"); sb.AppendLine($"SELECT @{IdVariableName}, '{mapping.TableName}', Reference, ReferenceTable FROM @references t"); sb.AppendLine($"WHERE NOT EXISTS (SELECT null FROM [{data.SchemaName}].[{data.TableName}] r WHERE r.[{data.IdColumnName}] = @{IdVariableName} AND r.[{data.RelatedDocumentIdColumnName}] = t.Reference )"); for (var x = 0; x < data.Related.Length; x++) { parameters.Add(relatedVariablePrefix + x, data.Related[x].relatedDocumentId); } } else { sb.AppendLine($"DELETE FROM [{data.SchemaName}].[{data.TableName}] WHERE [{data.IdColumnName}] = @Id"); } } return(sb.ToString()); }
public PreparedCommand(string statement, CommandParameterValues parameterValues, RetriableOperation operation = RetriableOperation.None, DocumentMap mapping = null, TimeSpan?commandTimeout = null, CommandBehavior commandBehavior = CommandBehavior.Default) { Mapping = mapping; Statement = statement; ParameterValues = parameterValues; Operation = operation; CommandTimeout = commandTimeout; CommandBehavior = commandBehavior; }
public SubquerySourceBuilder(ISelect select, IReadQueryExecutor readQueryExecutor, ITableAliasGenerator tableAliasGenerator, IUniqueParameterNameGenerator uniqueParameterNameGenerator, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults) : base(readQueryExecutor, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults) { this.select = select; }
public VariableCasingFixture() { query = null; parameters = null; transaction = Substitute.For <IRelationalTransaction>(); transaction.WhenForAnyArgs(c => c.Stream <object>("", Arg.Any <CommandParameterValues>())) .Do(c => { query = c.Arg <string>(); parameters = c.Arg <CommandParameterValues>(); }); }
public IEnumerable <TResult> Stream <TResult>(string query, CommandParameterValues args, Func <IProjectionMapper, TResult> projectionMapper, TimeSpan?commandTimeout = null) { var command = new PreparedCommand(query, args, RetriableOperation.Select, commandBehavior: CommandBehavior.Default, commandTimeout: commandTimeout); using var reader = ExecuteReader(command); var mapper = new ProjectionMapper(command, reader, configuration.ReaderStrategies); while (reader.Read()) { yield return(projectionMapper(mapper)); } }
public TableSourceQueryBuilder(string tableOrViewName, string schemaName, IReadTransaction readQueryExecutor, ITableAliasGenerator tableAliasGenerator, IUniqueParameterNameGenerator uniqueParameterNameGenerator, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults) : base(readQueryExecutor, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults) { this.schemaName = schemaName; this.tableOrViewName = tableOrViewName; }
IDeleteQueryBuilder <IId> CreateQueryBuilder() { return(new DeleteQueryBuilder <IId>( new UniqueParameterNameGenerator(), (_, q, p, __) => { query = q.GenerateSql(); parameters = p; }, Enumerable.Empty <IWhereClause>(), new CommandParameterValues() )); }
public async IAsyncEnumerable <TResult> StreamAsync <TResult>(string query, CommandParameterValues args, Func <IProjectionMapper, TResult> projectionMapper, TimeSpan?commandTimeout = null, [EnumeratorCancellation] CancellationToken cancellationToken = default) { var command = new PreparedCommand(query, args, RetriableOperation.Select, commandBehavior: CommandBehavior.Default, commandTimeout: commandTimeout); await using var reader = await ExecuteReaderAsync(command, cancellationToken); var mapper = new ProjectionMapper(command, reader, configuration.ReaderStrategies); while (await reader.ReadAsync(cancellationToken)) { yield return(projectionMapper(mapper)); } }
public DeleteQueryBuilder( IUniqueParameterNameGenerator uniqueParameterNameGenerator, DataModificationQueryBuilder queryBuilder, IWriteQueryExecutor queryExecutor, IEnumerable <IWhereClause> whereClauses = null, CommandParameterValues parameterValues = null) { this.uniqueParameterNameGenerator = uniqueParameterNameGenerator; this.queryBuilder = queryBuilder; this.queryExecutor = queryExecutor; this.whereClauses = whereClauses ?? new List <IWhereClause>(); this.parameterValues = parameterValues ?? new CommandParameterValues(); }
public void ShouldPadInParameters(int parameterCount, int expectedPaddingTo) { var parameters = new CommandParameterValues(); parameters.Add("ids", Enumerable.Range(0, parameterCount).Select(i => "A")); var command = new SqlCommand($"SELECT * FROM [Table] WHERE [Id] IN @ids"); parameters.ContributeTo(command, new TypeHandlerRegistry()); var parameterListString = string.Join(", ", Enumerable.Range(0, expectedPaddingTo).Select(i => "@ids_" + (i + 1))); command.CommandText.Should().Be($"SELECT * FROM [Table] WHERE [Id] IN ({parameterListString})"); }
protected SourceQueryBuilder(IReadQueryExecutor readQueryExecutor, ITableAliasGenerator tableAliasGenerator, IUniqueParameterNameGenerator uniqueParameterNameGenerator, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults) { ReadQueryExecutor = readQueryExecutor; TableAliasGenerator = tableAliasGenerator; UniqueParameterNameGenerator = uniqueParameterNameGenerator; ParamValues = parameterValues; Params = parameters; ParamDefaults = parameterDefaults; }
public void ShouldGenerateDeleteWithUnaryWhereClause() { string actual = null; CommandParameterValues values = null; transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v)); CreateQueryBuilder <IDocument>("Orders") .Where("Price", UnarySqlOperand.GreaterThan, 5) .Delete(); actual.Should().Be(@"DELETE FROM dbo.[Orders] WHERE ([Price] > @price_0)"); values["price_0"].Should().Be(5); }
public void ShouldReplaceParametersAcrossLineBreaks() { var parameters = new CommandParameterValues(); parameters.Add("someId", new[] { "id1", "id2" }); parameters.Add("someIdentifier", "value"); var command = new SqlCommand($"SELECT * FROM [Table]{Environment.NewLine}WHERE [Id] IN @someId{Environment.NewLine}AND [OtherId] = @someIdentifier"); parameters.ContributeTo(command, new TypeHandlerRegistry()); command.CommandText.Should().Be($"SELECT * FROM [Table]{Environment.NewLine}WHERE [Id] IN (@someId_1, @someId_2){Environment.NewLine}AND [OtherId] = @someIdentifier"); command.Parameters["someId_1"].Value.Should().Be("id1"); command.Parameters["someId_2"].Value.Should().Be("id2"); command.Parameters["someIdentifier"].Value.Should().Be("value"); }
public void ShouldReplaceParametersWithExactMatchSpecialCharacters(string paramName) { var parameters = new CommandParameterValues(); parameters.Add("someId", new[] { "id1", "id2" }); parameters.Add(paramName, "value"); var command = new SqlCommand($"SELECT * FROM [Table] WHERE [Id] IN @someId AND [OtherId] = @{paramName}"); parameters.ContributeTo(command, new TypeHandlerRegistry()); command.CommandText.Should().Be($"SELECT * FROM [Table] WHERE [Id] IN (@someId_1, @someId_2) AND [OtherId] = @{paramName}"); command.Parameters["someId_1"].Value.Should().Be("id1"); command.Parameters["someId_2"].Value.Should().Be("id2"); command.Parameters[paramName].Value.Should().Be("value"); }
public void ShouldReplaceParametersWithExactMatchEndOfQuery() { var parameters = new CommandParameterValues(); parameters.Add("someId", "value"); parameters.Add("someIdentifier", new[] { "id1", "id2" }); var command = new SqlCommand("SELECT * FROM [Table] WHERE [Id] = @someId AND [OtherId] IN @someIdentifier"); parameters.ContributeTo(command, new TypeHandlerRegistry()); command.CommandText.Should().Be("SELECT * FROM [Table] WHERE [Id] = @someId AND [OtherId] IN (@someIdentifier_1, @someIdentifier_2)"); command.Parameters["someId"].Value.Should().Be("value"); command.Parameters["someIdentifier_1"].Value.Should().Be("id1"); command.Parameters["someIdentifier_2"].Value.Should().Be("id2"); }
public void ShouldGenerateDeleteWithBinaryWhereClause() { string actual = null; CommandParameterValues values = null; transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v)); CreateQueryBuilder <IDocument>("Orders") .Where("Price", BinarySqlOperand.Between, 5, 10) .Delete(); actual.Should().Be(@"DELETE FROM dbo.[Orders] WHERE ([Price] BETWEEN @startvalue_0 AND @endvalue_1)"); values["startvalue_0"].Should().Be(5); values["endvalue_1"].Should().Be(10); }
public void ShouldUseMaxLengthsWhenAvailable() { var mapping = new Map(); var parameters = new CommandParameterValues(); parameters["FirstName"] = "Fred"; parameters["LastName"] = "Fred"; var command = new SqlCommand($"SELECT BLAH BLAH"); parameters.ContributeTo(command, new TypeHandlerRegistry(), ((IDocumentMap)mapping).Build()); command.Parameters["FirstName"].Size.Should().Be(128); command.Parameters["LastName"].Size.Should().Be(256); }
public QueryBuilder(TSelectBuilder selectBuilder, IReadQueryExecutor readQueryExecutor, ITableAliasGenerator tableAliasGenerator, IUniqueParameterNameGenerator uniqueParameterNameGenerator, CommandParameterValues paramValues, Parameters @params, ParameterDefaults paramDefaults) { this.selectBuilder = selectBuilder; this.readQueryExecutor = readQueryExecutor; this.tableAliasGenerator = tableAliasGenerator; this.uniqueParameterNameGenerator = uniqueParameterNameGenerator; this.paramValues = paramValues; this.@params = @params; this.paramDefaults = paramDefaults; }
public JoinSourceQueryBuilder(IAliasedSelectSource originalSource, JoinType joinType, IAliasedSelectSource nextJoin, IReadQueryExecutor readQueryExecutor, ITableAliasGenerator tableAliasGenerator, IUniqueParameterNameGenerator uniqueParameterNameGenerator, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults) : base(readQueryExecutor, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults) { this.originalSource = originalSource; clauses = new List <JoinClause>(); joinSource = nextJoin; type = joinType; }
protected static (IQueryBuilder <Foo> builder, (Parameters parameters, CommandParameterValues paramValues)) NewQueryBuilder(IUniqueParameterNameGenerator uniqueParameterNameGenerator = null) { var parameters = new Parameters(); var captures = new CommandParameterValues(); var builder = new QueryBuilder <Foo, TableSelectBuilder>( new TableSelectBuilder(new SimpleTableSource("Foo")), Substitute.For <IRelationalTransaction>(), new TableAliasGenerator(), uniqueParameterNameGenerator ?? CreateSubstituteParameterNameGenerator(), captures, parameters, new ParameterDefaults() ); return(builder, (parameters, captures)); }
int GetNextMaxValue() { using (var transaction = store.BeginWriteTransaction(IsolationLevel.Serializable)) { var parameters = new CommandParameterValues { { "collectionName", collectionName }, { "blockSize", blockSize } }; parameters.CommandType = CommandType.StoredProcedure; var result = transaction.ExecuteScalar <int>("GetNextKeyBlock", parameters); transaction.Commit(); return(result); } }
public void ShouldGenerateDeleteWithArrayWhereClause() { string actual = null; CommandParameterValues values = null; transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v)); CreateQueryBuilder <IDocument>("Orders") .Where("Price", ArraySqlOperand.In, new [] { 5, 10, 15 }) .Delete(); actual.Should().Be(@"DELETE FROM dbo.[Orders] WHERE ([Price] IN (@price0_0, @price1_1, @price2_2))"); values["price0_0"].Should().Be("5"); values["price1_1"].Should().Be("10"); values["price2_2"].Should().Be("15"); }
CommandParameterValues GetDocumentParameters(Func <DocumentMap, string> allocateId, object customAssignedId, IReadOnlyList <object> documents, DocumentMap mapping) { if (documents.Count == 1) { return(GetDocumentParameters(allocateId, customAssignedId, CustomIdAssignmentBehavior.ThrowIfIdAlreadySetToDifferentValue, documents[0], mapping, "")); } var parameters = new CommandParameterValues(); for (var x = 0; x < documents.Count; x++) { var instanceParameters = GetDocumentParameters(allocateId, customAssignedId, CustomIdAssignmentBehavior.IgnoreCustomIdIfIdAlreadySet, documents[x], mapping, $"{x}__"); parameters.AddRange(instanceParameters); } return(parameters); }
public DeleteQueryBuilderFixture() { query = null; parameters = null; mappings = Substitute.For <IDocumentMapRegistry>(); mappings.Resolve <object>().Returns(c => new EmptyMap()); mappings.Resolve(Arg.Any <Type>()).Returns(c => new EmptyMap()); queryExecutor = Substitute.For <IWriteQueryExecutor>(); queryExecutor.ExecuteNonQuery(Arg.Any <PreparedCommand>()).Returns(info => { query = info.Arg <PreparedCommand>().Statement; parameters = info.Arg <PreparedCommand>().ParameterValues; return(1); }); }
public void ShouldGenerateDeleteWithParameterisedArrayWhereClause() { string actual = null; CommandParameterValues values = null; transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v)); CreateQueryBuilder <IDocument>("Orders") .WhereParameterised("Price", ArraySqlOperand.In, new [] { new Parameter("LowerPrice"), new Parameter("UpperPrice") }) .ParameterValues(new object[] { 5, 10 }) .Delete(); actual.Should().Be(@"DELETE FROM dbo.[Orders] WHERE ([Price] IN (@lowerprice_0, @upperprice_1))"); values["lowerprice_0"].Should().Be(5); values["upperprice_1"].Should().Be(10); }
public void ShouldGenerateDeleteWithParameterisedBinaryWhereClause() { string actual = null; CommandParameterValues values = null; transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v)); CreateQueryBuilder <IDocument>("Orders") .WhereParameterised("Price", BinarySqlOperand.Between, new Parameter("LowerPrice"), new Parameter("UpperPrice")) .ParameterValues(5, 10) .Delete(); actual.Should().Be(@"DELETE FROM dbo.[Orders] WHERE ([Price] BETWEEN @lowerprice_0 AND @upperprice_1)"); values["lowerprice_0"].Should().Be(5); values["upperprice_1"].Should().Be(10); }