private ExtSupplierDTO SupplierRecordAdded(SupplierDTO newRecord, Object additionalData)
{
SuppliersMergeParameter mergeParam = additionalData as SuppliersMergeParameter;
ExtSupplierDTO supplierDetail = newRecord.Clone() as ExtSupplierDTO;
supplierDetail.LastUpdateTime = AdjustClientUtcTimeToServerTime(newRecord.LastUpdateTime, mergeParam.ClientServerTimeDifference);
supplierDetail.SystemUserID = mergeParam.SystemUserID;
try
{
mergeParam.DataAccessObject.AddSupplier(supplierDetail);
}
catch (SqlException ex)
{
switch (ex.Number)
{
// Duplicate record found.
case 2627:
break;
default:
// Re-throw the exception if we don't recognise the error code.
throw;
}
}
return(supplierDetail);
}
/// <summary>
/// Add a new record to Suppliers.Supplier
/// </summary>
/// <param name="supplierRec">
/// New supplier details.
/// </param>
public ExtSupplierDTO AddSupplier(ExtSupplierDTO supplierRec)
{
using (SqlConnection connection = CreateConnection())
{
using (SqlCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "[Suppliers].[AddSupplier]";
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@code", supplierRec.Code);
dbCommand.Parameters.AddWithValue("@descr", supplierRec.Descr);
dbCommand.Parameters.AddWithValue("@addressDetail", StringCollectionToString(supplierRec.Address));
dbCommand.Parameters.AddWithValue("@systemUserID", supplierRec.SystemUserID);
using (SqlDataReader dataReader = dbCommand.ExecuteReader())
{
int colIndexSupplierID = dataReader.GetOrdinal("SupplierID");
int colIndexUniqueID = dataReader.GetOrdinal("UniqueID");
dataReader.Read();
ExtSupplierDTO result = supplierRec.Clone() as ExtSupplierDTO;
result.ResultCode = 0;
result.SupplierID = dataReader.GetInt32(colIndexSupplierID);
result.UniqueIdentifier = dataReader.GetGuid(colIndexUniqueID);
return(result);
}
}
}
}
/// <summary>
/// Retrieve the Suppliers.Supplier records with known identifiers.
/// </summary>
/// <param name="supplierIdentifiers">
/// Collection of unique identifiers
/// </param>
/// <returns>
/// Returns a list of SupplierDTO records
/// </returns>
public IEnumerable <ExtSupplierDTO> GetExistingSupplierRecords(IEnumerable <Guid> supplierIdentifiers)
{
List <ExtSupplierDTO> result = new List <ExtSupplierDTO>();
using (SqlConnection connection = CreateConnection())
{
using (SqlCommand dbCommand = connection.CreateCommand())
{
DataTable idList = new DataTable("GuidTable", "Shared");
idList.Columns.Add("identifier", typeof(Guid));
idList.BeginLoadData();
try
{
foreach (Guid id in supplierIdentifiers)
{
idList.Rows.Add(id);
}
}
finally
{
idList.EndLoadData();
}
dbCommand.CommandText = "[Suppliers].[GetSuppliersWithIdentifiers]";
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@identifiersList", idList);
using (SqlDataReader dataReader = dbCommand.ExecuteReader())
{
Int32 colIndexSupplierID = dataReader.GetOrdinal("SupplierID");
Int32 colIndexUniqueID = dataReader.GetOrdinal("UniqueID");
Int32 colIndexCode = dataReader.GetOrdinal("Code");
Int32 colIndexDescr = dataReader.GetOrdinal("Descr");
Int32 colIndexAddressDetail = dataReader.GetOrdinal("AddressDetail");
Int32 colIndexLastUpdateTime = dataReader.GetOrdinal("LastUpdateTime");
Int32 colIndexSystemUserID = dataReader.GetOrdinal("SystemUserID");
while (dataReader.Read())
{
ExtSupplierDTO addRec = new ExtSupplierDTO()
{
SupplierID = dataReader.GetInt32(colIndexSupplierID),
UniqueIdentifier = dataReader.GetGuid(colIndexUniqueID),
Code = dataReader.GetString(colIndexCode),
Descr = dataReader.GetString(colIndexDescr),
Address = StringToStringCollection(dataReader.GetString(colIndexAddressDetail)),
LastUpdateTime = dataReader.GetDateTime(colIndexLastUpdateTime),
SystemUserID = dataReader.GetInt32(colIndexSystemUserID)
};
result.Add(addRec);
}
}
}
}
return(result);
}
/// <summary>
/// Update an existing Suppliers.Supplier record.
/// </summary>
/// <param name="updateRec">
/// Updated record values.
/// </param>
/// <returns>
/// Returns 1 if a previous record was found and the data was not updated. Returns 2 if the data was updated.
/// </returns>
/// <remarks>
/// When the return code is 1 the <paramref name="updateRec"/> is updated with the current values in the database.
/// </remarks>
public ExtSupplierDTO UpdateSupplier(ExtSupplierDTO updateRec)
{
using (SqlConnection connection = CreateConnection())
{
using (SqlCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "[Suppliers].[UpdateSupplier]";
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@code", updateRec.Code);
dbCommand.Parameters.AddWithValue("@descr", updateRec.Descr);
String address = StringCollectionToString(updateRec.Address);
if (String.IsNullOrWhiteSpace(address))
{
SqlParameter nullParam = new SqlParameter("@addressDetail", SqlDbType.VarChar, 200)
{
Direction = ParameterDirection.Input,
Value = DBNull.Value
};
dbCommand.Parameters.Add(nullParam);
}
else
{
dbCommand.Parameters.AddWithValue("@addressDetail", address);
}
dbCommand.Parameters.AddWithValue("@recordUpdatedTime", updateRec.LastUpdateTime);
dbCommand.Parameters.AddWithValue("@systemUserID", updateRec.SystemUserID);
using (SqlDataReader dataReader = dbCommand.ExecuteReader())
{
Int32 colIndexActionCode = dataReader.GetOrdinal("ActionCode");
Int32 colIndexSupplierID = dataReader.GetOrdinal("SupplierID");
Int32 colIndexUniqueID = dataReader.GetOrdinal("UniqueID");
Int32 colIndexCode = dataReader.GetOrdinal("Code");
Int32 colIndexDescr = dataReader.GetOrdinal("Descr");
Int32 colIndexAddressDetail = dataReader.GetOrdinal("AddressDetail");
Int32 colIndexLastUpdateTime = dataReader.GetOrdinal("LastUpdateTime");
Int32 colIndexSystemUserID = dataReader.GetOrdinal("SystemUserID");
dataReader.Read();
ExtSupplierDTO result = updateRec.Clone() as ExtSupplierDTO;
result.SupplierID = dataReader.GetInt32(colIndexSupplierID);
result.UniqueIdentifier = dataReader.GetGuid(colIndexUniqueID);
result.Code = dataReader.GetString(colIndexCode);
result.Descr = dataReader.GetString(colIndexDescr);
result.Address = dataReader.IsDBNull(colIndexAddressDetail) ? new List <String>() : new List <String>(StringToStringCollection(dataReader.GetString(colIndexAddressDetail)));
result.LastUpdateTime = dataReader.GetDateTime(colIndexLastUpdateTime);
result.SystemUserID = dataReader.GetInt32(colIndexSystemUserID);
result.ResultCode = dataReader.GetInt32(colIndexActionCode);
return(result);
}
}
}
}
private ExtSupplierDTO SupplierRecordUpdated(ExtSupplierDTO originalData, SupplierDTO updatedData, Object additionalData)
{
SuppliersMergeParameter mergeParam = additionalData as SuppliersMergeParameter;
ExtSupplierDTO supplierDetail = updatedData.Clone() as ExtSupplierDTO;
supplierDetail.LastUpdateTime = AdjustClientUtcTimeToServerTime(updatedData.LastUpdateTime, mergeParam.ClientServerTimeDifference);
supplierDetail.SystemUserID = mergeParam.SystemUserID;
mergeParam.DataAccessObject.UpdateSupplier(supplierDetail);
return(supplierDetail);
}
private void SupplierRecordDeleted(ExtSupplierDTO deletedRecord, Object additionalData)
{
SuppliersMergeParameter mergeParam = additionalData as SuppliersMergeParameter;
mergeParam.DataAccessObject.RetireSupplier
(
mergeParam.SystemUserID,
AdjustClientUtcTimeToServerTime(deletedRecord.LastUpdateTime, mergeParam.ClientServerTimeDifference),
deletedRecord.SupplierID,
true
);
}
/// <summary>
/// Mark a Suppliers.Supplier record as inactive
/// </summary>
/// <param name="systemUserID">
/// User ID of the user updating the record.
/// </param>
/// <param name="recordUpdatedTime">
/// UTC time when the record was updated.
/// </param>
/// <param name="supplierID">
/// ID of the record to update.
/// </param>
/// <param name="isRetired">
/// Flag to indicate the retired state of the supplier
/// </param>
public ExtSupplierDTO RetireSupplier(Int32 systemUserID, DateTime recordUpdatedTime, Int32 supplierID, Boolean isRetired)
{
using (SqlConnection connection = CreateConnection())
{
using (SqlCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "[Suppliers].[RetireSupplier]";
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.AddWithValue("@supplierID", supplierID);
dbCommand.Parameters.AddWithValue("@retiredState", isRetired ? 1 : 0);
dbCommand.Parameters.AddWithValue("@recordUpdatedTime", recordUpdatedTime);
dbCommand.Parameters.AddWithValue("@systemUserID", systemUserID);
using (SqlDataReader dataReader = dbCommand.ExecuteReader())
{
Int32 colIndexActionCode = dataReader.GetOrdinal("ActionCode");
Int32 colIndexSupplierID = dataReader.GetOrdinal("SupplierID");
Int32 colIndexUniqueID = dataReader.GetOrdinal("UniqueID");
Int32 colIndexCode = dataReader.GetOrdinal("Code");
Int32 colIndexDescr = dataReader.GetOrdinal("Descr");
Int32 colIndexAddressDetail = dataReader.GetOrdinal("AddressDetail");
Int32 colIndexLastUpdateTime = dataReader.GetOrdinal("LastUpdateTime");
Int32 colIndexSystemUserID = dataReader.GetOrdinal("SystemUserID");
dataReader.Read();
ExtSupplierDTO result = new ExtSupplierDTO();
result.SupplierID = dataReader.GetInt32(colIndexSupplierID);
result.UniqueIdentifier = dataReader.GetGuid(colIndexUniqueID);
result.Code = dataReader.GetString(colIndexCode);
result.Descr = dataReader.GetString(colIndexDescr);
result.Address = dataReader.IsDBNull(colIndexAddressDetail) ? new List <String>() : new List <String>(StringToStringCollection(dataReader.GetString(colIndexAddressDetail)));
result.LastUpdateTime = dataReader.GetDateTime(colIndexLastUpdateTime);
result.SystemUserID = dataReader.GetInt32(colIndexSystemUserID);
result.ResultCode = dataReader.GetInt32(colIndexActionCode);
return(result);
}
}
}
}
