private void btDelIsa_Click(object sender, RoutedEventArgs e)
{
myConn.Open();
//create query
OleDbCommand myCmd = myConn.CreateCommand();
//set passed SQL string to query
myCmd.CommandText = @"DELETE FROM account WHERE custID = @custID AND accID = @accID";
myCmd.Parameters.AddWithValue("custID"
, lbcustID.Content);
dataItem item = (dataItem)dgCustAccounts.SelectedItem;
myCmd.Parameters.AddWithValue("accID"
, item?.accID ?? "1=55");
try
{
myCmd.ExecuteReader();
//close connections (prevents exception errors!)
myConn.Close();
Filler = new datagridFiller(dgCustAccounts);
Filler.fillDataGrid("select * from account WHERE custID = " + (customer?.id ?? "1=2") + ";");
}
catch (Exception ex)
{
//close connections (prevents exception errors!)
myConn.Close();
MessageBox.Show(ex.ToString());
}
myConn.Close();
}
/// <summary>
/// Show transactions of selected account
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgCustAccounts_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
Filler = new datagridFiller(dgCustTransactions);
dataItem acc = (dataItem)dgCustAccounts.SelectedItem;
Filler.fillDataGrid("select * from tranx WHERE accID = " + (acc?.accID ?? "1=2") + ";");
}
private void btTransfer_Click(object sender, RoutedEventArgs e)
{
// validation
if (CBAccount.SelectedIndex == -1 || dgCustAccounts.SelectedIndex == -1)
{
return;
}
else if (double.TryParse(txtAmount.Text, out double x) == false)
{
return;
}
myConn.Open();
/*Command can't perform multiple queries, thus multiple commands are required*/
//create query
OleDbCommand myCmd = myConn.CreateCommand();
OleDbCommand myCmd2 = myConn.CreateCommand();
//set passed SQL string to query
myCmd.CommandText = @"UPDATE account SET balance = balance - @transAmnt1 WHERE accID = @accIDFrom;";
//parameter passing
myCmd.Parameters.AddWithValue("transAmnt1"
, txtAmount.Text);
dataItem item = (dataItem)dgCustAccounts.SelectedItem;
myCmd.Parameters.AddWithValue("accIDFrom"
, item?.accID ?? "1=55");
//set passed SQL string to query
myCmd2.CommandText = @"UPDATE account SET balance = balance + @transAmnt2 WHERE accID = @accIDTo;";
//parameter passing
myCmd2.Parameters.AddWithValue("transAmnt1"
, txtAmount.Text);
myCmd2.Parameters.AddWithValue("accIDTo"
, CBAccount.SelectedValue);
try
{
//execute query
myCmd.ExecuteNonQuery();
myCmd2.ExecuteNonQuery();
//close connections (prevents exception errors!)
myConn.Close();
Filler = new datagridFiller(dgCustAccounts);
Filler.fillDataGrid("select * from account WHERE custID = " + (customer?.id ?? "1=2") + ";");
}
catch (Exception ex)
{
//close connections (prevents exception errors!)
myConn.Close();
MessageBox.Show(ex.ToString());
}
myConn.Close();
}
private void SearchBox_TextChanged(object sender, TextChangedEventArgs e)
{
//if search box is empty or has default value then show all customers
if (txtSearchCustomers.Text == "" || txtSearchCustomers.Text == "Search")
{
filler?.fillDataGrid("select * from customer;");
return;
}
else
{
/* Fill data grid with records where fname or fname match searchbox contents.
* Includes parameter passing to prevent SQL injection
*/
filler?.fillDataGrid(@"select * from customer where"
+ @" [firstName] like @goGetFirstName or "
+ @"[lastName] = @goGetLastName;",
new List <string> {
"goGetFirstName", "goGetLastName"
},
new List <TextBox> {
txtSearchCustomers, txtSearchCustomers
});
}
}
public customerAccount(MainWindow window, dataItem dataItem)
{
InitializeComponent();
//set local objects as passed values.
winSender = window;
customer = dataItem;
//Display customer details
lbcustID.Content = customer?.id?.ToString() ?? "id = null";
txtCustFName.Text = customer?.fName?.ToString() ?? "fname = null";
txtCustLname.Text = customer?.lName?.ToString() ?? "lname = null";
txtDob.Text = customer?.DoB?.ToString() ?? "dob = null";
txtNatins.Text = customer?.natins?.ToString() ?? "natins = null";
txtEmail.Text = customer?.email?.ToString() ?? "email = null";
txtAllowence.Text = customer?.allowence?.ToString() ?? "allowance = null";
//add columns to account data grid
Column.AddColumn("account id", "accID", dgCustAccounts);
Column.AddColumn("customer id", "custID", dgCustAccounts);
Column.AddColumn("product id", "prodID", dgCustAccounts);
Column.AddColumn("balance", "balance", dgCustAccounts);
Column.AddColumn("accrued", "accrued", dgCustAccounts);
Column.AddColumn("active", "active", dgCustAccounts);
//add columns to transaction data grid
Column.AddColumn("Transaction ID", "tranxID", dgCustTransactions);
Column.AddColumn("account ID", "accID", dgCustTransactions);
Column.AddColumn("Action", "action", dgCustTransactions);
Column.AddColumn("Amount", "amnt", dgCustTransactions);
Column.AddColumn("DateTime", "theEvent", dgCustTransactions);
//Fill data grids; data base query
Filler = new datagridFiller(dgCustAccounts);
Filler.fillDataGrid("select * from account WHERE custID = " + (customer?.id ?? "1=2") + ";");
//fill product combo box
fillCombo(CBProduct, products, "SELECT ProdID, theName FROM Product;", "prodName", "id");
//fill account combo box
fillCombo(CBAccount, accounts, "SELECT accID FROM account WHERE custID = " + lbcustID.Content + ";", "id", "id");
}
private void btAddIsa_Click(object sender, RoutedEventArgs e)
{
//validation
if (CBProduct.SelectedIndex == -1)
{
return;
}
myConn.Open();
//create query
OleDbCommand myCmd = myConn.CreateCommand();
//set passed SQL string to query
myCmd.CommandText = @"INSERT INTO account (custID, prodID, active) VALUES (@custID, @prodID, true);";
myCmd.Parameters.AddWithValue("custID"
, lbcustID.Content);
myCmd.Parameters.AddWithValue("prodID"
, CBProduct.SelectedValue);
try
{
myCmd.ExecuteReader();
//close connections (prevents exception errors!)
myConn.Close();
Filler = new datagridFiller(dgCustAccounts);
Filler.fillDataGrid("select * from account WHERE custID = " + (customer?.id ?? "1=2") + ";");
}
catch (Exception ex)
{
//close connections (prevents exception errors!)
myConn.Close();
MessageBox.Show(ex.ToString());
}
myConn.Close();
//fill account combo box
fillCombo(CBAccount, accounts, "SELECT accID FROM account WHERE custID = " + lbcustID.Content + ";", "id", "id");
}
/// <summary>
/// Fill data grids
/// </summary>
public void fillDataGrids()
{
filler.fillDataGrid("select * from customer;");
fillerProd.fillDataGrid("select * from product;");
}
