【ASP.NET MVC系列】浅谈jqGrid 在ASP.NET MVC中增删改查

ASP.NET MVC系列文章

【01】浅谈Google Chrome浏览器（理论篇）

【02】浅谈Google Chrome浏览器（操作篇）（上）

【03】浅谈Google Chrome浏览器（操作篇）（下）

【04】浅谈ASP.NET框架

【05】浅谈ASP.NET MVC运行过程

【06】浅谈ASP.NET MVC 控制器

【07】浅谈ASP.NET MVC 路由

【08】浅谈ASP.NET MVC 视图

【09】浅谈ASP.NET MVC 视图与控制器传递数据

【10】浅谈jqGrid 在ASP.NET MVC中增删改查

【11】浅谈ASP.NET 页面之间传值的几种方式

【12】浅谈缓存技术在ASP.NET中的运用

【13】浅谈NuGet在VS中的运用

【14】浅谈ASP.NET 程序发布过程

【15】浅谈数据注解和验证

【16】浅谈依赖注入

【17】浅谈表单和HTML辅助方法

【18】浅谈基于APS.NET身份验证

【19】浅谈ASP.NET MVC 模型

【20】浅谈ASP.NET MVC 单元测试

【21】浅谈ASP.NET MVC网络安全;

【22】浅谈ASP.NET MVC八大类扩展

【23】再谈ASP.NET MVC Routing

【24】浅谈ASP.NET 高级话题

【25】浅谈大型ASP.NET MVC项目（含DEMO）

【26】下一系列：ASP.NET WebAPI

1 概述

本篇文章主要是关于JqGrid的，主要功能包括使用JqGrid增删查改，导入导出，废话不多说，直接进入正题。

2 Demo相关

2.1 Demo展示

第一部分

第二部分

2.2 源码和DB下载

本来国庆上传到github上的，现在github有点问题，因此后期再传到github，有需要源码的，可以在评论区留下自己联系联系方式，我直接传给你。

3 公共模块

3.1 Model实体—EmployeeInfo

 1 using MVCCrud.Areas.DBUtility;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Data;
 5 using System.Data.SqlClient;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Web;
 9 
10 namespace MVCCrud.Areas.JqGridDemo.Models
11 {
12    //EmployeeInfo实体类
13     public class EmployeeInfo
14     {
15         public string EmployeeID { get; set; }
16         public string EmployeeName { get; set; }
17         public string EmployeeMajor { get; set; }
18         public string EmployeeDepartment { get; set; }
19         public string EmployeeTel { get; set; }
20         public string EmployeeEmail { get; set; }
21         public string EmployeeJiGuan { get; set; }
22         public string EmployeeAddress { get; set; }
23         public string EmployeePosition { get; set; }
24         public DateTime EmployeeBirthday { get; set; }
25     }
26 }

View Code

3.2 DBHelper帮助类

  1 using System;
  2 using System.Collections;
  3 using System.Collections.Generic;
  4 using System.Configuration;
  5 using System.Data;
  6 using System.Data.SqlClient;
  7 using System.Linq;
  8 using System.Security.Cryptography;
  9 using System.Text;
 10 using System.Web;
 11 using System.Web.UI.WebControls;
 12 
 13 namespace MVCCrud.Areas.DBUtility
 14 {
 15     public abstract class DbHelperSQL
 16     {
 17         /*
 18          * content:DbHelper帮助类
 19          *author:Alan_beijing
 20          * date:2017-10-01
 21         */
 22         public DbHelperSQL()
 23         {
 24             //构造函数
 25         }
 26         protected static string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
 27         protected static SqlConnection Connection;
 28         //定义数据库的打开和关闭方法
 29         protected static void Open()
 30         {
 31             if (Connection == null)
 32             {
 33                 Connection = new SqlConnection(ConnectionString);
 34             }
 35             if (Connection.State.Equals(ConnectionState.Closed))
 36             {
 37                 Connection.Open();
 38             }
 39         }
 40         protected static void Close()
 41         {
 42             if (Connection != null)
 43             {
 44                 Connection.Close();
 45             }
 46         }
 47 
 48         // 公有方法，获取数据，返回一个DataSet。    
 49         public static DataSet GetDataSet(string SqlString)
 50         {
 51             using (SqlConnection connection = new SqlConnection(ConnectionString))
 52             {
 53                 connection.Open();
 54                 using (SqlCommand cmd = new SqlCommand(SqlString, connection))
 55                 {
 56                     using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 57                     {
 58                         DataSet ds = new DataSet();
 59                         try
 60                         {
 61                             da.Fill(ds, "ds");
 62                             cmd.Parameters.Clear();
 63                         }
 64                         catch (System.Data.SqlClient.SqlException ex)
 65                         {
 66                             throw new Exception(ex.Message);
 67                         }
 68                         connection.Close();
 69                         return ds;
 70                     }
 71                 }
 72             }
 73         }
 74         // 公有方法，获取数据，返回一个DataTable。    
 75         public static DataTable GetDataTable(string SqlString)
 76         {
 77             DataSet dataset = GetDataSet(SqlString);
 78             return dataset.Tables[0];
 79         }
 80         public static int ExecuteSQL(String SqlString, Hashtable MyHashTb)
 81         {
 82             int count = -1;
 83             SqlConnection connectiontemp = new SqlConnection(ConnectionString);
 84             connectiontemp.Open();
 85             try
 86             {
 87                 SqlCommand cmd = new SqlCommand(SqlString, connectiontemp);
 88                 foreach (DictionaryEntry item in MyHashTb)
 89                 {
 90                     string[] CanShu = item.Key.ToString().Split('|');
 91                     if (CanShu[1].ToString().Trim() == "string")
 92                     {
 93                         cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
 94                     }
 95                     else if (CanShu[1].ToString().Trim() == "int")
 96                     {
 97                         cmd.Parameters.Add(CanShu[0], SqlDbType.Int);
 98                     }
 99                     else if (CanShu[1].ToString().Trim() == "text")
100                     {
101                         cmd.Parameters.Add(CanShu[0], SqlDbType.Text);
102                     }
103                     else if (CanShu[1].ToString().Trim() == "datetime")
104                     {
105                         cmd.Parameters.Add(CanShu[0], SqlDbType.DateTime);
106                     }
107                     else
108                     {
109                         cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
110                     }
111                     cmd.Parameters[CanShu[0]].Value = item.Value.ToString();
112                 }
113                 count = cmd.ExecuteNonQuery();
114             }
115             catch
116             {
117                 count = -1;
118             }
119             finally
120             {
121                 connectiontemp.Close();
122             }
123             return count;
124         }
125         // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1
126         public static int ExecuteSQL(String SqlString)
127         {
128             int count = -1;
129             SqlConnection connectionTemp = new SqlConnection(ConnectionString);
130             connectionTemp.Open();
131             try
132             {
133                 SqlCommand cmd = new SqlCommand(SqlString, connectionTemp);
134                 count = cmd.ExecuteNonQuery();
135             }
136             catch
137             {
138                 count = -1;
139             }
140             finally
141             {
142                 connectionTemp.Close();
143             }
144             return count;
145         }
146         // 公有方法，执行一组Sql语句。返回是否成功,采用事务管理，发现异常时回滚数据
147         public static bool ExecuteSQL(string[] SqlStrings)
148         {
149             bool success = true;
150             SqlConnection connectionTemp = new SqlConnection(ConnectionString);
151             connectionTemp.Open();
152             SqlCommand cmd = new SqlCommand();
153             SqlTransaction trans = Connection.BeginTransaction();
154             cmd.Connection = connectionTemp;
155             cmd.Transaction = trans;
156             try
157             {
158                 foreach (string str in SqlStrings)
159                 {
160                     cmd.CommandText = str;
161                     cmd.ExecuteNonQuery();
162                 }
163                 trans.Commit();
164             }
165             catch
166             {
167                 success = false;
168                 trans.Rollback();
169             }
170             finally
171             {
172                 connectionTemp.Close();
173             }
174             return success;
175         }
176         // 执行一条计算查询结果语句，返回查询结果（object）。        
177         public static object GetSingle(string SQLString)
178         {
179             using (SqlConnection connection = new SqlConnection(ConnectionString))
180             {
181                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
182                 {
183                     try
184                     {
185                         connection.Open();
186                         object obj = cmd.ExecuteScalar();
187                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
188                         {
189                             connection.Close();
190                             return null;
191                         }
192                         else
193                         {
194                             connection.Close();
195                             return obj;
196                         }
197                     }
198                     catch (System.Data.SqlClient.SqlException e)
199                     {
200                         connection.Close();
201                         return null;
202                         //throw e;
203                     }
204                 }
205             }
206         }
207         public static object GetSingle(string SQLString, int Times)
208         {
209             using (SqlConnection connection = new SqlConnection(ConnectionString))
210             {
211                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
212                 {
213                     try
214                     {
215                         connection.Open();
216                         cmd.CommandTimeout = Times;
217                         object obj = cmd.ExecuteScalar();
218                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
219                         {
220                             connection.Close();
221                             return null;
222                         }
223                         else
224                         {
225                             connection.Close();
226                             return obj;
227                         }
228                     }
229                     catch (System.Data.SqlClient.SqlException e)
230                     {
231                         connection.Close();
232                         //throw e;
233                         return null;
234                     }
235                 }
236             }
237         }
238         public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
239         {
240             using (SqlConnection connection = new SqlConnection(ConnectionString))
241             {
242                 using (SqlCommand cmd = new SqlCommand())
243                 {
244                     try
245                     {
246                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
247                         object obj = cmd.ExecuteScalar();
248                         cmd.Parameters.Clear();
249                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
250                         {
251                             connection.Close();
252                             return null;
253                         }
254                         else
255                         {
256                             connection.Close();
257                             return obj;
258                         }
259                     }
260                     catch (System.Data.SqlClient.SqlException e)
261                     {
262                         connection.Close();
263                         //throw e;
264                         return null;
265                     }
266                 }
267             }
268         }
269         // 执行SQL语句，返回影响的记录数
270         public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
271         {
272             using (SqlConnection connection = new SqlConnection(ConnectionString))
273             {
274                 using (SqlCommand cmd = new SqlCommand())
275                 {
276                     try
277                     {
278                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
279                         int rows = cmd.ExecuteNonQuery();
280                         cmd.Parameters.Clear();
281                         connection.Close();
282                         return rows;
283                     }
284                     catch (System.Data.SqlClient.SqlException e)
285                     {
286                         connection.Close();
287                         //throw e;
288                         return 0;
289                     }
290                 }
291             }
292         }
293         //执行查询语句，返回DataSet
294         public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
295         {
296             using (SqlConnection connection = new SqlConnection(ConnectionString))
297             {
298                 SqlCommand cmd = new SqlCommand();
299                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
300                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
301                 {
302                     DataSet ds = new DataSet();
303                     try
304                     {
305                         da.Fill(ds, "ds");
306                         cmd.Parameters.Clear();
307 
308                     }
309                     catch (System.Data.SqlClient.SqlException ex)
310                     {
311                         throw new Exception(ex.Message);
312                     }
313                     connection.Close();
314                     return ds;
315                 }
316             }
317         }
318         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
319         {
320             if (conn.State != ConnectionState.Open)
321                 conn.Open();
322             cmd.Connection = conn;
323             cmd.CommandText = cmdText;
324             if (trans != null)
325                 cmd.Transaction = trans;
326             cmd.CommandType = CommandType.Text;//cmdType;
327             if (cmdParms != null)
328             {
329                 foreach (SqlParameter parameter in cmdParms)
330                 {
331                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
332                         (parameter.Value == null))
333                     {
334                         parameter.Value = DBNull.Value;
335                     }
336                     cmd.Parameters.Add(parameter);
337                 }
338             }
339         }
340     }
341 }

