private void EnsureLoadedCustomData(ArcenDynamicTableRow Row)
{
if (this.HaveLoadedData)
{
return;
}
this.HaveLoadedData = true;
CustomDataSet set = Row.GetCustomData("defensePlacer");
this.MinimumControllerDistance = (set.GetFInt("min_distance_percent_center_to_controller") * ExternalConstants.Instance.Balance_AverageGravWellRadius).IntValue;
this.MaximumControllerDistance = (set.GetFInt("max_distance_percent_center_to_controller") * ExternalConstants.Instance.Balance_AverageGravWellRadius).IntValue;
this.MinimumNumberOfNonControllerStrongAreas = set.GetInt("min_number_of_non_controller_strong_areas");
this.MaximumNumberOfNonControllerStrongAreas = set.GetInt("max_number_of_non_controller_strong_areas");
this.MinimumDistanceOfStrongAreaFromController = (set.GetFInt("min_distance_percent_controller_to_strong_area") * ExternalConstants.Instance.Balance_AverageGravWellRadius).IntValue;
this.MaximumDistanceOfStrongAreaFromController = (set.GetFInt("max_distance_percent_controller_to_strong_area") * ExternalConstants.Instance.Balance_AverageGravWellRadius).IntValue;
}
//checks the PropertyForRent, Staff and Registration tables for the branchNo passed in
public DataSet branchNumberConstraintExists(Dictionary<String, String> branchNumberConstraintExistsDict)
{
DataSet dsTemp = new DataSet(); // used to temporarily store the search results for the boolean check
DataSet ds = new CustomDataSet();
// Search for the existance of a foreign key across all tables where it may exist
String sqlStatement = "SELECT branchNo " +
"FROM (SELECT branchNo FROM PropertyForRent " +
"UNION " +
"SELECT branchNo FROM Registration " +
"UNION " +
"SELECT branchNo FROM Staff " +
") AS result " +
"WHERE result.branchNo = @branchNo ";
OleDbConnection conn = new OleDbConnection(Connections.DreamhomeConnStr());
try
{
OleDbCommand cmd = new OleDbCommand(sqlStatement, conn);
cmd.Parameters.AddWithValue("@branchNo", branchNumberConstraintExistsDict["branchNo"]);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
using (conn)
{
conn.Open();
da.Fill(dsTemp);
// if the row count result is > 0, return true into success table, else return false.
Boolean results = dsTemp.Tables[0].Rows.Count > 0;
ds.Tables[1].Rows.Add(results);
}
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "branchNumberConstraintExists");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public DataSet branchExists(Dictionary<String, String> branchExistsDict)
{
DataSet ds = new CustomDataSet();
// Run the lookupBranch method and, if the row count result is > 0,
// return true into success table, else return false.
try
{
Boolean results = lookupBranch(branchExistsDict["branchNo"]).Tables[0].Rows.Count > 0;
ds.Tables[1].Rows.Add(results.ToString());
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "branchExists");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public DataSet updateStaff(Dictionary<String, String> updateStaffDict)
{
DataSet ds = new CustomDataSet();
String sqlStatement = "UPDATE Staff SET staffNo = @staffNo, " +
"fName = @fName, " +
"lName = @lName, " +
"[position] = @position, " +
"sex = @sex, " +
"DOB = @DOB, " +
"salary = @salary, " +
"branchNo = @branchNo " +
"WHERE staffNo = @staffNo";
OleDbConnection conn = new OleDbConnection();
try
{
conn.ConnectionString = Connections.DreamhomeConnStr();
OleDbCommand cmd = new OleDbCommand(sqlStatement, conn);
cmd.Parameters.AddWithValue("@staffNo", checkForEmpty(updateStaffDict["staffNo"]));
cmd.Parameters.AddWithValue("@fName", checkForEmpty(updateStaffDict["fName"]));
cmd.Parameters.AddWithValue("@lName", checkForEmpty(updateStaffDict["lName"]));
cmd.Parameters.AddWithValue("@position", checkForEmpty(updateStaffDict["position"]));
cmd.Parameters.AddWithValue("@sex", checkForEmpty(updateStaffDict["sex"]));
cmd.Parameters.AddWithValue("@DOB", checkForEmpty(updateStaffDict["DOB"]));
cmd.Parameters.AddWithValue("@salary", checkForEmpty(updateStaffDict["salary"]));
cmd.Parameters.AddWithValue("@branchNo", checkForEmpty(updateStaffDict["branchNo"]));
//using block to dispose of the connection rather than a finally{ conn.Close(); }
using (conn)
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "updateStaff");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public DataSet updateBranch(Dictionary<String, String> updateBranchDict)
{
DataSet ds = new CustomDataSet();
String sqlStatement = "UPDATE Branch SET branchNo = @branchNo, " +
"street = @street, " +
"city = @city, " +
"postcode = @postcode " +
"WHERE branchNo = @branchNo ";
OleDbConnection conn = new OleDbConnection();
try
{
conn.ConnectionString = Connections.DreamhomeConnStr();
OleDbCommand cmd = new OleDbCommand(sqlStatement, conn);
cmd.Parameters.AddWithValue("@branchNo", checkForEmpty(updateBranchDict["branchNo"]));
cmd.Parameters.AddWithValue("@street", checkForEmpty(updateBranchDict["street"]));
cmd.Parameters.AddWithValue("@city", checkForEmpty(updateBranchDict["city"]));
cmd.Parameters.AddWithValue("@postcode", checkForEmpty(updateBranchDict["postcode"]));
using (conn)
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "updateBranch");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public DataSet insertStaff(Dictionary<String, String> insertStaffDict)
{
DataSet ds = new CustomDataSet();
String sqlStatement = "INSERT INTO Staff(staffNo, fName, lName, [position], sex, DOB, salary, branchNo) " +
"VALUES (@staffNo, @fName, @lName, @position, @sex, @DOB, @salary, @branchNo); ";
OleDbConnection conn = new OleDbConnection();
try
{
conn.ConnectionString = Connections.DreamhomeConnStr();
OleDbCommand cmd = new OleDbCommand(sqlStatement, conn);
cmd.Parameters.AddWithValue("@staffNo", checkForEmpty(insertStaffDict["staffNo"]));
cmd.Parameters.AddWithValue("@fName", checkForEmpty(insertStaffDict["fName"]));
cmd.Parameters.AddWithValue("@lName", checkForEmpty(insertStaffDict["lName"]));
cmd.Parameters.AddWithValue("@position", checkForEmpty(insertStaffDict["position"]));
cmd.Parameters.AddWithValue("@sex", checkForEmpty(insertStaffDict["sex"]));
cmd.Parameters.AddWithValue("@DOB", checkForEmpty(insertStaffDict["DOB"]));
cmd.Parameters.AddWithValue("@salary", checkForEmpty(insertStaffDict["salary"]));
cmd.Parameters.AddWithValue("@branchNo", checkForEmpty(insertStaffDict["branchNo"]));
using (conn)
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "insertStaff");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public DataSet managerExists(Dictionary<String, String> managerExistsDict)
{
DataSet dsTemp = new DataSet(); // used to temporarily store the search results for the boolean check
DataSet ds = new CustomDataSet();
String sqlStatement = "SELECT *FROM Staff WHERE branchNo = @branchNo AND [position] = @position ";
OleDbConnection conn = new OleDbConnection(Connections.DreamhomeConnStr());
try
{
OleDbCommand cmd = new OleDbCommand(sqlStatement, conn);
cmd.Parameters.AddWithValue("@branchNo", managerExistsDict["branchNo"]);
cmd.Parameters.AddWithValue("@position", "Manager");
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
using (conn)
{
conn.Open();
da.Fill(dsTemp);
// if the row count result is > 0, return true into success table, else return false.
Boolean results = dsTemp.Tables[0].Rows.Count > 0;
ds.Tables[1].Rows.Add(results);
}
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "managerExists");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public DataSet deleteStaff(Dictionary<String, String> deleteStaffDict)
{
DataSet ds = new CustomDataSet();
String sqlStatement = "DELETE FROM Staff WHERE staffNo=@staffNo";
OleDbConnection conn = new OleDbConnection(Connections.DreamhomeConnStr());
OleDbCommand cmd = new OleDbCommand(sqlStatement, conn);
try
{
cmd.Parameters.AddWithValue("@staffNo", deleteStaffDict["staffNo"]);
using (conn)
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
// catch error and enter into logError then pass a generic error
// back to the user through the custom return table
catch (Exception ex)
{
BusinessTier.logError(ex, "DataTier", "deleteStaff");
ds.Tables[0].Rows.Add("An unexpected error occurred. Please try again later or contact support.");
}
return ds;
}
public void LoadCustomData(ArcenDynamicTableRow Row)
{
CustomDataSet set = Row.GetCustomData("TargetSorter");
this.IsGroupSorter = set.GetBool("is_group_sorter");
}
/// <summary>
/// Performs multiple updates in a single transaction, with disparate (but presumably related) data.
/// </summary>
/// <param name="dataSet1">first data set</param>
/// <param name="dataSet2">second data set</param>
public void UpdateMultipleDetails(CustomDataSet dataSet1, SpecialDataSet dataSet2)
{
// Prepare the connection
IDbConnection connection = new System.Data.SqlClient.SqlConnection( /* Connection string goes here*/ );
Library.TransactionWrapper transactionWrapper = new Library.TransactionWrapper(connection);
// Hook data for first operation
CustomDataUpdate customDataUpdate = new CustomDataUpdate(this.log);
CustomTransactionedOperation operation1 = new CustomTransactionedOperation(dataSet1);
operation1.Execute += new Library.TransactionedOperationDelegate(customDataUpdate.UpdateData);
// Hook data for second operation
AggregateTransactionedOperation operation2 = new AggregateTransactionedOperation(dataSet2);
operation2.Execute += new Library.TransactionedOperationDelegate(AggregateTransactionedOperation.SelfContainedUpdateData);
// Collect together the operations and make them atomic
Library.TransactionedOperation[] operations = new Library.TransactionedOperation[] {operation1, operation2};
transactionWrapper.MakeAtomic(operations);
}
private ILog log = null; // Initialise this somewhere...
#endregion Fields
#region Methods
/// <summary>
///
/// </summary>
/// <param name="dataSet"></param>
public void UpdateCustomDetails(CustomDataSet dataSet)
{
IDbConnection connection = new System.Data.SqlClient.SqlConnection( /* Connection string goes here*/ );
Library.TransactionWrapper transactionWrapper = new Library.TransactionWrapper(connection);
// Hook the data onto the operation parameters
CustomDataUpdate customDataUpdate = new CustomDataUpdate(this.log);
CustomTransactionedOperation operation = new CustomTransactionedOperation(dataSet);
operation.Execute += new Library.TransactionedOperationDelegate(customDataUpdate.UpdateData);
transactionWrapper.MakeAtomic(operation);
}
public CustomTransactionedOperation(CustomDataSet dataSet)
{
this.Example = dataSet.ExampleData;
}