JqGrid: Add,Edit,Del in asp.net

https://github.com/1rosehip/jplist

https://github.com/free-jqgrid/jqGrid

https://plugins.jquery.com/tag/pagination/

https://dotnetsourcedileep.codeplex.com/

/// https://sql2mongo.codeplex.com/
/// https://forums.asp.net/t/1629287.aspx?serverside+code+for+Add+Edit+Del+in+jQGrid
/// https://forums.asp.net/t/1627666.aspx?Edit+Add+Del+in+Jqgrid
/// http://trirand.net/examples/grid/editing_data/edit_add_delete/default.aspx
/// http://www.trirand.com/blog/?page_id=6
/// https://jqgridaspnetmvc.codeplex.com/
/// https://github.com/tpeczek/Lib.AspNetCore.Mvc.JqGrid
/// https://jqmvcgrid.codeplex.com/
/// http://jqgridaspnetmvc.codeplex.com/
/// https://mvccrud.codeplex.com/
/// http://www.trirand.net/download.aspx
/// https://dotnetsourcedileep.codeplex.com/

http://www.c-sharpcorner.com/uploadfile/rahul4_saxena/jqgrid-in-asp-net-c-sharp/

https://github.com/tonytomov/jqGrid

sql:

--jQGridDemo AdventureWorks2012
CREATE  TABLE Employee
(
	_id VARCHAR(100),
	FirstName NVARCHAR(100),
	LastName NVARCHAR(100),
	LastSSN NVARCHAR(100),
	Department NVARCHAR(100),
	Age INT,
	Salary NVARCHAR(100),
	[Address] NVARCHAR(100),
	MaritalStatus NVARCHAR(100)
)
GO

SELECT * FROM dbo.Employee
GO

INSERT dbo.Employee
        ( _id ,
          FirstName ,
          LastName ,
          LastSSN ,
          Department ,
          Age ,
          Salary ,
          Address ,
          MaritalStatus
        )
VALUES  ( '1' , -- _id - varchar(100)
          N'du' , -- FirstName - nvarchar(100)
          N'geovindu' , -- LastName - nvarchar(100)
          N'N0002' , -- LastSSN - nvarchar(100)
          N'IT' , -- Department - nvarchar(100)
          40 , -- Age - int
          N'40000' , -- Salary - nvarchar(100)
          N'SZ' , -- Address - nvarchar(100)
          N'1'  -- MaritalStatus - nvarchar(100)
        )
GO

INSERT dbo.Employee
        ( _id ,
          FirstName ,
          LastName ,
          LastSSN ,
          Department ,
          Age ,
          Salary ,
          Address ,
          MaritalStatus
        )
VALUES  ( '2' , -- _id - varchar(100)
          N'涂' , -- FirstName - nvarchar(100)
          N'聚文' , -- LastName - nvarchar(100)
          N'N0001' , -- LastSSN - nvarchar(100)
          N'HR' , -- Department - nvarchar(100)
          40 , -- Age - int
          N'80000' , -- Salary - nvarchar(100)
          N'SZ' , -- Address - nvarchar(100)
          N'2'  -- MaritalStatus - nvarchar(100)
        )
GO

INSERT dbo.Employee
        ( _id ,
          FirstName ,
          LastName ,
          LastSSN ,
          Department ,
          Age ,
          Salary ,
          Address ,
          MaritalStatus
        )
VALUES  ( '3' , -- _id - varchar(100)
          N'涂' , -- FirstName - nvarchar(100)
          N'斯博' , -- LastName - nvarchar(100)
          N'N0003' , -- LastSSN - nvarchar(100)
          N'HR' , -- Department - nvarchar(100)
          10 , -- Age - int
          N'80000' , -- Salary - nvarchar(100)
          N'SZ' , -- Address - nvarchar(100)
          N'3'  -- MaritalStatus - nvarchar(100)
        )
GO

