I faced an issue of a solution wich should work with ADO, Entity and Dapper, so a made [this lib][1]; it generates batches in form of:
```lang-csharp
IEnumerable<(string SqlQuery, IEnumerable<SqlParameter> SqlParameters)>
IEnumerable<(string SqlQuery, DynamicParameters DapperDynamicParameters)>
```
[this link][2] contains instructions. It's safe against SQL Injection, because the usage of parameters instead concatenation; you can set identity insert to ON too, if needed, by an optional parameter.
Usage with ADO.NET:
```lang-csharp
using MsSqlHelpers;
// ...
var mapper = new MapperBuilder<Person>()
.SetTableName("People")
.AddMapping(person => person.FirstName, columnName: "Name")
.AddMapping(person => person.LastName, columnName: "Surename")
.AddMapping(person => person.DateOfBirth, columnName: "Birthday")
.Build();
var people = new List<Person>()
{
new Person()
{
FirstName = "John",
LastName = "Lennon",
DateOfBirth = new DateTime(1940, 10, 9)
},
new Person()
{
FirstName = "Paul",
LastName = "McCartney",
DateOfBirth = new DateTime(1942, 6, 18)
},
};
var connectionString = "Server=SERVER_ADDRESS;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;";
var sqlQueriesAndParameters = new MsSqlQueryGenerator()
.GenerateParametrizedBulkInserts(mapper, people);
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
// Default batch size: 1000 rows or (2100-1) parameters per insert.
foreach (var (SqlQuery, SqlParameters) in sqlQueriesAndParameters)
{
using (SqlCommand sqlCommand = new SqlCommand(SqlQuery, sqlConnection))
{
sqlCommand.Parameters.AddRange(SqlParameters.ToArray());
sqlCommand.ExecuteNonQuery();
}
}
}
```
Usage with Dapper:
```lang-csharp
using MsSqlHelpers;
// ...
var mapper = new MapperBuilder<Person>()
.SetTableName("People")
.AddMapping(person => person.FirstName, columnName: "Name")
.AddMapping(person => person.LastName, columnName: "Surename")
.AddMapping(person => person.DateOfBirth, columnName: "Birthday")
.Build();
var people = new List<Person>()
{
new Person()
{
FirstName = "John",
LastName = "Lennon",
DateOfBirth = new DateTime(1940, 10, 9)
},
new Person()
{
FirstName = "Paul",
LastName = "McCartney",
DateOfBirth = new DateTime(1942, 6, 18)
},
};
var connectionString = "Server=SERVER_ADDRESS;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;";
var sqlQueriesAndDapperParameters = new MsSqlQueryGenerator()
.GenerateDapperParametrizedBulkInserts(mapper, people);
using (var sqlConnection = new SqlConnection(connectionString))
{
// Default batch size: 1000 rows or (2100-1) parameters per insert.
foreach (var (SqlQuery, DapperDynamicParameters) in sqlQueriesAndDapperParameters)
{
sqlConnection.Execute(SqlQuery, DapperDynamicParameters);
}
}
```
Usage with Entity Framework:
```lang-csharp
using MsSqlHelpers;
// ...
var mapper = new MapperBuilder<Person>()
.SetTableName("People")
.AddMapping(person => person.FirstName, columnName: "Name")
.AddMapping(person => person.LastName, columnName: "Surename")
.AddMapping(person => person.DateOfBirth, columnName: "Birthday")
.Build();
var people = new List<Person>()
{
new Person()
{
FirstName = "John",
LastName = "Lennon",
DateOfBirth = new DateTime(1940, 10, 9)
},
new Person()
{
FirstName = "Paul",
LastName = "McCartney",
DateOfBirth = new DateTime(1942, 6, 18)
},
};
var sqlQueriesAndParameters = new MsSqlQueryGenerator()
.GenerateParametrizedBulkInserts(mapper, people);
// Default batch size: 1000 rows or (2100-1) parameters per insert.
foreach (var (SqlQuery, SqlParameters) in sqlQueriesAndParameters)
{
_context.Database.ExecuteSqlRaw(SqlQuery, SqlParameters);
// Depracated but still works: _context.Database.ExecuteSqlCommand(SqlQuery, SqlParameters);
}
```
[1]:
[To see links please register here]
[2]:
[To see links please register here]
]
