【ASP.NET MVC】jqGrid 增刪改查詳解

1 概述 1 概述本篇文章主要是關於JqGrid的，主要功能包括使用JqGrid增刪查改，導入導出，廢話不多說，直接進入正題。 2 Demo相關 2 Demo相關 2.1 Demo展示第一部分第二部分 2.2 源碼和DB下載國慶回來上傳到github上。 3 公共模塊 3 公共模塊 3.1 ...

1 概述

本篇文章主要是關於JqGrid的，主要功能包括使用JqGrid增刪查改，導入導出，廢話不多說，直接進入正題。

2 Demo相關

2.1 Demo展示

第一部分

第二部分

2.2 源碼和DB下載

國慶回來上傳到github上。

3 公共模塊

3.1 Model實體—EmployeeInfo

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

View Code

3.2 DBHelper幫助類

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

View Code

4 數據訪問層DAL

4.1 對EmployeeInfo的CRUD

  1 using MVCCrud.Areas.DBUtility;
  2 using MVCCrud.Areas.JqGridDemo.Models;
  3 using System;
  4 using System.Collections.Generic;
  5 using System.Data;
  6 using System.Data.SqlClient;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Web;
 10 
 11 namespace MVCCrud.Areas.DAL
 12 {
 13     public class EmployeeInfoToCRUD
 14     {
 15         /// <summary>
 16         /// 增加一條數據
 17         /// </summary>
 18         /// <param name="employeeInfo">EmployeeInfo對象</param>
 19         /// <returns>添加數據是否成功</returns>
 20         public int DALEmployeeInfoToDdd(EmployeeInfo employeeInfo)
 21         {
 22             StringBuilder strSql = new StringBuilder();
 23             strSql.Append("insert into EmployeeInfo(");
 24             strSql.Append("EmployeeID,EmployeeName,EmployeeMajor,EmployeeDepartment,EmployeeTel,EmployeeEmail,EmployeeJiGuan,EmployeeAddress,EmployeePosition,EmployeeBirthday)");
 25             strSql.Append(" values (");
 26             strSql.Append("@EmployeeID,@EmployeeName,@EmployeeMajor,@EmployeeDepartment,@EmployeeTel,@EmployeeEmail,@EmployeeJiGuan,@EmployeeAddress,@EmployeePosition,@EmployeeBirthday)");
 27             strSql.Append(";select @@IDENTITY");
 28             SqlParameter[] parameters =
 29                 {
 30                     new SqlParameter("@EmployeeID", SqlDbType.VarChar,50),
 31                     new SqlParameter("@EmployeeName", SqlDbType.VarChar,50),
 32                     new SqlParameter("@EmployeeMajor", SqlDbType.Text),
 33                     new SqlParameter("@EmployeeDepartment",SqlDbType.VarChar,50),
 34                     new SqlParameter("@EmployeeTel", SqlDbType.VarChar,50),
 35                     new SqlParameter("@EmployeeEmail", SqlDbType.VarChar,50),
 36                     new SqlParameter("@EmployeeJiGuan", SqlDbType.VarChar,50),
 37                     new SqlParameter("@EmployeeAddress", SqlDbType.VarChar,500),
 38                     new SqlParameter("@EmployeePosition", SqlDbType.VarChar, 50),
 39                     new SqlParameter("@EmployeeBirthday", SqlDbType.DateTime)
 40                  };
 41 
 42             parameters[0].Value = employeeInfo.EmployeeID;
 43             parameters[1].Value = employeeInfo.EmployeeName;
 44             parameters[2].Value = employeeInfo.EmployeeMajor;
 45             parameters[3].Value = employeeInfo.EmployeeDepartment;
 46             parameters[4].Value = employeeInfo.EmployeeTel;
 47             parameters[5].Value = employeeInfo.EmployeeEmail;
 48             parameters[6].Value = employeeInfo.EmployeeJiGuan;
 49             parameters[