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