//Fetches Account Number from the database
public int GetAccountNumber(int customerID)
{
try
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT AccountNumber FROM Account WHERE CustomerID = @customerID;";
command.Parameters.AddWithValue("customerID", customerID);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
accountNumber = ((int)reader["AccountNumber"]);
}
}
reader.Close();
}
catch (SqlException)
{
Console.WriteLine("Account Number not found in the database.");
}
return(accountNumber);
}
//Fetches Balance from the Account database
public decimal GetBalance(int customerID, string accountType)
{
try
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT Balance FROM Account WHERE CustomerID = @customerID AND AccountType = @accountType;";
command.Parameters.AddWithValue("AccountType", accountType);
command.Parameters.AddWithValue("CustomerID", customerID);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
balance = ((decimal)reader["Balance"]);
}
}
else
{
Console.WriteLine("Please choose correct Account Type (S or C).");
new CustomerMenu(customerID).Run();
}
reader.Close();
}
catch (SqlException)
{
Console.WriteLine("Account Balance not found in the database.");
}
return(balance);
}
public void InsertPerson(Account account)
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"insert into TestAccount (AccountNumber, AccountType, CustomerID, Balance) values (@accountNumber, @accountType, @customerID, @balance)";
command.Parameters.AddWithValue("AccountNumber", account.AccountNumber);
command.Parameters.AddWithValue("CustomerID", account.AccountType);
command.Parameters.AddWithValue("PasswordHash", account.CustomerID);
command.Parameters.AddWithValue("PasswordHash", account.Balance);
command.ExecuteNonQuery();
}
protected override void operation(Action <IDbConnection, IDbTransaction> action)
{
this.ExecuteDialect(() =>
{
using var connection = ConnectionString.CreateConnection();
OpenWithRetry(connection);
action.Invoke(connection, null);
}, () =>
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
action.Invoke(connection, null);
}
);
}
public void InsertPerson(Customer cus)
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"insert into TestCustomer (CustomerID,Name,Address,City,PostCode)values (@CustomerID, @Name, @Address,@City,@PostCode)";
command.Parameters.AddWithValue("CustomerID", cus.CustomerID);
command.Parameters.AddWithValue("Name", cus.Name);
command.Parameters.AddWithValue("Address", cus.Address);
command.Parameters.AddWithValue("City", cus.City);
command.Parameters.AddWithValue("PostCode", cus.PostCode);
command.ExecuteNonQuery();
}
//Establishing a connection and fetching all the Transaction Table data to the above list
public TransactionController()
{
try
{
ConnectionString = Program.ConnectionString;
using var connection = ConnectionString.CreateConnection();
var command = connection.CreateCommand();
command.CommandText = "SELECT * from [TransactionTable]";
//Transactions = command.GetDataTable().Select().Select(x =>
// new Transaction((int)x["TransactionID"], (char)x["TransactionType"], (int)x["AccountNumber"], (int)x["DestinationAccountNumber"], (decimal)x["Amount"], (string)x["Comment"], (DateTime)x["TransactionTimeUtc"])).ToList();
}
catch (SqlException)
{
Console.WriteLine("Transaction List is Empty");
}
}
public void InsertPerson(Transaction transaction)
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"insert into TestTransaction (TransactionID,TransactionType,AccountNumber,DestinationAccountNumber,Amount,Comment,TransactionTimeUtc) values (@TransactionID,@TransactionType,@AccountNumber,@DestinationAccountNumber,@Amount,@Comment,@TransactionTimeUtc)";
command.Parameters.AddWithValue("TransactionID", transaction.TransactionID);
command.Parameters.AddWithValue("TransactionType", transaction.TransactionType);
command.Parameters.AddWithValue("AccountNumber", transaction.AccountNumber);
command.Parameters.AddWithValue("DestinationAccountNumber", transaction.DestinationAccountNumber);
command.Parameters.AddWithValue("Amount", transaction.Amount);
command.Parameters.AddWithValue("Comment", transaction.Comment);
command.Parameters.AddWithValue("TransactionTimeUtc", transaction.TransactionTimeUtc);
command.ExecuteNonQuery();
}
//Validates the login details entered by the user
public void ValidateLogin(string loginID, string password)
{
try
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Login WHERE LoginID = @loginID;"; //Validates login ID with the database
command.Parameters.AddWithValue("loginID", loginID);
SqlDataReader reader = command.ExecuteReader();
bool key;
if (reader.HasRows)
{
while (reader.Read())
{
var passwordH = (string)reader["PasswordHash"];
key = PBKDF2.Verify(passwordH, password); //Compares the hashed password in the database with the passsword entered by the user. Returns true if the password is correct.
if (key)
{
new CustomerMenu((int)reader["CustomerID"]).Run();
}
else
{
//Prompts back to the Main menu if wrong password is entered.
Console.WriteLine("\nInvalid Password, please log in with correct password.\n");
break;
}
}
}
else
{
//Prompts back to the Main menu if wrong login id is entered.
Console.WriteLine("\nInvalid Login ID, please log in with correct login ID.\n");
}
reader.Close();
}
catch (SqlException)
{
Console.WriteLine("Please check connection with database.");
}
}
//Establishing a connection and fetching all the Accounts Table data to the above list
public AccountController()
{
try
{
ConnectionString = Program.ConnectionString; //Gets Connection String using the Facade Pattern
using var connection = ConnectionString.CreateConnection();
var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Account";
Accounts = command.GetDataTable().Select().Select(x =>
new Account((int)x["AccountNumber"], (string)x["AccountType"], (int)x["CustomerID"], (decimal)x["Balance"])).ToList();
}
catch (SqlException)
{
Console.WriteLine("Account List is empty.");
}
}
//Updates Balance in the Destination Account Database
public void UpdateDestAccountBalance(int accountNumber, decimal balance)
{
try
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"UPDATE Account SET Balance = @balance WHERE AccountNumber = @accountNumber";
command.Parameters.AddWithValue("Balance", balance);
command.Parameters.AddWithValue("AccountNumber", accountNumber);
command.ExecuteNonQuery();
Console.WriteLine("Destination Account Balance Updated");
}
catch (SqlException)
{
Console.WriteLine("Destination Account Balance could not be updated.");
}
}
//Updates Balance in the Account Database
public void UpdateBalance(int customerID, string accountType, decimal balance)
{
try
{
using var connection = ConnectionString.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText =
"UPDATE Account SET Balance = @balance WHERE AccountType = @accountType AND CustomerID = @customerID";
command.Parameters.AddWithValue("Balance", balance);
command.Parameters.AddWithValue("AccountType", accountType);
command.Parameters.AddWithValue("CustomerID", customerID);
command.ExecuteNonQuery();
Console.WriteLine("Balance Updated");
}
catch (SqlException)
{
Console.WriteLine("Account Balance could not be updated.");
}
}