public ActionResult AddPayee(AddPayeeViewModel model) { CustomerDAL objOfCustomerDAL = new CustomerDAL(); string result = (new CommonDAL()).CheckValidation("Customer", this.Session); if (result.Equals("LogIn")) return RedirectToAction("Login", "CommonBiz"); else if (result.Equals("Unauthorised")) return RedirectToAction("Unauthorised", "CommonBiz"); ModelState.Clear(); long customerID = (Session["User"] as UserRole).customerID; if (objOfCustomerDAL.ValidatePayeeAccountNumber(model.payeeAccountNumber, customerID) != null) { objOfCustomerDAL.AddPayee(model, customerID); ViewBag.message = "success"; return View(); } else { ViewBag.message = "Operation failed"; return View(); } }
public bool AddPayee(AddPayeeViewModel payeeDetails, long customerID) { bool result = false; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { int payeeID; SqlCommand command = new SqlCommand("SELECT COUNT(PayeeID) FROM Payee", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); reader.Read(); if(!reader.IsDBNull(0)) payeeID = Convert.ToInt16(reader[0]) + 1; else payeeID = 1; reader.Close(); command.CommandText = String.Format("INSERT INTO Payee VALUES('{0}', '{1}', '{2}', '{3}') ", payeeID, payeeDetails.payeeNickName, customerID, payeeDetails.payeeAccountNumber); if (command.ExecuteNonQuery() > 0) result = true; } return result; }
public AddPayeeViewModel ValidatePayeeAccountNumber(long payeeAccountNumber, long sourceCustomerID) { AddPayeeViewModel payeeDetails = new AddPayeeViewModel(); long payeeCustomerID, branchCode; using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { SqlCommand command = new SqlCommand("SELECT PayeeID FROM Payee WHERE CustomerID = " + sourceCustomerID.ToString() + " AND PayeeAccountNumber = " + payeeAccountNumber.ToString(), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { reader.Close(); return null; } } using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { SqlCommand command = new SqlCommand("SELECT CustomerID, BranchCode FROM Account WHERE AccountNumber = " + payeeAccountNumber.ToString() + " AND NOT Status = 'Closed' ", connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (!reader.HasRows) return null; else { reader.Read(); payeeCustomerID = Convert.ToInt64(reader[0]); branchCode = Convert.ToInt64(reader[1]); reader.Close(); } command.CommandText = String.Format("SELECT CustomerName FROM Customer WHERE CustomerID = " + payeeCustomerID.ToString()); reader = command.ExecuteReader(); reader.Read(); payeeDetails.payeeName = reader[0].ToString(); reader.Close(); command.CommandText = String.Format("SELECT BranchName FROM Branch WHERE BranchCode = " + branchCode.ToString()); reader = command.ExecuteReader(); reader.Read(); payeeDetails.branchName = reader[0].ToString(); reader.Close(); payeeDetails.payeeAccountNumber = payeeAccountNumber; } return payeeDetails; }