View Code

4 数据访问层DAL

4.1 对EmployeeInfo的CRUD

  1 using MVCCrud.Areas.DBUtility;
  2 using MVCCrud.Areas.JqGridDemo.Models;
  3 using System;
  4 using System.Collections.Generic;
  5 using System.Data;
  6 using System.Data.SqlClient;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Web;
 10 
 11 namespace MVCCrud.Areas.DAL
 12 {
 13     public class EmployeeInfoToCRUD
 14     {
 15         /// <summary>
 16         /// 增加一条数据
 17         /// </summary>
 18         /// <param name="employeeInfo">EmployeeInfo对象</param>
 19         /// <returns>添加数据是否成功</returns>
 20         public int DALEmployeeInfoToDdd(EmployeeInfo employeeInfo)
 21         {
 22             StringBuilder strSql = new StringBuilder();
 23             strSql.Append("insert into EmployeeInfo(");
 24             strSql.Append("EmployeeID,EmployeeName,EmployeeMajor,EmployeeDepartment,EmployeeTel,EmployeeEmail,EmployeeJiGuan,EmployeeAddress,EmployeePosition,EmployeeBirthday)");
 25             strSql.Append(" values (");
 26             strSql.Append("@EmployeeID,@EmployeeName,@EmployeeMajor,@EmployeeDepartment,@EmployeeTel,@EmployeeEmail,@EmployeeJiGuan,@EmployeeAddress,@EmployeePosition,@EmployeeBirthday)");
 27             strSql.Append(";select @@IDENTITY");
 28             SqlParameter[] parameters =
 29                 {
 30                     new SqlParameter("@EmployeeID", SqlDbType.VarChar,50),
 31                     new SqlParameter("@EmployeeName", SqlDbType.VarChar,50),
 32                     new SqlParameter("@EmployeeMajor", SqlDbType.Text),
 33                     new SqlParameter("@EmployeeDepartment",SqlDbType.VarChar,50),
 34                     new SqlParameter("@EmployeeTel", SqlDbType.VarChar,50),
 35                     new SqlParameter("@EmployeeEmail", SqlDbType.VarChar,50),
 36                     new SqlParameter("@EmployeeJiGuan", SqlDbType.VarChar,50),
 37                     new SqlParameter("@EmployeeAddress", SqlDbType.VarChar,500),
 38                     new SqlParameter("@EmployeePosition", SqlDbType.VarChar, 50),
 39                     new SqlParameter("@EmployeeBirthday", SqlDbType.DateTime)
 40                  };
 41 
 42             parameters[0].Value = employeeInfo.EmployeeID;
 43             parameters[1].Value = employeeInfo.EmployeeName;
 44             parameters[2].Value = employeeInfo.EmployeeMajor;
 45             parameters[3].Value = employeeInfo.EmployeeDepartment;
 46             parameters[4].Value = employeeInfo.EmployeeTel;
 47             parameters[5].Value = employeeInfo.EmployeeEmail;
 48             parameters[6].Value = employeeInfo.EmployeeJiGuan;
 49             parameters[7].Value = employeeInfo.EmployeeAddress;
 50             parameters[8].Value = employeeInfo.EmployeePosition;
 51             parameters[9].Value = employeeInfo.EmployeeBirthday;
 52 
 53             object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
 54             if (obj == null)
 55             {
 56                 return 1;
 57             }
 58             else
 59             {
 60                 return Convert.ToInt32(obj);
 61             }
 62         }
 63 
 64         /// <summary>
 65         /// 删除一条数据
 66         /// </summary>
 67         /// <param name="employeeID">查询参数：员工ID</param>
 68         /// <returns>是否成功删除</returns>
 69         public int DALEmployeeInfoToDelete(string employeeID)
 70         {
 71             StringBuilder strSql = new StringBuilder();
 72             strSql.Append("DELETE EmployeeInfo");
 73             strSql.Append(" WHERE EmployeeID=@EmployeeID ");
 74             SqlParameter[] parameters = {
 75                     new SqlParameter("@EmployeeID",SqlDbType.VarChar,50)};
 76             parameters[0].Value = employeeID;
 77 
 78             return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
 79         }
 80 
 81         /// <summary>
 82         /// 获取EmployeeInfo数据表
 83         /// </summary>
 84         /// <returns>返回EmployeeInfo数据表</returns>
 85         public DataTable DALEmployeeInfoToGetTable()
 86         {
 87             StringBuilder strSql = new StringBuilder();
 88             strSql.Append("SELECT * FROM EmployeeInfo ");
 89             return DbHelperSQL.GetDataTable(strSql.ToString());
 90         }
 91 
 92         /// <summary>
 93         /// 根据EmployeeName查询EmployeeInfo实体表数据
 94         /// </summary>
 95         /// <param name="EmployeeName">查询参数：EmployeeName</param>
 96         /// <returns>返回查询到的DataTable</returns>
 97         public DataTable DALEmployeeInfoToGetTable(string EmployeeName)
 98         {
 99             string strSql = @"SELECT * FROM EmployeeInfo WHERE EmployeeName=@EmployeeName";
100             SqlParameter[] parameters = {
101                     new SqlParameter("@EmployeeName",SqlDbType.VarChar,50)
102             };
103             parameters[0].Value = EmployeeName;
104             return DbHelperSQL.Query(strSql, parameters).Tables["ds"];
105         }
106         /// <summary>
107         /// 根据employeeInfo条件更新数据
108         /// </summary>
109         /// <param name="employeeInfo">更新条件：employeeInfo</param>
110         public void DALEmployeeInfoToUpdate(EmployeeInfo employeeInfo)
111         {
112             StringBuilder strSql = new StringBuilder();
113             strSql.Append("UPDATE EmployeeInfo SET ");
114             strSql.Append("EmployeeName=@EmployeeName,EmployeeMajor=@EmployeeMajor,");
115             strSql.Append("EmployeeDepartment=@EmployeeDepartment,EmployeeTel=@EmployeeTel,EmployeeEmail=@EmployeeEmail,");
116             strSql.Append("EmployeeJiGuan=@EmployeeJiGuan,EmployeeAddress=@EmployeeAddress,EmployeePosition=@EmployeePosition,EmployeeBirthday=@EmployeeBirthday");
117             strSql.Append(" WHERE EmployeeID=@EmployeeID");
118             SqlParameter[] parameters = {
119                     new SqlParameter("@EmployeeID", SqlDbType.VarChar,50),
120                     new SqlParameter("@EmployeeName", SqlDbType.VarChar,50),
121                     new SqlParameter("@EmployeeMajor", SqlDbType.Text),
122                     new SqlParameter("@EmployeeDepartment",SqlDbType.VarChar,50),
123                     new SqlParameter("@EmployeeTel", SqlDbType.VarChar,50),
124                     new SqlParameter("@EmployeeEmail", SqlDbType.VarChar,50),
125                     new SqlParameter("@EmployeeJiGuan", SqlDbType.VarChar,50),
126                     new SqlParameter("@EmployeeAddress", SqlDbType.VarChar,500),
127                     new SqlParameter("@EmployeePosition", SqlDbType.VarChar, 50),
128                     new SqlParameter("@EmployeeBirthday", SqlDbType.DateTime)
129             };
130             parameters[0].Value = employeeInfo.EmployeeID;
131             parameters[1].Value = employeeInfo.EmployeeName;
132             parameters[2].Value = employeeInfo.EmployeeMajor;
133             parameters[3].Value = employeeInfo.EmployeeDepartment;
134             parameters[4].Value = employeeInfo.EmployeeTel;
135             parameters[5].Value = employeeInfo.EmployeeEmail;
136             parameters[6].Value = employeeInfo.EmployeeJiGuan;
137             parameters[7].Value = employeeInfo.EmployeeAddress;
138             parameters[8].Value = employeeInfo.EmployeePosition;
139             parameters[9].Value = employeeInfo.EmployeeBirthday;
140 
141             DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
142         }
143     }
144 }

