JqGrid: Add,Edit,Del in asp.net

--jQGridDemo AdventureWorks2012CREATE ?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))GOSELECT * FROM dbo.EmployeeGOINSERT 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) ???????)GOINSERT 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) ???????)GOINSERT 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

 ???<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>

 ??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-PC\GEOVINDU; Initial Catalog=AdventureWorks2012; User ID=sa; Password=770214"; ???????????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"; ???????} ???}