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