public void EmptySql_Throws()
{
Assert.Throws <ArgumentException>(() =>
{
BulkInsertUtility.GetBulkInsertCommands("", new[] { new { foo = 1 } });
});
}
public void NoEllipsis_Throws()
{
Assert.Throws <ArgumentException>(() =>
{
BulkInsertUtility.GetBulkInsertCommands("VALUE (@foo)..", new[] { new { foo = 1 } });
});
}
public void NegativeBatchSize_Throws()
{
Assert.Throws <ArgumentOutOfRangeException>(() =>
{
BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)...", new[] { new { foo = 1 } }, batchSize: -1);
});
}
public void NullSql_Throws()
{
Assert.Throws <ArgumentNullException>(() =>
{
BulkInsertUtility.GetBulkInsertCommands(null, new[] { new { foo = 1 } });
});
}
public void SubstringNames()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("values (@a, @aa, @aaa, @aaaa)...", new { a = 1, aaa = 3 }, new[] { new { aa = 2, aaaa = 4 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("values (@a, @aa_0, @aaa, @aaaa_0)");
}
public void WhitespaceEverywhere()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("\r\n\t VALUES\n\t \r(\t \r\n@foo \r\n\t)\r\n\t ...\t\r\n", new[] { new { foo = 1 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("\r\n\t VALUES\n\t \r(\t \r\n@foo_0 \r\n\t)\t\r\n");
}
public void CaseInsensitiveNames()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("values (@foo, @Bar, @BAZ, @bam)...", new[] { new { Foo = 1, BAR = 2, baz = 3 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("values (@foo_0, @Bar_0, @BAZ_0, @bam)");
}
public void CaseInsensitiveValues()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VaLueS(@foo)...", new[] { new { foo = 1 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("VaLueS(@foo_0)");
}
public void InsertNotRequired()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)...", new[] { new { foo = 1 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("VALUES (@foo_0)");
}
public void NullInsertParams_Throws()
{
Assert.Throws <ArgumentNullException>(() =>
{
BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)...", default(object[]));
});
}
public void MultipleValues_Throws()
{
Assert.Throws <ArgumentException>(() =>
{
BulkInsertUtility.GetBulkInsertCommands("VALUES (@foo)... VALUES (@foo)...", new[] { new { foo = 1 } });
});
}
public void MultipleInserts()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("INSERT INTO t VALUES (@t); INSERT INTO u VALUES (@u)...; INSERT INTO v VALUES (@v);",
new[] { new { t = 1, u = 2, v = 3 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("INSERT INTO t VALUES (@t); INSERT INTO u VALUES (@u_0); INSERT INTO v VALUES (@v);");
}
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 EightRowsInThreeBatches()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES(@foo)...", Enumerable.Range(0, 8).Select(x => new { foo = x }), batchSize: 3).ToList();
commands.Count.Should().Be(3);
commands[0].CommandText.Should().Be("VALUES(@foo_0),(@foo_1),(@foo_2)");
commands[1].CommandText.Should().Be("VALUES(@foo_0),(@foo_1),(@foo_2)");
commands[2].CommandText.Should().Be("VALUES(@foo_0),(@foo_1)");
((SqlMapper.IParameterLookup)commands[2].Parameters)["foo_1"].Should().Be(7);
}
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 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 MinimalInsert()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("INSERT INTO t (foo)VALUES(@foo)...;", new[] { new { foo = 1 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("INSERT INTO t (foo)VALUES(@foo_0);");
var parameters = (DynamicParameters)commands[0].Parameters;
parameters.ParameterNames.Single().Should().Be("foo_0");
((SqlMapper.IParameterLookup)parameters)["foo_0"].Should().Be(1);
}
public void NoParameterNameValidation()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES (@a, @b, @c, @d)...", new { e = 1, f = 2 }, new[] { new { g = 3, h = 4 }, new { g = 5, h = 6 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("VALUES (@a, @b, @c, @d),(@a, @b, @c, @d)");
var parameters = (SqlMapper.IParameterLookup)commands[0].Parameters;
parameters["e"].Should().Be(1);
parameters["f"].Should().Be(2);
parameters["g_0"].Should().Be(3);
parameters["h_0"].Should().Be(4);
parameters["g_1"].Should().Be(5);
parameters["h_1"].Should().Be(6);
}
public void ComplexValues()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES (@a + (@d * @c) -\r\n\t@d)...", new { a = 1, b = 2 }, new[] { new { c = 3, d = 4 }, new { c = 5, d = 6 } }).ToList();
commands.Count.Should().Be(1);
commands[0].CommandText.Should().Be("VALUES (@a + (@d_0 * @c_0) -\r\n\t@d_0),(@a + (@d_1 * @c_1) -\r\n\t@d_1)");
var parameters = (SqlMapper.IParameterLookup)commands[0].Parameters;
parameters["a"].Should().Be(1);
parameters["b"].Should().Be(2);
parameters["c_0"].Should().Be(3);
parameters["d_0"].Should().Be(4);
parameters["c_1"].Should().Be(5);
parameters["d_1"].Should().Be(6);
}
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 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 }
});
}
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 PunctuatedNames()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("values (@foo, @bar)...",
DbParameters.Empty, new[] { DbParameters.Create(("@foo", 1), ("@Bar", 2)) }).ToList();
public void EmptySql_Throws()
{
Invoking(() => BulkInsertUtility.GetBulkInsertCommands("", DbParameters.Empty, new[] { DbParameters.FromDto(new { foo = 1 }) }).ToList())
.Should().Throw <ArgumentException>();
}
public void NothingToInsert()
{
var commands = BulkInsertUtility.GetBulkInsertCommands("VALUES(@foo)...", new object[0]).ToList();
commands.Count.Should().Be(0);
}
