public bool AddBranch(BranchDetails bd) { SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()); CommonDAL commonDALObj = new CommonDAL(); SqlCommand cmd = new SqlCommand("SELECT MAX(BranchCode) FROM Branch ", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); if (!reader.IsDBNull(0)) { bd.BranchCode = Convert.ToInt64(reader[0]) + 1; } else bd.BranchCode = 1; reader.Close(); cmd.CommandText = String.Format("SELECT MAX(Id) FROM UserRoles "); reader = cmd.ExecuteReader(); reader.Read(); int id = Convert.ToInt16(reader[0]) + 1; connection.Close(); SqlConnection cn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()); string command = String.Format("INSERT INTO Branch VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", bd.BranchCode, bd.BranchName, bd.CityName, bd.Address, bd.ContactNumber, bd.BankerName, bd.BranchLogInID,commonDALObj.GetHashedText(bd.BranchLogInPassword), bd.Email); cn2.Open(); SqlCommand cmd2 = new SqlCommand(command,cn2); int res = cmd2.ExecuteNonQuery(); SqlCommand cmd3 = new SqlCommand(String.Format("INSERT INTO UserRoles VALUES('{0}','{1}', '{2}', '{3}','{4}','{5}', 'A')", id, bd.BranchLogInID, commonDALObj.GetHashedText(bd.BranchLogInPassword), "Banker",DateTime.Now.ToString(),"0"), cn2); cmd3.ExecuteNonQuery(); cn2.Close(); if (res == 0) return false; else return true; }
public bool FinishReg(Customer c) { bool res; CommonDAL commonDALObj = new CommonDAL(); using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { string cmdtxt = String.Format("UPDATE Customer SET UserID = '{0}', Password = '******', CommunicationAddress = '{2}', TransactionPassword = '******', PhotoIDProof = '{4}' WHERE CustomerID = {5}", c.UserID, commonDALObj.GetHashedText(c.Password), c.CommunicationAddress, commonDALObj.GetHashedText(c.TransactionPassword), c.PhotoIDProof, c.CustomerID); SqlCommand command = new SqlCommand(cmdtxt, connection); connection.Open(); int rowaff = command.ExecuteNonQuery(); // Call Read before accessing data. if (rowaff == 0) res = false; else res = true; command.CommandText = String.Format("SELECT MAX(Id) FROM UserRoles "); SqlDataReader rd = command.ExecuteReader(); rd.Read(); int id = Convert.ToInt16(rd[0]) + 1; rd.Close(); command.CommandText = String.Format("INSERT INTO UserRoles VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', 'A') ", id, c.UserID, commonDALObj.GetHashedText(c.Password), "Customer", DateTime.Now.ToString(), "0"); command.ExecuteNonQuery(); } return res; }
public string ValidateAndSetPassword(long customerID, string oldPassword, string newPassword, string passwordType) { CommonDAL commonDALObj = new CommonDAL(); if (oldPassword.Equals(newPassword)) return "Old and New Passwords cannot be same !"; if(passwordType.Equals("userPassword")) { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { SqlCommand command = new SqlCommand(String.Format("SELECT Password, UserID FROM Customer WHERE CustomerID = {0} ", customerID), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); reader.Read(); var storedPassword = reader[0].ToString(); var userID = reader[1].ToString(); reader.Close(); if (!storedPassword.Equals(commonDALObj.GetHashedText(oldPassword))) return "Old Password entered is not correct !"; // update password in Customer and User Profile tables command.CommandText = String.Format("UPDATE Customer SET Password = '******' WHERE CustomerID = {1}", commonDALObj.GetHashedText(newPassword), customerID.ToString()); command.ExecuteNonQuery(); command.CommandText = String.Format("UPDATE UserRoles SET Password = '******' WHERE UserID = '{1}' ", commonDALObj.GetHashedText(newPassword), userID); command.ExecuteNonQuery(); } } else { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { SqlCommand command = new SqlCommand(String.Format("SELECT TransactionPassword FROM Customer WHERE CustomerID = {0} ", customerID), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); reader.Read(); var storedPassword = reader[0].ToString(); reader.Close(); if (!storedPassword.Equals(commonDALObj.GetHashedText(oldPassword))) return "Password entered is not correct !"; // update password in Customer and User Profile tables command.CommandText = String.Format("UPDATE Customer SET TransactionPassword = '******' WHERE CustomerID = {1}", commonDALObj.GetHashedText(newPassword), customerID.ToString()); command.ExecuteNonQuery(); } } return "Success"; }
public string CheckRole(string UserID, string Password) { string res; CommonDAL commonDALObj = new CommonDAL(); SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()); string query = "SELECT Password, Role,FailCount,Status FROM UserRoles WHERE UserID = @userID "; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Add(new SqlParameter("@userID", UserID)); connection.Open(); SqlDataReader reader = command.ExecuteReader(); int failCount; if (!reader.HasRows) { res = String.Copy("DoesNotExist"); } else { // account exists reader.Read(); if (reader[3].ToString().Equals("L")) // Account is locked { res = String.Copy("Locked"); } else { //Account is active if (reader[0].ToString().Equals(commonDALObj.GetHashedText(Password))) { //correct password res = String.Copy(reader[1].ToString()); //update last log in in UserRoles and set FailCount = 0 reader.Close(); command.CommandText = "UPDATE UserRoles SET FailCount = 0, LastLogInDate = '" + DateTime.Now.ToString() + "' WHERE UserID = '" + UserID + "'"; command.ExecuteNonQuery(); } else { //Account is active but wrong password ! if (reader[1].ToString().Equals("Customer")) { failCount = Convert.ToInt16(reader[2]) + 1; if (failCount == 3) { // lock customer account and report reader.Close(); command.CommandText = "UPDATE UserRoles SET FailCount = 3, Status = 'L' WHERE UserID = '" + UserID + "'"; command.ExecuteNonQuery(); res = String.Copy("Account has been locked. Contact your banker to unlock !"); } else { // update failcount in UserRoles and warn reader.Close(); command.CommandText = "UPDATE UserRoles SET FailCount = " + failCount.ToString() + " WHERE UserID = '" + UserID + "'"; command.ExecuteNonQuery(); res = String.Copy("Incorrect Password ! " + (3 - failCount).ToString() + " more attempts remaining."); } } else { // Admin or banker entered incorrect password res = String.Copy("Invalid"); } } } } connection.Close(); return res; }
public void Sync() { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString())) { string cmdtxt = "DELETE FROM UserRoles"; SqlCommand command = new SqlCommand(cmdtxt, connection); connection.Open(); CommonDAL commonDALObj = new CommonDAL(); int rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('1','Adminnnn', '"+commonDALObj.GetHashedText("A1@nnnnn")+"','Admin','2015/2/2','0','A')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('2','Banker11','" + commonDALObj.GetHashedText("B1@nnnnn") + "','Banker','2015/2/2','0','A')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('3','Customer1','" + commonDALObj.GetHashedText("C1@nnnnn") + "','Customer','2015/2/2','0','A')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO UserRoles VALUES('4','Banker22','" + commonDALObj.GetHashedText("B1@nnnnn") + "','Banker','2015/2/2','0','A')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM BranchTransferRequest"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM ClosingRequest"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM LoanRequest"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM NewAccountRequest"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM Branch"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO Branch VALUES('1','Branch1','City1','Address1','11','Banker11','Banker11','"+commonDALObj.GetHashedText("B1@nnnnn")+"','[email protected]')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO Branch VALUES('2','Branch2','City2','Address1','11','Banker22','Banker22','" + commonDALObj.GetHashedText("B1@nnnnn") + "','[email protected]')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM Customer"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("INSERT INTO Customer (CustomerID, CustomerName,DOB, UserID, Password, PermanentAddress, CommunicationAddress, ContactNumber, Email, TransactionPassword, Token) VALUES('1001','Customer','2015/12/12','Customer1','"+commonDALObj.GetHashedText("customer1") + "','PAddress','CAddress','11','[email protected]','"+commonDALObj.GetHashedText("tpassword")+"','IBI1234')"); rowaff = command.ExecuteNonQuery(); command.CommandText = String.Copy("DELETE FROM BranchTransferRequest"); rowaff = command.ExecuteNonQuery(); //insert into user profile too !! } }