public object FindByUsernameAndPassword(string username, string password) { var _connection = ConnectionHelper.GetConnection(); var sqlQuery = "select * from SHBAccount where Username = @username and Password = @password"; var cmd = new MySqlCommand(sqlQuery, _connection); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@password", password); var result = cmd.ExecuteReader(); if (result.Read()) { var account = new SHBAccount { Username = result.GetString("Username"), AccountNumber = result.GetString("AccountNumber"), Balance = result.GetDouble("Balance"), Password = result.GetString("Password"), CreatedAtMLS = result.GetDateTime("CreatedAtMLS"), UpdatedAtMLS = result.GetDateTime("UpdatedAtMLS") }; _connection.Close(); return(account); } _connection.Close(); return(null); }
public object GetAccountWithAccountNumber(string accountNumber) { var connection = ConnectionHelper.GetConnection(); var queryString = "select * from `shbaccount` where AccountNumber = @accountNumber"; var cmd = new MySqlCommand(queryString, connection); cmd.Parameters.AddWithValue("@AccountNumber", accountNumber); var reader = cmd.ExecuteReader(); SHBAccount shbAccount = null; if (reader.Read()) { var username = reader.GetString("Username"); var password = reader.GetString("Password"); var accountNumber2 = reader.GetString("AccountNumber"); var balance = reader.GetDouble("Balance"); var createdAt = reader.GetDateTime("CreatedAtMLS"); var updatedAt = reader.GetDateTime("UpdatedAtMLS"); shbAccount = new SHBAccount(accountNumber2, username, password, balance, createdAt, updatedAt); } reader.Close(); connection.Close(); return(shbAccount); }
public SHBAccount FindByUsernameAndPassword(string username, string password) { var cmd = new MySqlCommand("select * from account where username = @username And password = @password", ConnectionHelper.OpenConnection()); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@password", password); SHBAccount shbAccount = null; var reader = cmd.ExecuteReader(); if (reader.Read()) { shbAccount = new SHBAccount { Username = reader.GetString("username"), Password = reader.GetString("password"), Balance = reader.GetDouble("balance") }; } ConnectionHelper.CloseConnection(); return(shbAccount); }
{ // Bình thường không làm theo cách này, // phải mã hoá mật khẩu, kiểm tra tài khoản theo username sau đó so sánh mật khẩu sau khi mã hoá. public SHBAccount FindByUsernameAndPassword(string username, string password) { // Tạo connection đến db, lấy ra trong bảng shb account những tài khoản có username, password trùng. var cmd = new MySqlCommand( "select * from SHBAccount where username = @username and password = @password", ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@password", password); // Tạo ra một đối tượng của lớp shbAccount. SHBAccount shbAccount = null; // Đóng Connection và trả về đối tượng này. var dataReader = cmd.ExecuteReader(); if (dataReader.Read()) { shbAccount = new SHBAccount { AccountNumber = dataReader.GetString("accountNumber"), Username = dataReader.GetString("username"), Password = dataReader.GetString("password"), Balance = dataReader.GetDouble("balance") }; } ConnectionHelper.CloseConnection(); // Trong trường hợp không tìm thấy tài khoản thì trả về null. return(shbAccount); }
public void Register() { Console.WriteLine("Vui lòng nhập đầy đủ các thông tin bên dưới."); Console.WriteLine("Tài khoản đăng nhập: "); var username = Console.ReadLine(); Console.WriteLine("Mật khẩu: "); var password = Console.ReadLine(); Console.WriteLine("Nhập lại mật khẩu: "); var cpassword = Console.ReadLine(); Console.WriteLine("Số dư trong tài khoản: "); var balance = Utility.GetUnsignedDecimalNumber(); SHBAccount shbAccount = new SHBAccount(username, password, balance); // validate trước khi save. if (model.Save(shbAccount)) { Console.WriteLine( "Đăng ký tài khoản thành công."); } Console.WriteLine("Ấn enter để tiếp tục."); Console.ReadLine(); }
public bool Save(SHBAccount shbAccount) { var queryString = "insert into `shbaccount` (accountNumber, username, password, balance)" + " values (@accountNumber, @username, @password, @balance)"; var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@accountNumber", shbAccount.AccountNumber); cmd.Parameters.AddWithValue("@username", shbAccount.UserName); cmd.Parameters.AddWithValue("@password", shbAccount.Password); cmd.Parameters.AddWithValue("@balance", shbAccount.Balance); var result = cmd.ExecuteNonQuery(); ConnectionHelper.CloseConnection(); return(result == 1); }
private static void GenerateTransactionMenu(GiaoDich giaoDich) { while (true) { Console.WriteLine("Please select the transaction type."); Console.WriteLine("=================================="); Console.WriteLine("1. Withdraw."); Console.WriteLine("2. Deposit."); Console.WriteLine("3. Transfer."); Console.WriteLine("4. Exit."); Console.WriteLine("==================================="); Console.WriteLine("Please enter your choice: "); var choice1 = int.Parse(Console.ReadLine()); switch (choice1) { case 1: giaoDich.Withdraw(); break; case 2: giaoDich.Deposit(); break; case 3: giaoDich.Transfer(); break; case 4: break; default: Console.WriteLine("Wrong choice, please try again!"); break; } if (choice1 == 4) { Console.WriteLine("Bye! see you again!"); currentLoggedInAccount = null; currentLoggedInAddress = null; break; } } }
public SHBAccount GetAccountByUsername(string username) { var queryString = "select * from `blkaccount` where `username` = @username"; var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@username", username); var reader = cmd.ExecuteReader(); SHBAccount shbAccount = null; if (reader.Read()) { shbAccount = new SHBAccount(); shbAccount.UserName = reader.GetString("username"); shbAccount.Balance = reader.GetInt32("balance"); } reader.Close(); ConnectionHelper.GetConnection(); return(shbAccount); }
public void Login() { Program.currentLoggedInAccount = null; Console.Clear(); Console.WriteLine("Tiến hành đăng nhập Hệ thống Ngân hàng SHB."); Console.WriteLine("Vui lòng điền tên truy cập: "); var username = Console.ReadLine(); Console.WriteLine("Vui lòng nhập mật khẩu: "); var password = Console.ReadLine(); SHBAccount shbAccount = shbAccountModel.FindByUsernameAndPassword(username, password); if (shbAccount == null) { Console.WriteLine("Sai tài khoản, vui lòng thử lại."); Console.WriteLine("Ấn enter để tiếp tục."); Console.Read(); return; } Program.currentLoggedInAccount = shbAccount; }
public void Login() { Program.CurrentLoggedInAccount = null; Console.Clear(); Console.WriteLine("Login SHB System"); Console.WriteLine("Username: "******"Password: "******"Login failed. Please try again"); Console.WriteLine("Press any key to continue"); Console.Read(); return; } Program.CurrentLoggedInAccount = shbAccount; }
public SHBAccount GetAccountByUsername(string username) { ConnectionHelper.GetConnection(); var queryString = "select * from `SHBAccount` where `username` = @username"; var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@username", username); var dataReader = cmd.ExecuteReader(); SHBAccount shbAccount = null; if (dataReader.Read()) { shbAccount = new SHBAccount(); shbAccount.AccountNumber = dataReader.GetString("accountNumber"); shbAccount.Username = dataReader.GetString("username"); shbAccount.Password = dataReader.GetString("password"); shbAccount.Balance = dataReader.GetDouble("balance"); } dataReader.Close(); ConnectionHelper.CloseConnection(); return(shbAccount); }
public bool DoLogin() { Console.Clear(); Console.Out.Flush(); // Lấy thông tin từ người dùng nhập vào. Console.WriteLine("============= ĐĂNG NHẬP ============"); Console.WriteLine("TÀI KHOẢN: "); var username = Console.ReadLine(); Console.WriteLine("MẬT KHẨU: "); var password = Console.ReadLine(); var shbAccount = new SHBAccount(username, password); // Bắt đầu kiểm tra Valid user và password length khác null và lớn hơn 0. var errors = shbAccount.ValidLoginInformation(); if (errors.Count > 0) { Console.WriteLine("Vui lòng kiểm tra lại."); foreach (var messagErrorsValue in errors.Values) { Console.Error.WriteLine(messagErrorsValue); } Console.ReadLine(); return(false); } shbAccount = (SHBAccount)_model.FindByUsernameAndPassword(username, password); if (shbAccount == null) { // Sai thông tin username, trả về thông báo lỗi cụ thể. Console.WriteLine("Thông tin tài khoản không hợp lệ, vui lòng thử lại."); return(false); } // Login thành công, lưu thông tin vào biến static trong lớp Program. Program._SHB_CurrentLoggedIn = shbAccount; return(true); }
public SHBAccount GetAccountByAccountNumber(string accountNumber) { var queryString = "select * from `shbAccount` where `accountNumber` = @accountNumber "; var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@accountNumber", accountNumber); var reader = cmd.ExecuteReader(); SHBAccount shbAccount = null; if (reader.Read()) { shbAccount = new SHBAccount { AccountNumber = reader.GetString("accountNumber"), UserName = reader.GetString("username"), Password = reader.GetString("password"), Balance = reader.GetInt32("balance"), }; } reader.Close(); ConnectionHelper.CloseConnection(); return(shbAccount); }
public static bool UpdateBalance(SHBAccount currentLoggedInAccount, object blockchainTransaction) { throw new NotImplementedException(); }
public bool Transfer(SHBAccount currentLoggedInAccount, SHBTransaction transactionHistory) { ConnectionHelper.CloseConnect(); var mySqlTransaction = ConnectionHelper.GetConnect().BeginTransaction(); try { var selectBalance = "select balance from `accounts` where username = @username"; var cmdSelect = new MySqlCommand(selectBalance, ConnectionHelper.GetConnect()); cmdSelect.Parameters.AddWithValue("@username", currentLoggedInAccount.UserName); var reader = cmdSelect.ExecuteReader(); decimal currentAccountBalance = 0; if (reader.Read()) { currentAccountBalance = reader.GetDecimal("balance"); } reader.Close(); if (currentAccountBalance < transactionHistory.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } currentAccountBalance -= transactionHistory.Amount; var updateQuery = "update `accounts` set `balance` = @balance where username = @username"; var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnect()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@username", currentLoggedInAccount.UserName); var updateResult = sqlCmd.ExecuteNonQuery(); var selectBalanceReceiver = "select balance from `accounts` where username = @username"; var cmdSelectReceiver = new MySqlCommand(selectBalanceReceiver, ConnectionHelper.GetConnect()); cmdSelectReceiver.Parameters.AddWithValue("@username", transactionHistory.ReceiverAccountNumber); var readerReceiver = cmdSelectReceiver.ExecuteReader(); decimal receiverBalance = 0; if (readerReceiver.Read()) { receiverBalance = readerReceiver.GetDecimal("balance"); } readerReceiver.Close(); receiverBalance += transactionHistory.Amount; var updateQueryReceiver = "update `accounts` set `balance` = @balance where username = @username"; var sqlCmdReceiver = new MySqlCommand(updateQueryReceiver, ConnectionHelper.GetConnect()); sqlCmdReceiver.Parameters.AddWithValue("@balance", receiverBalance); sqlCmdReceiver.Parameters.AddWithValue("@username", transactionHistory.ReceiverAccountNumber); var updateResultReceiver = sqlCmdReceiver.ExecuteNonQuery(); var historyTransactionQuery = "insert into `transactions` (id, type, senderId, receiverId, amount, message) " + "values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnect()); historyTransactionCmd.Parameters.AddWithValue("@id", transactionHistory.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@amount", transactionHistory.Amount); historyTransactionCmd.Parameters.AddWithValue("@type", transactionHistory.Type); historyTransactionCmd.Parameters.AddWithValue("@message", transactionHistory.Message); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", transactionHistory.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", transactionHistory.ReceiverAccountNumber); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateResult != 1 || historyResult != 1 || updateResultReceiver != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } mySqlTransaction.Commit(); return(true); } catch (Exception e) { mySqlTransaction.Rollback(); return(false); } finally { ConnectionHelper.CloseConnect(); } }
public bool DoRegister() { Console.Clear(); Console.Out.Flush(); Console.WriteLine("Nhập thông tin tài khoản."); Console.WriteLine("-----------------------------------"); Console.WriteLine("Tài khoản: "); var username = Console.ReadLine(); Console.WriteLine("Mật khẩu: "); var password = Console.ReadLine(); var shbAccount = new SHBAccount(username, password); var errors = shbAccount.CheckValid(); if (errors.Count == 0 && _model.FindByUsernameAndPassword(username, password) == null) { if (!_model.SaveAccount(shbAccount)) { return(false); } Console.WriteLine("Đăng ký thành công."); Console.WriteLine("--------------------------------"); int countLoop = 0; while (true) { if (countLoop > 1) { Console.Clear(); Console.Out.Flush(); } Console.WriteLine("Bạn có muốn đăng nhập không? Y/N"); Console.WriteLine("Vui lòng nhập lựa chọn của bạn:"); var choice = Console.ReadLine(); if (choice != null && choice.Equals("N")) { DoLogin(); break; } if (choice != null && choice.Equals("Y")) { return(false); } Console.WriteLine("Lựa chọn không hợp lệ, vui lòng thử lại."); } } else { Console.Error.WriteLine("Thông tin tài khoản không hợp lệ, vui lòng thử lại."); foreach (var messagErrorsValue in errors.Values) { Console.Error.WriteLine(messagErrorsValue); } Console.ReadLine(); } return(false); }
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction) { ConnectionHelper.CloseConnect(); var tran = ConnectionHelper.GetConnect().BeginTransaction(); try { var cmd = new MySqlCommand("select * from accounts where username = @Username", ConnectionHelper.GetConnect()); cmd.Parameters.AddWithValue("@Username", currentLoggedInAccount.UserName); SHBAccount shbAccount = null; var reader = cmd.ExecuteReader(); decimal currentAccountBalance = 0; if (reader.Read()) { currentAccountBalance = reader.GetDecimal("balance"); } reader.Close(); if (currentAccountBalance < 0) { Console.WriteLine("Không đủ tiền trong tài khoản."); return(false); } if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW) { if (currentAccountBalance < transaction.Amount) { Console.WriteLine("Khong du tien thuc hien giao dich"); return(false); } currentAccountBalance -= transaction.Amount; } else if (transaction.Type == SHBTransaction.TransactionType.DEPOSIT) { currentAccountBalance += transaction.Amount; } var updateQuery = "update `accounts` set `balance` = @balance where username = @username"; var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnect()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@username", currentLoggedInAccount.UserName); var updateResult = sqlCmd.ExecuteNonQuery(); var historyTransactionQuery = "insert into `transactions` (id, type, senderId, receiverId, amount, message) " + "values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnect()); historyTransactionCmd.Parameters.AddWithValue("@id", transaction.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount); historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type); historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", transaction.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", transaction.ReceiverAccountNumber); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateResult != 1 || historyResult != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } tran.Commit(); } catch (Exception e) { Console.WriteLine(e.Message); tran.Rollback(); return(false); } ConnectionHelper.CloseConnect(); return(true); }
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction) { // 1. Kiểm tra số dư tài khoản hiện tại. // 2. Update số dư tài khoản hiện tại. // 3. Lưu thông tin giao dịch. // 4. Commit transaction. ConnectionHelper.GetConnection(); var transaction1 = ConnectionHelper.GetConnection().BeginTransaction(); // mở giao dịch. try { // Kiểm tra số dư tài khoản. var cmd = new MySqlCommand("select balance from SHBAccount where accountNumber = @accountNumber", ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber); // SHBAccount shbAccount = null; var dataReader = cmd.ExecuteReader(); double currentAccountBalance = 0; if (dataReader.Read()) { currentAccountBalance = dataReader.GetDouble("balance"); } dataReader.Close(); if (currentAccountBalance < transaction.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW && currentAccountBalance < transaction.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW) { currentAccountBalance -= transaction.Amount; } else if (transaction.Type == SHBTransaction.TransactionType.DEPOSIT) { currentAccountBalance += transaction.Amount; } var updateQuery = "update `SHBAccount` set `balance` = @balance where accountNumber = @accountNumber"; var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber); var updateResult = sqlCmd.ExecuteNonQuery(); var historyTransactionQuery = "insert into `SHBTransaction` (transactionId, type, senderAccountNumber, receiverAccountNumber, amount, message) " + "values (@transactionId, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnection()); historyTransactionCmd.Parameters.AddWithValue("@transactionId", transaction.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount); historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type); historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", transaction.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", transaction.ReceiverAccountNumber); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateResult != 1 || historyResult != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } transaction1.Commit(); } catch (Exception e) { Console.WriteLine(e.Message); transaction1.Rollback(); // lưu giao dịch vào. return(false); } ConnectionHelper.CloseConnection(); return(true); }
public bool Transfer(SHBAccount currentLoggedInAccount, SHBTransaction transaction) { ConnectionHelper.GetConnection(); var transaction1 = ConnectionHelper.GetConnection().BeginTransaction(); // mở giao dịch. try { // Kiểm tra số dư tài khoản. var selectBalance = "select balance from SHBAccount where accountNumber = @accountNumber"; var cmdSelect = new MySqlCommand(selectBalance, ConnectionHelper.GetConnection()); cmdSelect.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber); var dataReader = cmdSelect.ExecuteReader(); double currentAccountBalance = 0; if (dataReader.Read()) { currentAccountBalance = dataReader.GetDouble("balance"); } dataReader.Close(); if (currentAccountBalance < transaction.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } currentAccountBalance -= transaction.Amount; //Tiến hành trừ tiền tài khoản gửi. // Update tài khoản. var updateQuery = "update `SHBAccount` set `balance` = @balance where accountNumber = @accountNumber"; var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber); var updateResult = sqlCmd.ExecuteNonQuery(); // Kiểm tra số dư tài khoản. var selectBalanceReceiver = "select balance from `SHBAccount` where accountNumber = @accountNumber"; var cmdSelectReceiver = new MySqlCommand(selectBalanceReceiver, ConnectionHelper.GetConnection()); cmdSelectReceiver.Parameters.AddWithValue("@accountNumber", transaction.ReceiverAccountNumber); var readerReceiver = cmdSelectReceiver.ExecuteReader(); double receiverBalance = 0; if (readerReceiver.Read()) { receiverBalance = readerReceiver.GetDouble("balance"); } readerReceiver.Close(); // important. //Tiến hành cộng tiền tài khoản nhận. receiverBalance += transaction.Amount; // Update tài khoản. var updateQueryReceiver = "update `SHBAccount` set `balance` = @balance where accountNumber = @accountNumber"; var sqlCmdReceiver = new MySqlCommand(updateQueryReceiver, ConnectionHelper.GetConnection()); sqlCmdReceiver.Parameters.AddWithValue("@balance", receiverBalance); sqlCmdReceiver.Parameters.AddWithValue("@accountNumber", transaction.ReceiverAccountNumber); var updateResultReceiver = sqlCmdReceiver.ExecuteNonQuery(); // Lưu lịch sử giao dịch. var historyTransactionQuery = "insert into `SHBTransaction` (transactionId, amount, type, message, senderAccountNumber, receiverAccountNumber) " + "values (@transactionId, @amount, @type, @message, @senderAccountNumber, @receiverAccountNumber)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnection()); historyTransactionCmd.Parameters.AddWithValue("@transactionId", transaction.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount); historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type); historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", transaction.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", transaction.ReceiverAccountNumber); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateResult != 1 || historyResult != 1 || updateResultReceiver != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } transaction1.Commit(); return(true); } catch (Exception e) { transaction1.Rollback(); Console.WriteLine(e.StackTrace); Console.WriteLine(e.Message); Console.WriteLine(e.Source); Console.WriteLine(e.ToString()); return(false); } finally { ConnectionHelper.CloseConnection(); } }
public bool Tranfer(SHBAccount currentLoggedInAccount, SHBTransaction shbTransaction) { var conn = ConnectionHelper.GetConnection(); var myTransaction = conn.BeginTransaction(); try { var balanceSender = new MySqlCommand("select * from accounts where AccountNumber = @AccountNumber ", conn); balanceSender.Parameters.AddWithValue("@AccountNumber", currentLoggedInAccount.AccountNumber); double currentAccountBalance = 0; var reader = balanceSender.ExecuteReader(); if (reader.Read()) { currentAccountBalance = reader.GetDouble("Balance"); } reader.Close(); if (shbTransaction.Type == SHBTransaction.TransactionType.TRANFER && currentAccountBalance < shbTransaction.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } currentAccountBalance -= shbTransaction.Amount; var updateQuery = ("update accounts set Balance = @balance where AccountNumber = @AccountNumber"); var sqlCmd = new MySqlCommand(updateQuery, conn); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@AccountNumber", currentLoggedInAccount.AccountNumber); var updateRs = sqlCmd.ExecuteNonQuery(); var balanceReceiver = new MySqlCommand("select * from accounts where AccountNumber = @AccountNumber ", conn); balanceReceiver.Parameters.AddWithValue("@AccountNumber", shbTransaction.ReceiverAccountNumber); double receiverBalance = 0; var readerReceiver = balanceReceiver.ExecuteReader(); if (readerReceiver.Read()) { receiverBalance = readerReceiver.GetDouble("balance"); } readerReceiver.Close(); receiverBalance += shbTransaction.Amount; var updateQueryReceiver = ("update accounts set Balance = @balance where AccountNumber = @AccountNumber"); var sqlCmdReceiver = new MySqlCommand(updateQueryReceiver, conn); sqlCmdReceiver.Parameters.AddWithValue("@balance", receiverBalance); sqlCmdReceiver.Parameters.AddWithValue("@AccountNumber", shbTransaction.ReceiverAccountNumber); var updateResultReceiver = sqlCmdReceiver.ExecuteNonQuery(); var historyTransactionQuery = "insert into shbtransaction (transaction_id, type, sender_account_number, receiver_account_number, amount, message, createdAt, updatedAt, status) " + "values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message, @createdAtMLS, @updatedAtMLS, @status)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, conn); historyTransactionCmd.Parameters.AddWithValue("@id", shbTransaction.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@type", shbTransaction.Type); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", shbTransaction.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", shbTransaction.ReceiverAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@amount", shbTransaction.Amount); historyTransactionCmd.Parameters.AddWithValue("@message", shbTransaction.Message); historyTransactionCmd.Parameters.AddWithValue("@createdAtMLS", shbTransaction.CreatedAtMLS); historyTransactionCmd.Parameters.AddWithValue("@updatedAtMLS", shbTransaction.UpdatedAtMLS); historyTransactionCmd.Parameters.AddWithValue("@status", shbTransaction.Status); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateRs != 1 || historyResult != 1 || updateResultReceiver != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } myTransaction.Commit(); return(true); } catch (Exception e) { Console.WriteLine(e.Message); myTransaction.Rollback(); return(false); } finally { conn.Close(); } }
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction) { var trans = ConnectionHelper.GetConnection().BeginTransaction(); try { var cmd = new MySqlCommand("select * from accounts where Username = @username ", ConnectionHelper.GetConnection()); cmd.Parameters.AddWithValue("@username", currentLoggedInAccount.Username); double currentAccountBalance = 0; var reader = cmd.ExecuteReader(); if (reader.Read()) { currentAccountBalance = reader.GetDouble("Balance"); } reader.Close(); if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW && currentAccountBalance < transaction.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW) { currentAccountBalance -= transaction.Amount; } else if (transaction.Type == SHBTransaction.TransactionType.DEPOSIT) { currentAccountBalance += transaction.Amount; } var updateQuery = ("update accounts set Balance = @balance where Username = @username"); var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@username", currentLoggedInAccount.Username); var updateRs = sqlCmd.ExecuteNonQuery(); var transactionQuery = "insert into shbtransaction (transaction_id, type, sender_account_number, receiver_account_number, amount, message, createdAt, updatedAt, status) " + "values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message, @createdAtMLS, @updatedAtMLS, @status)"; var historyTransactionCmd = new MySqlCommand(transactionQuery, ConnectionHelper.GetConnection()); historyTransactionCmd.Parameters.AddWithValue("@id", transaction.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", transaction.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", transaction.ReceiverAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount); historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message); historyTransactionCmd.Parameters.AddWithValue("@createdAtMLS", transaction.CreatedAtMLS); historyTransactionCmd.Parameters.AddWithValue("@updatedAtMLS", transaction.UpdatedAtMLS); historyTransactionCmd.Parameters.AddWithValue("@status", transaction.Status); var historyResult = historyTransactionCmd.ExecuteNonQuery(); Console.WriteLine("query status: " + historyResult); if (updateRs != 1 || historyResult != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } trans.Commit(); } catch (Exception e) { Console.WriteLine("Err: " + e.Message); try { trans.Rollback(); } catch (Exception exception) { Console.WriteLine(exception); throw; } return(false); } ConnectionHelper.CloseConnection(); return(true); }
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction) { ConnectionHelper.OpenConnection(); MySqlConnection mySqlConnection; var tran = ConnectionHelper.OpenConnection().BeginTransaction(); try { var cmd = new MySqlCommand("select * from account where username = @username", ConnectionHelper.OpenConnection()); cmd.Parameters.AddWithValue("@Username", currentLoggedInAccount.AccountNumber); SHBAccount shbAccount = null; var reader = cmd.ExecuteReader(); double currentAccountBalance = 0; if (reader.Read()) { currentAccountBalance = reader.GetDouble("balance"); } reader.Close(); if (currentAccountBalance < 0) { Console.WriteLine("You have not enough money"); return(false); } if (transaction.Type == 1) { if (currentAccountBalance < transaction.Amount) { Console.WriteLine("Not enough money"); return(false); } currentAccountBalance -= transaction.Amount; } else if (transaction.Type == 2) { currentAccountBalance += transaction.Amount; } var updateQuery = "update `account` set `balance` = @balance where accountId = @accountId"; var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.OpenConnection()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@accountId", currentLoggedInAccount.AccountNumber); var updateResult = sqlCmd.ExecuteNonQuery(); var historyTransactionQuery = "insert into `SHB` (transactionId, type, senderId, receiverId, amount, message) " + "values (@transactionId, @type, @senderId, @receiverId, @amount, @message)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, ConnectionHelper.OpenConnection()); historyTransactionCmd.Parameters.AddWithValue("@transactionId", transaction.TransactionId); historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount); historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type); historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message); historyTransactionCmd.Parameters.AddWithValue("@senderId", transaction.SenderAccountId); historyTransactionCmd.Parameters.AddWithValue("@receiverId", transaction.ReceiverAccountId); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateResult != 1 || historyResult != 1) { throw new Exception("Can not transaction or update your account"); } tran.Commit(); } catch (Exception e) { Console.WriteLine(e.Message); tran.Rollback(); return(false); } ConnectionHelper.CloseConnection(); return(true); }
public bool UpdateBalance(SHBAccount currentLoggedInAccount, TransactionHistory transactionHistory) { try { // Kiểm tra số dư tài khoản. var selectBalance = "select balance from `blkaccount` where accountNumber = @accountNumber "; var cmdSelect = new MySqlCommand(selectBalance, ConnectionHelper.GetConnection()); cmdSelect.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber); var reader = cmdSelect.ExecuteReader(); decimal currentAccountBalance = 0; if (reader.Read()) { currentAccountBalance = reader.GetDecimal("balance"); } reader.Close(); // important. if (transactionHistory.Type == TransactionHistory.TransactionType.WITHDRAW && currentAccountBalance < transactionHistory.Amount) { throw new Exception("Không đủ tiền trong tài khoản."); } if (transactionHistory.Type == TransactionHistory.TransactionType.WITHDRAW) { currentAccountBalance -= transactionHistory.Amount; } else if (transactionHistory.Type == TransactionHistory.TransactionType.DEPOSIT) { currentAccountBalance += transactionHistory.Amount; } // Update tài khoản. var updateQuery = "update `blkaccount` set `balance` = @balance where accountNumber = @accountNumber "; var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection()); sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance); sqlCmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber); var updateResult = sqlCmd.ExecuteNonQuery(); // Lưu lịch sử giao dịch. var historyTransactionQuery = "insert into `blktransaction` (TransactionId,Type, SenderAccountNumber, ReceiverAccountNumber, Amount, Message ) " + "values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount @content)"; var historyTransactionCmd = new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnection()); historyTransactionCmd.Parameters.AddWithValue("@id", transactionHistory.Id); historyTransactionCmd.Parameters.AddWithValue("@amount", transactionHistory.Amount); historyTransactionCmd.Parameters.AddWithValue("@type", transactionHistory.Type); historyTransactionCmd.Parameters.AddWithValue("@content", transactionHistory.Content); historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber", transactionHistory.SenderAccountNumber); historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber", transactionHistory.ReceiverAccountNumber); var historyResult = historyTransactionCmd.ExecuteNonQuery(); if (updateResult != 1 || historyResult != 1) { throw new Exception("Không thể thêm giao dịch hoặc update tài khoản."); } transaction.Comit(); } catch (Exception e) { Console.WriteLine(e.Message); transaction.Rollback(); // lưu giao dịch vào. return(false); } ConnectionHelper.CloseConnection(); return(true); }