爱站程序员基地前言
博主由于工作需要自学了ASP.NET和C#，前端用的是Layui，写篇博客记录一下实现过程，就当是公开的有道云笔记哈哈，如果有更简便的方法欢迎留言评论~
前端Layui表格
layui.use(\'table\', function () {var table = layui.table;var form = layui.form;table.render({elem: \'#test\', url: \'Edittable.ashx\', where: {id: 1, Query: $(\"#Query\").val(), Sub_query: $(\"#Sub_query\").val(), Created_Alias: $(\"#Created_Alias\").val()}, method: \'get\', cols: [[{ type: \'checkbox\', fixed: \'left\' }, { field: \'ID\', width: 60, title: \'ID\', sort: true, fixed: \'left\' }, {field: \'Warning_Time\', width: 195, title: \'Warning Time\', sort: true,templet: function (d) {if (d.Status != \"Solved\") {var mydate = new Date(d.Created_Date);var tp = (parseInt(new Date() - mydate) / 1000 / 60 / 60);if (tp <= 4) {return \'<span><object data=\"image/bulb_green.svg\" height=\"30px\" />\' + d.Warning_Time + \'</span>\'} else if (4 < tp && tp <= 6) {return \'<span><object data=\"image/bulb_blue.svg\" height=\"30px\" />\' + d.Warning_Time + \'</span>\'} else if (6 < tp && tp <= 8) {return \'<span><object data=\"image/bulb_orange.svg\" height=\"30px\" />\' + d.Warning_Time + \'</span>\'} else if (tp > 8) {return \'<span><object data=\"image/bulb_red.svg\" height=\"30px\" />\' + d.Warning_Time + \'</span>\'}} else {return \'<span> \' + d.Warning_Time + \'</span>\'}}}, {field: \'Status\', width: 150, title: \'Status\',templet: \'#StatusTpl\'}, { field: \'SIT_PIC\', width: 150, title: \'SIT PIC\' }, { field: \'Query\', templet: \'#titleTpl\', width: 150, title: \'Query\' }, { field: \'Sub_query\', width: 150, title: \'Sub query\' }, { field: \'Created_Alias\', width: 120, title: \'Created Alias\' }, { field: \'Team\', width: 180, title: \'Team\' }, { field: \'Case_Priority\', width: 180, title: \'Case Priority\' }, { field: \'Created_Date\', width: 150, title: \'Created Date\' }, { field: \'Issue_Summay\', width: 180, title: \'Issue Summay\' }, { field: \'Open_Date\', width: 150, title: \'Open Date\' }, { field: \'Dependency_Department\', width: 220, title: \'Dependency Department\' }, { field: \'Escalation\', width: 180, title: \'Escalation\' }, { field: \'Closed_Date\', width: 180, title: \'Closed Date\' }, { field: \'Resolution_Summary\', width: 180, title: \'Resolution Summary\' }, { fixed: \'right\', title: \'操作\', toolbar: \'#barDemo\', width: 120 }]], page: true//固定列高度不一致的问题, done: function (res, curr, count) {$(\".layui-table-main tr\").each(function (index, val) {$(\".layui-table-fixed\").each(function () {$($(this).find(\".layui-table-body tbody tr\")[index]).height($(val).height());});});$(\".layui-table-header tr\").each(function (index, val) {$(\".layui-table-fixed\").each(function () {$($(this).find(\".layui-table-header thead tr\")[index]).height($(val).height());});});}});table.on(\'tool(test)\', function (obj) {var data = obj.data;var layEvent = obj.event;var tr = obj.tr;var open = this;var rowid = data.ID;if (layEvent === \'start\') {if (data.Status != \"Not start\") {return false;}layer.open({title: \'Open\', type: 1, tipsMore: true, area: [\'300px\', \'200px\'], btn: [\'提交\', \'取消\'], yes: function (index, layero) {if ($(\"#Open_date\").val() == \"\") {layer.msg(\'请填写时间！\', { anim: 6, time: 2000, icon: 7 })return false;}$.ajax({type: \"POST\",cache: false,url: \'Handler2.ashx\',data: {OrderId: \"open\", EditId: rowid, Open_Date: $(\"#Open_date\").val()},beforeSend: function () { },success: function (data) {layer.msg(\'Case已打开！\', { icon: 6 });open.classList.add(\"animated\", \"zoomOutLeft\")//this代表点击的按钮layer.close(index);//更新表中数据obj.update({Status: \"In progress\"});},error: function (XmlHttpRequest, textStatus, errorThrown) {layer.msg(\'更新失败，请检查网络！\', { icon: 5 });}});}, btn2: function (index, layero) {}, content: \'<input type=\"text\" class=\"layui-input\" id=\"Open_date\" style=\"width:180px;margin: auto;margin-top:30px\" placeholder=\"Open date\">\'});} else if (layEvent === \'edit\') {if (data.Status == \"Not start\") {layer.alert(\'请先打开Case\', { icon: 7, title: \'提示\' });return false;} else if (data.Status == \"Solved\") {layer.alert(\'Case已完成！\', { icon: 7, title: \'提示\' });return false;}layer.open({shade: 0,area: [\'800px\', \'530px\'],skin: \'layui-layer-lan\',btn: [\'提交\', \'取消\'],type: 1,anim: 4,title: \'编辑\', content: $(\'#eidtform\'), yes: function (index, layero) {if ($(\"#Dependency_Department\").val() == \"\" || $(\"#SIT_PIC\").val() == \"\" || $(\"#Closed_date\").val() == \"\" || $(\"#Resolution_Summary\").val()==\"\") {layer.msg(\'请填写完整！\', { anim: 6, time: 2000, icon: 7 })return false;}$.ajax({type: \"POST\",cache: false,url: \'Handler2.ashx\',data: {OrderId: \"SITupdate\", EditId: rowid, Dependency_Department: $(\"#Dependency_Department\").val(), SIT_PIC: $(\"#SIT_PIC\").val(), Escalation: $(\"#Escalation\").val(), Closed_Date: $(\"#Closed_date\").val(), Resolution_Summary: $(\"#Resolution_Summary\").val()},beforeSend: function () { },success: function (data) {layer.msg(\'更新成功！\', { icon: 6 });layer.close(index);obj.update({Dependency_Department: $(\"#Dependency_Department\").val(), SIT_PIC: $(\"#SIT_PIC\").val(), Escalation: $(\"#Escalation\").val(), Closed_Date: $(\"#Closed_date\").val(), Resolution_Summary: $(\"#Resolution_Summary\").val(), Status: \'Solved\'});$(\"#eidtform\")[0].reset();},error: function (XmlHttpRequest, textStatus, errorThrown) {layer.msg(\'更新失败，请检查网络！\', { icon: 5 });}});},});}layui.use(\'laydate\', function () {var laydate = layui.laydate;laydate.render({elem: \'#Open_date\', type: \'datetime\'//, lang: \'en\'});});layui.use(\'laydate\', function () {var laydate = layui.laydate;laydate.render({elem: \'#open_date\', type: \'datetime\'});laydate.render({elem: \'#closed_date\', type: \'datetime\'});});layui.use(\'form\', function () {var form = layui.form;form.render();});});});
表格的实现方法可以参考layuid的官网教程。有一个需要注意的点是，如果你的表格采用了内容自动换行而不是隐藏多余的文字，同时有使用了固定列，这时候就会操作表格的固定高度和其他列的高度不一致，用一个回调函数可以解决这个问题。
, done: function (res, curr, count) {$(\".layui-table-main tr\").each(function (index, val) {$(\".layui-table-fixed\").each(function () {$($(this).find(\".layui-table-body tbody tr\")[index]).height($(val).height());});});$(\".layui-table-header tr\").each(function (index, val) {$(\".layui-table-fixed\").each(function () {$($(this).find(\".layui-table-header thead tr\")[index]).height($(val).height());});});}
后端分页代码
layui向后端发送请求时会默认带有两个参数page和limit分表表示当前页数和每一页数据的条数，利用这两个参数可以得到数据库中数据的起始和终止序号，每一次翻页时就只请求这一页的数据。
Int32 nPages = Convert.ToInt32(context.Request[\"page\"]);Int32 nPageSize = Convert.ToInt32(context.Request[\"limit\"]);var start = nPageSize * nPages - nPageSize + 1;//分页数据的开始序号var end = nPageSize * nPages;//分页数据的终止序号comm.CommandText = \"SELECT * FROM (SELECT ROW_NUMBER() over(ORDER BY ID ASC) AS num, * FROM CaseSubmit WHERE ([Created_Date] BETWEEN CONVERT(varchar(100), GETDATE(), 23) +\' 00:00:00\' AND CONVERT(varchar(100), GETDATE(), 23) +\' 23:59:59\')) CaseSubmit WHERE (num BETWEEN \" + start + \" AND \" + end + \")\";//ROW_NUMBER() over是Sql实现分页的关键函数
Layui的table表对于返回的数字格式有要求，必须是这样的json格式：
sJson = \"{\\\"code\\\": \" + 0 + \",\\\"msg\\\": \\\"\\\",\\\"count\\\":\" + count + \",\\\"data\\\":\" + JsonString + \"}\";
comm.Connection = conn;SqlDataAdapter Adapter = new SqlDataAdapter();Adapter.SelectCommand = comm;DataSet ds = new DataSet();Adapter.Fill(ds);Adapter.Dispose();comm.Dispose();DataTable dt = ds.Tables[0];var iso = new Newtonsoft.Json.Converters.IsoDateTimeConverter();iso.DateTimeFormat = \"yyyy-MM-dd HH:mm:ss\";JsonString = JsonConvert.SerializeObject(dt,iso);sJson = \"{\\\"code\\\": \" + 0 + \",\\\"msg\\\": \\\"\\\",\\\"count\\\":\" + count + \",\\\"data\\\":\" + JsonString + \"}\";context.Response.Write(sJson);context.Response.End();
如果查询结果中时间，那么转换为json后日期和时间中会有一个T，如2020-03-13T23:13:22，这时候用var iso = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
iso.DateTimeFormat = “yyyy-MM-dd HH:mm:ss”;
JsonString = JsonConvert.SerializeObject(dt,iso);可以去掉中间的T从而正常显示。
如果有更简便的方法欢迎留言评论~?
