欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

JqGrid: Add,Edit,Del in asp.net

程序员文章站 2022-03-25 11:50:25
https://github.com/1rosehip/jplist https://github.com/free-jqgrid/jqGrid https://plugins.jquery.com/tag/pagination/ https://dotnetsourcedileep.codeple ......

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";
        }
    }