Datatables应用实例（极致精简）

（仅供参考，不喜勿喷）

//附sql------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 1 CREATE DATABASE Exams
 2 
 3 USE Exams
 4 
 5 CREATE TABLE datatables
 6 (
 7     ID       INT   PRIMARY KEY IDENTITY,
 8     Name    NVARCHAR(50),
 9     Age      NVARCHAR(50),
10     Sex         NVARCHAR(50),
11     [Address] NVARCHAR(50),
12     Office NVARCHAR(50)
13 )
14 DECLARE @i INT =0;
15 WHILE (@i<100)
16 BEGIN
17 
18 INSERT dbo.datatables
19         ( Name, Age, Sex, Address, Office )
20 VALUES  ( N'杨'+CONVERT(NVARCHAR(50),@i) , -- Name - nvarchar(50)
21           N'5'+@i, -- Age - nvarchar(50)
22           N'0'+@i, -- Sex - nvarchar(50)
23           N'beiji'+CONVERT(NVARCHAR(50),@i), -- Address - nvarchar(50)
24           N'bangong'+CONVERT(NVARCHAR(50),@i)  -- Office - nvarchar(50)
25           )
26           SET @i=@i+1;
27 END

//前台主界面------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  1 @{
  2     Layout = null;
  3 }
  4 
  5 <!DOCTYPE html>
  6 
  7 <html>
  8 <head>
  9     <meta name="viewport" content="width=device-width" />
 10     <title>Index</title>
 11     @*<script src="~/DataTables-1.10.15/media/js/jquery.js"></script>*@
 12     <script src="~/jquery-1.8.2.min.js"></script>
 13     <link href="~/DataTables-1.10.15/media/css/jquery.dataTables.min.css" rel="stylesheet" />
 14     <script src="~/DataTables-1.10.15/media/js/jquery.dataTables.min.js"></script>
 15     <script src="~/layer-v3.0.3/layer/layer.js"></script>
 16 </head>
 17 <body>
 18     <div>
 19         <input id="selname" /><input type="button" value="查询" onclick="AddStudent()" />
 20         <div>
 21             <input type="button" value="添加" onclick="AddStudent()" />
 22             <table id="exec" class="display" cellspacing="0" width="100%">
 23                 <thead>
 24                     <tr>
 25                         <th>ID</th>
 26                         <th>Name</th>
 27                         <th>Age</th>
 28                         <th>Sex</th>
 29                         <th>Address</th>
 30                         <th>Office</th>
 31                         <th>操作</th>
 32                     </tr>
 33                 </thead>
 34 
 35                 <tfoot>
 36                     <tr>
 37                         <th>ID</th>
 38                         <th>Name</th>
 39                         <th>Age</th>
 40                         <th>Sex</th>
 41                         <th>Address</th>
 42                         <th>Office</th>
 43                         <th>操作</th>
 44                         
 45                     </tr>
 46                 </tfoot>
 47             </table>
 48         </div>
 49         </div>
 50     <script>
 51         function UpdateFromId(id)
 52         {
 53             layer.open({
 54                 type: 2,
 55                 area: ['40%', '80%'],
 56                 content: '/Default/UpdateFromId/' + id, end: function () { search(); }
 57 
 58             });
 59         }
 60         function AddStudent()
 61         {
 62             layer.open({
 63                 type: 2,
 64                 area: ['40%','80%'],
 65                 content: '/Default/AddStuden',
 66                 end: function () { search(); }
 67             });
 68              
 69 
 70         }
 71         function delFromID(id)
 72         {
 73             if (confirm("确定要删除吗？"))
 74             {
 75                 $.ajax({
 76 
 77                     url: "delectbyid/"+id,
 78                     type:"get",
 79                     success: function (data)
 80                     {
 81                         alert(data);
 82                         search();
 83                     }
 84                 });
 85             }
 86         }
 87         search();
 88         function search() {
 89             $('#example').dataTable().fnClearTable();
 90 
 91                 var datatable = $('#exec').dataTable({
 92                 "pageLength": 5, //每页显示多少条
 93                 "serverSide": true,//服务端模式
 94                 "paging": true, //是否显示分页
 95                  destroy: true,//--------------不能少，少了就不对
 96                 ajax: {
 97                     url: "Getdata",
 98                     data: function (d) {           
 99                         d.name = $("#selname").val();
100                     }
101                 },
102                 "columns":
103                     [
104                                 { "data": "ID" },
105                                 { "data": "Name" },
106                                 { "data": "Age" },
107                                 { "data": "Sex" },
108                                 { "data": "Address" },
109                                 { "data": "Office" },
110                                 { "data": "Caozuo" }
111 
112                     ]
113                 
114                 //"columnDefs":
115                 //    [{
116                 //        targets: 6,
117                 //        render: function (data, type, row, meta) {
118                 //            return '<input type="button"  onclick=delFromID(' + ID + ')  value="删除" />';
119                 //        }
120                 //    },
121 
122                 //          { "orderable": false, "targets": 6},
123                 //    ],
124                     
125                 });
126 
127         }
128 
129 
130 
131 
132     </script>
133 </body>
134 </html>

