【ASP.NET MVC系列】浅谈jqGrid 在ASP.NET MVC中…

 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 }

  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 }

  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 }

  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  

  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>

  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>

  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>  

 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