public static bool UpdateInvoices(Invoice oldInvoice, Invoice newInvoice) { SqlConnection connection = TableclothDB.GetConnection(); string updateStatement = "UPDATE Invoices SET " + "BorrowerId = @NewBorrowerId, " + "ProductId = @NewProductId, " + "Quantity = @NewQuantity, " + "InvoiceDate = @NewInvoiceDate, " + "WHERE InvoiceId = @oldInvoiceId " + "AND BorrowerId = @OldBorrowerId " + "AND ProductId = @OldProductId " + "AND Quantity = @OldQuantity " + "AND InvoiceDate = @OldInvoiceDate "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@NewBorrowerId", newInvoice.BorrowerId); updateCommand.Parameters.AddWithValue( "@NewProductId", newInvoice.ProductId); updateCommand.Parameters.AddWithValue( "@NewQuantity", newInvoice.Quantity); updateCommand.Parameters.AddWithValue( "@NewInvoiceDate", newInvoice.InvoiceDate); updateCommand.Parameters.AddWithValue( "@OldInvoiceId", oldInvoice.InvoiceId); updateCommand.Parameters.AddWithValue( "@OldBorrowerId", oldInvoice.BorrowerId); updateCommand.Parameters.AddWithValue( "@OldProductId", oldInvoice.ProductId); updateCommand.Parameters.AddWithValue( "@OldQuantity", oldInvoice.Quantity); updateCommand.Parameters.AddWithValue( "@OldInvoiceDate", oldInvoice.InvoiceDate); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool UpdateBorrower(Borrowers oldBorrower, Borrowers newBorrower) { SqlConnection connection = TableclothDB.GetConnection(); string updateStatement = "UPDATE Borrowers SET " + "BorrowerFName = @NewBorrowerFName, " + "BorrowerLName = @NewBorrowerLName, " + "BorrowerPhoneNumber = @NewBorrowerPhoneNumber, " + "BorrowerEmail = @NewBorrowerEmail, " + "WHERE BorrowerId = @oldBorrowerId " + "AND BorrowerFName = @OldBorrowerFName " + "AND BorrowerLName = @OldBorrowerLName " + "AND BorrowerPhoneNumber = @OldBorrowerPhoneNumber " + "AND BorrowerEmail = @OldBorrowerEmail "; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@NewBorrowerFName", newBorrower.BorrowerFName); updateCommand.Parameters.AddWithValue( "@NewBorrowerLName", newBorrower.BorrowerLName); updateCommand.Parameters.AddWithValue( "@NewBorrowerPhoneNumber", newBorrower.BorrowerPhoneNumber); updateCommand.Parameters.AddWithValue( "@NewBorrowerEmail", newBorrower.BorrowerEmail); updateCommand.Parameters.AddWithValue( "@OldBorrowerId", oldBorrower.BorrowerId); updateCommand.Parameters.AddWithValue( "@OldBorrowerFName", oldBorrower.BorrowerFName); updateCommand.Parameters.AddWithValue( "@OldBorrowerLName", oldBorrower.BorrowerLName); updateCommand.Parameters.AddWithValue( "@OldBorrowerPhoneNumber", oldBorrower.BorrowerPhoneNumber); updateCommand.Parameters.AddWithValue( "@OldBorrowerEmail", oldBorrower.BorrowerEmail); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
private void PopulateProducts() { using (connection = TableclothDB.GetConnection()) using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ProductId, ProductName FROM Product", connection)) { DataTable nameTable = new DataTable(); adapter.Fill(nameTable); cmbProduct.DisplayMember = "ProductName"; cmbProduct.ValueMember = "ProductId"; cmbProduct.DataSource = nameTable; } }
private void PopulateBorrowers() { using (connection = TableclothDB.GetConnection()) using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT BorrowerId, BorrowerFName + BorrowerLName " + "as FullName FROM Borrowers", connection)) { DataTable nameTable = new DataTable(); adapter.Fill(nameTable); cmbBorrower.DisplayMember = "FullName"; cmbBorrower.ValueMember = "BorrowerId"; cmbBorrower.DataSource = nameTable; } }
public static bool DeleteInvoice(Invoice invoice) { SqlConnection connection = TableclothDB.GetConnection(); string deleteStatement = "DELETE FROM Invoices " + "WHERE InvoiceId = @InvoiceId " + "AND BorrowerId = @BorrowerId " + "AND ProductId = @ProductId " + "AND Quantity = @Quantity " + "AND InvoiceDate = @InvoiceDate "; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue( "@InvoiceId", invoice.InvoiceId); deleteCommand.Parameters.AddWithValue( "@BorrowerId", invoice.BorrowerId); deleteCommand.Parameters.AddWithValue( "@ProductId", invoice.ProductId); deleteCommand.Parameters.AddWithValue( "@Quantity", invoice.Quantity); deleteCommand.Parameters.AddWithValue( "@InvoiceDate", invoice.InvoiceDate); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool DeleteBorrower(Borrowers borrower) { SqlConnection connection = TableclothDB.GetConnection(); string deleteStatement = "DELETE FROM Borrowers " + "WHERE BorrowerId = @BorrowerId " + "AND BorrowerFName = @BorrowerFName " + "AND BorrowerLName = @BorrowerLName " + "AND BorrowerPhoneNumber = @BorrowerPhoneNumber " + "AND BorrowerEmail = @BorrowerEmail "; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue( "@BorrowerId", borrower.BorrowerId); deleteCommand.Parameters.AddWithValue( "@BorrowerFName", borrower.BorrowerFName); deleteCommand.Parameters.AddWithValue( "@BorrowerLName", borrower.BorrowerLName); deleteCommand.Parameters.AddWithValue( "@BorrowerPhoneNumber", borrower.BorrowerPhoneNumber); deleteCommand.Parameters.AddWithValue( "@BorrowerEmail", borrower.BorrowerEmail); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static Invoice GetInvoice(int InvoiceId) { SqlConnection connection = TableclothDB.GetConnection(); string selectStatement = "SELECT InvoiceId, BorrowerId, ProductId, Quantity, InvoiceDate" + "FROM Invoices " + "WHERE InvoiceId = @InvoiceId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@InvoiceID", InvoiceId); try { connection.Open(); SqlDataReader custReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (custReader.Read()) { Invoice invoice = new Invoice(); invoice.InvoiceId = (int)custReader["InvoiceId"]; invoice.BorrowerId = (int)custReader["BorrowerId"]; invoice.ProductId = (int)custReader["ProductId"]; invoice.Quantity = (int)custReader["Quantity"]; invoice.InvoiceDate = (DateTime)custReader["InvoiceDate"]; return(invoice); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static Borrowers GetBorrower(int BorrowerId) { SqlConnection connection = TableclothDB.GetConnection(); string selectStatement = "SELECT BorrowerId, BorrowerFName, BorrowerLName, BorrowerPhoneNumber, BorrowerEmail" + "FROM Borrowers " + "WHERE BorrowerId = @BorrowerId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@BorrowerId", BorrowerId); try { connection.Open(); SqlDataReader custReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (custReader.Read()) { Borrowers borrower = new Borrowers(); borrower.BorrowerId = (int)custReader["BorrowerId"]; borrower.BorrowerFName = custReader["BorrowerFName"].ToString(); borrower.BorrowerLName = custReader["BorrowerLName"].ToString(); borrower.BorrowerPhoneNumber = custReader["BorrowerPhoneNumber"].ToString(); borrower.BorrowerEmail = custReader["BorrowerEmail"].ToString(); return(borrower); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static int AddInvoice(Invoice invoice) { SqlConnection connection = TableclothDB.GetConnection(); string insertStatement = "INSERT Invoices" + "(BorrowerId, ProductId, Quantity, InvoiceDate)" + "VALUES (@BorrowerId, @ProductId, @Quantity, @InvoiceDate)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@BorrowerId", invoice.BorrowerId); insertCommand.Parameters.AddWithValue( "@ProductId", invoice.ProductId); insertCommand.Parameters.AddWithValue( "@Quantity", invoice.Quantity); insertCommand.Parameters.AddWithValue( "@InvoiceDate", invoice.InvoiceDate); try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Invoices') FROM Invoices"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int customerID = Convert.ToInt32(selectCommand.ExecuteScalar()); return(customerID); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static int AddBorrower(Borrowers borrower) { SqlConnection connection = TableclothDB.GetConnection(); string insertStatement = "INSERT Borrowers" + "(BorrowerFName, BorrowerLName, BorrowerPhoneNumber, BorrowerEmail)" + "VALUES (@BorrowerFName, @BorrowerLName, @BorrowerPhoneNumber, @BorrowerEmail)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@BorrowerFName", borrower.BorrowerFName); insertCommand.Parameters.AddWithValue( "@BorrowerLName", borrower.BorrowerLName); insertCommand.Parameters.AddWithValue( "@BorrowerPhoneNumber", borrower.BorrowerPhoneNumber); insertCommand.Parameters.AddWithValue( "@BorrowerEmail", borrower.BorrowerEmail); try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Borrowers') FROM Borrowers"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int customerID = Convert.ToInt32(selectCommand.ExecuteScalar()); return(customerID); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
private void PopulateInvoice() { /* query to get the invoices matching to the selected borrower from the combo box, * using an alias so I can refference that borrowerId value in the PopulateNames method*/ string query = "SELECT *" + "FROM Invoices a INNER JOIN Borrowers b ON a.BorrowerId = b.BorrowerId " + "WHERE b.BorrowerId = @BorrowerId"; using (connection = TableclothDB.GetConnection()) using (SqlCommand command = new SqlCommand(query, connection)) /* sqlcommand is the same as a data adapter except it allows parameters, * and thats why we pass the query and connection there first unlike by populateName*/ using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@BorrowerId", cmbChooseBrrwr.SelectedValue); DataTable invoiceTable = new DataTable(); adapter.Fill(invoiceTable); lstInvoiceId.DisplayMember = "InvoiceId"; lstInvoiceId.ValueMember = "InvoiceId"; lstInvoiceId.DataSource = invoiceTable; lstBorrowerId.DisplayMember = "BorrowerId"; lstBorrowerId.ValueMember = "BorrowerId"; lstBorrowerId.DataSource = invoiceTable; lstProductId.DisplayMember = "ProductId"; lstProductId.ValueMember = "ProductId"; lstProductId.DataSource = invoiceTable; lstQuantity.DisplayMember = "Quantity"; lstQuantity.ValueMember = "Quantity"; lstQuantity.DataSource = invoiceTable; lstDate.DisplayMember = "InvoiceDate"; lstDate.ValueMember = "InvoiceDate"; lstDate.DataSource = invoiceTable; } }
private void btnDeleteInvoice_Click(object sender, EventArgs e) { bool works; string message = "Are you sure you want to delete invoice" + lstInvoiceId.SelectedValue + "?"; string command1 = "SELECT *" + "FROM Invoices a INNER JOIN Borrowers b ON a.BorrowerId = b.BorrowerId " + "WHERE b.BorrowerId = @BorrowerId"; DialogResult button = MessageBox.Show(message, "Confirm Delete", MessageBoxButtons.YesNo); if (button == DialogResult.Yes) { using (connection = TableclothDB.GetConnection()) using (SqlCommand command = new SqlCommand(command1, connection)) using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@BorrowerId", lstInvoiceId.SelectedValue); DataTable invoiceTable = new DataTable(); adapter.Fill(invoiceTable); string deleteStatement = "DELETE FROM Invoices " + "WHERE InvoiceId = @InvoiceId " + "AND BorrowerId = @BorrowerId " + "AND ProductId = @ProductId " + "AND Quantity = @Quantity " + "AND InvoiceDate = @InvoiceDate "; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue( "@InvoiceId", Convert.ToInt32(lstInvoiceId.SelectedValue)); deleteCommand.Parameters.AddWithValue( "@BorrowerId", Convert.ToInt32(lstBorrowerId.SelectedValue)); deleteCommand.Parameters.AddWithValue( "@ProductId", Convert.ToInt32(lstProductId.SelectedValue)); deleteCommand.Parameters.AddWithValue( "@Quantity", Convert.ToInt32(lstQuantity.SelectedValue)); deleteCommand.Parameters.AddWithValue( "@InvoiceDate", Convert.ToDateTime(lstDate.SelectedValue)); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { works = true; } else { works = false; } if (works == false) { MessageBox.Show("Problem with SQL Command!"); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } } PopulateInvoice(); } }