View Code

4.2 简要分析

5 控制器层

5.1 方法

  1 using MVCCrud.Areas.JqGridDemo.Models;
  2 using Newtonsoft.Json;
  3 using System;
  4 using System.Collections.Generic;
  5 using System.Configuration;
  6 using System.Data;
  7 using System.Data.SqlClient;
  8 using System.IO;
  9 using System.Linq;
 10 using System.Web;
 11 using System.Web.Mvc;
 12 using MVCCrud.Areas.DAL;
 13 
 14 namespace MVCCrud.Areas.JqGridDemo.Controllers
 15 {
 16     public class JqGridCRUDController : Controller
 17     {
 18         // GET: JqGridDemo/JqGridCRUD
 19         //初始视图表
 20         public ActionResult Index()
 21         {
 22             return View();
 23         }
 24          //导入模板
 25         public ActionResult GetEmployeeInfoTemple()
 26         {
 27             string path = Server.MapPath(@"~/Content/Upload/");
 28             string fileName = "EmployeeImport.xlsx";
 29             return File(new FileStream(path + fileName, FileMode.Open, FileAccess.Read), "xls", fileName);
 30         }
 31 
 32         //预设置初始化数据表
 33         public ActionResult EmployeeInfoToLoad()
 34         {
 35             List<EmployeeInfo> ltPI = new List<EmployeeInfo>();
 36             EmployeeInfoToCRUD employeeInfoToCRUD = new EmployeeInfoToCRUD();
 37             DataTable dt = employeeInfoToCRUD.DALEmployeeInfoToGetTable();
 38             for (int i = 0; i < dt.Rows.Count; i++)
 39             {
 40                 EmployeeInfo custInfo = new EmployeeInfo();
 41                 custInfo.EmployeeID = dt.Rows[i]["EmployeeID"].ToString();
 42                 custInfo.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
 43                 custInfo.EmployeeMajor = dt.Rows[i]["EmployeeMajor"].ToString();
 44                 custInfo.EmployeeDepartment = dt.Rows[i]["EmployeeDepartment"].ToString();
 45                 custInfo.EmployeeTel = dt.Rows[i]["EmployeeTel"].ToString();
 46                 custInfo.EmployeeEmail = dt.Rows[i]["EmployeeEmail"].ToString();
 47                 custInfo.EmployeeJiGuan = dt.Rows[i]["EmployeeJiGuan"].ToString();
 48                 custInfo.EmployeeAddress = dt.Rows[i]["EmployeeAddress"].ToString();
 49                 custInfo.EmployeePosition = dt.Rows[i]["EmployeePosition"].ToString();
 50                 if (dt.Rows[i]["EmployeeBirthday"] != System.DBNull.Value)
 51                 {
 52                     custInfo.EmployeeBirthday = Convert.ToDateTime(dt.Rows[i]["EmployeeBirthday"]);
 53                 }
 54                 ltPI.Add(custInfo);
 55                 TempData["CustomerInfo"] = ltPI;
 56             }
 57             return Content(ToJsonString(ltPI));
 58         }
 59 
 60         //预设置添加数据
 61         public void EmployeeInfoToDdd(EmployeeInfo employeeInfo)
 62         {
 63             EmployeeInfoToCRUD employeeInfoToCRUD = new EmployeeInfoToCRUD();
 64             employeeInfoToCRUD.DALEmployeeInfoToDdd(employeeInfo);
 65         }
 66         //预设置删除数据
 67         [HttpPost]
 68         public void EmployeeInfoToDel(string EmployeeID)
 69         {
 70             EmployeeInfoToCRUD employeeInfoToCRUD = new EmployeeInfoToCRUD();
 71             employeeInfoToCRUD.DALEmployeeInfoToDelete(EmployeeID);
 72         }
 73 
 74         //预设置更新数据
 75         public void EmployeeInfoToUpdate(EmployeeInfo employeeInfo)
 76         {
 77             EmployeeInfoToCRUD employeeInfoToCRUD = new EmployeeInfoToCRUD();
 78             employeeInfoToCRUD.DALEmployeeInfoToUpdate(employeeInfo);
 79         }
 80 
 81         //预设置查询数据
 82         public ActionResult EmployeeInfoToSearch(string employeeName)
 83         {
 84             List<EmployeeInfo> ltPI = new List<EmployeeInfo>();
 85             EmployeeInfoToCRUD employeeInfoToCRUD = new EmployeeInfoToCRUD();
 86             DataTable dt = employeeInfoToCRUD.DALEmployeeInfoToGetTable(employeeName);
 87             for (int i = 0; i < dt.Rows.Count; i++)
 88             {
 89                 EmployeeInfo custInfo = new EmployeeInfo();
 90                 custInfo.EmployeeID = dt.Rows[i]["EmployeeID"].ToString();
 91                 custInfo.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
 92                 custInfo.EmployeeMajor = dt.Rows[i]["EmployeeMajor"].ToString();
 93                 custInfo.EmployeeDepartment = dt.Rows[i]["EmployeeDepartment"].ToString();
 94                 custInfo.EmployeeTel = dt.Rows[i]["EmployeeTel"].ToString();
 95                 custInfo.EmployeeEmail = dt.Rows[i]["EmployeeEmail"].ToString();
 96                 custInfo.EmployeeJiGuan = dt.Rows[i]["EmployeeJiGuan"].ToString();
 97                 custInfo.EmployeeAddress = dt.Rows[i]["EmployeeAddress"].ToString();
 98                 custInfo.EmployeePosition = dt.Rows[i]["EmployeePosition"].ToString();
 99                 if (dt.Rows[i]["EmployeeBirthday"] != System.DBNull.Value)
100                 {
101                     custInfo.EmployeeBirthday = Convert.ToDateTime(dt.Rows[i]["EmployeeBirthday"]);
102                 }
103                 ltPI.Add(custInfo);
104                 TempData["CustomerInfo"] = ltPI;
105             }
106             return Content(ToJsonString(ltPI));
107         }
108 
109         /// <summary>
110         ///  为Oject对象增加ToJsonString方法（注意对项目添加Newtonsoft.Json.dll引用）
111         /// </summary>
112         /// <param name="obj"></param>
113         /// <returns></returns>
114         public string ToJsonString(Object obj)
115         {
116             JsonSerializerSettings jsSettings = new JsonSerializerSettings();
117             jsSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
118             return JsonConvert.SerializeObject(obj, jsSettings);
119         }
120     }
121 }
122 
123