//添加界面------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 1 @{
 2     Layout = null;
 3 }
 4 
 5 <!DOCTYPE html>
 6 
 7 <html>
 8 <head>
 9     <meta name="viewport" content="width=device-width" />
10 
11     <title>AddStuden</title>
12     <script src="~/bootstrap.min.js"></script>
13     <script src="~/jquery-1.8.2.min.js"></script>
14 </head>
15 <body>
16     <div style="margin:0 auto; width:auto; height:auto; margin-left:200px; margin-top:100px;"   >
17        <form method="post"  action="" enctype="multipart/form-data">
18            <h1>添加学生</h1>
19            <table class="table-responsive" >
20                 <tr><td>姓名：<input name="Name" /></td></tr>
21                <tr></tr>
22                <tr></tr>
23                 <tr><td>年龄：<input name="Age" /></td></tr>
24                <tr></tr>
25                <tr></tr>
26                 <tr><td>性别：<input name="Sex" /></td></tr>
27                <tr></tr>
28                <tr></tr>
29                 <tr><td>住址：<input name="Address" /></td></tr>
30                <tr></tr>
31                <tr></tr>
32                 <tr><td> 职务：<input name="Office" /></td></tr>
33                <tr></tr>
34                <tr></tr>
35            </table>
36            <input  type="submit" value="添加" onclick="thisclear()"/> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="button" value="返回" onclick="    thisclear()"/>
37        </form>
38     </div>
39     <script>
40         function thisclear()
41         {
42             var index = parent.layer.getFrameIndex(window.name);
43             parent.layer.close(index);
44         }
45     </script>
46 </body>
47 </html>

//编辑界面------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 1 @{
 2     Layout = null;
 3 }
 4 
 5 <!DOCTYPE html>
 6 @model WebApplication1.Controllers.datatables
 7 <html>
 8 <head>
 9     <meta name="viewport" content="width=device-width" />
10     <title>UpdateFromId</title>
11     <script src="~/bootstrap.min.js"></script>
12     <script src="~/jquery-1.8.2.min.js"></script>
13 </head>
14 <body>
15     <div style="margin:0 auto; width:auto; height:auto; margin-left:200px; margin-top:100px;">
16         <form method="post" action="" enctype="multipart/form-data">
17             <h1>编辑信息</h1>
18             <table class="table-responsive">
19                 <tr style="display:none;"><td>编号：@Html.TextBoxFor(T => T.ID)</td></tr>
20                 <tr></tr>
21                 <tr></tr>
22               
23                 <tr><td>姓名：@Html.TextBoxFor(T=>T.Name)</td></tr>
24                 <tr></tr>
25                 <tr></tr>
26                 <tr><td>年龄：@Html.TextBoxFor(T => T.Age)</td></tr>
27                 <tr></tr>
28                 <tr></tr>
29                 <tr><td>性别：@Html.TextBoxFor(T => T.Sex)</td></tr>
30                 <tr></tr>
31                 <tr></tr>
32                 <tr><td>住址：@Html.TextBoxFor(T => T.Address)</td></tr>
33                 <tr></tr>
34                 <tr></tr>
35                 <tr><td> 职务：@Html.TextBoxFor(T => T.Office)</td></tr>
36                 <tr></tr>
37                 <tr></tr>
38             </table>
39             <input type="submit" value="保存" onclick="thisclear()" /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="button" value="返回" onclick="    thisclear()" />
40         </form>
41     </div>
42     <script>
43         function thisclear()
44         {
45             var index = parent.layer.getFrameIndex(window.name);
46             parent.layer.close(index);
47         }
48     </script>
49 </body>
50 </html>

