1 概述
本篇文章主要是关于JqGrid的，主要功能包括使用JqGrid增删查改，导入导出，废话不多说，直接进入正题。
2 Demo相关
2.1 Demo展示
第一部分
第二部分
2.2 源码和DB下载
国庆回来上传到github上。
3 公共模块
3.1 Model实体—EmployeeInfo
1 using MVCCrud.Areas.DBUtility;View Code
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 }
3.2 DBHelper帮助类
1 using System;View Code
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 }
4 数据访问层DAL
4.1 对EmployeeInfo的CRUD
1 using MVCCrud.Areas.DBUtility;View Code
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 }
4.2 简要分析
5 控制器层
5.1 方法
1 using MVCCrud.Areas.JqGridDemo.Models;View Code
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
5.2 分析
6 展示层
6.1 View Code
1 @{View Code
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>
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">View Code
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>
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">View Code
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>
6.2.6 导出
(1)图解功能
(2)功能Code
1 using System;View Code
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
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 版权
- 感谢您的阅读，若有不足之处，欢迎指教，共同学习、共同进步。
- 博主网址：http://www.cnblogs.com/wangjiming/。
- 极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的，大部分为原创。
- 如您喜欢，麻烦推荐一下；如您有新想法，欢迎提出，邮箱：2016177728@qq.com。
- 可以转载该博客，但必须著名博客来源。