// GET: UserMaster
        public ActionResult ShowUsers()
        {
            UserViewModel userViewModel = new UserViewModel();

            userViewModel.Users = new List <User>();
            ClsDB db = new ClsDB();

            try
            {
                _SbQry = new StringBuilder("Select * From UserMaster");
                db.Connect();
                DataTable dt = db.GetDataTable(_SbQry.ToString());
                foreach (DataRow row in dt.Rows)
                {
                    userViewModel.Users.Add(new User {
                        Id = row["UserId"].ToString(), Name = row["Name"].ToString()
                    });
                }

                return(View(userViewModel));
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("ErrorMessage", ex.Message);
                ViewBag.ErrorMessage = ex.Message;
                return(View(userViewModel.Users));
            }
            finally
            {
                db.DisConnect();
                db = null;
            }
        }
        public JsonResult EditUser(string Id)
        {
            ClsDB db   = new ClsDB();
            User  user = new User();

            try
            {
                _SbQry = new StringBuilder("Select * From UserMaster Where UserId='" + Id + "'");
                db.Connect();
                DataTable dt = db.GetDataTable(_SbQry.ToString());
                foreach (DataRow row in dt.Rows)
                {
                    user.Id   = row["UserId"].ToString();
                    user.Name = row["Name"].ToString();
                }
                user.Response = "Y";
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("ErrorMessage", ex.Message);
                ViewBag.ErrorMessage = ex.Message;
                user.Response        = "N";
                user.ErrorMessage    = "Error : " + ex.Message;
            }
            finally
            {
                db.DisConnect();
                db = null;
            }
            return(Json(user, JsonRequestBehavior.AllowGet));
        }
        public JsonResult DeleteUser(string Id)
        {
            ClsDB db   = new ClsDB();
            User  user = new User();

            try
            {
                _SbQry = new StringBuilder("Delete From UserMaster Where UserId='" + Id + "'");
                db.Connect();
                int Count = db.ExecuteNonQuery(_SbQry.ToString());
                if (Count > 0)
                {
                    user.Response = "Y";
                }
                else
                {
                    user.Response     = "N";
                    user.ErrorMessage = "User could not be deleted";
                }
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("ErrorMessage", ex.Message);
                ViewBag.ErrorMessage = ex.Message;
                user.Response        = "N";
                user.ErrorMessage    = "Error : " + ex.Message;
            }
            finally
            {
                db.DisConnect();
                db = null;
            }
            return(Json(user, JsonRequestBehavior.AllowGet));
        }
        public ActionResult EditUser(string Id)
        {
            ClsDB db   = new ClsDB();
            User  user = new User();

            try
            {
                _SbQry = new StringBuilder("Select * From UserMaster Where UserId='" + Id + "'");
                db.Connect();
                DataTable dt = db.GetDataTable(_SbQry.ToString());
                foreach (DataRow row in dt.Rows)
                {
                    user.Id   = row["UserId"].ToString();
                    user.Name = row["Name"].ToString();
                }
                return(PartialView(user));
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("ErrorMessage", ex.Message);
                user.ErrorMessage = ex.Message;
                return(PartialView(user));
            }
            finally
            {
                db.DisConnect();
                db = null;
            }
        }
        public JsonResult AddNewUser(User user)
        {
            ClsDB db = new ClsDB();

            try
            {
                if (ModelState.IsValid)
                {
                    _SbQry = new StringBuilder("Insert Into UserMaster(UserId,Name) Values('" + user.Id + "','" + user.Name + "')");
                    db.Connect();
                    int Count = db.ExecuteNonQuery(_SbQry.ToString());
                    if (Count > 0)
                    {
                        user.Response = "Y";
                    }
                    else
                    {
                        user.Response     = "N";
                        user.ErrorMessage = "User could not be added";
                    }
                }
                else
                {
                    foreach (var Key in ModelState.Keys)
                    {
                        if (ModelState[Key].Errors.Count > 0)
                        {
                            user.Response     = "N";
                            user.ErrorMessage = ModelState[Key].Errors[0].ErrorMessage;

                            return(Json(user, JsonRequestBehavior.AllowGet));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("ErrorMessage", ex.Message);
                ViewBag.ErrorMessage = ex.Message;
                user.Response        = "N";
                user.ErrorMessage    = "Error : " + ex.Message;
            }
            finally
            {
                db.DisConnect();
                db = null;
            }
            return(Json(user, JsonRequestBehavior.AllowGet));
        }
        private void button3_Click(object sender, EventArgs e)
        {
            ClsDB db = new ClsDB();

            try
            {
                db.Connect();
                db.GetDataTable_Proc("Prc_User");
                MessageBox.Show("Saved");
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); Log.Error(ex, "Error Occured"); }
            finally
            {
                db.DisConnect();
                db = null;
            }
        }
        private void btnInsert_Click(object sender, EventArgs e)
        {
            ClsDB db = new ClsDB();

            try
            {
                string qry = $"Exec Prc_User {txtId.Text},'{txtName.Text}'";
                db.Connect();
                db.GetDataTable(qry);
                MessageBox.Show("Saved");
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); Log.Error(ex, "Error Occured"); }
            finally
            {
                db.DisConnect();
                db = null;
            }
        }
        private void button4_Click(object sender, EventArgs e)
        {
            ClsDB db = new ClsDB();

            try
            {
                db.Connect();
                Dictionary <string, string> DicProcParameter = new Dictionary <string, string>();
                DicProcParameter.Add("@Id", txtId.Text);
                DicProcParameter.Add("@Name", txtName.Text);

                db.GetDataTable_Proc("Prc_User", DicProcParameter);
                MessageBox.Show("Saved");
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); Log.Error(ex, "Error Occured"); }
            finally
            {
                db.DisConnect();
                db = null;
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            ClsDB db = new ClsDB();

            try
            {
                db.Connect();
                SqlParameter[] parameters =
                {
                    new SqlParameter("@Id",   txtId.Text),
                    new SqlParameter("@Name", txtName.Text)
                };
                db.GetDataTable_Proc("Prc_User", parameters);
                MessageBox.Show("Saved");
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); Log.Error(ex, "Error Occured"); }
            finally
            {
                db.DisConnect();
                db = null;
            }
        }