public ActionResult CreateUser() { vwUSER objUser = new vwUSER(); objUser.lstCountry = new SelectList(new List <SelectListItem>(), "Value", "Text"); objUser.lstState = new SelectList(new List <SelectListItem>(), "Value", "Text"); return(View(objUser)); }
public ActionResult Users() { List <vwUSER> objUserlst = new List <vwUSER>(); try { using (SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["UsermvcContext"].ToString())) { string qryUsers = "SELECT U.id, U.Username, U.EmailID, C.Country,S.StateName, U.CountryID, U.StateID,U.Active FROM [MT_USER] U join [MT_STATE] S on s.id=U.StateID and S.Active=1 join [MT_COUNTRY] C on C.id=U.CountryID and C.Active=1 order by U.Username"; SqlCommand objCmd = new SqlCommand(qryUsers, objConn); objConn.Open(); SqlDataReader objRe = objCmd.ExecuteReader(); while (objRe.Read()) { vwUSER objUser = new vwUSER(); objUser.id = Convert.ToInt32(objRe["id"]); objUser.Username = Convert.ToString(objRe["Username"]); objUser.EmailID = Convert.ToString(objRe["EmailID"]); objUser.CountryName = Convert.ToString(objRe["Country"]); objUser.StateName = Convert.ToString(objRe["StateName"]); objUser.Active = Convert.ToBoolean(objRe["Active"]); objUserlst.Add(objUser); } objRe.Close(); objCmd.Dispose(); objConn.Close(); } } catch (Exception ex) { vwUSER objUser = new vwUSER(); objUserlst.Add(objUser); //code here } return(View(objUserlst)); }
public ActionResult CreateUser(vwUSER objvwUSER) { try { using (SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["UsermvcContext"].ToString())) { string qryUsers = "insert into [MT_USER](Username, CountryID, StateID, EmailID) VALUES(@Username,@CountryID,@StateID,@EmailID)"; SqlCommand objCmd = new SqlCommand(qryUsers, objConn); objConn.Open(); objCmd.Parameters.AddWithValue("@Username", objvwUSER.Username.Trim()); objCmd.Parameters.AddWithValue("@EmailID", objvwUSER.EmailID); objCmd.Parameters.AddWithValue("@CountryID", objvwUSER.CountryName); objCmd.Parameters.AddWithValue("@StateID", objvwUSER.StateName); if (objCmd.ExecuteNonQuery() > 0) { objCmd.Dispose(); objConn.Close(); return(RedirectToAction("Users")); } else { objCmd.Dispose(); objConn.Close(); return(new EmptyResult()); } } } catch (Exception ex) { //code here return(RedirectToAction("CreateUser")); } }
public ActionResult EditUser(int id, vwUSER objvwUSER) { try { using (SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["UsermvcContext"].ToString())) { string qryUsers = "UPDATE [MT_USER] SET Username=@Username, CountryID=@CountryID, StateID=@StateID, EmailID=@EmailID,Active=@Active WHERE id=@UserID"; SqlCommand objCmd = new SqlCommand(qryUsers, objConn); objConn.Open(); objCmd.Parameters.AddWithValue("@Username", objvwUSER.Username.Trim()); objCmd.Parameters.AddWithValue("@EmailID", objvwUSER.EmailID); objCmd.Parameters.AddWithValue("@CountryID", objvwUSER.CountryName); objCmd.Parameters.AddWithValue("@StateID", objvwUSER.StateName); objCmd.Parameters.AddWithValue("@Active", objvwUSER.Active); objCmd.Parameters.AddWithValue("@UserID", id); if (objCmd.ExecuteNonQuery() > 0) { objCmd.Dispose(); objConn.Close(); return(RedirectToAction("Users")); } else { objCmd.Dispose(); objConn.Close(); return(new EmptyResult()); } } } catch (Exception ex) { //code here return(new EmptyResult()); } }
public ActionResult EditUser(int id) { vwUSER objUser = new vwUSER(); objUser.lstCountry = new SelectList(new List <SelectListItem>(), "0", "--Select Country--"); objUser.lstState = new SelectList(new List <SelectListItem>(), "0", "--Select State--"); try { using (SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["UsermvcContext"].ToString())) { string qryUsers = "SELECT U.id, U.Username, U.EmailID, C.Country,S.StateName, U.CountryID, U.StateID,U.Active FROM [MT_USER] U join [MT_STATE] S on s.id=U.StateID and S.Active=1 join [MT_COUNTRY] C on C.id=U.CountryID and C.Active=1 WHERE U.id=@UserID order by U.Username"; SqlCommand objCmd = new SqlCommand(qryUsers, objConn); objConn.Open(); objCmd.Parameters.AddWithValue("@UserID", id); SqlDataReader objRe = objCmd.ExecuteReader(); while (objRe.Read()) { objUser.id = Convert.ToInt32(objRe["id"]); objUser.Username = Convert.ToString(objRe["Username"]); objUser.EmailID = Convert.ToString(objRe["EmailID"]); objUser.CountryName = Convert.ToString(objRe["CountryID"]); objUser.StateName = Convert.ToString(objRe["StateID"]); objUser.Active = Convert.ToBoolean(objRe["Active"]); } objRe.Close(); objCmd.Dispose(); objConn.Close(); } //Country ddl List <SelectListItem> lstCountryItem = new List <SelectListItem>(); using (SqlConnection objConnC = new SqlConnection(ConfigurationManager.ConnectionStrings["UsermvcContext"].ToString())) { string qryUsersC = "SELECT id, Country FROM [MT_COUNTRY] WHERE Active=1 Order by [Country]"; SqlCommand objCmdC = new SqlCommand(qryUsersC, objConnC); objConnC.Open(); SqlDataReader objReC = objCmdC.ExecuteReader(); while (objReC.Read()) { SelectListItem objCountry = new SelectListItem(); objCountry.Text = Convert.ToString(objReC["Country"]); objCountry.Value = Convert.ToString(objReC["id"]); lstCountryItem.Add(objCountry); } objReC.Close(); objCmdC.Dispose(); objConnC.Close(); } //State ddl List <SelectListItem> lstStateItem = new List <SelectListItem>(); using (SqlConnection objConnS = new SqlConnection(ConfigurationManager.ConnectionStrings["UsermvcContext"].ToString())) { string qryUsersS = "SELECT id, StateName FROM [MT_STATE] WHERE Active=1 AND [CountryID]=@CountryID Order by [StateName]"; SqlCommand objCmdS = new SqlCommand(qryUsersS, objConnS); objConnS.Open(); objCmdS.Parameters.AddWithValue("@CountryID", objUser.CountryName); SqlDataReader objReS = objCmdS.ExecuteReader(); while (objReS.Read()) { SelectListItem objState = new SelectListItem(); objState.Text = Convert.ToString(objReS["StateName"]); objState.Value = Convert.ToString(objReS["id"]); lstStateItem.Add(objState); } objReS.Close(); objCmdS.Dispose(); objConnS.Close(); } lstCountryItem.Add(new SelectListItem() { Value = "0", Text = "--Select Country--" }); lstStateItem.Add(new SelectListItem() { Value = "0", Text = "--Select State--" }); lstCountryItem.Find(m => m.Value == Convert.ToString(objUser.CountryName)).Selected = true; lstStateItem.Find(m => m.Value == Convert.ToString(objUser.StateName)).Selected = true; objUser.lstCountry = new SelectList(lstCountryItem, "Value", "Text"); objUser.lstState = new SelectList(lstStateItem, "Value", "Text"); return(View(objUser)); } catch (Exception ex) { //code here return(View(objUser)); } }