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