/// <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>
/// 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);
}
}
}
}
