protected IDbJob <int?> BuildInsertOrUpdateProducts(IEnumerable <Product> products)
{
if (products == null || !products.Any())
{
throw new ArgumentException($"{nameof(products)} is null or empty.", nameof(products));
}
string sql = $@"
MERGE INTO {Product.TableName} target
USING
(
VALUES (
@{nameof(Product.ProductKey)},
@{nameof(Product.ProductName)},
@{nameof(Product.ProductLineKey)},
@{nameof(Product.ShippingWeight)},
@{nameof(Product.ProductWeight)}
)
) source (
{nameof(Product.ProductKey)},
{nameof(Product.ProductName)},
{nameof(Product.ProductLineKey)},
{nameof(Product.ShippingWeight)},
{nameof(Product.ProductWeight)}
)
ON target.ProductKey = source.{nameof(Product.ProductKey)}
WHEN MATCHED THEN
UPDATE SET ProductName = source.{nameof(Product.ProductName)},
ProductLineKey = source.{nameof(Product.ProductLineKey)},
ShippingWeight = source.{nameof(Product.ShippingWeight)},
ProductWeight = source.{nameof(Product.ProductWeight)}
WHEN NOT MATCHED THEN
INSERT
(
ProductName,
ProductLineKey,
ShippingWeight,
ProductWeight
)
VALUES
(
source.{nameof(Product.ProductName)},
source.{nameof(Product.ProductLineKey)},
source.{nameof(Product.ShippingWeight)},
source.{nameof(Product.ProductWeight)}
)
OUTPUT Inserted.ProductKey;";
Product firstProd = products.First();
//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
return(DbConnector.Build <int?>(
sql: sql,
param: firstProd,
onExecute: (int?result, IDbExecutionModel em) =>
{
//Set the command
DbCommand command = em.Command;
//Execute first row.
firstProd.ProductKey = (int)command.ExecuteScalar();
em.NumberOfRowsAffected = 1;
//Set and execute remaining rows.
foreach (var prod in products.Skip(1))
{
command.Parameters[nameof(Product.ProductKey)].Value = prod.ProductKey;
command.Parameters[nameof(Product.ProductName)].Value = prod.ProductName ?? (object)DBNull.Value;
command.Parameters[nameof(Product.ProductLineKey)].Value = prod.ProductLineKey;
command.Parameters[nameof(Product.ShippingWeight)].Value = prod.ShippingWeight ?? (object)DBNull.Value;
command.Parameters[nameof(Product.ProductWeight)].Value = prod.ProductWeight ?? (object)DBNull.Value;
prod.ProductKey = (int)command.ExecuteScalar();
em.NumberOfRowsAffected += 1;
}
return em.NumberOfRowsAffected;
}
)
.WithIsolationLevel(IsolationLevel.ReadCommitted));//Use a transaction
}
