/// <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); }