/// <summary> /// Deletes all the bill rows from the given bill (from the database). /// </summary> /// <param name="bill">The bill which rows are wanted to be removed.</param> public static void DeleteAllBillRowsFromBill(Bill bill) { string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString; using (MySqlConnection con = new MySqlConnection(connectionString)) { try { con.Open(); MySqlCommand command = new MySqlCommand(); command.Connection = con; command.CommandText = "DELETE FROM billRows WHERE billId=@id"; command.Parameters.AddWithValue("@id", bill.BillId); command.ExecuteNonQuery(); } catch (MySqlException ex) { throw ex; } finally { con.Close(); } } }
/// <summary> /// Saves the bill rows from the specified bill in to the database. /// </summary> /// <param name="bill"></param> public static void SaveBillRows(Bill bill) { string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString; using (MySqlConnection con = new MySqlConnection(connectionString)) { try { con.Open(); foreach (BillRow row in bill.Rows) { MySqlCommand command = new MySqlCommand(); command.Connection = con; command.CommandText = "INSERT INTO billRows (productName, price, amount, unit, ale, accountId, accountName, billId, vat) VALUES (@name, @price, @amount, @unit, @ale, @accountId, @accountName, @billId, @vat)"; command.Parameters.AddWithValue("@name", row.ProductName); command.Parameters.AddWithValue("@price", row.ProductPrice); command.Parameters.AddWithValue("@amount", row.ProductAmount); command.Parameters.AddWithValue("@unit", row.Unit); command.Parameters.AddWithValue("@ale", row.Ale); command.Parameters.AddWithValue("@vat", row.Vat); command.Parameters.AddWithValue("@accountId", row.AccountId); command.Parameters.AddWithValue("@accountName", row.AccountName); command.Parameters.AddWithValue("@billId", bill.BillId); command.ExecuteNonQuery(); } } catch (MySqlException ex) { throw ex; } finally { con.Close(); } } }
/// <summary> /// Saves the given bill in to the database. /// </summary> /// <param name="bill">Bill to be saved.</param> /// <returns>Saved or not.</returns> public static bool SaveBill(Bill bill) { string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString; using (MySqlConnection con = new MySqlConnection(connectionString)) { try { con.Open(); MySqlCommand command = new MySqlCommand(); command.Connection = con; command.CommandText = "INSERT INTO bills (customerName, customerAddress, billDate, dueDate, billId, referenceNumber, documentId, paymentType, paid) VALUES (@name, @address, @billDate, @dueDate, @billId, @referenceNumber, @documentId, @paymentType, @paid)"; command.Parameters.AddWithValue("@name", bill.Customer); command.Parameters.AddWithValue("@address", bill.Address); command.Parameters.AddWithValue("@billDate", bill.BillDate); command.Parameters.AddWithValue("@dueDate", bill.DueDate); command.Parameters.AddWithValue("@billId", bill.BillId); command.Parameters.AddWithValue("@referenceNumber", bill.ReferenceNumber); command.Parameters.AddWithValue("@documentId", 0); command.Parameters.AddWithValue("@paymentType", bill.PaymentType); command.Parameters.AddWithValue("@paid", bill.Paid); command.ExecuteNonQuery(); SaveBillRows(bill); } catch (MySqlException ex) { throw ex; } finally { con.Close(); } } return true; }
/// <summary> /// Returns the bill object retrieved from the database with the specified id. /// </summary> /// <param name="billId">Bill's id.</param> /// <returns>The bill.</returns> public static Bill GetBillById(int billId) { Bill bill = new Bill(); string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString; using (MySqlConnection con = new MySqlConnection(connectionString)) { MySqlCommand command = new MySqlCommand(); try { con.Open(); command.Connection = con; command.CommandText = "SELECT * FROM bills WHERE billId=@billId"; command.Prepare(); command.Parameters.AddWithValue("@billId", billId); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { bill.Customer = reader.GetString("customerName"); bill.Address = reader.GetString("customerAddress"); bill.BillDate = reader.GetDateTime("billDate"); bill.DueDate = reader.GetDateTime("dueDate"); bill.BillId = reader.GetInt16("billId"); bill.PaymentType = reader.GetInt16("paymentType"); bill.ReferenceNumber = reader.GetString("referenceNumber"); bill.Paid = reader.GetBoolean("paid"); bill.Rows = GetBillRowsByBillId(bill.BillId); } } catch (MySqlException ex) { throw ex; } finally { con.Close(); } } return bill; }
/// <summary> /// Returns the list containing all bills saved in to the database. /// </summary> /// <returns>The list of all bills</returns> public static List<Bill> GetAllBills() { List<Bill> documents = new List<Bill>(); string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString; using (MySqlConnection con = new MySqlConnection(connectionString)) { MySqlCommand command = new MySqlCommand(); try { con.Open(); command.Connection = con; command.CommandText = "SELECT * FROM bills"; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Bill bill = new Bill(); bill.Customer = reader.GetString("customerName"); bill.Address = reader.GetString("customerAddress"); bill.BillDate = reader.GetDateTime("billDate"); bill.DueDate = reader.GetDateTime("dueDate"); bill.BillId = reader.GetInt16("billId"); bill.PaymentType = reader.GetInt16("paymentType"); bill.Paid = reader.GetBoolean("paid"); bill.ReferenceNumber = reader.GetString("referenceNumber"); bill.Rows = GetBillRowsByBillId(bill.BillId); documents.Add(bill); } } catch (MySqlException ex) { throw ex; } finally { con.Close(); } } return documents; }