public bool DoFundTransfer(FundTransferViewModel fundTransferDetails) { bool result = false; char accountType; // read balance of from account Decimal balance = 0; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { connection.Open(); SqlCommand command = new SqlCommand("SELECT Balance FROM Account WHERE AccountNumber = " + fundTransferDetails.FromAccount.ToString(), connection); SqlDataReader reader = command.ExecuteReader(); reader.Read(); balance = Convert.ToDecimal(reader[0]); reader.Close(); } // debit amount from FromAccount balance -= fundTransferDetails.Amount; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { connection.Open(); SqlCommand command = new SqlCommand(String.Format("UPDATE Account SET Balance = {0} WHERE AccountNumber = {1}", balance.ToString(), fundTransferDetails.FromAccount.ToString()), connection); command.ExecuteNonQuery(); } //read balance and type of ToAccount using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { connection.Open(); SqlCommand command = new SqlCommand("SELECT Balance, AccountType FROM Account WHERE AccountNumber = " + fundTransferDetails.ToAccount.ToString(), connection); SqlDataReader reader = command.ExecuteReader(); reader.Read(); balance = Convert.ToDecimal(reader[0]); accountType = Convert.ToChar(reader[1]); reader.Close(); } // credit based on type of ToAccount if (accountType == 'S') balance += fundTransferDetails.Amount; else balance -= fundTransferDetails.Amount; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { connection.Open(); SqlCommand command = new SqlCommand(String.Format("UPDATE Account SET Balance = {0} WHERE AccountNumber = {1}", balance.ToString(), fundTransferDetails.ToAccount.ToString()), connection); command.ExecuteNonQuery(); } if (balance == 0 && accountType == 'L') { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { connection.Open(); SqlCommand command = new SqlCommand(String.Format("UPDATE Account SET Status = 'Closed' WHERE AccountNumber = {0} ", fundTransferDetails.ToAccount.ToString()), connection); if (command.ExecuteNonQuery() > 0) result = true; } } // Insert into Transactions Table using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { int transactionID; connection.Open(); SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Transactions", connection); SqlDataReader reader = command.ExecuteReader(); reader.Read(); transactionID = Convert.ToInt16(reader[0]) + 1; reader.Close(); command.CommandText = String.Format("INSERT INTO Transactions(TransactionID, Type, TransactionDate, Amount, TransactionRemarks, SrcAccount, DestAccount) Values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}' )", transactionID.ToString(), "Fund Transfer", DateTime.Now.ToString(), fundTransferDetails.Amount.ToString(), fundTransferDetails.remarks, fundTransferDetails.FromAccount.ToString(), fundTransferDetails.ToAccount.ToString()); if (command.ExecuteNonQuery() > 0) result = true; } return result; }
public ActionResult TransferFunds(FundTransferViewModel model) { long customerID = (Session["User"] as UserRole).customerID; BankerDAL objBDAL = new BankerDAL(); CustomerDAL objCDAL = new CustomerDAL(); ViewBag.savingsAccountList = objCDAL.GetAllSavingsAccountByCustomerID(customerID); ViewBag.payeeAccounts = objCDAL.GetAllPayeeAccountByCustomerID(customerID); string message=""; if (model.Amount <= 0) { //ModelState.AddModelError("", "Source and Dest cant be same"); message = "Amount must be positive"; @ViewBag.message = message; return View(model); } if (model.FromAccount == model.ToAccount) { //ModelState.AddModelError("", "Source and Dest cant be same"); message = "Source and Destination account can't be same"; @ViewBag.message = message; return View(model); } if (model.Amount > objBDAL.GetAccountBalance(model.FromAccount)) { message = "Insufficient funds. Please check balance"; @ViewBag.message = message; return View(model); } if (objCDAL.ValidateTransactionPassword(customerID, model.TransactionPassword) == false) { message = "Password is not valid"; @ViewBag.message = message; return View(model); } if (objBDAL.GetAccountType(model.ToAccount) == 'L') { if (model.Amount > objBDAL.GetAccountBalance(model.ToAccount)) { message = "Invalid transaction"; @ViewBag.message = message; return View(model); } } objCDAL.DoFundTransfer(model); message = "Transaction Successful"; @ViewBag.message = message; return View(model); }