　　Default.aspx:

    <link href="~/Styles/Site.css" rel="stylesheet" type="text/css" />
    <script src="JQGridReq/jquery-1.9.0.min.js" type="text/javascript"></script>
    <link href="JQGridReq/jquery-ui-1.9.2.custom.css" rel="stylesheet" type="text/css" />
    <script src="JQGridReq/jquery.jqGrid.js" type="text/javascript"></script>
    <link href="JQGridReq/ui.jqgrid.css" rel="stylesheet" type="text/css" />
    <script src="JQGridReq/grid.locale-cn.js" type="text/javascript"></script>

    <h2>
        Welcome To Kodoths JQGrid Demo
    </h2>
    <table id="jQGridDemo">
    </table>
    <div id="jQGridDemoPager">
    </div>
    <script type="text/javascript">           
        jQuery("#jQGridDemo").jqGrid({
            url: 'JQGridHandler.ashx',
            datatype: "json",
            height:500,
            colNames: ['Id', 'First Name', 'Last Name', 'Last 4 SSN', 'Department', 'Age', 'Salary', "Address", 'Marital Status'],  //名称
            colModel: [
                        { name: '_id', index: '_id', width: 20, stype: 'text' },
   		                { name: 'FirstName', index: 'FirstName', width: 150, stype: 'text', sortable: true, editable: true },
   		                { name: 'LastName', index: 'LastName', width: 150, editable: true },
   		                { name: 'LastSSN', index: 'LastSSN', width: 60, editable: true },
   		                { name: 'Department', index: 'Department', width: 80, align: "right", editable: true },
   		                { name: 'Age', index: 'Age', width: 40, align: "right", editable: true },
   		                { name: 'Salary', index: 'Salary', width: 80, align: "right", editable: true },
   		                { name: 'Address', index: 'Address', width: 150, sortable: false, editable: true },
                        { name: 'MaritalStatus', index: 'MaritalStatus', width: 100, sortable: false, editable: true }
   	                  ],
            rowNum: 10,
            mtype: 'GET',
            loadonce: true,
            rowList: [10, 20, 30],
            pager: '#jQGridDemoPager',
            sortname: '_id',            
            viewrecords: true,
            sortorder: 'desc',
            caption: "List Employee Details 客户列表",
            editurl: 'JQGridHandler.ashx' //http://localhost:58404/
        });

        $('#jQGridDemo').jqGrid('navGrid', '#jQGridDemoPager',
                   {
                       edit: true,
                       add: true,
                       del: true,
                       search: true,
                       searchtext: "Search",
                       addtext: "Add",
                       edittext: "Edit",
                       deltext: "Delete"
                   },
                   {   //EDIT
                       //                       height: 300,
                       //                       width: 400,
                       //                       top: 50,
                       //                       left: 100,
                       //                       dataheight: 280,
                       closeOnEscape: true, //Closes the popup on pressing escape key
                       reloadAfterSubmit: true,
                       drag: true,
                       afterSubmit: function (response, postdata) {
                           if (response.responseText == "") {

                               $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid'); //Reloads the grid after edit
                               return [true, '']
                           }
                           else {
                               $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid'); //Reloads the grid after edit
                               return [false, response.responseText]//Captures and displays the response text on th Edit window
                           }
                       },
                       editData: {
                           EmpId: function () {
                               var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
                               var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
                               return value;
                           }
                       }
                   },
                   {
                       closeAfterAdd: true, //Closes the add window after add
                       afterSubmit: function (response, postdata) {
                           if (response.responseText == "") {

                               $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid')//Reloads the grid after Add
                               return [true, '']
                           }
                           else {
                               $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid')//Reloads the grid after Add
                               return [false, response.responseText]
                           }
                       }
                   },
                   {   //DELETE
                       closeOnEscape: true,
                       closeAfterDelete: true,
                       reloadAfterSubmit: true,
                       closeOnEscape: true,
                       drag: true,
                       afterSubmit: function (response, postdata) {
                           if (response.responseText == "") {

                               $("#jQGridDemo").trigger("reloadGrid", [{ current: true}]);
                               return [false, response.responseText]
                           }
                           else {
                               $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid');
                               return [true, response.responseText]
                           }
                       },
                       delData: {
                           EmpId: function () {
                               var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
                               var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
                               return value;
                           }
                       }
                   },
                   {//SEARCH
                       closeOnEscape: true

                   }
            );
              
    </script>

