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

强烈建议你试试无所不能的chatGPT，快点击我

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

阅读量：5323 次

发布时间：2019-06-14

本文共 32989 字，大约阅读时间需要 109 分钟。

ASP.NET MVC系列文章

【01】

【02】

【03】

【04】

【05】

【06】

【07】

【08】

【09】

【10】

【11】

【12】

【13】

【14】

1 概述

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

2 Demo相关

2.1 Demo展示

第一部分

第二部分

2.2 源码和DB下载

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

3 公共模块

3.1 Model实体—EmployeeInfo

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

View Code

3.2 DBHelper帮助类

1 using System;  2 using System.Collections;  3 using System.Collections.Generic;  4 using System.Configuration;  5 using System.Data;  6 using System.Data.SqlClient;  7 using System.Linq;  8 using System.Security.Cryptography;  9 using System.Text; 10 using System.Web; 11 using System.Web.UI.WebControls; 12  13 namespace MVCCrud.Areas.DBUtility 14 { 15     public abstract class DbHelperSQL 16     { 17         /* 18          * content:DbHelper帮助类 19          *author:Alan_beijing 20          * date:2017-10-01 21         */ 22         public DbHelperSQL() 23         { 24             //构造函数 25         } 26         protected static string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString; 27         protected static SqlConnection Connection; 28         //定义数据库的打开和关闭方法 29         protected static void Open() 30         { 31             if (Connection == null) 32             { 33                 Connection = new SqlConnection(ConnectionString); 34             } 35             if (Connection.State.Equals(ConnectionState.Closed)) 36             { 37                 Connection.Open(); 38             } 39         } 40         protected static void Close() 41         { 42             if (Connection != null) 43             { 44                 Connection.Close(); 45             } 46         } 47  48         // 公有方法，获取数据，返回一个DataSet。     49         public static DataSet GetDataSet(string SqlString) 50         { 51             using (SqlConnection connection = new SqlConnection(ConnectionString)) 52             { 53                 connection.Open(); 54                 using (SqlCommand cmd = new SqlCommand(SqlString, connection)) 55                 { 56                     using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 57                     { 58                         DataSet ds = new DataSet(); 59                         try 60                         { 61                             da.Fill(ds, "ds"); 62                             cmd.Parameters.Clear(); 63                         } 64                         catch (System.Data.SqlClient.SqlException ex) 65                         { 66                             throw new Exception(ex.Message); 67                         } 68                         connection.Close(); 69                         return ds; 70                     } 71                 } 72             } 73         } 74         // 公有方法，获取数据，返回一个DataTable。     75         public static DataTable GetDataTable(string SqlString) 76         { 77             DataSet dataset = GetDataSet(SqlString); 78             return dataset.Tables[0]; 79         } 80         public static int ExecuteSQL(String SqlString, Hashtable MyHashTb) 81         { 82             int count = -1; 83             SqlConnection connectiontemp = new SqlConnection(ConnectionString); 84             connectiontemp.Open(); 85             try 86             { 87                 SqlCommand cmd = new SqlCommand(SqlString, connectiontemp); 88                 foreach (DictionaryEntry item in MyHashTb) 89                 { 90                     string[] CanShu = item.Key.ToString().Split('|'); 91                     if (CanShu[1].ToString().Trim() == "string") 92                     { 93                         cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar); 94                     } 95                     else if (CanShu[1].ToString().Trim() == "int") 96                     { 97                         cmd.Parameters.Add(CanShu[0], SqlDbType.Int); 98                     } 99                     else if (CanShu[1].ToString().Trim() == "text")100                     {101                         cmd.Parameters.Add(CanShu[0], SqlDbType.Text);102                     }103                     else if (CanShu[1].ToString().Trim() == "datetime")104                     {105                         cmd.Parameters.Add(CanShu[0], SqlDbType.DateTime);106                     }107                     else108                     {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             catch116             {117                 count = -1;118             }119             finally120             {121                 connectiontemp.Close();122             }123             return count;124         }125         // 公有方法，执行Sql语句。对Update、Insert、Delete为影响到的行数，其他情况为-1126         public static int ExecuteSQL(String SqlString)127         {128             int count = -1;129             SqlConnection connectionTemp = new SqlConnection(ConnectionString);130             connectionTemp.Open();131             try132             {133                 SqlCommand cmd = new SqlCommand(SqlString, connectionTemp);134                 count = cmd.ExecuteNonQuery();135             }136             catch137             {138                 count = -1;139             }140             finally141             {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             try157             {158                 foreach (string str in SqlStrings)159                 {160                     cmd.CommandText = str;161                     cmd.ExecuteNonQuery();162                 }163                 trans.Commit();164             }165             catch166             {167                 success = false;168                 trans.Rollback();169             }170             finally171             {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                     try184                     {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                         else193                         {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                     try214                     {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                         else224                         {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                     try245                     {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                         else255                         {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                     try277                     {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         //执行查询语句，返回DataSet294         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                     try304                     {305                         da.Fill(ds, "ds");306                         cmd.Parameters.Clear();307 308                     }309                     catch (System.Data.SqlClient.SqlException ex)310                     {311                         throw new Exception(ex.Message);312                     }313                     connection.Close();314                     return ds;315                 }316             }317         }318         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)319         {320             if (conn.State != ConnectionState.Open)321                 conn.Open();322             cmd.Connection = conn;323             cmd.CommandText = cmdText;324             if (trans != null)325                 cmd.Transaction = trans;326             cmd.CommandType = CommandType.Text;//cmdType;327             if (cmdParms != null)328             {329                 foreach (SqlParameter parameter in cmdParms)330                 {331                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&332                         (parameter.Value == null))333                     {334                         parameter.Value = DBNull.Value;335                     }336                     cmd.Parameters.Add(parameter);337                 }338             }339         }340     }341 }

View Code

4 数据访问层DAL

4.1 对EmployeeInfo的CRUD

1 using MVCCrud.Areas.DBUtility;  2 using MVCCrud.Areas.JqGridDemo.Models;  3 using System;  4 using System.Collections.Generic;  5 using System.Data;  6 using System.Data.SqlClient;  7 using System.Linq;  8 using System.Text;  9 using System.Web; 10  11 namespace MVCCrud.Areas.DAL 12 { 13     public class EmployeeInfoToCRUD 14     { 15         ///  16         /// 增加一条数据 17         /// 
 18         /// EmployeeInfo对象 19         /// 
      
       添加数据是否成功
       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         ///  65         /// 删除一条数据 66         /// 
 67         /// 查询参数：员工ID 68         /// 
      
       是否成功删除
       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         ///  82         /// 获取EmployeeInfo数据表 83         /// 
 84         /// 
      
       返回EmployeeInfo数据表
       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         ///  93         /// 根据EmployeeName查询EmployeeInfo实体表数据 94         /// 
 95         /// 查询参数：EmployeeName 96         /// 
      
       返回查询到的DataTable
       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         /// 107         /// 根据employeeInfo条件更新数据108         /// 
109         /// 更新条件：employeeInfo110         public void DALEmployeeInfoToUpdate(EmployeeInfo employeeInfo)111         {112             StringBuilder strSql = new StringBuilder();113             strSql.Append("UPDATE EmployeeInfo SET ");114             strSql.Append("EmployeeName=@EmployeeName,EmployeeMajor=@EmployeeMajor,");115             strSql.Append("EmployeeDepartment=@EmployeeDepartment,EmployeeTel=@EmployeeTel,EmployeeEmail=@EmployeeEmail,");116             strSql.Append("EmployeeJiGuan=@EmployeeJiGuan,EmployeeAddress=@EmployeeAddress,EmployeePosition=@EmployeePosition,EmployeeBirthday=@EmployeeBirthday");117             strSql.Append(" WHERE EmployeeID=@EmployeeID");118             SqlParameter[] parameters = {119                     new SqlParameter("@EmployeeID", SqlDbType.VarChar,50),120                     new SqlParameter("@EmployeeName", SqlDbType.VarChar,50),121                     new SqlParameter("@EmployeeMajor", SqlDbType.Text),122                     new SqlParameter("@EmployeeDepartment",SqlDbType.VarChar,50),123                     new SqlParameter("@EmployeeTel", SqlDbType.VarChar,50),124                     new SqlParameter("@EmployeeEmail", SqlDbType.VarChar,50),125                     new SqlParameter("@EmployeeJiGuan", SqlDbType.VarChar,50),126                     new SqlParameter("@EmployeeAddress", SqlDbType.VarChar,500),127                     new SqlParameter("@EmployeePosition", SqlDbType.VarChar, 50),128                     new SqlParameter("@EmployeeBirthday", SqlDbType.DateTime)129             };130             parameters[0].Value = employeeInfo.EmployeeID;131             parameters[1].Value = employeeInfo.EmployeeName;132             parameters[2].Value = employeeInfo.EmployeeMajor;133             parameters[3].Value = employeeInfo.EmployeeDepartment;134             parameters[4].Value = employeeInfo.EmployeeTel;135             parameters[5].Value = employeeInfo.EmployeeEmail;136             parameters[6].Value = employeeInfo.EmployeeJiGuan;137             parameters[7].Value = employeeInfo.EmployeeAddress;138             parameters[8].Value = employeeInfo.EmployeePosition;139             parameters[9].Value = employeeInfo.EmployeeBirthday;140 141             DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);142         }143     }144 }

View Code

4.2 简要分析

5 控制器层

5.1 方法

1 using MVCCrud.Areas.JqGridDemo.Models;  2 using Newtonsoft.Json;  3 using System;  4 using System.Collections.Generic;  5 using System.Configuration;  6 using System.Data;  7 using System.Data.SqlClient;  8 using System.IO;  9 using System.Linq; 10 using System.Web; 11 using System.Web.Mvc; 12 using MVCCrud.Areas.DAL; 13  14 namespace MVCCrud.Areas.JqGridDemo.Controllers 15 { 16     public class JqGridCRUDController : Controller 17     { 18         // GET: JqGridDemo/JqGridCRUD 19         //初始视图表 20         public ActionResult Index() 21         { 22             return View(); 23         } 24          //导入模板 25         public ActionResult GetEmployeeInfoTemple() 26         { 27             string path = Server.MapPath(@"~/Content/Upload/"); 28             string fileName = "EmployeeImport.xlsx"; 29             return File(new FileStream(path + fileName, FileMode.Open, FileAccess.Read), "xls", fileName); 30         } 31  32         //预设置初始化数据表 33         public ActionResult EmployeeInfoToLoad() 34         { 35             List
      
        ltPI = new List
       
        (); 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
        
          ltPI = new List
         
          (); 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         /// 
          110         ///  为Oject对象增加ToJsonString方法（注意对项目添加Newtonsoft.Json.dll引用）111         /// 
112         /// 
          113         /// 
          114         public string ToJsonString(Object obj)115         {116             JsonSerializerSettings jsSettings = new JsonSerializerSettings();117             jsSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;118             return JsonConvert.SerializeObject(obj, jsSettings);119         }120     }121 }122 123

View Code

5.2 分析

6 展示层

6.1 View Code

1 @{  2     Layout = null;  3 }  4   5   6   7   8   9     
       10     
       11     
       12      13      14      15      16      17      18      19     员工信息表 20             196 197 198     
      
       199         
       
        200             
        201             
        202             
        (点击此处下载模板)203             
        模糊查询： 
        204             
        205             
        206             
        207             
        208             
        209             
        210             211         
       
212         
       
        213             
        
214             
        
215             
        
         216                 
         
          217                     
           218                         
            219 
220 
221 
222                         
            223 
224 
225 
226                         
            227 
228 
229 
230                         
            231 
232 
233 
234                         
            235 
236 
237 
238                     
239                 
                                 员工ID：             员工姓名：            
                       员工专业：             员工部门：            
                       员工电话：             员工邮件：            
                       员工籍贯：             员工住址：            
                       员工职位：             员工生日：            
          
         
240             
        
241             
        
         242                
         
          243                     
           244                         
            245 
246 
247 
248                         
            249 
250 
251 
252                         
            253 
254 
255 
256                         
            257 
258 
259 
260                         
            261 
262 
263 
264                     
265                 
                                 员工ID：             员工姓名：            
                       员工专业：             员工部门：            
                       员工电话：             员工邮件：            
                       员工籍贯：             员工住址：            
                       员工职位：             员工生日：            
          
         
266             
        
267         
       
268         
       420     
      
421 422

View Code

6.2 分析

6.2.1 初始化数据

(1)图解功能

(2)功能Code

定义table获取区和翻页区

1 
     
2

JqGrid

View Code

6.2.2 JqGrid自带的CRUD

(1)图解功能

(2)功能Code

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

6.2.3 自动检测区：

(1)图解功能

(1)功能Code

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

6.2.4 JqGrid自带导出功能

(1)图解功能

(2)功能Code

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

6.2.5 自定义CRUD

(1)图解功能

(2)功能Code

View Code

6.2.6 导出

(1)图解功能

(2)功能Code

1 using System; 2 using System.Collections.Generic; 3 using System.Configuration; 4 using System.Data; 5 using System.Data.OleDb; 6 using System.Data.SqlClient; 7 using System.Linq; 8 using System.Web; 9 using System.Web.Mvc;10 11 namespace MVCCrud.Areas.JqGridDemo.Controllers12 {13     public class ImportDataController : Controller14     {15         // GET: JqGridDemo/ImportData16         public ActionResult Index()17         {18             return View();19         }20 21         /// 22         /// 从excel读取数据23         /// 
24         /// excel文件路径25         /// 
      26         public static DataSet ReadExcel(string FilePath)27         {28             try29             {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         /// 47         /// 将excel数据插入到EmployeeName表中48         /// 
49         /// excel文件所在路径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
      
        ListData = (from DataRow row in ds.Tables["ExcelToTalbe"].Rows select String.Format("INSERT INTO EmployeeInfo(EmployeeID,EmployeeName,EmployeeMajor,EmployeeDepartment,EmployeeTel,EmployeeEmail, EmployeeJiGuan,EmployeeAddress,EmployeePosition,EmployeeBirthday) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9])).ToList();59             string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;60             SqlConnection conn = new SqlConnection(ConStr);61             conn.Open();62             foreach (string item in ListData)63             {64                 SqlCommand comn = new SqlCommand(item, conn);65                 comn.ExecuteNonQuery();66             }67         }68     }69 }70                  71                 72

View Code

7 问题区

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

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

8 待解决问题

Q1:统一异常控制

Q2:JqGrid换肤

Q3:压力测试

Q4:友好提示

Q4:其他

9 最后

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

10 参考文献

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

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

11 服务区

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

12 版权

感谢您的阅读，若有不足之处，欢迎指教，共同学习、共同进步。

博主网址：http://www.cnblogs.com/wangjiming/。

极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的，大部分为原创。

如您喜欢，麻烦推荐一下；如您有新想法，欢迎提出，邮箱：2098469527@qq.com。

可以转载该博客，但必须著名博客来源。

转载于:https://www.cnblogs.com/wangjiming/p/7613160.html

你可能感兴趣的文章

Git之初体验持续更新

Exception in thread "AWT-EventQueue-0" java.lang.IllegalThreadStateException

随手练——HDU 5015 矩阵快速幂

启动redis一闪就关

Maven之setting.xml配置文件详解

ASP.NET 4.5 Web Forms and Visual Studio vs2013年入门1

JUC - ReentrantLock 的基本用法以及 lock()、tryLock()、lockInterruptibly()的区别

java 连接 Access数据库的两种方法

SDK目录结构

malloc() & free()

HDU 2063 过山车

高精度1--加法

在线文件管理器elFinder支持中文

Django之Models

Spring缓存注解@Cache使用

CSS 透明度级别及背景透明

Linux 的 date 日期的使用

PHP zip压缩文件及解压

SOAP web service用AFNetWorking实现请求

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！

当前时间: 2024-11-28 04:32:34 当前IP: 3.135.184.27 联系邮箱:javaeecc@qq.com Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2

强烈建议你试试无所不能的CHAT-GPT，快点击我

强烈建议你试试无所不能的CHAT-GPT，快点击我