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