// Method to push ALL Customer objects into database
public void InsertAllCustomers()
{
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
conn.Open();
foreach (var customer in BankingSys.Instance().Customers)
{
var PopulateCustomer = conn.CreateCommand();
PopulateCustomer.CommandText = "insert into customer (CustomerID, Name, Address, City, PostCode) values (@customerID, @name, @address, @city, @postCode)";
PopulateCustomer.Parameters.AddWithValue("customerID", customer.CustomerID);
PopulateCustomer.Parameters.AddWithValue("name", customer.Name);
// Some parameters requires check for NULL values
if (customer.Address == null)
{
PopulateCustomer.Parameters.AddWithValue("address", "NULL");
}
else
{
PopulateCustomer.Parameters.AddWithValue("address", customer.Address);
}
if (customer.City == null)
{
PopulateCustomer.Parameters.AddWithValue("city", "NULL");
}
else
{
PopulateCustomer.Parameters.AddWithValue("city", customer.City);
}
if (customer.City == null)
{
PopulateCustomer.Parameters.AddWithValue("postCode", "NULL");
}
else
{
PopulateCustomer.Parameters.AddWithValue("postCode", customer.PostCode);
}
PopulateCustomer.ExecuteNonQuery();
}
}
catch (Exception se)
{
Console.WriteLine("Exception: {0}", se.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
// Method to push ALL Login objects into database
public void InsertAllLogins()
{
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
conn.Open();
foreach (var login in BankingSys.Instance().Logins)
{
var PopulateLogin = conn.CreateCommand();
PopulateLogin.CommandText = "insert into login (LoginID, CustomerID, PasswordHash) values (@loginID, @customerID, @passwordHash)";
PopulateLogin.Parameters.AddWithValue("loginID", login.LoginID);
PopulateLogin.Parameters.AddWithValue("customerID", login.CustomerID);
PopulateLogin.Parameters.AddWithValue("passwordHash", login.PasswordHash);
PopulateLogin.ExecuteNonQuery();
}
}
catch (Exception se)
{
Console.WriteLine("Exception: {0}", se.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
public static BankingSys Instance()
{
if (instance == null)
{
instance = new BankingSys();
}
return(instance);
}
// Method to push ALL Transaction objects into database
public void InsertAllTransactions()
{
SqlConnection conn = new SqlConnection(ConnectionString);
foreach (var customer in BankingSys.Instance().Customers)
{
foreach (var account in customer.Accounts)
{
foreach (var transaction in account.Transactions)
{
try
{
conn.Open();
var populateTransactions = conn.CreateCommand();
populateTransactions.CommandText = "insert into [transaction] (TransactionType, AccountNumber, DestinationAccountNumber, Amount, TransactionTimeUTC) " +
"values (@transactionType, @accountNumber, @destinationAccountNumber, @amount, @transactionTimeUTC)";
populateTransactions.Parameters.AddWithValue("transactionType", transaction.TransactionType);
populateTransactions.Parameters.AddWithValue("accountNumber", account.AccountNumber);
populateTransactions.Parameters.AddWithValue("destinationAccountNumber", account.AccountNumber);
populateTransactions.Parameters.AddWithValue("amount", account.Balance);
populateTransactions.Parameters.AddWithValue("transactionTimeUTC", transaction.TransactionTimeUTC);
populateTransactions.ExecuteNonQuery();
}
catch (SqlException se)
{
Console.WriteLine("Exception: {0}", se.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
}
// Method to push ALL Account objects into database
public void InsertAllAccounts()
{
SqlConnection conn = new SqlConnection(ConnectionString);
foreach (var customer in BankingSys.Instance().Customers)
{
foreach (var account in customer.Accounts)
{
try
{
conn.Open();
var populateAccounts = conn.CreateCommand();
populateAccounts.CommandText = "insert into account (AccountNumber, AccountType, CustomerID, Balance) values (@accountNumber, @accountType, @customerID, @balance)";
populateAccounts.Parameters.AddWithValue("accountNumber", account.AccountNumber);
populateAccounts.Parameters.AddWithValue("accountType", account.AccountType);
populateAccounts.Parameters.AddWithValue("customerID", account.CustomerID);
populateAccounts.Parameters.AddWithValue("balance", account.Balance);
populateAccounts.ExecuteNonQuery();
}
catch (SqlException se)
{
Console.WriteLine("Exception: {0}", se.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
// NEED: Method to map Login Table into Login object in the program
public void DownloadLoginArray()
{
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
conn.Open();
var LoginQuery = "select * from login";
SqlDataReader ReadLogin;
using (SqlCommand LoginCmd = new SqlCommand(LoginQuery, conn))
{
using (ReadLogin = LoginCmd.ExecuteReader())
{
// Create new login object and add it into Logins List belonging to BankingSys
while (ReadLogin.Read())
{
Logins login = new Logins(ReadLogin["LoginID"].ToString(), int.Parse(ReadLogin["CustomerID"].ToString()), ReadLogin["PasswordHash"].ToString());
BankingSys.Instance().Logins.Add(login);
}
}
}
}
catch (SqlException se)
{
Console.WriteLine("Exception: {0}", se.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
public Facade()
{
_bankingSys = BankingSys.Instance();
}
// NEED: Method to map Customer, Account and Transaction table into corresponding objects in the program
public void DownloadCustomerArray()
{
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
conn.Open();
string CustomerQuery = "select * from customer";
SqlDataReader ReadLogin;
SqlCommand LoginCmd;
SqlDataReader ReadCustomer;
SqlDataReader ReadAccount;
SqlCommand AccountCmd;
SqlCommand TransactionCmd;
SqlDataReader ReadTransaction;
using (SqlCommand CustomerCmd = new SqlCommand(CustomerQuery, conn))
{
using (ReadCustomer = CustomerCmd.ExecuteReader())
{
while (ReadCustomer.Read())
{
Customer customer = new Customer(0, "", int.Parse(ReadCustomer["CustomerID"].ToString()), ReadCustomer["Name"].ToString(),
ReadCustomer["Address"].ToString(), ReadCustomer["City"].ToString(), ReadCustomer["PostCode"].ToString());
BankingSys.Instance().Customers.Add(customer);
}
}
}
foreach (var customer in BankingSys.Instance().Customers)
{
using (LoginCmd = new SqlCommand("select loginID, passwordhash from login where customerID = " + customer.CustomerID, conn))
{
using (ReadLogin = LoginCmd.ExecuteReader())
{
while (ReadLogin.Read())
{
customer.LoginID = int.Parse(ReadLogin["loginID"].ToString());
customer.Password = ReadLogin["PasswordHash"].ToString();
}
}
}
}
foreach (var customer in BankingSys.Instance().Customers)
{
using (AccountCmd = new SqlCommand("select accountnumber, accounttype, balance from account where customerID = " + customer.CustomerID, conn))
{
using (ReadAccount = AccountCmd.ExecuteReader())
{
while (ReadAccount.Read())
{
Account account;
if (ReadAccount["AccountType"].ToString().Contains('S'))
{
account = AccountFactory.Create('S') as SavingsAccount;
account.AccountNumber = int.Parse(ReadAccount["AccountNumber"].ToString());
account.CustomerID = customer.CustomerID;
account.Balance = double.Parse(ReadAccount["Balance"].ToString());
customer.Accounts.Add(account);
}
else if (ReadAccount["AccountType"].ToString().Contains('C'))
{
account = AccountFactory.Create('C') as CheckingAccount;
account.AccountNumber = int.Parse(ReadAccount["AccountNumber"].ToString());
account.CustomerID = customer.CustomerID;
account.Balance = double.Parse(ReadAccount["Balance"].ToString());
customer.Accounts.Add(account);
}
}
}
}
}
foreach (var customer in BankingSys.Instance().Customers)
{
foreach (var account in customer.Accounts)
{
using (TransactionCmd = new SqlCommand("select [transactionid], [transactiontype], [accountnumber], [destinationaccountnumber]," +
" [amount], [comment], [transactiontimeutc] from [transaction] where [accountnumber] = " + account.AccountNumber, conn))
{
using (ReadTransaction = TransactionCmd.ExecuteReader())
{
while (ReadTransaction.Read())
{
Transaction transaction = new Transaction(int.Parse(ReadTransaction["TransactionID"].ToString()), Convert.ToChar(ReadTransaction["TransactionType"].ToString()),
int.Parse(ReadTransaction["AccountNumber"].ToString()), int.Parse(ReadTransaction["DestinationAccountNumber"].ToString()),
double.Parse(ReadTransaction["Amount"].ToString()), null, DateTime.Parse(ReadTransaction["TransactionTimeUTC"].ToString()));
if (ReadTransaction["Comment"] != null)
{
transaction.Comment = ReadTransaction["Comment"].ToString();
}
account.Transactions.Add(transaction);
}
}
}
}
}
}
catch (SqlException se)
{
Console.WriteLine("Exception: {0}", se.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