　　JQGridHandler.ashx:

   public class JQGridHandler : IHttpHandler
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        public void ProcessRequest(HttpContext context)
        {
            System.Collections.Specialized.NameValueCollection forms = context.Request.Form;
            string strOperation = forms.Get("oper");

            MONGOConnect objMC = new MONGOConnect();//Helper Class
            var collectionEmployee = objMC.GetMongoCollection("Employee");//Gets Employee Collection

            List<Employee> emy = new List<Employee>();
            //Employee ep = new Employee();
            //ep._id = "1";
            //ep.Address = "sz";
            //ep.Age = 40;
            //ep.Department = "it";
            //ep.FirstName = "du";
            //ep.LastName = "geovin";
            //ep.LastSSN = "N0001";
            //ep.MaritalStatus = "1";
            //ep.Salary = "4000";
            //emy.Add(ep);
            //ep = new Employee();
            //ep._id = "2";
            //ep.Address = "深圳";
            //ep.Age = 40;
            //ep.Department = "人事部";
            //ep.FirstName = "涂";
            //ep.LastName = "涂聚文";
            //ep.LastSSN = "N0002";
            //ep.MaritalStatus = "1";
            //ep.Salary = "8000";
            //emy.Add(ep);
            emy = getList();

            string strResponse = string.Empty;
            //查询
            if (strOperation == null)
            {
                //oper = null which means its first load.
                var jsonSerializer = new JavaScriptSerializer();
                context.Response.Write(jsonSerializer.Serialize(emy));//collectionEmployee.AsQueryable<Employee>().ToList<Employee>()



            }
            else if (strOperation == "del") //删除
            {
                var query = Query.EQ("_id", forms.Get("EmpId").ToString());
                string de = forms.Get("EmpId").ToString();
                //collectionEmployee.Remove(query);
                strResponse = "Employee record successfully removed:"+de;
                context.Response.Write(strResponse);
            }
            else
            {
                 string strOut=string.Empty;
                 AddEdit(forms, collectionEmployee, out strOut);
                 context.Response.Write(strOut);
            }
                       
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private List<Employee> getList()
        {
             List<Employee> users = new List<Employee>();
            string connectionString = @"Data Source=GEOVINDU; Initial Catalog=AdventureWorks2012; User ID=sa; Password=geovindu";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "SELECT * FROM dbo.Employee";
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        Employee user;
                        while (dataReader.Read())
                        {
                            user = new Employee();
                            user._id = Convert.ToString(dataReader["_id"]);
                            user.Address = Convert.ToString(dataReader["Address"]);
                            user.Age = Convert.ToInt32(dataReader["Age"]);
                            user.Department = Convert.ToString(dataReader["Department"]);
                            user.FirstName = Convert.ToString(dataReader["FirstName"]);
                            user.LastName = Convert.ToString(dataReader["LastName"]);
                            user.LastSSN = Convert.ToString(dataReader["LastSSN"]);
                            user.MaritalStatus = Convert.ToString(dataReader["MaritalStatus"]);
                            user.Salary = Convert.ToString(dataReader["Salary"]);
                            users.Add(user);
                        }
                    }
                }
            }
            return users;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="numberOfRows"></param>
        /// <param name="pageIndex"></param>
        /// <param name="sortColumnName"></param>
        /// <param name="sortOrderBy"></param>
        /// <param name="totalRecords"></param>
        /// <returns></returns>
        private List<Employee> GetUsers(string numberOfRows, string pageIndex, string sortColumnName, string sortOrderBy, out int totalRecords)
        {
            List<Employee> users = new List<Employee>();
            string connectionString = @"Data Source=GEOVINDU; Initial Catalog=AdventureWorks2012; User ID=sa; Password=geovindu";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "SelectjqGridUsers";
                    command.CommandType = CommandType.StoredProcedure;

                    SqlParameter paramPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
                    paramPageIndex.Value = Convert.ToInt32(pageIndex);
                    command.Parameters.Add(paramPageIndex);

                    SqlParameter paramColumnName = new SqlParameter("@SortColumnName", SqlDbType.VarChar, 50);
                    paramColumnName.Value = sortColumnName;
                    command.Parameters.Add(paramColumnName);

                    SqlParameter paramSortorderBy = new SqlParameter("@SortOrderBy", SqlDbType.VarChar, 4);
                    paramSortorderBy.Value = sortOrderBy;
                    command.Parameters.Add(paramSortorderBy);

                    SqlParameter paramNumberOfRows = new SqlParameter("@NumberOfRows", SqlDbType.Int);
                    paramNumberOfRows.Value = Convert.ToInt32(numberOfRows);
                    command.Parameters.Add(paramNumberOfRows);

                    SqlParameter paramTotalRecords = new SqlParameter("@TotalRecords", SqlDbType.Int);
                    totalRecords = 0;
                    paramTotalRecords.Value = totalRecords;
                    paramTotalRecords.Direction = ParameterDirection.Output;
                    command.Parameters.Add(paramTotalRecords);


                    connection.Open();
                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        Employee user;
                        while (dataReader.Read())
                        {
                            user = new Employee();
                            user._id = Convert.ToString(dataReader["_id"]);
                            user.Address = Convert.ToString(dataReader["UserName"]);
                            user.Age = Convert.ToInt32(dataReader["Age"]);
                            user.Department = Convert.ToString(dataReader["Department"]);
                            user.FirstName = Convert.ToString(dataReader["FirstName"]);
                            user.LastName = Convert.ToString(dataReader["LastName"]);
                            user.LastSSN = Convert.ToString(dataReader["LastSSN"]);
                            user.MaritalStatus = Convert.ToString(dataReader["MaritalStatus"]);
                            user.Salary = Convert.ToString(dataReader["Salary"]); 
                            users.Add(user);
                        }
                    }
                    totalRecords = (int)paramTotalRecords.Value;
                }

                return users;
            }

        }  



        /// <summary>
        /// 添加，修改
        /// </summary>
        /// <param name="forms"></param>
        /// <param name="collectionEmployee"></param>
        /// <param name="strResponse"></param>
        private void AddEdit(NameValueCollection forms, MongoCollection collectionEmployee,out string strResponse)
        {
            string strOperation = forms.Get("oper");
            string strEmpId = string.Empty;
            if (strOperation == "add") //添加
            {
                var result = 40;// collectionEmployee.AsQueryable<Employee>().Select(c => c._id).Max();
                strEmpId = (Convert.ToInt32(result) + 1).ToString();
            }
            else if (strOperation == "edit") //编辑
            {
                strEmpId = forms.Get("EmpId").ToString();
               
            }
           
            string strFirstName = forms.Get("FirstName").ToString();
            string strLastName = forms.Get("LastName").ToString();
            string strLastSSN = forms.Get("LastSSN").ToString();
            string strDepartment = forms.Get("Department").ToString();
            string strAge = forms.Get("Age").ToString();
            string strSalary = forms.Get("Salary").ToString();
            string strAddress = forms.Get("Address").ToString();
            string strMaritalStatus = forms.Get("MaritalStatus").ToString();
            Employee objEmp = new Employee();
            objEmp._id = strEmpId;
            objEmp.FirstName = strFirstName;
            objEmp.LastName = strLastName;
            objEmp.LastSSN = strLastSSN;
            objEmp.Department = strDepartment;
            objEmp.Age = Convert.ToInt32(strAge);
            objEmp.Address = strAddress;
            objEmp.MaritalStatus = strMaritalStatus;
            objEmp.Salary = strSalary;

            //collectionEmployee.Save(objEmp);
            strResponse = "Employee record successfully updated";
        }
    }