public void ParametersFromDtoTests()
{
using var connector = CreateConnector();
const string item1 = "one";
const string item2 = "two";
connector.Command("create table Items (ItemId integer primary key, Name text not null);").Execute().Should().Be(0);
connector.Command("insert into Items (Name) values (@item1); insert into Items (Name) values (@item2);",
DbParameters.FromDto(new { item1, item2 })).Execute().Should().Be(2);
connector.Command("select Name from Items order by ItemId;").Query <string>().Should().Equal(item1, item2);
}
public void CommonAndInsertedParameters()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES (@a, @b, @c, @d)...",
DbParameters.FromDto(new { a = 1, b = 2 }), new[] { DbParameters.FromDto(new { c = 3, d = 4 }), DbParameters.FromDto(new { c = 5, d = 6 }) }).ToList();
commands.Count.Should().Be(1);
commands[0].Sql.Should().Be("VALUES (@a, @b, @c_0, @d_0), (@a, @b, @c_1, @d_1)");
commands[0].Parameters.ToDictionary().Should().Equal(
new Dictionary <string, object?>
{
{ "a", 1 },
{ "b", 2 },
{ "c_0", 3 },
{ "d_0", 4 },
{ "c_1", 5 },
{ "d_1", 6 },
});
}
public void MultipleInserts()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("INSERT INTO t VALUES (@t); INSERT INTO u VALUES (@u)...; INSERT INTO v VALUES (@v);",
DbParameters.Empty, new[] { DbParameters.FromDto(new { t = 1, u = 2, v = 3 }) }).ToList();
commands.Count.Should().Be(1);
commands[0].Sql.Should().Be("INSERT INTO t VALUES (@t); INSERT INTO u VALUES (@u_0); INSERT INTO v VALUES (@v);");
commands[0].Parameters.ToDictionary().Should().Equal(new Dictionary <string, object?> {
{ "u_0", 2 }
});
}
public void InsertNotRequired()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)...",
DbParameters.Empty, new[] { DbParameters.FromDto(new { foo = 1 }) }).ToList();
commands.Count.Should().Be(1);
commands[0].Sql.Should().Be("VALUES (@foo_0)");
commands[0].Parameters.ToDictionary().Should().Equal(new Dictionary <string, object?> {
{ "foo_0", 1 }
});
}
public void NegativeBatchSize_Throws()
{
Invoking(() => BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)...",
DbParameters.Empty, new[] { DbParameters.FromDto(new { foo = 1 }) }, new BulkInsertSettings {
MaxRowsPerBatch = -1
}).ToList())
.Should().Throw <ArgumentException>();
}
public void MultipleValues_Throws()
{
Invoking(() => BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)... VALUES (@foo)...", DbParameters.Empty, new[] { DbParameters.FromDto(new { foo = 1 }) }).ToList())
.Should().Throw <ArgumentException>();
}
public void CaseInsensitiveNames()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("values (@foo, @Bar, @BAZ, @bam)...",
DbParameters.Empty, new[] { DbParameters.FromDto(new { Foo = 1, BAR = 2, baz = 3 }) }).ToList();
commands.Count.Should().Be(1);
commands[0].Sql.Should().Be("values (@foo_0, @Bar_0, @BAZ_0, @bam)");
commands[0].Parameters.ToDictionary().Should().Equal(new Dictionary <string, object?> {
{ "Foo_0", 1 }, { "BAR_0", 2 }, { "baz_0", 3 }
});
}
public void EmptySql_Throws()
{
Invoking(() => BulkInsertUtility.GetBulkInsertCommands("", DbParameters.Empty, new[] { DbParameters.FromDto(new { foo = 1 }) }).ToList())
.Should().Throw <ArgumentException>();
}
public void EightRowsInThreeBatches(int?maxRecordsPerBatch, int?maxParametersPerBatch)
{
var settings = new BulkInsertSettings
{
MaxRowsPerBatch = maxRecordsPerBatch,
MaxParametersPerBatch = maxParametersPerBatch,
};
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES(@foo,@bar)...",
DbParameters.Empty, Enumerable.Range(0, 8).Select(x => DbParameters.FromDto(new { foo = x, bar = x * 2 })), settings).ToList();
commands.Count.Should().Be(3);
commands[0].Sql.Should().Be("VALUES(@foo_0,@bar_0), (@foo_1,@bar_1), (@foo_2,@bar_2)");
commands[0].Parameters.ToDictionary().Should().Equal(new Dictionary <string, object?> {
{ "foo_0", 0 }, { "bar_0", 0 }, { "foo_1", 1 }, { "bar_1", 2 }, { "foo_2", 2 }, { "bar_2", 4 }
});
commands[1].Sql.Should().Be("VALUES(@foo_0,@bar_0), (@foo_1,@bar_1), (@foo_2,@bar_2)");
commands[1].Parameters.ToDictionary().Should().Equal(new Dictionary <string, object?> {
{ "foo_0", 3 }, { "bar_0", 6 }, { "foo_1", 4 }, { "bar_1", 8 }, { "foo_2", 5 }, { "bar_2", 10 }
});
commands[2].Sql.Should().Be("VALUES(@foo_0,@bar_0), (@foo_1,@bar_1)");
commands[2].Parameters.ToDictionary().Should().Equal(new Dictionary <string, object?> {
{ "foo_0", 6 }, { "bar_0", 12 }, { "foo_1", 7 }, { "bar_1", 14 }
});
}