asp.net layui数据表格数据交互实现动态数据表格 asp.net 真分页
2021/4/9 20:29:02
本文主要是介绍asp.net layui数据表格数据交互实现动态数据表格 asp.net 真分页，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.引用layui.css layui.js
2.前台页面 AssetsListNew.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AssetsListNew.aspx.cs" Inherits="DQPA.PAManager.AssetsListNew" %>
<!DOCTYPE html>
<html>
<head runat="server">
<meta charset="utf-8">
<title>点趣-资产列表</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link href="css/style1.css" rel="stylesheet" />
<link href="css/ace.min.css" rel="stylesheet" />
<link href="css/bootstrap.min.css" rel="stylesheet" />
<link href="css/codemirror.css" rel="stylesheet" />
<link href="css/font-awesome.min.css" rel="stylesheet" />
<link href="layui/css/layui.css" rel="stylesheet" />
<script src="js/jquery-1.9.1.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="layui/layui.js"></script>
<style>
#anone1, #anone2:link {
font-size: 14px;
color: #fff;
text-decoration: none;
}
#anone1, #anone2:visited {
font-size: 14px;
color: #fff;
text-decoration: none;
}
#anone1, #anone2:hover {
font-size: 14px;
color: #fff;
text-decoration: none;
}
/*link1,link1:link,link1:visited,link1:hover{font-size:14px;color:#fff;text-decoration: none;}*/
</style>
</head>
<body>
<form id="form1" class="layui-form pagediv" runat="server" style="margin-top: 5px">
<div class="search_style">
<ul class="search_content clearfix" style="padding-left: 2rem;">
<li>
<label class="l_f">导入：</label>
<span class="add_name" style="float: right;">
<a href="AssetsListNew.aspx?action=down" id="anone1" class=" layui-btn ">下载模板</a>
</span>
<li>
<input type="file" id="fileUpload" runat="server" class="btn btn-warning" style="background-color: #abbac3!important; border-color: #abbac3;" />
</li>
<li>
<button type="submit" class=" layui-btn" runat="server" onserverclick="btnImport_Click">导入</button>
</li>
</ul>
</div>
<div class="search_style">
<ul class="search_content clearfix" style="padding-left: 2rem;">
<li>
<label class="l_f">搜索项：</label>
<span class="add_name" style="float: right;">
<select id="sSearch" runat="server">
<option value="">请选择</option>
<option value="Number">编号</option>
<option value="type">类型</option>
<option value="brand">品牌</option>
<option value="BelongName">使用人</option>
</select>
</span>
<input type="text" hidden="hidden" runat="server" id="hidSearch" /></li>
<li>
<input type="text" runat="server" id="txtCondition" class="layui-input" placeholder="输入搜索值" onkeyup="value=value.replace(/[^\a-\z\A-\Z0-9\u4E00-\u9FA5\ ]/g,'')" onpaste="value=value.replace(/[^\a-\z\A-\Z0-9\u4E00-\u9FA5\ ]/g,'')" oncontextmenu="value=value.replace(/[^\a-\z\A-\Z0-9\u4E00-\u9FA5\ ]/g,'')" autocomplete="off" style="width: 180px" /></li>
<li>
<label class="l_f">购买时间：</label>
</li>
<li>
<input class="layui-input " id="start" runat="server" style="margin-left: 10px;" readonly="readonly" placeholder="开始时间" autocomplete="off" />
</li>
<li>
<label class="l_f">     ---</label>
</li>
<li>
<input class="layui-input " id="end" runat="server" style="margin-left: 10px;" readonly="readonly" placeholder="结束时间" autocomplete="off" />
</li>
<li>
<button class="layui-btn" type="button" id="btnSearch" onclick="searchList()"><i class="layui-icon"></i>查询</button>
<a href="AssetsListNewAdd.aspx" id="anone2" class="layui-btn"><i class="layui-icon"></i>新增</a>
<%--<button class="layui-btn" data-type="reload"><i class="layui-icon"></i>添加</button>--%>
</li>
<%--<li style="width: 90px;">
<button type="submit" id="btnSearch" class="btn_search">查询</button></li>--%>
</ul>
</div>
<script type="text/javascript">
function searchList() {
if ($("#start").val() != null && $("#end").val() != null) {
const _PlanEndTime = new Date($("#start").val());
const _PlanStartTime = new Date($("#end").val());
if (_PlanEndTime.getTime() > _PlanStartTime.getTime()) {
alert("开始时间不能大于结束时间!");
return false;
}
}
salesf(encodeURI('AssetsListNew.aspx?action=list&selectc=' + $("#sSearch").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));
}
</script>
<%--<script type="text/html" id="toolbarDemo">
<div class="layui-inline" style="float: right">
<a class="layui-btn layui-btn-sm" lay-event="search" style="text-decoration: none"><i class="layui-icon"></i>查询</a>
<a class="layui-btn layui-btn-sm" lay-event="add" style="text-decoration: none"><i class="layui-icon"></i> 添加</a>
<a class="layui-btn layui-btn-sm" lay-event="delele" style="display: none;"><i class="layui-icon"></i> 导出</a>
</div>
</script>--%>
<table class="layui-hide" id="test" lay-filter="demo"></table>
<div id="laypage" style="float: right;"></div>
<script type="text/html" id="barDemo">
<a class="layui-btn layui-btn-xs" lay-event="make" data-type="auto" style="margin-left: 0; text-decoration: none;">二维码</a>
<a class="layui-btn layui-btn-xs" lay-event="edit" data-type="auto" style="margin-left: 0; text-decoration: none;">修改</a>
<a class="layui-btn layui-btn-danger layui-btn-xs" style="margin-left: 0; text-decoration: none;" lay-event="del">删除</a>
</script>
<script type="text/javascript">
var sortName = "id";
var sortType = "desc";
var pages = 1;
var rows = 10;
var total; //总条数
var title;
var amtype;
$(document).ready(function () {
salesf('AssetsListNew.aspx?action=list');
layui.use('laydate', function () {
var laydate = layui.laydate;
//自定义格式
laydate.render({
elem: '#start',
format: 'yyyy-MM-dd',
trigger: 'click' //日期框只能点击选择，不能手动输入
});
laydate.render({
elem: '#end',
format: 'yyyy-MM-dd',
trigger: 'click' //日期框只能点击选择，不能手动输入
});
});
});
//salesf('table.aspx?action=list');
function salesf(url) {
layui.use(['table', 'laypage', 'form', 'layer'], function () {
var table = layui.table,
laypage = layui.laypage,
form = layui.form,
layer = layui.layer;
var tableOptions = {
elem: '#test'
, url: url
, height: 470
//, toolbar: '#toolbarDemo'
, id: 'idTest'
, defaultToolbar: []
, method: 'POST' //方式
, page: false //是否分页
, where: {
page: pages,
rows: rows,
sort: sortName,
order: sortType
} //请求后端接口的条件，该处就是条件错误点，按照官方给出的代码示例，原先写成了 where: { key : { type: "all" } }，结果并不是我想的那样，如此写，key 将是后端的一个类作为参数，里面有 type 属性，如果误以为 key 是 Layui 提供的格式，那就大错特错了
, cols: [[
{ field: 'row', width: 80, title: '序号', sort: true }
, { field: 'number', width: 120, title: '资产编号' }
, { field: 'type', width: 80, title: '类型', sort: true }
, { field: 'brand', width: 80, title: '品牌' }
, { field: 'ismac', width: 80, title: 'MAC' }
, { field: 'videocard', title: '显卡' }
, { field: 'ram', width: 80, title: '内存', sort: true }
, { field: 'rigiddisk', width: 80, title: '硬盘', sort: true }
, { field: 'cpu', width: 80, title: 'CPU' }
, { field: 'purchasetime1', width: 120, title: '购买时间', sort: true }
, { field: 'monetary', minWidth: 120, title: '购买金额' }
, { field: 'size', width: 80, title: '尺寸', sort: true }
, { field: 'department', width: 120, title: '部门' }
, { field: 'belongname', width: 80, title: '使用人' }
, { field: 'position', title: '位置', minWidth: 150, sort: true }
, { field: 'prodirection', width: 80, title: '产品去向', sort: true }
, { field: 'sellingprice', minWidth: 120, title: '产品已售金额' }
, { field: 'remark', width: 135, title: '产品去向备注', sort: true }
, { field: 'auditter', fixed: 'right', title: '操作', minWidth: 180, align: 'center', templet: '#barDemo' }
]]
, parseData: function (res) {
return {
"code": "0",
"msg": "cg",
"count": res.total,
"data": res.rows
};
}
, done: function (res, curr, count) {
laypage.render({
elem: 'laypage',
count: count,
curr: pages,
limit: rows,
layout: ['prev', 'page', 'next', 'skip', 'count', 'limit'],
jump: function (obj, first) {
if (!first) {
pages = obj.curr;
rows = obj.limit;//$("#sSearch option:selected").val()
salesf(encodeURI('AssetsListNew.aspx?action=list&selectc=' + $("#sSearch").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));
}
}
});
form.render("select");
}
};
table.render(tableOptions);
//监听行工具条
table.on('tool(demo)', function (obj) {
var data = obj.data;
if (obj.event === 'make') {
location.href = "AssetsListNew.aspx?action=make&id=" + data.id + "";
//alert("可以生成" + data.id + "的二维码")
//make(table, obj.data.id);
//layer.msg('ID：' + data.id + ' 的查看操作');
} else if (obj.event === 'del') {
del(table, obj.data.id);
}
else if (obj.event === 'edit') {
location.href = "AssetsListNewAdd.aspx?action=edit&id=" + data.id + "";
//EditData1('auto', "id", "修改", "PAManager/AssetsAdd.aspx?action=edit&id=" + data.id + "", '75%', '80%');
//EditData1('auto', "id", "修改", "../DiBaoGuanLi/60_Add.aspx?idcard=" + data.idcard + "", '75%', '80%');
}
});
//单条删除操作
function del(table, id) {
if (confirm("确定删除?")) {
$.post("AssetsListNew.aspx?action=delete&id=" + id, {}, function (data) {
var res = JSON.parse(data);
if (res.code == 0) {
alert("删除成功!");
salesf(encodeURI('AssetsListNew.aspx?action=list&selectc=' + $("#sSearch").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));
}
else {
alert("删除失败!");
salesf(encodeURI('AssetsListNew.aspx?action=list&selectc=' + $("#sSearch").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));
}
});
}
}
//单条生成二维码操作
function make(table, id) {
$.post("AssetsListNew.aspx?action=make&id=" + id, {}, function (data) {
//var res = JSON.parse(data);
//if (res.code == 0) {
// alert("成功生成二维码!");
// /*salesf(encodeURI('AssetsListNew.aspx?action=list&selectc=' + $("#sSearch").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));*/
//}
//else {
// alert("生成二维码失败!");
// /*salesf(encodeURI('AssetsListNew.aspx?action=list&selectc=' + $("#sSearch").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));*/
//};
})
}
//添加人员弹框
function EditData1(offset, id, title, src, height, width) {
top.layer.config({
extend: 'myskin/style.css', //加载新皮肤
skin: 'demo-class' //一旦设定，所有弹层风格都采用此主题。
}).open({
type: 2
, offset: offset //具体配置参考：http://www.layui.com/doc/modules/layer.html#offset
, id: id //防止重复弹出
, title: title
, area: [height, width]
, content: src
//, btn: '关闭全部'
, btnAlign: 'c' //按钮居中
, shade: 0 //不显示遮罩
, yes: function () {
parent.layer.closeAll()
}
, cancel: function (index, layero) {
salesf('table.aspx?action=list');
}
});
}
头工具栏事件
//table.on('toolbar(demo)', function (obj) {
// switch (obj.event) {
// case 'search':
// salesf(encodeURI('table.aspx?action=list&selectc=' + $("#sSearch option:selected").val() + '&txtselect=' + $("#txtCondition").val() + '&start=' + $("#start").val() + '&end=' + $("#end").val() + ''));
// break;
// case 'add':
// window.location.href = "tableadd.aspx";
// break;
// //case 'delele':
// // //var data = checkStatus.data;
// // var delids = "";
// // $.each(data, function (i, item) {
// // if (delids) {
// // delids += ","
// // }
// // delids += item.amid;
// // });
// // if (!delids) {
// // alert("没有选中的数据");
// // return false;
// // }
// // var r = confirm("确定要删除记录吗?")
// // if (r == true) {
// // $.post("table.aspx?action=delete&delids=" + delids, {}, function (data) {
// // if (data.d != "") {
// // salesf('table.aspx?action=load&type=' + typeval);
// // } else {
// // }
// // });
// // }
// // break;
// };
//});
});
}
</script>
</form>
</body>
</html>
3.后台代码 AssetsListNew.aspx.cs
using Common;
using DAL;
using DQPA.BLL;
using DQPA.IBLL;
using DQPA.MODEL;
using Gma.QrCodeNet.Encoding;
using Gma.QrCodeNet.Encoding.Windows.Render;
using Maticsoft.DBUtility;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace DQPA.PAManager
{
public partial class AssetsListNew : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Auxiliary aux = new Auxiliary();
if (Session["UId"] == null || string.IsNullOrEmpty(Server.UrlDecode(Session["UId"].ToString())))
{
Response.Redirect("UserLogin.aspx");
return;
}
if (!IsPostBack)
{
var action = Request.QueryString["action"];
var id = Request.QueryString["id"];
switch (action)
{
case "list":
loadAssets();
break;
case "delete":
DelAssets();
break;
case "upload":
//var fileup = test8.PostedFile;
//Upload(fileup);
break;
case "make":
MakeORCode(id);
break;
case "down":
DownloadOperation();
break;
default:
break;
}
}
//var a = hidSearch.Value;
//var b = txtCondition.Value;
//var download = Request.QueryString["down"];
//if (download != null)
//{
// var downtype = download.ToString();
// switch (downtype)
// {
// case "1":
// DownloadOperation();
// break;
// default:
// break;
// }
//}
}
protected void loadAssets()
{
try
{
//DataTable dt = new DataTable();
int count;
StringBuilder strwhere = new StringBuilder();
IAssetsBll assetsBll = new AssetsBll();
strwhere.Append(" 1=1");//and ylyid in (select id from M_yanglaoyuan where type ='" + type + "')
strwhere.Append(" and ISNULL(IsDelete,0) <> 1 ");
//if (!string.IsNullOrEmpty(txtCondition.Value))
//{
// where.Append(" and Number like '%" + txtCondition.Value + "%' or type like '%" + txtCondition.Value + "%' or brand like '%" + txtCondition.Value + "%' ");
//}
var searchC = Request.QueryString["selectc"];
var txtsearch = Request.QueryString["txtselect"];
var startTime = Request.QueryString["start"];
var endTime = Request.QueryString["end"];
if (searchC != null && !string.IsNullOrEmpty(searchC))
{
if (txtsearch != null && !string.IsNullOrEmpty(txtsearch))
{
var whereSearch = string.Format(@" and {0} like '%" + txtsearch + "%'", searchC);
strwhere.Append(whereSearch);
}
}
if (!string.IsNullOrEmpty(startTime))
{
if (!string.IsNullOrEmpty(endTime))
{
DateTime start1 = DateTime.ParseExact(startTime, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
//DateTime fStart = start1.AddDays(1);
DateTime fStart = DateTime.ParseExact(endTime, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
//if (start1 <= fStart)
//{
strwhere.Append(" and PurchaseTime >= '" + start1 + "' and PurchaseTime < '" + fStart + "'");
//}
}
}
//if (!string.IsNullOrEmpty(start.Value))
//{
// if (!string.IsNullOrEmpty(end.Value))
// {
// DateTime start1 = DateTime.ParseExact(start.Value, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
// //DateTime fStart = start1.AddDays(1);
// DateTime fStart = DateTime.ParseExact(end.Value, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
// strwhere.Append(" and PurchaseTime >= '" + start1 + "' and PurchaseTime < '" + fStart + "'");
// }
//}
//if (hidSearch.Value != null && !string.IsNullOrEmpty(hidSearch.Value))
//{
// if (txtCondition.Value != null && !string.IsNullOrEmpty(txtCondition.Value))
// {
// var whereSearch = string.Format(@" and {0} like '%" + txtCondition.Value + "%'", hidSearch.Value, txtCondition.Value);
// strwhere.Append(whereSearch);
// }
//}
int page = Request.Form["page"] != "" ? Convert.ToInt32(Request.Form["page"]) : 0;
int size = Request.Form["rows"] != "" ? Convert.ToInt32(Request.Form["rows"]) : 0;
string sort = Request.Form["sort"] != "" ? Request.Form["sort"] : "";
string order = Request.Form["order"] != "" ? Request.Form["order"] : "";
var dt = assetsBll.DataPage("Assets", "*,CONVERT(varchar(100), PurchaseTime, 23) as PurchaseTime1", "addtime", "desc", size, page, strwhere.ToString(), out count);
string json = string.Empty;
//if (dt != null && dt.Rows.Count > 0 && dt.Rows[0] != null)
//{
json = JsonHelper.CreateJsonParameters(dt, true, count);
//}
//else
//{
// json = JsonHelper.CreateJsonParameters(null, false, count);
//}
Response.Write(json);
Response.End();
}
catch (Exception ex)
{
throw ex;
}
}
public void DelAssets()
{
var id = Request.QueryString["id"];
if (id != null)
{
string msg = "";
IAssetsBll bll = new AssetsBll();
if (!string.IsNullOrEmpty(id))
{
bool res = bll.DeleteDetail(Convert.ToInt32(id), out msg);
if (res)
{
Response.Write("{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
Response.End();
//Response.Write("{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
}
else
{
Response.Write("{\"code\": 1,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
Response.End();
}
}
}
else
{
Response.Write("{\"code\": 1,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
Response.End();
}
}
#region 下载上传模板
protected void DownloadOperation()
{
//string filePath = Server.MapPath("../excel") + @"\" + "电脑盘点导入模板.xls" + "";
string filePath = Server.MapPath("../excel") + @"\" + "电脑盘点导入模板.xlsx" + "";
byte[] data = File.ReadAllBytes(filePath);
MemoryStream stream = new MemoryStream(data);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "电脑盘点导入模板.xlsx"));
Response.BinaryWrite(stream.ToArray());
stream.Close();
stream.Dispose();
//File.Delete(filePath);
Response.End();
}
#endregion
#region 导入excel .xlsx
protected void btnImport_Click(object sender, EventArgs e)
{
var fileup = fileUpload.PostedFile;
//InsetData(Upload(fileup));
//InsetData(fileup);
// 说明：导入的方法
if (fileUpload == null)
{
Response.Write("<script>alert('请先选择Excel文件！');window.location.href='AssetsListNew.aspx'</script>");
}
else
{
string fileUrl = "";
#region 文件上传
//try
//{
//}
//catch
//{
// Response.Write("<script>alert('数据上传失败，请重新导入');window.location.href='table.aspx'</script>");
// res = false;
//}
//全名
string excelFile = this.fileUpload.PostedFile.FileName;
//获取文件名（不包括扩展名）
string fileName = Path.GetFileNameWithoutExtension(fileup.FileName);
if (fileName == "" || fileName == null)
{
Response.Write("<script>alert('请先选择Excel文件！');window.location.href='AssetsListNew.aspx'</script>");
}
else
{
//扩展名
string extentionName = excelFile.Substring(excelFile.LastIndexOf(".") + 1);
if (extentionName != "xlsx")
{
Response.Write("<script>alert('您上传的不是.xlsx文件！');window.location.href='AssetsListNew.aspx'</script>");
}
else
{
//浏览器安全性限制 无法直接获取客户端文件的真实路径，将文件上传到服务器端 然后获取文件源路径
#region 设置上传路径将文件保存到服务器
string dateTime = DateTime.Now.Date.ToString("yyyyMMdd");
string time = DateTime.Now.ToShortTimeString().Replace(":", "");
string newFileName = dateTime + time + DateTime.Now.Millisecond.ToString() + ".xlsx";
//自己创建的文件夹 位置随意 合理即可
fileUrl = Server.MapPath("..\\excel") + "\\" + newFileName;
//fileUrl = Path.Combine(Request.MapPath("~/excel"), Path.GetFileName(fileup.FileName));
fileup.SaveAs(fileUrl);
//DataTable dtData = ExcelHelper.Import(fileUrl);
//得到EXCEL的第二种方法(第一个参数是文件流,第二个是excel标签名,第三个是第几行开始读0算第一行)
DataTable dt = ExcelHelper.RenderDataTableFromExcel(fileUrl, "Sheet1", 0);
//Response.Write("<script>alert('已经上传到服务器文件夹')</script>");
//return fileUrl;
//3.删除服务器上的excel文件 获取路径并且删除
//string FilePath = Server.MapPath(fileUrl); // 必须转化以下文件路径，不能直接delete("image/4jpg");
File.Delete(fileUrl);
#endregion
#region dt导入数据库
//3：从System.Data.DataTable导入数据到数据库
//@param System.Data.DataTable dt
IAssetsBll assetBll = new AssetsBll();
IUserBll userBll = new UserBll();
int i = 0;
int num = 1;
string numList = string.Empty;
bool result = false;
var addTime = DateTime.Now;
var updateTime = DateTime.Now;
if (dt != null && dt.Rows.Count > 0 && dt.Rows[0] != null)
{
//查找现在数据表数据
var assetsList = assetBll.SearchList(string.Format(@"select * from assets withnolck"));
string msg = "";
foreach (DataRow dr in dt.Rows)
{
try
{
num += 1;
if (dr != null)
{
Assets assetmodel = new Assets();
if (dr[0] != null)
{
assetmodel.Number = dr[0].ToString().Trim();
if (dr[1] != null)
{
assetmodel.Type = dr[1].ToString().Trim();
}
if (dr[2] != null)
{
assetmodel.Brand = dr[2].ToString().Trim();
}
if (dr[3] != null)
{
assetmodel.IsMac = dr[3].ToString().Trim();
}
if (dr[4] != null)
{
assetmodel.VideoCard = dr[4].ToString().Trim();
}
if (dr[5] != null)
{
assetmodel.RAM = dr[5].ToString().Trim();
}
if (dr[6] != null)
{
assetmodel.RigidDisk = dr[6].ToString().Trim();
}
if (dr[7] != null)
{
assetmodel.CPU = dr[7].ToString().Trim();
}
if (!(dr[8] is DBNull))
{
DateTime purchaseTime = DateTime.Now;
if (DateTime.TryParse(dr[8].ToString(), out purchaseTime))
{
assetmodel.PurchaseTime = Convert.ToDateTime(dr[8].ToString());
}
}
if (!(dr[9] is DBNull))
{
assetmodel.Monetary = Convert.ToDecimal(dr[9]);
}
if (dr[10] != null)
{
assetmodel.Size = dr[10].ToString().Trim();
}
if (dr[11] != null)
{
assetmodel.Department = dr[11].ToString().Trim();
}
if (dr[12] != null)
{
assetmodel.BelongName = dr[12].ToString().Trim();
string sql = string.Format(@"select top 1 id from [user] where account = '{0}'", assetmodel.BelongName);
var dtUser = userBll.SearchAll(sql);
if (dtUser != null && dtUser.Rows.Count > 0 && dtUser.Rows[0] != null)
{
assetmodel.BelongUser = Convert.ToInt32(dtUser.Rows[0]["id"]);
}
}
if (dr[13] != null)
{
assetmodel.Position = dr[13].ToString().Trim();
}
if (dr[14] != null)
{
assetmodel.ProDirection = dr[14].ToString().Trim();
}
if (!(dr[15] is DBNull))
{
assetmodel.SellingPrice = Convert.ToDecimal(dr[15]);
}
if (dr[16] != null)
{
assetmodel.Remark = dr[16].ToString().Trim();
}
assetmodel.AddTime = addTime;
assetmodel.UpdateTime = updateTime;
var assetsId = assetsList.Where(t => t.Number == assetmodel.Number).Select(m => m.Id).FirstOrDefault();
if (assetsId > 0)//存在就修改
{
assetmodel.AddTime = assetsList.Where(t => t.Number == assetmodel.Number).Select(m => m.AddTime).FirstOrDefault();
assetmodel.UpdateTime = updateTime;
assetmodel.Id = assetsId;
result = assetBll.Update(assetmodel, out msg);
}
else//不存在就添加
{
result = assetBll.Add(assetmodel, out msg);
}
}
}
if (result)
{
i++;
}
else
{
numList = numList + num + ',';
continue;
//Response.Write("<script>alert(' 导入失败，数据格式出错！');window.location.href='AssetsList.aspx'</script>");
}
}
catch (Exception ex)
{
numList = numList + num + ',';
//continue;
throw ex;
//Response.Write("<script>alert(' 未完全导入：共导入" + i + "组数据！ 未完全导入数据为第" + numList.Trim(',') + "行！');window.location.href='AssetsList.aspx'</script>");
}
}
}
else
{
Response.Write("<script>alert('EXCEL文件为空文件!');window.location.href='AssetsListNew.aspx'</script>");
}
if (numList == string.Empty)
{
Response.Write("<script>alert(' 导入成功：共导入" + i + "组数据！');window.location.href='AssetsListNew.aspx'</script>");
//res = true;
}
else
{
Response.Write("<script>alert(' 未完全导入：共导入" + i + "组数据！ 未完全导入数据为第" + numList.Trim(',') + "行！');window.location.href='AssetsListNew.aspx'</script>");
//res = true;
}
}
}
#endregion
#endregion
}
}
#endregion
protected Assets loadAssets(string id)
{
var assets = new Assets();
try
{
IAssetsBll assetsBll = new AssetsBll();
string sql = string.Format(@"select * from Assets withnolock where ISNULL(IsDelete,0)<>1 and id='{0}'", id);
var assetsList = assetsBll.SearchList(sql);
if (assetsList.Any())
{
assets = assetsList.FirstOrDefault();
}
return assets;
}
catch (Exception ex)
{
throw ex;
}
}
protected void MakeORCode(string id)
{
try
{
var asset = loadAssets(id);
if (asset.Id > 0)
{
var url = Server.MapPath("..\\qrcode") + "\\" + "bg.jpg";
//var urlHtml = Server.MapPath("UserLogin.aspx");
string urlHtml = string.Format(@"AssetDetailsShow.aspx?id='{0}'", asset.Id);
var img = CreateORCode.GenerateQrCodeWithLogo(urlHtml, 400, 400, url, "资产编号：" + asset.Number);
System.IO.MemoryStream MStream = new System.IO.MemoryStream();
img.Save(MStream, System.Drawing.Imaging.ImageFormat.Png);
Response.ClearContent();
//Response.ContentType = "image/Png";
//Response.BinaryWrite(MStream.ToArray());
//Response.End();
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", asset.Number + ".png"));
Response.BinaryWrite(MStream.ToArray());
//string dateTime = DateTime.Now.Date.ToString("yyyyMMdd");
//string time = DateTime.Now.ToShortTimeString().Replace(":", "");
//string newFileName = dateTime + time + DateTime.Now.Millisecond.ToString() + ".png";
//string fileUrl = Server.MapPath("..\\qrcode") + "\\" + newFileName;
string fileUrl = Server.MapPath("..\\qrcode") + "\\" + asset.Number + ".png";
if (System.IO.File.Exists(fileUrl))
{
//存在文件
FileInfo file = new FileInfo(fileUrl);
file.Delete();
}
//不存在文件
FileStream fs = new FileStream(fileUrl, FileMode.CreateNew, FileAccess.ReadWrite);
BinaryWriter bw = new BinaryWriter(fs, UTF8Encoding.UTF8);
byte[] by = MStream.ToArray();
for (int i = 0; i < MStream.ToArray().Length; i++)
{
bw.Write(by[i]);
}
fs.Close();
MStream.Close();
MStream.Dispose();
//Response.End();
//Response.Write("<script>alert('成功生成二维码!');</script>");
}
else
{
Response.Write("<script>window.location.href='AssetsListNew.aspx';alert('生成二维码失败，不存在该条记录!');</script>");
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
4.真分页操作Dal BaseDalNew.cs
/// <summary>
/// 分页查询
/// </summary>
/// <param name="tableName"></param>
/// <param name="getFields"></param>
/// <param name="orderName"></param>
/// <param name="desc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="strWhere"></param>
/// <param name="count"></param>
/// <returns></returns>
public virtual DataTable DataPage(string tableName, string getFields, string orderName, string desc, int pageSize, int pageIndex, string strWhere, out int count)
{
int pageStart, pageEnd = 0;
pageEnd = pageSize * pageIndex;
pageStart = pageEnd - pageSize + 1;
string sql = "select " + getFields + " from ( select ROW_NUMBER() over(order by " + orderName + " " + desc + ") as row,* from " + tableName + " where " + strWhere + ") r where r.row between " + pageStart + " and " + pageEnd;
string sql1 = "select " + getFields + " from ( select ROW_NUMBER() over(order by id) as row,* from " + tableName + " where " + strWhere + ") r order by r." + orderName + " " + desc;
DataTable dt = SqlHelper.Search(sql);
DataTable dt2 = SqlHelper.Search(sql1);
count = dt2.Rows.Count;
return dt;
}
5.ADO 操作数据库类 sqlhelper.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DQPA.DAL
{
public class SqlHelper
{
static string constr = System.Configuration.ConfigurationSettings.AppSettings["sqlcon"];
/// <summary>
/// 数据库连接字符串
/// Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=用户名;Password=密码
/// </summary>
//static string constr = "Data Source=.;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=";
//执行增删改操作 返回受影响行数
public static int ExcuteSql(string sql)
{
int result = 0;
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
result = cmd.ExecuteNonQuery();
}
return result;
}
//执行查询操作 返回datatable 查询所有
public static DataTable Search(string field, string tableName)
{
DataTable dt = null;
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select" + field + "from " + tableName;
SqlDataAdapter adp = new SqlDataAdapter(sql, con);
con.Open();
DataSet ds = new DataSet();
if (ds != null)
{
adp.Fill(ds);
dt = ds.Tables[0];
}
}
return dt;
}
//执行查询操作 返回datatable 条件查询
public static DataTable Search(string field, string tableName, string where)
{
DataTable dt = null;
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select " + field + " from " + tableName + " where 1=1" + where + "";
SqlDataAdapter adp = new SqlDataAdapter(sql, con);
con.Open();
DataSet ds = new DataSet();
if (ds != null)
{
adp.Fill(ds);
dt = ds.Tables[0];
}
}
return dt;
}
//执行查询操作 返回datatable sql语句查询
public static DataTable Search(string sql)
{
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
SqlDataAdapter adp = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
DataTable dt = null;
if (ds != null)
{
adp.Fill(ds);
dt = ds.Tables[0];
}
return dt;
}
}
//执行存储过程操作 返回datatable 分页查询
public static DataTable ExcuteProc(string procName, SqlParameter[] parameters)
{
//DataTable dt = null;
using (SqlConnection con = new SqlConnection(constr))
{
//声明执行sql语句对象
SqlCommand cmd = new SqlCommand(procName, con);
//制定SqlCommand执行的是存储过程
cmd.CommandType = CommandType.StoredProcedure;
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
con.Open();
DataSet ds = new DataSet();
DataTable dt = null;
if (ds != null)
{
adp.Fill(ds);
dt = ds.Tables[0];
}
return dt;
}
}
//执行事务操作
public static int ExcuteTran(List<string> sqls)
{
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();//开始一个事物
int result = 0;
try
{
foreach (string sql in sqls)
{
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Transaction = tran;
result = cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
}
return result;
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="tableName"></param>
/// <param name="getFields"></param>
/// <param name="orderName"></param>
/// <param name="desc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="strWhere"></param>
/// <param name="count"></param>
/// <returns></returns>
public static DataTable DataPage(string tableName, string getFields, string orderName, string desc, int pageSize, int pageIndex, string strWhere, out int count)
{
int pageStart, pageEnd = 0;
pageEnd = pageSize * pageIndex;
pageStart = pageEnd - pageSize + 1;
string sql = "select " + getFields + " from ( select ROW_NUMBER() over(order by " + orderName + " " + desc + ") as row,* from " + tableName + " where " + strWhere + ") r where r.row between " + pageStart + " and " + pageEnd;
string sql1 = "select " + getFields + " from ( select ROW_NUMBER() over(order by id) as row,* from " + tableName + " where " + strWhere + ") r order by r." + orderName + " " + desc;
DataTable dt = Search(sql);
DataTable dt2 = Search(sql);
count = dt2.Rows.Count;
return dt;
}
}
}
这篇关于asp.net layui数据表格数据交互实现动态数据表格 asp.net 真分页的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2022-03-01沐雪多租宝商城源码从.NetCore3.1升级到.Net6的步骤
- 2024-11-18微软研究：RAG系统的四个层次提升理解与回答能力
- 2024-11-15C#中怎么从PEM格式的证书中提取公钥？-icode9专业技术文章分享
- 2024-11-14云架构设计——如何用diagrams.net绘制专业的AWS架构图？
- 2024-05-08首个适配Visual Studio平台的国产智能编程助手CodeGeeX正式上线！C#程序员必备效率神器！
- 2024-03-30C#设计模式之十六迭代器模式（Iterator Pattern）【行为型】
- 2024-03-29c# datetime tryparse
- 2024-02-21list find index c#
- 2024-01-24convert toint32 c#
- 2024-01-24Advanced .Net Debugging 1：你必须知道的调试工具
