public IEnumerable <InvoiceDTO> GetInvoices() { string queryString = "SELECT * FROM dbo.Invoice"; List <InvoiceDTO> results = new List <InvoiceDTO>(); InvoiceDTO invoice; AccountDTO account; ShippmentDTO shipping; try { using (SqlConnection con = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(queryString, con)) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { invoice = new InvoiceDTO(); account = new AccountDTO(); shipping = new ShippmentDTO(); invoice = GenerateInvoice(reader, invoice, account, shipping); Debug.Print("InvoiceDAL: /FindAll/ " + invoice.GetID()); //add data objects to result-list results.Add(invoice); } } } } catch (Exception e) { e.GetBaseException(); } return(results); }
public InvoiceDTO GetInvoiceBy(int id) { string queryString = "SELECT * FROM dbo.Invoice WHERE invoiceID = @id"; InvoiceDTO invoice = new InvoiceDTO(); try { using (SqlConnection con = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(queryString, con)) { cmd.Parameters.AddWithValue("invoiceID", SqlDbType.Int).Value = id; con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { AccountDTO account = new AccountDTO(); ShippmentDTO shipping = new ShippmentDTO(); invoice = GenerateInvoice(reader, invoice, account, shipping); //return product instance as data object Debug.Print("InvoiceDAL: /FindByID/ " + invoice.GetID()); } } } } catch (Exception e) { e.GetBaseException(); Debug.Print(e.ToString()); } return(invoice); }
private static InvoiceDTO GenerateInvoice(SqlDataReader reader, InvoiceDTO invoice, AccountDTO account, ShippmentDTO shipping) { invoice.SetID(Convert.ToInt32(reader["invoiceID"])); account.SetID(Convert.ToInt32(reader["accountID"])); shipping.SetID(Convert.ToInt32(reader["shippingID"])); invoice.SetCustomer(account); invoice.SetShippment(shipping); invoice.SetQuantity(Convert.ToInt32(reader["totalQuantity"])); invoice.SetShippingCost(Convert.ToDecimal(reader["shippingCost"])); invoice.SetTotal(Convert.ToDecimal(reader["totalProductCost"])); invoice.SetTax(Convert.ToDecimal(reader["totalTax"])); invoice.SetTotal(Convert.ToDecimal(reader["totalAmount"])); invoice.SetOrderDate(Convert.ToDateTime(reader["orderDate"])); invoice.SetPaymentDate(Convert.ToDateTime(reader["paymentDate"])); invoice.SetStatus(Convert.ToInt32(reader["paymentStatus"])); invoice.SetEmail(reader["customerMail"].ToString()); invoice.SetArrivalDate(Convert.ToDateTime(reader["arrivalDate"])); invoice.SetPostDate(Convert.ToDateTime(reader["postageDate"])); Debug.Print("InvoiceDAL: Invoice ID " + invoice.GetID()); return(invoice); }
public IEnumerable <InvoiceDTO> FindByStatus(int accountID, int paymentStatus) { string queryString = "SELECT * FROM dbo.Invoice WHERE accountID = @accountID AND paymentStatus = @paymentStatus"; List <InvoiceDTO> results = new List <InvoiceDTO>(); InvoiceDTO invoice; AccountDTO account; ShippmentDTO shipping; try { using (SqlConnection con = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(queryString, con)) { cmd.Parameters.AddWithValue("@accountID", SqlDbType.Int).Value = accountID; cmd.Parameters.AddWithValue("@paymentStatus", SqlDbType.Int).Value = paymentStatus; cmd.CommandType = CommandType.Text; con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { invoice = new InvoiceDTO(); account = new AccountDTO(); shipping = new ShippmentDTO(); invoice = GenerateInvoice(reader, invoice, account, shipping); Debug.Print("InvoiceDAL: /FindInvoiceByStatus/ " + invoice.GetID()); //add data objects to result-list results.Add(invoice); } } } } catch (Exception e) { e.GetBaseException(); } return(results); }
public IEnumerable <InvoiceDTO> FindAllByCustomer(int accountID) { string queryString = "SELECT * FROM dbo.Invoice WHERE accountID = @accountID"; List <InvoiceDTO> results = new List <InvoiceDTO>(); InvoiceDTO invoice; AccountDTO account; ShippmentDTO shipping; try { using (SqlConnection con = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(queryString, con)) { cmd.Parameters.AddWithValue("@accountID", accountID); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { invoice = new InvoiceDTO(); account = new AccountDTO(); shipping = new ShippmentDTO(); invoice = GenerateInvoice(reader, invoice, account, shipping); //return product instance as data object Debug.Print("InvoiceDAL: /FindInvoiceBy/ " + invoice.GetID()); //add data objects to result-list results.Add(invoice); } } } } catch (Exception e) { e.GetBaseException(); } return(results); }