//控制器------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Data.SqlClient;
  5 using System.Linq;
  6 using System.Web;
  7 using System.Web.Mvc;
  8 
  9 namespace WebApplication1.Controllers
 10 {
 11     public class DefaultController : Controller
 12     {
 13         // GET: Default
 14         public ActionResult Index()
 15         {
 16             return View();
 17         }
 18         [HttpGet]
 19         public JsonResult Getdata(int draw, string name, int length = 5, int start = 0)
 20         {
 21             List<datatables> list = new List<datatables>();
 22             int pagecount = 0;
 23             using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Exams;Integrated Security=True"))
 24             {
 25                 conn.Open();
 26                 string sql = "select top ("+length+") * from  datatables where  Name like  '%" + name + "%' and  ID not  in (select  top ("+start+ ") ID  from  datatables where  Name like  '%" + name + "%' ) ";
 27                 SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
 28 
 29                 SqlCommand cmd = new SqlCommand("select count(1)  from datatables where  Name like  '%" + name + "%' ", conn);
 30                 pagecount = Convert.ToInt32(cmd.ExecuteScalar());
 31                 DataTable tb = new DataTable();
 32                 adp.Fill(tb);
 33 
 34 
 35                 foreach (DataRow item in tb.Rows)
 36                 {
 37                     datatables tbs = new datatables()
 38                     {
 39 
 40                         ID = Convert.ToInt32(item["ID"]),
 41                         Address = item["Address"].ToString(),
 42                         Age = item["Age"].ToString(),
 43                         Name = item["Name"].ToString(),
 44                         Office = item["Office"].ToString(),
 45                         Sex = item["Sex"].ToString(),
 46                         Caozuo = "<input type='button'  onclick=delFromID("+ Convert.ToInt32(item["ID"]) + ")  value='删除' /> &nbsp;&nbsp;<input type='button'  onclick=UpdateFromId(" + Convert.ToInt32(item["ID"]) + ")  value='修改' />"
 47                     };
 48                     list.Add(tbs);
 49                     conn.Close();
 50                 }
 51                 
 52             }
 53 
 54                 var result = new
 55                 {
 56                     draw = draw,  //客户端唯一标识
 57                     recordsFiltered = pagecount,
 58                     recordsTotal = pagecount,   //总行数
 59                     data = list
 60                 };
 61                 return Json(result, JsonRequestBehavior.AllowGet);
 62         }
 63 
 64         public string delectbyid(int id)
 65         {
 66             int i = 0;
 67             using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Exams;Integrated Security=True"))
 68             {
 69                 conn.Open();
 70                 SqlCommand cmd = new SqlCommand("delete  from  datatables where ID="+id, conn);
 71                 i = Convert.ToInt32(cmd.ExecuteNonQuery());
 72                 conn.Close();
 73             }
 74             if (i==1)
 75             {
 76                 return "删除成功";
 77             }
 78             else
 79             {
 80                 return "删除失败";
 81             }
 82 
 83         }
 84 
 85        
 86         public ActionResult AddStuden()
 87         {
 88 
 89             return View();
 90 
 91         }
 92         [HttpPost]
 93         public ActionResult AddStuden(datatables s)
 94         {
 95             int i = 0;
 96             using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Exams;Integrated Security=True"))
 97             {
 98                 conn.Open();
 99                 string sql = string.Format("insert datatables values ('{0}','{1}','{2}','{3}','{4}')",s.Name,s.Age,s.Sex,s.Address,s.Office);
100                 SqlCommand cmd = new SqlCommand(sql,conn);
101                 i = Convert.ToInt32(cmd.ExecuteNonQuery());
102                 conn.Close();
103             }
104             if (i == 1)
105             {
106                 return View("Index");
107             }
108             else
109             {
110                 return Content("<script>alert('添加失败');;</script>");
111             }
112 
113         }
114 
115 
116         public ActionResult UpdateFromId(int id)
117         {
118             datatables tbs = new datatables();
119             using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Exams;Integrated Security=True"))
120             {
121                 #region
122                 string sql = "select  *  from datatables where ID="+id;
123                 SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
124                 DataTable tb = new DataTable();
125                 adp.Fill(tb);
126                 foreach (DataRow item in tb.Rows)
127                 {
128                         tbs = new datatables()
129                     {
130 
131                         ID = Convert.ToInt32(item["ID"]),
132                         Address = item["Address"].ToString(),
133                         Age = item["Age"].ToString(),
134                         Name = item["Name"].ToString(),
135                         Office = item["Office"].ToString(),
136                         Sex = item["Sex"].ToString(),
137                         Caozuo = "<input type='button'  onclick=delFromID(" + Convert.ToInt32(item["ID"]) + ")  value='删除' /> &nbsp;&nbsp;<input type='button'  onclick=UpdateFromId(" + Convert.ToInt32(item["ID"]) + ")  value='修改' />"
138                     };
139 
140                    
141                 }
142                 #endregion
143                 return View(tbs);
144             }
145 
146         }
147         [HttpPost]
148         public ActionResult UpdateFromId(datatables s)
149         {
150 
151             int i = 0;
152             using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Exams;Integrated Security=True"))
153             {
154                 conn.Open();
155                 string sql = string.Format("update datatables set Name='{0}',Age='{1}',Sex='{2}',Address='{3}',Office='{4}' where ID={5}", s.Name, s.Age, s.Sex, s.Address, s.Office,s.ID);
156                 SqlCommand cmd = new SqlCommand(sql, conn);
157                 i = Convert.ToInt32(cmd.ExecuteNonQuery());
158                 conn.Close();
159             }
160             if (i == 1)
161             {
162                 return Content("<script>alert('编辑成功');location.href='/Default/Index';</script>");
163             }
164             else
165             {
166                 return Content("<script>alert('编辑失败');</script>");
167             }
168         }
169 
170 
171     }
172     public class datatables
173     {
174         public int ID { get; set; }
175         public string Name { get; set; }
176         public string Age { get; set; }
177         public string Sex { get; set; }
178         public string Address { get; set; }
179         public string Office { get; set; }
180 
181         public string Caozuo { get; set; }
182     }
183 }

Datatables应用实例 （极致精简）

（仅供参考，不喜勿喷）

Datatables应用实例（极致精简）