/** Crea un nuevo cliente */ public string createCustomer(Customer customer) { StringBuilder sql = new StringBuilder(); sql.Append("INSERT INTO CUSTOMERS.CUSTOMER (Id, first_name, last_name, phone_number, "); sql.Append("email, creditcard_number, creditcard_type, status ) VALUES ( :Id, "); sql.Append(":first_name, :last_name, :phone_number, :email, :creditcard_number, "); sql.Append(":creditcard_type, :status)"); 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("Id", customer.Id)); command.Parameters.Add(new OracleParameter("first_name", customer.first_name)); command.Parameters.Add(new OracleParameter("last_name", customer.last_name)); command.Parameters.Add(new OracleParameter("phone_number", customer.phone_number)); command.Parameters.Add(new OracleParameter("email", customer.email)); command.Parameters.Add(new OracleParameter("creditcard_number", customer.creditcard_number)); command.Parameters.Add(new OracleParameter("creditcard_type", customer.creditcard_type)); command.Parameters.Add(new OracleParameter("status", customer.status)); command.ExecuteNonQuery(); createCustomerAddress(customer.Id, customer.address, connection, transaction); transaction.Commit(); } } catch (Exception ex) { throw new PlatformException(ex.Message); } } return "OK"; }
public string createCustomer(Customer customer) { try { customerDAO = new CustomerDAO(); return customerDAO.createCustomer(customer); } catch (BusinessException e) { throw e; } catch (PlatformException e) { throw e; } catch (Exception e) { throw new PlatformException(e.Message); } }
public string createCustomer(Customer customer) { try { customerBoundary = new CustomerBoundary(); return customerBoundary.createCustomer(customer); }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); } }
/** Actualiza los datos principales de un cliente */ public string updateCustomer(Customer customer) { StringBuilder sql = new StringBuilder(); sql.Append("UPDATE CUSTOMERS.CUSTOMER SET first_name = :first_name, last_name = :last_name, "); sql.Append("phone_number = :phone_number, email = :email, "); sql.Append("creditcard_number = :creditcard_number, creditcard_type = :creditcard_type, status = :status "); 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", customer.Id)); command.Parameters.Add(new OracleParameter("first_name", customer.first_name)); command.Parameters.Add(new OracleParameter("last_name", customer.last_name)); command.Parameters.Add(new OracleParameter("phone_number", customer.phone_number)); command.Parameters.Add(new OracleParameter("email", customer.email)); command.Parameters.Add(new OracleParameter("creditcard_number", customer.creditcard_number)); command.Parameters.Add(new OracleParameter("creditcard_type", customer.creditcard_type)); command.Parameters.Add(new OracleParameter("status", customer.status)); command.ExecuteNonQuery(); return "OK"; } } catch (Exception ex) { throw new PlatformException(ex.Message); } } }
/** Obtiene una lista de clientes @pagina numero de pagina a consultar @regPagina numero de registros por pagina */ public IList<Customer> getCustomers(int pagina, int regPagina) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT Id, first_name, last_name, phone_number, email, "); sql.Append("creditcard_number, creditcard_type, status FROM "); sql.Append("(SELECT ROW_NUMBER()Over(Order by CUSTOMERS.CUSTOMER.first_name ASC) As RowNum, "); sql.Append("Id, first_name, last_name, phone_number, email, "); sql.Append("creditcard_number, creditcard_type, status FROM CUSTOMERS.CUSTOMER) AS Resultado "); sql.Append("WHERE RowNum BETWEEN (:pagina -1) * :regPagina + 1 AND :pagina * :regPagina"); IList<Customer> customers = new List<Customer>(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString; using (OracleCommand command = new OracleCommand(sql.ToString(), connection)) { command.Parameters.Add(new OracleParameter("pagina", pagina)); command.Parameters.Add(new OracleParameter("regPagina", regPagina)); try { connection.Open(); OracleDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Customer customer = new Customer() { Id = reader.GetString(0), first_name = reader.GetString(1), last_name = reader.GetString(2), phone_number = reader.GetString(3), email = reader.GetString(4), creditcard_number = reader.GetString(5), creditcard_type = reader.GetString(6), status = reader.GetString(7) }; customers.Add(customer); } return customers; } else { throw new BusinessException("Customers result is empty"); } } catch (Exception ex) { throw new PlatformException(ex.Message, ex); } } } }
public Customer getCustomerByEmail(string email) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT Id, first_name, last_name, phone_number, email, "); sql.Append("creditcard_number, creditcard_type, status FROM CUSTOMERS.CUSTOMER WHERE email=:email"); Customer customer = new Customer(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString; using (OracleCommand command = new OracleCommand(sql.ToString(), connection)) { command.Parameters.Add(new OracleParameter("email", email)); connection.Open(); try { OracleDataReader reader = command.ExecuteReader(); if (reader.Read()) { customer = new Customer() { Id = reader.GetString(0), first_name = reader.GetString(1), last_name = reader.GetString(2), phone_number = reader.GetString(3), email = reader.GetString(4), creditcard_number = reader.GetString(5), creditcard_type = reader.GetString(6), status = reader.GetString(7) }; return customer; } else { throw new BusinessException("The customer not exist"); } } catch (PlatformException ex) { throw new PlatformException(ex.Message, ex); } catch (BusinessException ex) { throw ex; } } } }