View Code

5.2 分析

6 展示层

6.1 View Code

  1 @{
  2     Layout = null;
  3 }
  4 
  5 <!DOCTYPE html>
  6 
  7 <html>
  8 <head>
  9     <meta name="viewport" content="width=device-width" />
 10     <link href="~/OuterLibrary/jquery-ui-themes-1.12.1/jquery-ui.css" rel="stylesheet" />
 11     <link href="~/OuterLibrary/Guriddo_jqGrid_JS_5.2.1/src/css/ui.jqgrid.css" rel="stylesheet" />
 12     <script src="~/OuterLibrary/Guriddo_jqGrid_JS_5.2.1/js/jquery-1.11.0.min.js"></script>
 13     <script src="~/OuterLibrary/Guriddo_jqGrid_JS_5.2.1/src/jquery.jqGrid.js"></script>
 14     <script src="//apps.bdimg.com/libs/jqueryui/1.10.4/jquery-ui.min.js"></script>
 15     <script src="~/OuterLibrary/tonytomov-jqGrid-6659334/js/i18n/grid.locale-cn.js"></script>
 16     <script type="text/javascript" language="javascript" src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
 17     <script src="~/OuterLibrary/Guriddo_jqGrid_JS_5.2.1/src/grid.export.js"></script>
 18     <script src="~/OuterLibrary/Guriddo_jqGrid_JS_5.2.1/src/grid.import.js"></script>
 19     <title>员工信息表</title>
 20     <script type="text/javascript">
 21         $(document).ready(function () {
 22             //添加
 23             $("#btn_add").click(function () {
 24                 //var model = jQuery("#JqGrid-table").jqGrid('getRowData', EmployeeID);
 25                 $("#AddEmployeeInfo").dialog({
 26                     height: 400,
 27                     width: 500,
 28                     resizable: false,
 29                     modal: true,  //这里就是控制弹出为模态
 30                     buttons: {
 31                         "确定": function () {
 32                             //alert("在这里对数据进行修改！");
 33                             //$(this).dialog("close");
 34                             //var birthdayTime ="2017/9/28";
 35                             var employeeID = $("#ADD_EmployeeID").val();
 36                             var employeeName = $("#ADD_EmployeeName").val();
 37                             var employeeMajor = $("#ADD_EmployeeMajor").val();
 38                             var employeeDepartment = $("#ADD_EmployeeDepartment").val();
 39                             var employeeTel = $("#ADD_EmployeeTel").val();
 40                             var employeeEmail = $("#ADD_EmployeeEmail").val();
 41                             var employeeJiGuan = $("#ADD_EmployeeJiGuan").val();
 42                             var employeeAddress = $("#ADD_EmployeeAddress").val();
 43                             var employeePosition = $("#ADD_EmployeePosition").val();
 44                             var employeeBirthday = $("#ADD_EmployeeBirthday").val();
 45                             $.ajax({
 46                                 url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToDdd",
 47                                 type: "GET",
 48                                 data: {
 49                                     EmployeeID: employeeID, EmployeeName: employeeName, EmployeeMajor: employeeMajor,
 50                                     EmployeeDepartment: employeeDepartment, EmployeeTel: employeeTel, EmployeeEmail: employeeEmail,
 51                                     EmployeeJiGuan: employeeJiGuan, EmployeeAddress: employeeAddress, EmployeePosition: employeePosition,
 52                                     EmployeeBirthday: employeeBirthday
 53                                 },
 54                                 success: function (message) {
 55                                     $("#JqGrid-table").jqGrid("setGridParam",
 56                                         {
 57                                             url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
 58                                             page: 1,
 59                                             datatype: "json"
 60                                         }).trigger("reloadGrid");
 61                                     alert('添加数据成功');
 62 
 63                                 },
 64                                 error: function (message) {
 65                                     alert('error!');
 66                                 }
 67                             });
 68                         },
 69                         "取消": function () {
 70                             $(this).dialog("close");
 71                         }
 72                     }
 73                 });
 74             });
 75             //删除
 76             $("#btn_del").click(function () {
 77                 var employeeID = $('#JqGrid-table').jqGrid('getGridParam', 'selrow');//获取行id
 78                 $.ajax({
 79                     url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToDel",
 80                     type: "post",
 81                     data: { EmployeeID: employeeID },
 82                     success: function (message) {
 83                         $("#JqGrid-table").jqGrid("setGridParam",
 84                             {
 85                                 url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
 86                                 page: 1,
 87                                 datatype: "json"
 88                             }).trigger("reloadGrid");
 89                         alert('成功删除一条数据');
 90                     },
 91                     error: function (message) {
 92                         alert('error!');
 93                     }
 94                 });
 95             });
 96             //编辑
 97             $("#btn_edit").click(function () {
 98                 //var ids = jQuery("#JqGrid-table").jqGrid('getDataIDs');//返回grid里所有数据的id
 99                 var id = jQuery("#JqGrid-table").jqGrid('getGridParam', 'selarrrow');
100                 var EmployeeInfoModel = jQuery("#JqGrid-table").jqGrid('getRowData', id);
101                 $("#Modify_EmployeeID").val(EmployeeInfoModel.EmployeeID);
102                 $("#Modify_EmployeeName").val(EmployeeInfoModel.EmployeeName);
103                 $("#Modify_EmployeeMajor").val(EmployeeInfoModel.EmployeeMajor);
104                 $("#Modify_EmployeeDepartment").val(EmployeeInfoModel.EmployeeDepartment);
105                 $("#Modify_EmployeeTel").val(EmployeeInfoModel.EmployeeTel);
106                 $("#Modify_EmployeeEmail").val(EmployeeInfoModel.EmployeeEmail);
107                 $("#Modify_EmployeeJiGuan").val(EmployeeInfoModel.EmployeeJiGuan);
108                 $("#Modify_EmployeeAddress").val(EmployeeInfoModel.EmployeeAddress);
109                 $("#Modify_EmployeePosition").val(EmployeeInfoModel.EmployeePosition);
110                 $("#Modify_EmployeeBirthday").val(EmployeeInfoModel.EmployeeBirthday);
111                 $("#ModifyEmployeeInfo").dialog({
112                     height: 400,
113                     width: 500,
114                     resizable: false,
115                     modal: true,  //这里就是控制弹出为模态
116                     buttons: {
117                         "确定": function () {
118                             //alert("在这里对数据进行修改！");
119                             //$(this).dialog("close");
120                             //var birthdayTime ="2017/9/28";
121                             //$("#Modify_EmployeeEmployeeName").value = 'ddd';
122                             //提交前的初始值
123                             var employeeID = $("#Modify_EmployeeID").val();
124                             var employeeName = $("#Modify_EmployeeName").val();
125                             var employeeMajor = $("#Modify_EmployeeMajor").val();
126                             var employeeDepartment = $("#Modify_EmployeeDepartment").val();
127                             var employeeTel = $("#Modify_EmployeeTel").val();
128                             var employeeEmail = $("#Modify_EmployeeEmail").val();
129                             var employeeJiGuan = $("#Modify_EmployeeJiGuan").val();
130                             var employeeAddress = $("#Modify_EmployeeAddress").val();
131                             var employeePosition = $("#Modify_EmployeePosition").val();
132                             var employeeBirthday = $("#Modify_EmployeeBirthday").val();
133                             $.ajax({
134                                 url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToUpdate",
135                                 type: "GET",
136                                 data: {
137                                     EmployeeID: employeeID, EmployeeName: employeeName, EmployeeMajor: employeeMajor,
138                                     EmployeeDepartment: employeeDepartment, EmployeeTel: employeeTel, EmployeeEmail: employeeEmail,
139                                     EmployeeJiGuan: employeeJiGuan, EmployeeAddress: employeeAddress, EmployeePosition: employeePosition,
140                                     EmployeeBirthday: employeeBirthday
141                                 },
142                                 success: function (message) {
143                                     $("#JqGrid-table").jqGrid("setGridParam",
144                                         {
145                                             url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
146                                             page: 1,
147                                             datatype: "json"
148                                         }).trigger("reloadGrid");
149                                     alert('编辑成功！！');
150 
151                                 },
152                                 error: function (message) {
153                                     alert('error!');
154                                 }
155                             });
156                         },
157                         "取消": function () {
158                             $(this).dialog("close");
159                         }
160                     }
161                 });
162 
163             });
164             //查询
165             $("#btn_search").click(function () {
166                 var employeeName = $("#precisionSearch_input").val();
167                 $("#JqGrid-table").jqGrid("setGridParam",
168                     {
169                         url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToSearch" + "?EmployeeName=" + employeeName,
170                         page: 1,
171                         datatype: "json"
172                     }).trigger("reloadGrid");
173 
174             });
175             //导出
176             $("#btn_export").on("click", function () {
177                 $("#JqGrid-table").jqGrid("exportToExcel", {
178                     includeLabels: true,
179                     includeGroupHeader: true,
180                     includeFooter: true,
181                     fileName: "jqGridExport.xlsx",
182                     maxlength: 40 // maxlength for visible string
183                 });
184             });
185             //导入
186             $("#btn_import").click(function () {
187                 var FileName = $("#UpLoadFile").val();
188                 $.ajax({
189                     url: '/JqGridDemo/ImportData/InsertDataToDB',
190                     type: 'post',
191                     data: { fileName: FileName }
192                 });
193             });
194         })
195     </script>        
196 </head>
197 <body>
198     <div>
199         <div>
200             <input id="UpLoadFile" type="file" />
201             <input id="btn_import" type="button" value="批量导入" class="btn btn-info" />
202             <a href="/JqGridDemo/JqGridCRUD/GetEmployeeInfoTemple">(点击此处下载模板)</a>
203             <label>模糊查询：</label> <input id="search_input" type="text" placeholder="模糊查询" />
204             <input id="precisionSearch_input" type="text" placeholder="请输入您要查询的姓名" />
205             <input id="btn_search" type="button" value="查询" class="btn btn-info" />
206             <input id="btn_add" type="button" value="添加" class="btn btn-primary" />
207             <input id="btn_edit" type="button" value="编辑" class="btn btn-success" />
208             <input id="btn_del" type="button" value="删除" class="btn btn-danger" />
209             <input id="btn_export" type="button" value="导出" class="btn btn-info" />
210             
211         </div>
212         <div class="main" id="main">
213             <table id="JqGrid-table"></table>
214             <div id="JqGrid-pager"></div>
215             <div id="ModifyEmployeeInfo" title="修改员工信息" style="display:none;">
216                 <table>
217                     <tbody>
218                         <tr>
219                             <td>员工ID：<input type="text" id="Modify_EmployeeID" placeholder="请输入员工ID" /></td>
220                             <td>员工姓名：<input type="text" id="Modify_EmployeeName" placeholder="请输入员工姓名" /></td>
221                         </tr>
222                         <tr>
223                             <td>员工专业：<input type="text" id="Modify_EmployeeMajor" placeholder="请输入员工专业" /></td>
224                             <td>员工部门：<input type="text" id="Modify_EmployeeDepartment" placeholder="请输入员工部门" /></td>
225                         </tr>
226                         <tr>
227                             <td>员工电话：<input type="text" id="Modify_EmployeeTel" placeholder="请输入员工电话" /></td>
228                             <td>员工邮件：<input type="text" id="Modify_EmployeeEmail" placeholder="请输入员工邮件" /></td>
229                         </tr>
230                         <tr>
231                             <td>员工籍贯：<input type="text" id="Modify_EmployeeJiGuan" placeholder="请输入员工籍贯" /></td>
232                             <td>员工住址：<input type="text" id="Modify_EmployeeAddress" placeholder="请输入员工住址" /></td>
233                         </tr>
234                         <tr>
235                             <td>员工职位：<input type="text" id="Modify_EmployeePosition" placeholder="请输入员工职位" /></td>
236                             <td>员工生日：<input type="text" id="Modify_EmployeeBirthday" placeholder="请输入员工生日" /></td>
237                         </tr>
238                     </tbody>
239                 </table>
240             </div>
241             <div id="AddEmployeeInfo" title="修改员工信息" style="display:none;">
242                <table>
243                     <tbody>
244                         <tr>
245                             <td>员工ID：<input type="text" id="ADD_EmployeeID" placeholder="请输入员工ID" /></td>
246                             <td>员工姓名：<input type="text" id="ADD_EmployeeName" placeholder="请输入员工姓名" /></td>
247                         </tr>
248                         <tr>
249                             <td>员工专业：<input type="text" id="ADD_EmployeeMajor" placeholder="请输入员工专业" /></td>
250                             <td>员工部门：<input type="text" id="ADD_EmployeeDepartment" placeholder="请输入员工部门" /></td>
251                         </tr>
252                         <tr>
253                             <td>员工电话：<input type="text" id="ADD_EmployeeTel" placeholder="请输入员工电话" /></td>
254                             <td>员工邮件：<input type="text" id="ADD_EmployeeEmail" placeholder="请输入员工邮件" /></td>
255                         </tr>
256                         <tr>
257                             <td>员工籍贯：<input type="text" id="ADD_EmployeeJiGuan" placeholder="请输入员工籍贯" /></td>
258                             <td>员工住址：<input type="text" id="ADD_EmployeeAddress" placeholder="请输入员工住址" /></td>
259                         </tr>
260                         <tr>
261                             <td>员工职位：<input type="text" id="ADD_EmployeePosition" placeholder="请输入员工职位" /></td>
262                             <td>员工生日：<input type="text" id="ADD_EmployeeBirthday" placeholder="请输入员工生日" value="2014/03/01" /></td>
263                         </tr>
264                     </tbody>
265                 </table>
266             </div>
267         </div>
268         <script type="text/javascript">
269             $(document).ready(function () {
270                 $("#JqGrid-table").jqGrid({
271                     url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
272                     //editurl: 'clientArray',
273                     datatype: "json",
274                     height: 150,
275                     mtype: "Get",
276                     colNames: ['员工ID', '员工姓名', '员工专业', '员工部门', '员工电话', '员工邮件', '员工籍贯', '员工住址', '员工职位', '员工生日'],
277                     colModel: [{
278                         name: 'EmployeeID',
279                         index: 'EmployeeID',
280                         key: true,
281                         width: 100,
282                         edittype: Text,
283                         editable: true,
284                         editoptions: {
285                             size: "20",
286                             maxlength: "30",
287                             sorttable: false,
288                         },
289                         searchoptions: {
290                             searchOperMenu: false,
291                             sopt: ['eq', 'gt', 'lt', 'ge', 'le']
292                         }
293                     }, {
294                         name: 'EmployeeName',
295                         index: 'EmployeeName',
296                         width: 200,
297                         editable: true,
298                         editoptions: {
299                             size: "20",
300                             maxlength: "30",
301                             sortable: true
302                         }
303                     }, {
304                         name: 'EmployeeMajor',
305                         index: 'EmployeeMajor',
306                         width: 200,
307                         editable: true,
308                         //edittype: false,
309                         editoptions: {
310                             size: "20",
311                             maxlength: "30"
312                         }
313                     },
314                     {
315                         name: 'EmployeeDepartment',
316                         index: 'EmployeeDepartment',
317                         width: 200,
318                         editable: true,
319                         //edittype: false,
320                         editoptions: {
321                             size: "20",
322                             maxlength: "30"
323                         }
324                     }, {
325                         name: 'EmployeeTel',
326                         index: 'EmployeeTel',
327                         width: 200,
328                         editable: true,
329                         //edittype: false,
330                         editoptions: {
331                             size: "20",
332                             maxlength: "30"
333                         }
334                     }, {
335                         name: 'EmployeeEmail',
336                         index: 'EmployeeEmail',
337                         width: 200,
338                         editable: true,
339                         //edittype: false,
340                         editoptions: {
341                             size: "20",
342                             maxlength: "30"
343                         }
344                     }, {
345                         name: 'EmployeeJiGuan',
346                         index: 'EmployeeJiGuan',
347                         width: 200,
348                         editable: true,
349                         //edittype: false,
350                         editoptions: {
351                             size: "20",
352                             maxlength: "30"
353                         }
354                     }, {
355                         name: 'EmployeeAddress',
356                         index: 'EmployeeAddress',
357                         width: 200,
358                         editable: true,
359                         //edittype: false,
360                         editoptions: {
361                             size: "20",
362                             maxlength: "30"
363                         }
364                     }, {
365                         name: 'EmployeePosition',
366                         index: 'EmployeePosition',
367                         width: 200,
368                         editable: true,
369                         //edittype: false,
370                         editoptions: {
371                             size: "20",
372                             maxlength: "30"
373                         }
374                     }, {
375                         name: 'EmployeeBirthday',
376                         index: 'EmployeeBirthday',
377                         width: 200,
378                         editable: true,
379                         //edittype: false,
380                         editoptions: {
381                             size: "20",
382                             maxlength: "30"
383                         }
384                     }],
385                     viewrecords: true,
386                     rowNum: 10,
387                     rowList: [5, 10, 20, 30],
388                     pager: '#JqGrid-pager',
389                     altRows: true,
390                     multiselect: true,
391                     multiboxonly: true,
392                     caption: "员工信息表",
393                     autowidth: true,
394                     //width: "100%",
395                     height: Audio,
396                     sortable: true,
397                     sortorder: 'asc',
398                     loadonce: true, //排序时，必须添加该字段
399                 })
400                  //JqGrid自带的CRUD
401                 $('#JqGrid-table').jqGrid('navGrid', "#JqGrid-pager", {
402                     search: true, // show search button on the toolbar
403                     add: true,
404                     edit: true,
405                     del: true,
406                     refresh: true
407                 });
408                 //模糊查询
409                 var timer;
410                 $("#search_input").on("keyup", function () {
411                     var self = this;
412                     if (timer) { clearTimeout(timer); }
413                     timer = setTimeout(function () {
414                         //timer = null;
415                         $("#JqGrid-table").jqGrid('filterInput', self.value);
416                     }, 0);
417                 });
418             })
419         </script>
420     </div>
421 </body>
422 </html>

