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();
}
}
