/// <summary>
/// Insert many rows
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="insert">INSERT command "insert into Bar values "</param>
/// <param name="values">sql values</param>
/// <param name="list">IList<T> objects</param>
/// <param name="maxItens">Maximun values per execution</param>
/// <param name="maxPacketSize">Max size of command in bytes</param>
/// <param name="commandTimeout">commandTimeout</param>
/// <param param name="transaction">transaction</param>
/// <returns>Numbers of rows affected</returns>
public static int InsertMany <T>(this IDbConnection connection, string insert, string values, IList <T> list, int maxItens = 1000, int maxPacketSize = 4194304, int?commandTimeout = null, IDbTransaction transaction = null)
{
if (string.IsNullOrWhiteSpace(insert))
{
throw new ArgumentNullException("insert can not be null or white space.");
}
if (string.IsNullOrWhiteSpace(values))
{
throw new ArgumentNullException("values can not be null or white space.");
}
if (list == null)
{
throw new ArgumentNullException("list can not be null.");
}
if (maxItens < 0)
{
throw new ArgumentException("maxItens can not be less than 0.");
}
var commands = InsertGenerator.GenerateMany(insert, values, list, maxItens, maxPacketSize);
var total = 0;
foreach (var command in commands)
{
total += connection.Execute(command, commandTimeout: commandTimeout, transaction: transaction);
}
return(total);
}
public void GeneratedFullCommand()
{
var insert = "Insert into table values";
var values = "(@Foo1, @Foo2, 'murilo', 10, 11.12, @Foo3, @Foo4, @Foo5, @Foo6, @Foo7)";
var commands = InsertGenerator.GenerateMany(insert, values, new List <Bar>
{
new Bar {
Foo1 = "uma string",
Foo2 = "outra string",
Foo3 = "mais uma string",
Foo4 = true,
Foo5 = null,
Foo6 = 15.1m,
Foo7 = 10.1d
},
new Bar {
Foo1 = "1",
Foo2 = "2",
Foo3 = "3",
Foo4 = false,
Foo5 = null,
Foo6 = 20,
Foo7 = 10.1d
},
}, 1000, 4194304);
Assert.AreEqual("Insert into table values ('uma string', 'outra string', 'murilo', 10, 11.12, 'mais uma string', 1, null, 15.1, 10.1), ('1', '2', 'murilo', 10, 11.12, '3', 0, null, 20, 10.1);", commands[0]);
}
public void GenerateMultipleInsertTyped()
{
var commands = InsertGenerator.GenerateMany(new List <Bar>
{
new Bar {
Foo1 = "uma string",
Foo2 = "outra string",
Foo3 = "mais uma string",
Foo4 = true,
Foo5 = null,
Foo6 = 15.1m
},
new Bar {
Foo1 = "1",
Foo2 = "2",
Foo3 = "3",
Foo4 = false,
Foo5 = null,
Foo6 = 20
},
}, 1000, 4194304);
var value = "INSERT INTO Bar (Foo1, Foo2, Foo3, Foo4, Foo5, Foo6, Foo7) VALUES ('uma string', 'outra string', 'mais uma string', 1, null, 15.1, null), ('1', '2', '3', 0, null, 20, null);";
Assert.AreEqual(value, commands[0]);
}
public void InvalidProperty()
{
var insert = "Insert into table values";
var values = "(@Foo1, @FooX)";
var commands = InsertGenerator.GenerateMany(insert, values, new List <Bar>
{
new Bar()
}, 1000, 4194304);
Assert.AreEqual("Insert into table values ('uma string', 'outra string', 'murilo', 10, 11.12, 'mais uma string', 1, null, 15.1, 10.1), ('1', '2', 'murilo', 10, 11.12, '3', 0, null, 20, 10.1);", commands[0]);
}
public void TestMaxPacketSize()
{
var barList = new List <Bar>();
for (int i = 0; i < 101; i++)
{
barList.Add(new Bar
{
Foo1 = $"uma string {i}",
Foo2 = "outra string",
Foo3 = "mais uma string",
Foo4 = true,
Foo5 = null,
Foo6 = 15.1m
});
}
var commands = InsertGenerator.GenerateMany(barList, 1000, 250);
Assert.AreEqual(51, commands.Count);
}
public void TestMaxItens()
{
var barList = new List <Bar>();
for (int i = 0; i < 101; i++)
{
barList.Add(new Bar
{
Foo1 = "uma string",
Foo2 = "outra string",
Foo3 = "mais uma string",
Foo4 = true,
Foo5 = null,
Foo6 = 15.1m
});
}
var commands = InsertGenerator.GenerateMany(barList, 10, 4194304);
Assert.AreEqual(11, commands.Count);
}