View Code

6.2 分析

6.2.1 初始化数据

(1)图解功能

(2)功能Code

定义table获取区和翻页区

1 <table id="JqGrid-table"></table>
2 <div id="JqGrid-pager"></div>

JqGrid

  1  <script type="text/javascript">
  2             $(document).ready(function () {
  3                 $("#JqGrid-table").jqGrid({
  4                     url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
  5                     //editurl: 'clientArray',
  6                     datatype: "json",
  7                     height: 150,
  8                     mtype: "Get",
  9                     colNames: ['员工ID', '员工姓名', '员工专业', '员工部门', '员工电话', '员工邮件', '员工籍贯', '员工住址', '员工职位', '员工生日'],
 10                     colModel: [{
 11                         name: 'EmployeeID',
 12                         index: 'EmployeeID',
 13                         key: true,
 14                         width: 100,
 15                         edittype: Text,
 16                         editable: true,
 17                         editoptions: {
 18                             size: "20",
 19                             maxlength: "30",
 20                             sorttable: false,
 21                         },
 22                         searchoptions: {
 23                             searchOperMenu: false,
 24                             sopt: ['eq', 'gt', 'lt', 'ge', 'le']
 25                         }
 26                     }, {
 27                         name: 'EmployeeName',
 28                         index: 'EmployeeName',
 29                         width: 200,
 30                         editable: true,
 31                         editoptions: {
 32                             size: "20",
 33                             maxlength: "30",
 34                             sortable: true
 35                         }
 36                     }, {
 37                         name: 'EmployeeMajor',
 38                         index: 'EmployeeMajor',
 39                         width: 200,
 40                         editable: true,
 41                         //edittype: false,
 42                         editoptions: {
 43                             size: "20",
 44                             maxlength: "30"
 45                         }
 46                     },
 47                     {
 48                         name: 'EmployeeDepartment',
 49                         index: 'EmployeeDepartment',
 50                         width: 200,
 51                         editable: true,
 52                         //edittype: false,
 53                         editoptions: {
 54                             size: "20",
 55                             maxlength: "30"
 56                         }
 57                     }, {
 58                         name: 'EmployeeTel',
 59                         index: 'EmployeeTel',
 60                         width: 200,
 61                         editable: true,
 62                         //edittype: false,
 63                         editoptions: {
 64                             size: "20",
 65                             maxlength: "30"
 66                         }
 67                     }, {
 68                         name: 'EmployeeEmail',
 69                         index: 'EmployeeEmail',
 70                         width: 200,
 71                         editable: true,
 72                         //edittype: false,
 73                         editoptions: {
 74                             size: "20",
 75                             maxlength: "30"
 76                         }
 77                     }, {
 78                         name: 'EmployeeJiGuan',
 79                         index: 'EmployeeJiGuan',
 80                         width: 200,
 81                         editable: true,
 82                         //edittype: false,
 83                         editoptions: {
 84                             size: "20",
 85                             maxlength: "30"
 86                         }
 87                     }, {
 88                         name: 'EmployeeAddress',
 89                         index: 'EmployeeAddress',
 90                         width: 200,
 91                         editable: true,
 92                         //edittype: false,
 93                         editoptions: {
 94                             size: "20",
 95                             maxlength: "30"
 96                         }
 97                     }, {
 98                         name: 'EmployeePosition',
 99                         index: 'EmployeePosition',
100                         width: 200,
101                         editable: true,
102                         //edittype: false,
103                         editoptions: {
104                             size: "20",
105                             maxlength: "30"
106                         }
107                     }, {
108                         name: 'EmployeeBirthday',
109                         index: 'EmployeeBirthday',
110                         width: 200,
111                         editable: true,
112                         //edittype: false,
113                         editoptions: {
114                             size: "20",
115                             maxlength: "30"
116                         }
117                     }],
118                     viewrecords: true,
119                     rowNum: 10,
120                     rowList: [5, 10, 20, 30],
121                     pager: '#JqGrid-pager',
122                     altRows: true,
123                     multiselect: true,
124                     multiboxonly: true,
125                     caption: "员工信息表",
126                     autowidth: true,
127                     //width: "100%",
128                     height: Audio,
129                     sortable: true,
130                     sortorder: 'asc',
131                     loadonce: true, //排序时，必须添加该字段
132                 })
133             })
134         </script>

