public string addAddress(string customer_id, Address address) { StringBuilder sql = new StringBuilder(); sql.Append("INSERT INTO CUSTOMERS.ADDRESS (Id, street, state, zip, country, address_type, city) VALUES"); sql.Append("(CUSTOMERS.SEQ_ADDRESS.NEXTVAL, :street, :state, :zip, :country, :address_type, :city)"); StringBuilder sqlCustomerAddress = new StringBuilder(); sqlCustomerAddress.Append("INSERT INTO CUSTOMERS.CUSTOMER_ADDRESS (address_id, customer_id) VALUES"); sqlCustomerAddress.Append("(CUSTOMERS.SEQ_ADDRESS.CURRVAL, :customer_id)"); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString; OracleTransaction transaction; try { connection.Open(); transaction = connection.BeginTransaction(); using (OracleCommand command = new OracleCommand(sql.ToString(), connection)) { command.Parameters.Add(new OracleParameter("street", address.street)); command.Parameters.Add(new OracleParameter("state", address.state)); command.Parameters.Add(new OracleParameter("zip", address.zip)); command.Parameters.Add(new OracleParameter("country", address.country)); command.Parameters.Add(new OracleParameter("address_type", address.address_type)); command.Parameters.Add(new OracleParameter("city", address.city)); command.ExecuteNonQuery(); using (OracleCommand secondCommand = new OracleCommand(sqlCustomerAddress.ToString(), connection)) { secondCommand.CommandText = sqlCustomerAddress.ToString(); secondCommand.Parameters.Add(new OracleParameter("customer_id", customer_id)); secondCommand.ExecuteNonQuery(); } transaction.Commit(); } return "OK"; } catch (Exception ex) { throw new PlatformException(ex.Message); } } }
public string updateAddress(Address address) { try { addressBoundary = new AddressBoundary(); return addressBoundary.updateAddress(address); } catch(BusinessException e) { throw new WebFaultException<GeneralResponse> (new GeneralResponse { message = e.Message, status = GeneralResponse.STATUS_ERROR, code = "400" }, HttpStatusCode.BadRequest); } catch(PlatformException e) { throw new WebFaultException<GeneralResponse> (new GeneralResponse { message = e.Message, status = GeneralResponse.STATUS_ERROR, code = "500" }, HttpStatusCode.BadRequest); } }
/** Permite actualizar una direccion */ public string updateAddress(Address address) { StringBuilder sql = new StringBuilder(); sql.Append("UPDATE CUSTOMERS.ADDRESS SET street = :street, state = :state, zip = :zip, "); sql.Append("country = :country, address_type = :address_type, city = :city "); sql.Append("WHERE Id = :id"); using (OracleConnection connection = new OracleConnection()) { try { connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString; connection.Open(); using (OracleCommand command = new OracleCommand(sql.ToString(), connection)) { command.Parameters.Add(new OracleParameter("id", address.Id)); command.Parameters.Add(new OracleParameter("street", address.street)); command.Parameters.Add(new OracleParameter("state", address.state)); command.Parameters.Add(new OracleParameter("zip", address.zip)); command.Parameters.Add(new OracleParameter("country", address.country)); command.Parameters.Add(new OracleParameter("address_type", address.address_type)); command.Parameters.Add(new OracleParameter("city", address.city)); command.ExecuteNonQuery(); return "OK"; } } catch (Exception ex) { throw new PlatformException(ex.Message); } } }
/** Obtienes las direcciones de un cliente */ public IList<Address> getCustomerAddress(string customer_id) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT ad.Id, ad.street, ad.state, ad.zip, ad.country, ad.address_type, ad.city "); sql.Append("FROM CUSTOMERS.ADDRESS ad INNER JOIN CUSTOMERS.CUSTOMER_ADDRESS cus "); sql.Append("ON ad.Id = cus.address_id WHERE cus.customer_id = :id"); List<Address> addresses = new List<Address>(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString; using (OracleCommand command = new OracleCommand(sql.ToString(), connection)) { command.Parameters.Add(new OracleParameter("id", customer_id)); connection.Open(); try { OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { Address address = new Address() { Id = Int32.Parse(reader.GetValue(0).ToString()), street = reader.GetString(1), state = reader.GetString(2), zip = reader.GetString(3), country = reader.GetString(4), address_type = reader.GetString(5), city = reader.GetString(6) }; addresses.Add(address); } return addresses; } catch (Exception ex) { throw new PlatformException(ex.Message); } } } }
public string updateAddress(Address address) { try { AddressDAO addressDAO = new AddressDAO(); return addressDAO.updateAddress(address); } catch (BusinessException e) { throw e; } catch (PlatformException e) { throw e; } catch (Exception e) { throw new PlatformException(e.Message); } }