//Save Transaction Method
public void SaveTransaction()
{
//blank the error message
errorMessageLabelTransaction.Content = "";
//current record is the selected record(#) in the listbox
CurrentRecord = TransactionsListBox.SelectedIndex;
//current transaction is the current selected item in the transaction listbox
CurrentTransaction = (AccountTransaction)TransactionsListBox.SelectedItem;
//check validation if a record is selected
if (CurrentRecord == -1)
{
//if no record is selected, display this error message
errorMessageLabelTransaction.Content = "You must select an existing transaction to save.";
errorMessageLabelTransaction.Foreground = Brushes.Red;
return;
}
//if validation is correct, proceed
//remove current transaction from the listbox
TransactionList.Remove(CurrentTransaction);
//update the new data
CurrentTransaction.TransactionDate = Convert.ToDateTime(txbTransactionDate.Text);
CurrentTransaction.TransactionAmount = Convert.ToDecimal(txbTransactionAmount.Text);
//declare a connection object
using (SqlConnection connection = new SqlConnection())
{
//point connection to database
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//open the connection
connection.Open();
// Create a SQL command object.
// setup a SQL Command - here Update
string sql = $"Update AccountTransactions Set " +
$"TransactionDate = '{CurrentTransaction.TransactionDate}', " +
$"TransactionAmount = '{CurrentTransaction.TransactionAmount}' " +
$"Where TransactionNumber = '{CurrentTransaction.TransactionNumber}';";
// set the SQL Command string on the connection
// in a new connection object
SqlCommand myCommand = new SqlCommand(sql, connection);
// run the UPDATE command - use command.ExecuteNonQuery()
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
}
//call the load transaction method to update the new data and repopulate the list array
LoadTransaction();
//display the updated data
DisplayTransactionRecord();
}
}
//Display Transactions for the Customer selected
public void DisplayTransactionRecord()
{
//variable
int value = 0;
//proceed only if therre is no selected item in the transaction lisbox and the customer listbox has a selected item
if (TransactionsListBox.SelectedItem == null && CustomersListBox.SelectedItem != null)
{
//clear transaction listbox
TransactionsListBox.Items.Clear();
//loop and populate the listbox
for (value = 0; value < TransactionList.Count; value++)
{
CurrentCustomer = (CustomerAccount)CustomersListBox.SelectedItem;
CurrentTransaction = TransactionList[value];
if (CurrentCustomer.AccountNumber == CurrentTransaction.AccountNumber)
{
TransactionsListBox.Items.Add(CurrentTransaction);
}
}
}
//current transaction is the slected item in the lisbox
CurrentTransaction = (AccountTransaction)TransactionsListBox.SelectedItem;
//if there is a selected item in the transaction listbox, proceed
if (TransactionsListBox.SelectedItem != null)
{
//assign the selected data to the textboxes
txbTransactionAmount.Text = Convert.ToString(CurrentTransaction.TransactionAmount);
txbTransactionDate.Text = Convert.ToString(CurrentTransaction.TransactionDate);
txbTransactionNumber.Text = Convert.ToString(CurrentTransaction.TransactionNumber);
}
else
{
//empty the form
txbTransactionAmount.Text = "";
txbTransactionDate.Text = "";
txbTransactionNumber.Text = "";
}
//get the balance
getBalance();
}
//Load Existing Transaction Data Method
public void LoadTransaction()
{
//clear listbox and List array
TransactionList.Clear();
TransactionsListBox.Items.Clear();
//declare a connection object
using (SqlConnection connection = new SqlConnection())
{
//point connection to database
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//open the connection
connection.Open();
// Create a SQL command object.
// setup a SQL Command - here SELECT
string sql = "SELECT* FROM AccountTransactions Order By AccountNumber; ";
// set the SQL Command string on the connection
// in a new connection object
SqlCommand myCommand1 = new SqlCommand(sql, connection);
// Run the command: into a data reader using the ExecuteReader() method.
using (SqlDataReader myDataReader1 = myCommand1.ExecuteReader())
{
// Loop over the results. myDataReader.Read returns false if we run out of rows
while (myDataReader1.Read())
{
// each colum of the datareader goes into one property of a AccountTransaction Object
AccountTransaction LoadTransaction = new AccountTransaction(myDataReader1.GetInt32(0),
myDataReader1.GetInt32(1),
myDataReader1.GetDateTime(2),
myDataReader1.GetDecimal(3));
//add the Customer to the List<Contact>
TransactionList.Add(LoadTransaction);
}
}
}
}
//Method to add a trransaction
private void AddTransaction(int TransactionNumber, int AccountNumber, DateTime TransactionDate, decimal TransactionAmount)
{
//declare a connection object
using (SqlConnection connection = new SqlConnection())
{
//new class object
AccountTransaction NewTransaction = new AccountTransaction(TransactionNumber, AccountNumber, TransactionDate, TransactionAmount);
//point connection to database
connection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomerAccounts.mdf;Integrated Security=True";
//open the connection
connection.Open();
// Format and execute SQL statement.
// setup a SQL Command - here insert
string sql = "Insert Into AccountTransactions" +
"(TransactionNumber, AccountNumber, TransactionDate, TransactionAmount) Values " +
$"('{NewTransaction.TransactionNumber}', " +
$"'{NewTransaction.AccountNumber}', " +
$"'{NewTransaction.TransactionDate}', " +
$"'{NewTransaction.TransactionAmount}' )";
// set the SQL Command string on the connection
// in a new connection object
// run the Insert command - use command.ExecuteNonQuery()
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
}
TransactionList.Add(NewTransaction);
// add the contact to the listbox
TransactionsListBox.Items.Add(NewTransaction);
//call the LoadCustomer method
LoadTransaction();
//call the DisplayRecord method
DisplayTransactionRecord();
}
}