View Code

6.2.2 JqGrid自带的CRUD

(1)图解功能

(2)功能Code

 //JqGrid自带的CRUD
 $('#JqGrid-table').jqGrid('navGrid', "#JqGrid-pager", {
       search: true, // show search button on the toolbar
       add: true,
       edit: true,
       del: true,
       refresh: true
});

6.2.3 自动检测区：

(1)图解功能

(1)功能Code

 1 //模糊查询
 2    var timer;
 3    $("#search_input").on("keyup", function () {
 4            var self = this;
 5             if (timer) { clearTimeout(timer); }
 6             timer = setTimeout(function () {
 7              //timer = null;
 8              $("#JqGrid-table").jqGrid('filterInput', self.value);
 9            }, 0);
10 });

6.2.4 JqGrid自带导出功能

(1)图解功能

(2)功能Code

$("#btn_export").on("click", function () {
     $("#JqGrid-table").jqGrid("exportToExcel", {
          includeLabels: true,
          includeGroupHeader: true,
          includeFooter: true,
          fileName: "jqGridExport.xlsx",
          maxlength: 40 // maxlength for visible string
        });
 });

6.2.5 自定义CRUD

(1)图解功能

(2)功能Code

  1 <script type="text/javascript">
  2         $(document).ready(function () {
  3             //添加
  4             $("#btn_add").click(function () {
  5                 //var model = jQuery("#JqGrid-table").jqGrid('getRowData', EmployeeID);
  6                 $("#AddEmployeeInfo").dialog({
  7                     height: 400,
  8                     width: 500,
  9                     resizable: false,
 10                     modal: true,  //这里就是控制弹出为模态
 11                     buttons: {
 12                         "确定": function () {
 13                             //alert("在这里对数据进行修改！");
 14                             //$(this).dialog("close");
 15                             //var birthdayTime ="2017/9/28";
 16                             var employeeID = $("#ADD_EmployeeID").val();
 17                             var employeeName = $("#ADD_EmployeeName").val();
 18                             var employeeMajor = $("#ADD_EmployeeMajor").val();
 19                             var employeeDepartment = $("#ADD_EmployeeDepartment").val();
 20                             var employeeTel = $("#ADD_EmployeeTel").val();
 21                             var employeeEmail = $("#ADD_EmployeeEmail").val();
 22                             var employeeJiGuan = $("#ADD_EmployeeJiGuan").val();
 23                             var employeeAddress = $("#ADD_EmployeeAddress").val();
 24                             var employeePosition = $("#ADD_EmployeePosition").val();
 25                             var employeeBirthday = $("#ADD_EmployeeBirthday").val();
 26                             $.ajax({
 27                                 url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToDdd",
 28                                 type: "GET",
 29                                 data: {
 30                                     EmployeeID: employeeID, EmployeeName: employeeName, EmployeeMajor: employeeMajor,
 31                                     EmployeeDepartment: employeeDepartment, EmployeeTel: employeeTel, EmployeeEmail: employeeEmail,
 32                                     EmployeeJiGuan: employeeJiGuan, EmployeeAddress: employeeAddress, EmployeePosition: employeePosition,
 33                                     EmployeeBirthday: employeeBirthday
 34                                 },
 35                                 success: function (message) {
 36                                     $("#JqGrid-table").jqGrid("setGridParam",
 37                                         {
 38                                             url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
 39                                             page: 1,
 40                                             datatype: "json"
 41                                         }).trigger("reloadGrid");
 42                                     alert('添加数据成功');
 43 
 44                                 },
 45                                 error: function (message) {
 46                                     alert('error!');
 47                                 }
 48                             });
 49                         },
 50                         "取消": function () {
 51                             $(this).dialog("close");
 52                         }
 53                     }
 54                 });
 55             });
 56             //删除
 57             $("#btn_del").click(function () {
 58                 var employeeID = $('#JqGrid-table').jqGrid('getGridParam', 'selrow');//获取行id
 59                 $.ajax({
 60                     url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToDel",
 61                     type: "post",
 62                     data: { EmployeeID: employeeID },
 63                     success: function (message) {
 64                         $("#JqGrid-table").jqGrid("setGridParam",
 65                             {
 66                                 url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
 67                                 page: 1,
 68                                 datatype: "json"
 69                             }).trigger("reloadGrid");
 70                         alert('成功删除一条数据');
 71                     },
 72                     error: function (message) {
 73                         alert('error!');
 74                     }
 75                 });
 76             });
 77             //编辑
 78             $("#btn_edit").click(function () {
 79                 //var ids = jQuery("#JqGrid-table").jqGrid('getDataIDs');//返回grid里所有数据的id
 80                 var id = jQuery("#JqGrid-table").jqGrid('getGridParam', 'selarrrow');
 81                 var EmployeeInfoModel = jQuery("#JqGrid-table").jqGrid('getRowData', id);
 82                 $("#Modify_EmployeeID").val(EmployeeInfoModel.EmployeeID);
 83                 $("#Modify_EmployeeName").val(EmployeeInfoModel.EmployeeName);
 84                 $("#Modify_EmployeeMajor").val(EmployeeInfoModel.EmployeeMajor);
 85                 $("#Modify_EmployeeDepartment").val(EmployeeInfoModel.EmployeeDepartment);
 86                 $("#Modify_EmployeeTel").val(EmployeeInfoModel.EmployeeTel);
 87                 $("#Modify_EmployeeEmail").val(EmployeeInfoModel.EmployeeEmail);
 88                 $("#Modify_EmployeeJiGuan").val(EmployeeInfoModel.EmployeeJiGuan);
 89                 $("#Modify_EmployeeAddress").val(EmployeeInfoModel.EmployeeAddress);
 90                 $("#Modify_EmployeePosition").val(EmployeeInfoModel.EmployeePosition);
 91                 $("#Modify_EmployeeBirthday").val(EmployeeInfoModel.EmployeeBirthday);
 92                 $("#ModifyEmployeeInfo").dialog({
 93                     height: 400,
 94                     width: 500,
 95                     resizable: false,
 96                     modal: true,  //这里就是控制弹出为模态
 97                     buttons: {
 98                         "确定": function () {
 99                             //alert("在这里对数据进行修改！");
100                             //$(this).dialog("close");
101                             //var birthdayTime ="2017/9/28";
102                             //$("#Modify_EmployeeEmployeeName").value = 'ddd';
103                             //提交前的初始值
104                             var employeeID = $("#Modify_EmployeeID").val();
105                             var employeeName = $("#Modify_EmployeeName").val();
106                             var employeeMajor = $("#Modify_EmployeeMajor").val();
107                             var employeeDepartment = $("#Modify_EmployeeDepartment").val();
108                             var employeeTel = $("#Modify_EmployeeTel").val();
109                             var employeeEmail = $("#Modify_EmployeeEmail").val();
110                             var employeeJiGuan = $("#Modify_EmployeeJiGuan").val();
111                             var employeeAddress = $("#Modify_EmployeeAddress").val();
112                             var employeePosition = $("#Modify_EmployeePosition").val();
113                             var employeeBirthday = $("#Modify_EmployeeBirthday").val();
114                             $.ajax({
115                                 url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToUpdate",
116                                 type: "GET",
117                                 data: {
118                                     EmployeeID: employeeID, EmployeeName: employeeName, EmployeeMajor: employeeMajor,
119                                     EmployeeDepartment: employeeDepartment, EmployeeTel: employeeTel, EmployeeEmail: employeeEmail,
120                                     EmployeeJiGuan: employeeJiGuan, EmployeeAddress: employeeAddress, EmployeePosition: employeePosition,
121                                     EmployeeBirthday: employeeBirthday
122                                 },
123                                 success: function (message) {
124                                     $("#JqGrid-table").jqGrid("setGridParam",
125                                         {
126                                             url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad",
127                                             page: 1,
128                                             datatype: "json"
129                                         }).trigger("reloadGrid");
130                                     alert('编辑成功！！');
131 
132                                 },
133                                 error: function (message) {
134                                     alert('error!');
135                                 }
136                             });
137                         },
138                         "取消": function () {
139                             $(this).dialog("close");
140                             $("#JqGrid-table").jqGrid('clearGridData');
141                             //$("#JqGrid-table").trigger('reloadGrid');
142                             $("#JqGrid-table").setGridParam({ url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToLoad" }).trigger('reloadGrid');
143                         }
144                     }
145                 });
146 
147             });
148             //查询
149             $("#btn_search").click(function () {
150                 var employeeName = $("#precisionSearch_input").val();
151                 $("#JqGrid-table").jqGrid("setGridParam",
152                     {
153                         url: "/JqGridDemo/JqGridCRUD/EmployeeInfoToSearch" + "?EmployeeName=" + employeeName,
154                         page: 1,
155                         datatype: "json"
156                     }).trigger("reloadGrid");
157 
158             });
159             //导出
160             $("#btn_export").on("click", function () {
161                 $("#JqGrid-table").jqGrid("exportToExcel", {
162                     includeLabels: true,
163                     includeGroupHeader: true,
164                     includeFooter: true,
165                     fileName: "jqGridExport.xlsx",
166                     maxlength: 40 // maxlength for visible string
167                 });
168             });
169             //导入
170             $("#btn_import").click(function () {
171                 var FileName = $("#UpLoadFile").val();
172                 $.ajax({
173                     url: '/JqGridDemo/ImportData/InsertDataToDB',
174                     type: 'post',
175                     data: { fileName: FileName }
176                 });
177             });
178         })
179     </script>

View Code

6.2.6 导出

(1)图解功能

(2)功能Code

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Configuration;
 4 using System.Data;
 5 using System.Data.OleDb;
 6 using System.Data.SqlClient;
 7 using System.Linq;
 8 using System.Web;
 9 using System.Web.Mvc;
10 
11 namespace MVCCrud.Areas.JqGridDemo.Controllers
12 {
13     public class ImportDataController : Controller
14     {
15         // GET: JqGridDemo/ImportData
16         public ActionResult Index()
17         {
18             return View();
19         }
20 
21         /// <summary>
22         /// 从excel读取数据
23         /// </summary>
24         /// <param name="filepath">excel文件路径</param>
25         /// <returns></returns>
26         public static DataSet ReadExcel(string FilePath)
27         {
28             try
29             {
30                 string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", FilePath);
31                 using (OleDbConnection OleDbConn = new OleDbConnection(strConn))
32                 {
33                     OleDbConn.Open();
34                     string sql = "SELECT * FROM [Sheet1$]";
35                     OleDbDataAdapter OleDbDA = new OleDbDataAdapter(sql, OleDbConn);
36                     DataSet OleDS = new DataSet();
37                     OleDbDA.Fill(OleDS, "ExcelToTalbe");
38                     return OleDS;
39                 }
40             }
41             catch (Exception ex)
42             {
43                 throw new Exception(ex.Message);
44             }
45         }
46         /// <summary>
47         /// 将excel数据插入到EmployeeName表中
48         /// </summary>
49         /// <param name="fileName">excel文件所在路径</param>
50         public void InsertDataToDB(string fileName)
51         {
52             //fileName = @"E:\testData.xls";
53             if (!System.IO.File.Exists(fileName))
54             {
55                 throw new Exception("指定路径的Excel文件不存在！");
56             }
57             DataSet ds = ReadExcel(fileName);
58             List<string> ListData = (from DataRow row in ds.Tables["ExcelToTalbe"].Rows select String.Format("INSERT INTO EmployeeInfo(EmployeeID,EmployeeName,EmployeeMajor,EmployeeDepartment,EmployeeTel,EmployeeEmail, EmployeeJiGuan,EmployeeAddress,EmployeePosition,EmployeeBirthday) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9])).ToList();
59             string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
60             SqlConnection conn = new SqlConnection(ConStr);
61             conn.Open();
62             foreach (string item in ListData)
63             {
64                 SqlCommand comn = new SqlCommand(item, conn);
65                 comn.ExecuteNonQuery();
66             }
67         }
68     }
69 }
70                  
71                 
72

View Code

7 问题区

Q1:批量导入时，excel版本问题

Q2:批量导入时，input 上传文件路径，需要使用相对路径，而非绝对路劲

8 待解决问题

Q1:统一异常控制

Q2:JqGrid换肤

Q3:压力测试

Q4:友好提示

Q4:其他

9 最后

首先祝福大家国庆快乐，除此之外，若有任何问题，欢迎指教。

10 参考文献

[01]http://www.trirand.com/blog/?page_id=5

[02]http://blog.mn886.net/jqGrid/

11 服务区

有喜欢的朋友，可以看一下，不喜欢的的朋友，勿喷，谢谢！！

12 版权

感谢您的阅读，若有不足之处，欢迎指教，共同学习、共同进步。
博主网址：http://www.cnblogs.com/wangjiming/。
极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的，大部分为原创。
如您喜欢，麻烦推荐一下；如您有新想法，欢迎提出，邮箱：2098469527@qq.com。
可以转载该博客，但必须著名博客来源。

posted @ 2017-10-02 03:47 Alan_beijing 阅读(8518) 评论(44) 编辑收藏举报

刷新页面返回顶部

Alan_beijing

今天对我最有意义的事; 我今天体验到的巧合、预感和重要新发现; 我今天的新想法; 我今天的思绪、感觉..;

【ASP.NET MVC系列】浅谈jqGrid 在ASP.NET MVC中增删改查

公告