public void UpdateGraphWithChildDeletes(ProductLine productLine)
{
if (productLine == null)
{
throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
}
productLine.ApplyKeys();
//Note: This could all be done in a single query transaction leveraging the MERGE feature
using (var con = OpenConnection())
using (var trans = con.BeginTransaction())
{
//Find products
var originalProductKeys = BuildGetProductKeys(productLine.ProductLineKey).Execute(trans);
foreach (var item in productLine.Products)
{
originalProductKeys.Remove(item.ProductKey);
}
//Leverage DbConnector's Job Batching feature
DbJob.ExecuteAll(trans, BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
//Remove products
if (originalProductKeys.Count > 0)
{
BuildDeleteProducts(originalProductKeys).Execute(trans);
}
trans.Commit();
}
}
public int Create(ProductLine productLine)
{
if (productLine == null)
{
throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
}
const string sql = "INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);";
//Build the main IDbJob.
IDbJob <int> jobProductLine = DbConnector
.Scalar <int>(sql, productLine)
.OnExecuted((int result, IDbExecutedModel im) =>
{
productLine.ProductLineKey = result;
productLine.ApplyKeys();
return(result);
});
if (productLine.Products.Count == 0)
{
return(jobProductLine.Execute());
}
else
{
//Leverage DbConnector's Job Batching feature
DbJob.ExecuteAll(jobProductLine, BuildInsertOrUpdateProducts(productLine.Products));
return(productLine.ProductLineKey);
}
}
public void UpdateGraph(ProductLine productLine)
{
if (productLine == null)
{
throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
}
productLine.ApplyKeys();
//Leverage DbConnector's Job Batching feature
DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
}
public void JoinedExecution_Custom_Transaction()
{
using (var conn = new SqlConnection(TestBase.ConnectionString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
IDbJob <int?> jobInsert = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
INSERT INTO [Production].[Location]
([Name]
,[CostRate])
VALUES
(@Name
,@CostRate)
";
cmd.Parameters.AddFor(new
{
Name = "9102",
CostRate = 5
});
});
IDbJob <int?> jobDelete = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
DELETE FROM [Production].[Location] WHERE Name = @Name
";
cmd.Parameters.AddFor(new
{
Name = "9102",
CostRate = 5
});
});
var result = DbJob.ExecuteAll(transaction, jobInsert, jobDelete);
transaction.Commit();
Assert.NotNull(result);
Assert.Equal(1, result[0].Item2);
Assert.Equal(1, result[1].Item2);
}
}
}
public void UpdateGraphWithDeletes(ProductLine productLine, IList <int> productKeysToRemove)
{
if (productLine == null)
{
throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
}
productLine.ApplyKeys();
if (productKeysToRemove == null || productKeysToRemove.Count == 0)
{
DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
}
else
{
DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products), BuildDeleteProducts(productKeysToRemove));
}
}
public void JoinedExecution_Exception()
{
Assert.Throws <Exception>(() =>
{
IDbJob <int?> jobInsert = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
INSERT INTO [Production].[Location]
([Name]
,[CostRate])
VALUES
(@Name
,@CostRate)
";
cmd.Parameters.AddFor(new
{
Name = "9200",
CostRate = 5
});
});
IDbJob <int?> jobDelete = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
DELETE FROM [Production].[Location] WHERE Name = @Name
";
cmd.Parameters.AddFor(new
{
Name = "9200",
CostRate = 5
});
throw new System.Exception("Test Exception!");
});
var result = DbJob.ExecuteAll(jobInsert, jobDelete);
});
}
public void JoinedExecution()
{
IDbJob <int?> jobInsert = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
INSERT INTO [Production].[Location]
([Name]
,[CostRate])
VALUES
(@Name
,@CostRate)
";
cmd.Parameters.AddFor(new
{
Name = "9100",
CostRate = 5
});
});
IDbJob <int?> jobDelete = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
DELETE FROM [Production].[Location] WHERE Name = @Name
";
cmd.Parameters.AddFor(new
{
Name = "9100",
CostRate = 5
});
});
var result = DbJob.ExecuteAll(jobInsert, jobDelete);
Assert.NotNull(result);
Assert.Equal(1, result[0].Item2);
Assert.Equal(1, result[1].Item2);
}
public void NonQueries()
{
var jobInsert = _dbConnector.NonQueries((cmds) =>
{
cmds.Enqueue((cmd) =>
{
cmd.CommandText = @"
INSERT INTO [Production].[Location]
([Name]
,[CostRate])
VALUES
(@Name
,@CostRate)
";
cmd.Parameters.AddFor(new
{
Name = "9021",
CostRate = 5
});
});
cmds.Enqueue((cmd) =>
{
cmd.CommandText = @"
INSERT INTO [Production].[Location]
([Name]
,[CostRate])
VALUES
(@Name
,@CostRate)
";
cmd.Parameters.AddFor(new
{
Name = "9031",
CostRate = 6
});
});
});
var jobDelete = _dbConnector.NonQuery((cmd) =>
{
cmd.CommandText = @"
DELETE FROM [Production].[Location] WHERE Name = @Name OR Name = @Name2;
";
cmd.Parameters.AddFor(new
{
Name = "9021",
Name2 = "9031",
});
});
var results = DbJob.ExecuteAll(jobInsert, jobDelete);
Assert.NotNull(results.First().Item2);
Assert.Equal(2, results.First().Item2);
Assert.NotNull(results[1].Item2);
Assert.Equal(2, results[1].Item2);
}