CRUD Operations on Multiple Objects
These scenarios demonstrate how to perform Create, Read, Update, and Delete operations on a collection of 100 objects.
If the ORM supports it, the operation should be performed with a single SQL statement.
Scenario Prototype
public interface IMultipleCrudScenario<TEmployeeSimple>
where TEmployeeSimple : class, IEmployeeSimple, new()
{
/// <summary>
/// Delete a collection of Employee rows.
/// </summary>
void DeleteBatch(IList<TEmployeeSimple> employees);
/// <summary>
/// Delete a collection of Employee rows by key.
/// </summary>
void DeleteBatchByKey(IList<int> employeeKeys);
/// <summary>
/// Gets a collection of Employee rows by their name. Assume the name is not unique.
/// </summary>
IList<TEmployeeSimple> FindByLastName(string lastName);
/// <summary>
/// Insert a collection of Employee rows.
/// </summary>
void InsertBatch(IList<TEmployeeSimple> employees);
/// <summary>
/// Insert a collection of Employee rows, returning the newly created keys.
/// </summary>
IList<int> InsertBatchReturnKeys(IList<TEmployeeSimple> employees);
/// <summary>
/// Insert a collection of Employee rows, returning the newly created rows.
/// </summary>
/// <remarks>This MAY return the original objects or new objects.</remarks>
IList<TEmployeeSimple> InsertBatchReturnRows(IList<TEmployeeSimple> employees);
/// <summary>
/// Insert a collection of Employee rows and update the original objects with the new keys.
/// </summary>
void InsertBatchWithRefresh(IList<TEmployeeSimple> employees);
/// <summary>
/// Update a collection of Employee rows.
/// </summary>
void UpdateBatch(IList<TEmployeeSimple> employees);
}
ADO.NET
public class MultipleCrudScenario : SqlServerScenarioBase, IMultipleCrudScenario<EmployeeSimple>
{
public MultipleCrudScenario(string connectionString) : base(connectionString)
{ }
public void DeleteBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var keyList = string.Join(", ", employees.Select(x => x.EmployeeKey));
var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
var keyList = string.Join(", ", employeeKeys);
var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
public IList<EmployeeSimple> FindByLastName(string lastName)
{
const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.EmployeeDetail e WHERE e.LastName = @LastName";
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@LastName", lastName);
var results = new List<EmployeeSimple>();
using (var reader = cmd.ExecuteReader())
while (reader.Read())
results.Add(new EmployeeSimple(reader));
return results;
}
}
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES ");
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
}
sql.AppendLine(";");
//No transaction is needed because a single SQL statement is used.
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql.ToString(), con))
{
for (var i = 0; i < employees.Count; i++)
{
cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
}
cmd.ExecuteNonQuery();
}
}
public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey
VALUES ");
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
}
sql.AppendLine(";");
//No transaction is needed because a single SQL statement is used.
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql.ToString(), con))
{
for (var i = 0; i < employees.Count; i++)
{
cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
}
var result = new List<int>();
using (var reader = cmd.ExecuteReader())
while (reader.Read())
result.Add(reader.GetInt32(0));
return result;
}
}
public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES ");
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
}
sql.AppendLine(";");
//No transaction is needed because a single SQL statement is used.
using (var con = OpenConnection())
using (var cmd = new SqlCommand(sql.ToString(), con))
{
for (var i = 0; i < employees.Count; i++)
{
cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
}
var result = new List<EmployeeSimple>();
using (var reader = cmd.ExecuteReader())
while (reader.Read())
result.Add(new EmployeeSimple(reader));
return result;
}
}
public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder();
//In order to ensure the right objects are refreshed, each object is inserted separately.
//If we returned them all at the same time, they might not come back in the same order.
for (var i = 0; i < employees.Count; i++)
{
sql.AppendLine($@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES (@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i});");
}
//A transaction is needed because this example uses multiple SQL statements.
using (var con = OpenConnection())
using (var trans = con.BeginTransaction())
{
using (var cmd = new SqlCommand(sql.ToString(), con, trans))
{
for (var i = 0; i < employees.Count; i++)
{
cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
}
using (var reader = cmd.ExecuteReader())
{
for (var i = 0; i < employees.Count; i++)
{
reader.Read();
employees[i].Refresh(reader);
reader.NextResult(); //each row is coming back as a separate result set
}
}
}
trans.Commit();
}
}
public void UpdateBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder();
for (var i = 0; i < employees.Count; i++)
{
sql.AppendLine($@"UPDATE HR.Employee
SET FirstName = @FirstName_{i},
MiddleName = @MiddleName_{i},
LastName = @LastName_{i},
Title = @Title_{i},
OfficePhone = @OfficePhone_{i},
CellPhone = @CellPhone_{i},
EmployeeClassificationKey = @EmployeeClassificationKey_{i}
WHERE EmployeeKey = @EmployeeKey_{i};");
}
//A transaction is needed because this example uses multiple SQL statements.
using (var con = OpenConnection())
using (var trans = con.BeginTransaction())
{
using (var cmd = new SqlCommand(sql.ToString(), con, trans))
{
for (var i = 0; i < employees.Count; i++)
{
cmd.Parameters.AddWithValue($"@EmployeeKey_{i}", employees[i].EmployeeKey);
cmd.Parameters.AddWithValue($"@FirstName_{i}", employees[i].FirstName);
cmd.Parameters.AddWithValue($"@MiddleName_{i}", (object?)employees[i].MiddleName ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@LastName_{i}", employees[i].LastName);
cmd.Parameters.AddWithValue($"@Title_{i}", (object?)employees[i].Title ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@OfficePhone_{i}", (object?)employees[i].OfficePhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@CellPhone_{i}", (object?)employees[i].CellPhone ?? DBNull.Value);
cmd.Parameters.AddWithValue($"@EmployeeClassificationKey_{i}", employees[i].EmployeeClassificationKey);
}
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
}
Chain
public class MultipleCrudScenario : IMultipleCrudScenario<EmployeeSimple>
{
readonly SqlServerDataSource m_DataSource;
public MultipleCrudScenario(SqlServerDataSource dataSource)
{
m_DataSource = dataSource;
}
public void DeleteBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
m_DataSource.DeleteByKeyList<EmployeeSimple>(employees.Select(x => x.EmployeeKey)).Execute();
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
m_DataSource.DeleteByKeyList<EmployeeSimple>(employeeKeys).Execute();
}
public IList<EmployeeSimple> FindByLastName(string lastName)
{
return m_DataSource.From<EmployeeSimple>(new { lastName }).ToCollection().Execute();
}
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).Execute();
}
public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
return m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).ToInt32List().Execute();
}
public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
return m_DataSource.InsertBatch((IReadOnlyList<EmployeeSimple>)employees).ToCollection<EmployeeSimple>().Execute();
}
public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
//Chain does not support updating multiple rows at one time from objects
//Use the WithRefresh() link to update the original object.
using (var trans = m_DataSource.BeginTransaction())
{
foreach (var item in employees)
trans.Insert(item).WithRefresh().Execute();
trans.Commit();
}
}
public void UpdateBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
//Chain does not support updating multiple rows at one time from objects
using (var trans = m_DataSource.BeginTransaction())
{
foreach (var item in employees)
trans.Update(item).Execute();
trans.Commit();
}
}
}
Dapper
public class MultipleCrudScenario : ScenarioBase, IMultipleCrudScenario<EmployeeSimple>
{
public MultipleCrudScenario(string connectionString) : base(connectionString)
{ }
virtual public void DeleteBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var keyList = string.Join(", ", employees.Select(x => x.EmployeeKey));
var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";
using (var con = OpenConnection())
con.Execute(sql);
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
var keyList = string.Join(", ", employeeKeys);
var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";
using (var con = OpenConnection())
con.Execute(sql);
}
public IList<EmployeeSimple> FindByLastName(string lastName)
{
const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.EmployeeDetail e WHERE e.LastName = @LastName";
using (var con = OpenConnection())
return con.Query<EmployeeSimple>(sql, new { lastName }).ToList();
}
virtual public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
VALUES ");
var parameters = new Dictionary<string, object?>();
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
parameters[$"@FirstName_{i}"] = employees[i].FirstName;
parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
parameters[$"@LastName_{i}"] = employees[i].LastName;
parameters[$"@Title_{i}"] = employees[i].Title;
parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
}
sql.AppendLine(";");
//No transaction is needed because a single SQL statement is used.
using (var con = OpenConnection())
con.Execute(sql.ToString(), parameters);
}
public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey
VALUES ");
var parameters = new Dictionary<string, object?>();
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
parameters[$"@FirstName_{i}"] = employees[i].FirstName;
parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
parameters[$"@LastName_{i}"] = employees[i].LastName;
parameters[$"@Title_{i}"] = employees[i].Title;
parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
}
sql.AppendLine(";");
//No transaction is needed because a single SQL statement is used.
using (var con = OpenConnection())
return con.Query<int>(sql.ToString(), parameters).ToList();
}
public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder(@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES ");
var parameters = new Dictionary<string, object?>();
for (var i = 0; i < employees.Count; i++)
{
if (i != 0)
sql.AppendLine(",");
sql.Append($"(@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i})");
parameters[$"@FirstName_{i}"] = employees[i].FirstName;
parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
parameters[$"@LastName_{i}"] = employees[i].LastName;
parameters[$"@Title_{i}"] = employees[i].Title;
parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
}
sql.AppendLine(";");
//No transaction is needed because a single SQL statement is used.
using (var con = OpenConnection())
return con.Query<EmployeeSimple>(sql.ToString(), parameters).ToList();
}
public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder();
//In order to ensure the right objects are refreshed, each object is inserted separately.
//If we returned them all at the same time, they might not come back in the same order.
var parameters = new Dictionary<string, object?>();
for (var i = 0; i < employees.Count; i++)
{
sql.AppendLine($@"INSERT INTO HR.Employee
(FirstName, MiddleName, LastName, Title, OfficePhone, CellPhone, EmployeeClassificationKey)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES (@FirstName_{i}, @MiddleName_{i}, @LastName_{i}, @Title_{i}, @OfficePhone_{i}, @CellPhone_{i}, @EmployeeClassificationKey_{i});");
parameters[$"@FirstName_{i}"] = employees[i].FirstName;
parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
parameters[$"@LastName_{i}"] = employees[i].LastName;
parameters[$"@Title_{i}"] = employees[i].Title;
parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
}
//A transaction is needed because this example uses multiple SQL statements.
using (var con = OpenConnection())
using (var trans = con.BeginTransaction())
{
var results = con.QueryMultiple(sql.ToString(), parameters, transaction: trans);
for (var i = 0; i < employees.Count; i++)
{
var temp = results.ReadSingle<EmployeeSimple>(); //each row is coming back as a separate result set
employees[i].Refresh(temp);
}
}
}
virtual public void UpdateBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var sql = new StringBuilder();
var parameters = new Dictionary<string, object?>();
for (var i = 0; i < employees.Count; i++)
{
sql.AppendLine($@"UPDATE HR.Employee
SET FirstName = @FirstName_{i},
MiddleName = @MiddleName_{i},
LastName = @LastName_{i},
Title = @Title_{i},
OfficePhone = @OfficePhone_{i},
CellPhone = @CellPhone_{i},
EmployeeClassificationKey = @EmployeeClassificationKey_{i}
WHERE EmployeeKey = @EmployeeKey_{i};");
parameters[$"@EmployeeKey_{i}"] = employees[i].EmployeeKey;
parameters[$"@FirstName_{i}"] = employees[i].FirstName;
parameters[$"@MiddleName_{i}"] = employees[i].MiddleName;
parameters[$"@LastName_{i}"] = employees[i].LastName;
parameters[$"@Title_{i}"] = employees[i].Title;
parameters[$"@OfficePhone_{i}"] = employees[i].OfficePhone;
parameters[$"@CellPhone_{i}"] = employees[i].CellPhone;
parameters[$"@EmployeeClassificationKey_{i}"] = employees[i].EmployeeClassificationKey;
}
//A transaction is needed because this example uses multiple SQL statements.
using (var con = OpenConnection())
using (var trans = con.BeginTransaction())
{
con.Execute(sql.ToString(), parameters, transaction: trans);
trans.Commit();
}
}
}
Info
The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.
DbConnector
public class MultipleCrudScenario : ScenarioBase, IMultipleCrudScenario<EmployeeSimple>
{
public MultipleCrudScenario(string connectionString) : base(connectionString)
{ }
public void DeleteBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
var keyList = string.Join(", ", employees.Select(x => x.EmployeeKey));
var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";
DbConnector.NonQuery(sql).Execute();
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
var keyList = string.Join(", ", employeeKeys);
var sql = $"DELETE FROM HR.Employee WHERE EmployeeKey IN ({keyList});";
DbConnector.NonQuery(sql).Execute();
}
public IList<EmployeeSimple> FindByLastName(string lastName)
{
const string sql = "SELECT e.EmployeeKey, e.FirstName, e.MiddleName, e.LastName, e.Title, e.OfficePhone, e.CellPhone, e.EmployeeClassificationKey FROM HR.EmployeeDetail e WHERE e.LastName = @lastName";
return DbConnector.ReadToList<EmployeeSimple>(sql, new { lastName }).Execute();
}
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || !employees.Any())
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
//Best approach for unlimited inserts since SQL server has parameter amount restrictions
//https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
DbConnector.Build<int?>(
sql: @$"INSERT INTO {EmployeeSimple.TableName}
(
CellPhone,
EmployeeClassificationKey,
FirstName,
LastName,
MiddleName,
OfficePhone,
Title
)
VALUES (
@{nameof(EmployeeSimple.CellPhone)},
@{nameof(EmployeeSimple.EmployeeClassificationKey)},
@{nameof(EmployeeSimple.FirstName)},
@{nameof(EmployeeSimple.LastName)},
@{nameof(EmployeeSimple.MiddleName)},
@{nameof(EmployeeSimple.OfficePhone)},
@{nameof(EmployeeSimple.Title)}
)",
param: employees.First(),
onExecute: (int? result, IDbExecutionModel em) =>
{
//Set the command
DbCommand command = em.Command;
//Execute first row.
em.NumberOfRowsAffected = command.ExecuteNonQuery();
//Set and execute remaining rows.
foreach (var emp in employees.Skip(1))
{
command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;
em.NumberOfRowsAffected += command.ExecuteNonQuery();
}
return em.NumberOfRowsAffected;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
.Execute();
}
public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
string sql = @$"INSERT INTO {EmployeeSimple.TableName}
(
CellPhone,
EmployeeClassificationKey,
FirstName,
LastName,
MiddleName,
OfficePhone,
Title
)
OUTPUT Inserted.EmployeeKey
VALUES (
@{nameof(EmployeeSimple.CellPhone)},
@{nameof(EmployeeSimple.EmployeeClassificationKey)},
@{nameof(EmployeeSimple.FirstName)},
@{nameof(EmployeeSimple.LastName)},
@{nameof(EmployeeSimple.MiddleName)},
@{nameof(EmployeeSimple.OfficePhone)},
@{nameof(EmployeeSimple.Title)}
)";
//Best approach since SQL server has parameter amount restrictions
//https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
return DbConnector.ReadTo<List<int>>(
onInit: (cmds) =>
{
foreach (var emp in employees)
{
cmds.Enqueue(cmd =>
{
cmd.CommandText = sql;
cmd.CommandBehavior = CommandBehavior.SingleResult;
cmd.Parameters.AddFor(emp);
});
}
},
onLoad: (List<int> data, IDbExecutionModel em, DbDataReader odr) =>
{
if (data == null)
data = new List<int>();
data.Add(odr.SingleOrDefault<int>(em.Token, em.JobCommand));
return data;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
.Execute();
}
public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
string sql = @$"INSERT INTO {EmployeeSimple.TableName}
(
CellPhone,
EmployeeClassificationKey,
FirstName,
LastName,
MiddleName,
OfficePhone,
Title
)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES (
@{nameof(EmployeeSimple.CellPhone)},
@{nameof(EmployeeSimple.EmployeeClassificationKey)},
@{nameof(EmployeeSimple.FirstName)},
@{nameof(EmployeeSimple.LastName)},
@{nameof(EmployeeSimple.MiddleName)},
@{nameof(EmployeeSimple.OfficePhone)},
@{nameof(EmployeeSimple.Title)}
)";
//Best approach since SQL server has parameter amount restrictions
//https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
return DbConnector.ReadTo<List<EmployeeSimple>>(
onInit: (cmds) =>
{
foreach (var emp in employees)
{
cmds.Enqueue(cmd =>
{
cmd.CommandText = sql;
cmd.CommandBehavior = CommandBehavior.SingleResult;
cmd.Parameters.AddFor(emp);
});
}
},
onLoad: (List<EmployeeSimple> data, IDbExecutionModel em, DbDataReader odr) =>
{
if (data == null)
data = new List<EmployeeSimple>();
data.Add(odr.SingleOrDefault<EmployeeSimple>(em.Token, em.JobCommand));
return data;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
.Execute();
}
public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
string sql = @$"INSERT INTO {EmployeeSimple.TableName}
(
CellPhone,
EmployeeClassificationKey,
FirstName,
LastName,
MiddleName,
OfficePhone,
Title
)
OUTPUT Inserted.EmployeeKey, Inserted.FirstName, Inserted.MiddleName, Inserted.LastName, Inserted.Title, Inserted.OfficePhone, Inserted.CellPhone, Inserted.EmployeeClassificationKey
VALUES (
@{nameof(EmployeeSimple.CellPhone)},
@{nameof(EmployeeSimple.EmployeeClassificationKey)},
@{nameof(EmployeeSimple.FirstName)},
@{nameof(EmployeeSimple.LastName)},
@{nameof(EmployeeSimple.MiddleName)},
@{nameof(EmployeeSimple.OfficePhone)},
@{nameof(EmployeeSimple.Title)}
)";
//Best approach since SQL server has parameter amount restrictions
//https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
DbConnector.ReadTo<bool>(
onInit: (cmds) =>
{
foreach (var emp in employees)
{
cmds.Enqueue(cmd =>
{
cmd.CommandText = sql;
cmd.CommandBehavior = CommandBehavior.SingleResult;
cmd.Parameters.AddFor(emp);
});
}
},
onLoad: (bool result, IDbExecutionModel em, DbDataReader odr) =>
{
employees[em.Index].Refresh(odr.SingleOrDefault<EmployeeSimple>(em.Token, em.JobCommand));
return true;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
.Execute();
}
public void UpdateBatch(IList<EmployeeSimple> employees)
{
if (employees == null || !employees.Any())
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
//Best approach since SQL server has parameter amount restrictions
//https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
DbConnector.Build<int?>(
sql: @$"UPDATE {EmployeeSimple.TableName}
SET
CellPhone = @{nameof(EmployeeSimple.CellPhone)},
EmployeeClassificationKey = @{nameof(EmployeeSimple.EmployeeClassificationKey)},
FirstName = @{nameof(EmployeeSimple.FirstName)},
LastName = @{nameof(EmployeeSimple.LastName)},
MiddleName = @{nameof(EmployeeSimple.MiddleName)},
OfficePhone = @{nameof(EmployeeSimple.OfficePhone)},
Title = @{nameof(EmployeeSimple.Title)}
WHERE EmployeeKey = @{nameof(EmployeeSimple.EmployeeKey)}",
param: employees.First(),
onExecute: (int? result, IDbExecutionModel em) =>
{
//Set the command
DbCommand command = em.Command;
//Execute first row.
em.NumberOfRowsAffected = command.ExecuteNonQuery();
//Set and execute remaining rows.
foreach (var emp in employees.Skip(1))
{
command.Parameters[nameof(EmployeeSimple.EmployeeKey)].Value = emp.EmployeeKey;
command.Parameters[nameof(EmployeeSimple.CellPhone)].Value = emp.CellPhone ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.EmployeeClassificationKey)].Value = emp.EmployeeClassificationKey;
command.Parameters[nameof(EmployeeSimple.FirstName)].Value = emp.FirstName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.LastName)].Value = emp.LastName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.MiddleName)].Value = emp.MiddleName ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.OfficePhone)].Value = emp.OfficePhone ?? (object)DBNull.Value;
command.Parameters[nameof(EmployeeSimple.Title)].Value = emp.Title ?? (object)DBNull.Value;
em.NumberOfRowsAffected += command.ExecuteNonQuery();
}
return em.NumberOfRowsAffected;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted)//Use a transaction
.Execute();
}
}
Dapper.Contrib
The Dapper.Contrib library can elimiante the boilerplate for some common scenarios.
public class MultipleCrudScenarioContrib : MultipleCrudScenario
{
public MultipleCrudScenarioContrib(string connectionString) : base(connectionString)
{ }
override public void DeleteBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var con = OpenConnection())
con.Delete(employees);
}
override public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var con = OpenConnection())
con.Insert(employees);
}
override public void UpdateBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var con = OpenConnection())
con.Update(employees);
}
}
Entity Framework 6
public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
private Func<OrmCookbookContext> CreateDbContext;
public MultipleCrudScenario(Func<OrmCookbookContext> dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public void DeleteBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Entry(employee).State = EntityState.Deleted;
context.SaveChanges();
}
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
using (var context = CreateDbContext())
{
foreach (var key in employeeKeys)
context.Entry(new Employee() { EmployeeKey = key }).State = EntityState.Deleted;
context.SaveChanges();
}
}
public IList<Employee> FindByLastName(string lastName)
{
using (var context = CreateDbContext())
return context.Employee.Where(ec => ec.LastName == lastName).ToList();
}
public void InsertBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employee.Add(employee);
context.SaveChanges();
}
}
public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employee.Add(employee);
context.SaveChanges();
}
return employees.Select(x => x.EmployeeKey).ToList();
}
public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employee.Add(employee);
context.SaveChanges();
}
return employees;
}
public void InsertBatchWithRefresh(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employee.Add(employee);
context.SaveChanges();
}
}
public void UpdateBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Entry(employee).State = EntityState.Modified;
context.SaveChanges();
}
}
}
Entity Framework Core
public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
private Func<OrmCookbookContext> CreateDbContext;
public MultipleCrudScenario(Func<OrmCookbookContext> dBContextFactory)
{
CreateDbContext = dBContextFactory;
}
public void DeleteBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Entry(employee).State = EntityState.Deleted;
context.SaveChanges();
}
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
using (var context = CreateDbContext())
{
context.Employees.Where(x => employeeKeys.Contains(x.EmployeeKey)).ExecuteDelete();
context.SaveChanges();
}
}
public IList<Employee> FindByLastName(string lastName)
{
using (var context = CreateDbContext())
return context.Employees.Where(ec => ec.LastName == lastName).ToList();
}
public void InsertBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employees.Add(employee);
context.SaveChanges();
}
}
public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employees.Add(employee);
context.SaveChanges();
}
return employees.Select(x => x.EmployeeKey).ToList();
}
public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employees.Add(employee);
context.SaveChanges();
}
return employees;
}
public void InsertBatchWithRefresh(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Employees.Add(employee);
context.SaveChanges();
}
}
public void UpdateBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var context = CreateDbContext())
{
foreach (var employee in employees)
context.Entry(employee).State = EntityState.Modified;
context.SaveChanges();
}
}
}
It should be noted that performance of the batch update and delete operations is pretty low, Entity Framework Core will Update or Delete every record one by one.
There is a third-party library can implement high-performance batch operations, which is named Zack.EFCore.Batch. It can be obtained from https://github.com/yangzhongke/Zack.EFCore.Batch.
LINQ to DB
TODO
LLBLGen Pro
public class MultipleCrudScenario : IMultipleCrudScenario<EmployeeEntity>
{
public void DeleteBatch(IList<EmployeeEntity> employees)
{
if(employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using(var adapter = new DataAccessAdapter())
{
// Delete the entities directly using an IN (key1, key2...) predicate.
adapter.DeleteEntitiesDirectly(typeof(EmployeeEntity),
new RelationPredicateBucket(EmployeeFields.EmployeeKey
.In(employees.Select(e => e.EmployeeKey).ToList())));
}
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if(employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
using(var adapter = new DataAccessAdapter())
{
// Delete the entities directly using an IN (key1, key2...) predicate.
adapter.DeleteEntitiesDirectly(typeof(EmployeeEntity),
new RelationPredicateBucket(EmployeeFields.EmployeeKey.In(employeeKeys)));
}
}
public IList<EmployeeEntity> FindByLastName(string lastName)
{
using(var adapter = new DataAccessAdapter())
{
return new LinqMetaData(adapter).Employee.Where(e => e.LastName == lastName).ToList();
}
}
public void InsertBatch(IList<EmployeeEntity> employees)
{
if(employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
// The collection is disposed as it assigns event handlers to the entities it contains. Keeping the
// entities around would keep the collection in memory.
using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
{
using(var adapter = new DataAccessAdapter())
{
// use batching for inserts. We'll use 100 for a batch size here.
// This will send at most 100 inserts at one time to the database
// in a single DbCommand
adapter.BatchSize = 100;
adapter.SaveEntityCollection(toInsert);
}
}
}
public IList<int> InsertBatchReturnKeys(IList<EmployeeEntity> employees)
{
if(employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
{
using(var adapter = new DataAccessAdapter())
{
// use batching for inserts. We'll use 100 for a batch size here.
// This will send at most 100 inserts at one time to the database
// in a single DbCommand
adapter.BatchSize = 100;
adapter.SaveEntityCollection(toInsert);
}
return toInsert.Select(e => e.EmployeeKey).ToList();
}
}
public IList<EmployeeEntity> InsertBatchReturnRows(IList<EmployeeEntity> employees)
{
if(employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
// Use a unit of work here. We could have created a new EntityCollection here but as well
var uow = new UnitOfWork2();
foreach(var e in employees)
{
uow.AddForSave(e);
}
using(var adapter = new DataAccessAdapter())
{
// use batching for inserts. We'll use 100 for a batch size here.
// This will send at most 100 inserts at one time to the database
// in a single DbCommand
adapter.BatchSize = 100;
uow.Commit(adapter);
}
// LLBLGen Pro will update entities in-place after an insert, so we can return what we received.
return employees;
}
public void InsertBatchWithRefresh(IList<EmployeeEntity> employees)
{
if(employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using(var toInsert = new EntityCollection<EmployeeEntity>(employees))
{
using(var adapter = new DataAccessAdapter())
{
// use batching for inserts. We'll use 100 for a batch size here.
// This will send at most 100 inserts at one time to the database
// in a single DbCommand. LLBLGen Pro will update the entity saved
// with the new PK.
adapter.BatchSize = 100;
adapter.SaveEntityCollection(toInsert);
}
}
}
public void UpdateBatch(IList<EmployeeEntity> employees)
{
if(employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using(var toUpdate = new EntityCollection<EmployeeEntity>(employees))
{
using(var adapter = new DataAccessAdapter())
{
// use batching for updates. We'll use 100 for a batch size here.
// This will send at most 100 updates at one time to the database
// in a single DbCommand
adapter.BatchSize = 100;
adapter.SaveEntityCollection(toUpdate);
}
}
}
}
NHibernate
public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
readonly ISessionFactory m_SessionFactory;
public MultipleCrudScenario(ISessionFactory sessionFactory)
{
m_SessionFactory = sessionFactory;
}
public void DeleteBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Delete(employee);
session.Flush();
}
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var key in employeeKeys)
session.Delete(new Employee() { EmployeeKey = key });
session.Flush();
}
}
public IList<Employee> FindByLastName(string lastName)
{
using (var session = m_SessionFactory.OpenSession())
return session.Query<Employee>().Where(ec => ec.LastName == lastName).ToList();
}
public void InsertBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Save(employee);
session.Flush();
}
}
public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Save(employee);
session.Flush();
}
return employees.Select(x => x.EmployeeKey).ToList();
}
public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Save(employee);
session.Flush();
}
return employees;
}
public void InsertBatchWithRefresh(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Save(employee);
session.Flush();
}
}
public void UpdateBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var session = m_SessionFactory.OpenSession())
{
foreach (var employee in employees)
session.Update(employee);
session.Flush();
}
}
}
RepoDb
public class MultipleCrudScenario : IMultipleCrudScenario<EmployeeSimple>
{
private readonly string m_ConnectionString;
public MultipleCrudScenario(string connectionString)
{
m_ConnectionString = connectionString;
}
public void DeleteBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
{
var keys = employees.Select(e => e.EmployeeKey).AsList();
repository.Delete(e => keys.Contains(e.EmployeeKey));
}
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
repository.Delete(e => employeeKeys.Contains(e.EmployeeKey));
}
public IList<EmployeeSimple> FindByLastName(string lastName)
{
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
return repository.Query(e => e.LastName == lastName).AsList();
}
public void InsertBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
repository.InsertAll(employees);
}
public IList<int> InsertBatchReturnKeys(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
repository.InsertAll(employees);
return employees.Select(e => e.EmployeeKey).AsList();
}
public IList<EmployeeSimple> InsertBatchReturnRows(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
repository.InsertAll(employees);
return employees;
}
public void InsertBatchWithRefresh(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
repository.InsertAll(employees);
}
public void UpdateBatch(IList<EmployeeSimple> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var repository = new EmployeeSimpleRepository(m_ConnectionString, ConnectionPersistency.Instance))
repository.UpdateAll(employees);
}
}
ServiceStack
public class MultipleCrudScenario : IMultipleCrudScenario<Employee>
{
private readonly IDbConnectionFactory _dbConnectionFactory;
public MultipleCrudScenario(IDbConnectionFactory dbConnectionFactory)
{
_dbConnectionFactory = dbConnectionFactory;
}
public void DeleteBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var db = _dbConnectionFactory.OpenDbConnection())
db.DeleteAll(employees);
}
public void DeleteBatchByKey(IList<int> employeeKeys)
{
if (employeeKeys == null || employeeKeys.Count == 0)
throw new ArgumentException($"{nameof(employeeKeys)} is null or empty.", nameof(employeeKeys));
using (var db = _dbConnectionFactory.OpenDbConnection())
db.DeleteByIds<Employee>(employeeKeys);
}
public IList<Employee> FindByLastName(string lastName)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
return db.Select<Employee>(e => e.LastName == lastName);
}
public void InsertBatch(IList<Employee> employees)
{
using (var db = _dbConnectionFactory.OpenDbConnection())
db.InsertAll(employees);
}
public IList<int> InsertBatchReturnKeys(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var db = _dbConnectionFactory.OpenDbConnection())
db.SaveAll(employees);
return employees.Select(e => e.Id).ToList();
}
public IList<Employee> InsertBatchReturnRows(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var db = _dbConnectionFactory.OpenDbConnection())
db.SaveAll(employees);
return employees;
}
public void InsertBatchWithRefresh(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var db = _dbConnectionFactory.OpenDbConnection())
db.SaveAll(employees);
}
public void UpdateBatch(IList<Employee> employees)
{
if (employees == null || employees.Count == 0)
throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));
using (var db = _dbConnectionFactory.OpenDbConnection())
db.UpdateAll(employees);
}
}