C# Api+MVC Dapper Redis 增删改查分页
Api
DAl
public List<Dai> Show(string name="")
{
using (SqlConnection conn=new SqlConnection("Data Source=.;Initial Catalog=Dai6_21;Integrated Security=True"))
{
if (string.IsNullOrEmpty(name))
{
return conn.Query<Dai>($"select * from Dai").ToList();
}
else
{
return conn.Query<Dai>($"select * from Dai where Name like '%{name}%'").ToList();
}
}
}
public int Add(Dai m)
{
using (SqlConnection conn=new SqlConnection("Data Source=.;Initial Catalog=Dai6_21;Integrated Security=True"))
{
var sql = string.Format($"insert into Dai values('{m.Name}','{m.Zhon}','{m.identit}','{m.DDate}','{m.DMoney}')");
return conn.Execute(sql);
}
}
public int Del(int id)
{
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Dai6_21;Integrated Security=True"))
{
var sql = $"delete from Dai where Id={id}";
return conn.Execute(sql);
}
}
public int Upd(Dai d)
{
using (SqlConnection conn=new SqlConnection("Data Source=.;Initial Catalog=Dai6_21;Integrated Security=True"))
{
string sql = $"update Dai set Name='{d.Name}',Zhon='{d.Zhon}',identit='{d.identit}',DDate='{d.DDate}',DMoney='{d.DMoney}' where Id='{d.Id}'";
return conn.Execute(sql);
}
}
public Dai Getfan(int DId)
{
var list =Show();
var mod = list.Where(m => m.Id == DId).FirstOrDefault();
return mod;
}
public int Login(string Name,string Pwd)
{
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Dai6_21;Integrated Security=True"))
{
string sql = $"select * from deposit where UName='{Name}' and Pwd='{Pwd}'";
return Convert.ToInt32(conn.ExecuteScalar(sql));
}
}
控制器
[HttpGet]
public async Task<PageModel> Show(int pageIndex=1,int pageSize=2,string name="")
{
List<Dai> list = await Task.Run(() => { return dal.Show(name); });
PageModel page = new PageModel();
page.TotalCount = list.Count / 2 + (list.Count % 2 > 0 ? 1 : 0);
page.List = list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return page;
}
[HttpPost]
public int Add(Dai m)
{
return dal.Add(m);
}
[HttpDelete]
public int Del(int id)
{
return dal.Del(id);
}
[HttpPost]
public int Upd(Dai d)
{
return dal.Upd(d);
}
[HttpGet]
public Dai Getfan(int DId)
{
return dal.Getfan(DId);
}
[HttpGet]
public int Login(string Name, string Pwd)
{
return dal.Login(Name, Pwd);
}
Model
public List<Dai> List { get; set; }
public int TotalCount { get; set; }
MVC
控制器
public ActionResult Show()
{
return View();
}
public ActionResult Add()
{
return View();
}
public ActionResult Del()
{
return View();
}
public ActionResult Upd(int id)
{
ViewBag.Sid = id;
return View();
}
public ActionResult Getfan()
{
return View();
}
public ActionResult Login()
{
return View();
}
Add.cshtml
@{
ViewBag.Title = "Add";
}
<h2>Add</h2>
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>
<script src="~/Content/My97DatePicker/WdatePicker.js"></script>
<table>
<tr>
<td>贷款人</td>
<td><input id="Text1" type="text" /></td>
</tr>
<tr>
<td>账号</td>
<td><input id="Text2" type="text" /></td>
</tr>
<tr>
<td>身份证号</td>
<td><input id="Text3" type="text" /></td>
</tr>
<tr>
<td>日期</td>
<td><input id="Text4" type="text" onclick="WdatePicker()" /></td>
</tr>
<tr>
<td>贷款金额</td>
<td><input id="Text5" type="text" /></td>
</tr>
<tr>
<td></td>
<td><input id="Button1" type="button" value="添加" onclick="Add()"/></td>
</tr>
</table>
<script>
function Add() {
var obj = {
Name: $("#Text1").val(),
Zhon: $("#Text2").val(),
identit: $("#Text3").val(),
DDate: $("#Text4").val(),
DMoney: $("#Text5").val()
}
$.ajax({
url: "http://localhost:54276/api/Default/Add",
type: "post",
dataType: "json",
data: obj,
success: function (d) {
if (d>0) {
alert("添加成功");
location.href = '/Default/Show';
}
else {
alert("添加失败");
}
}
})
}
</script>
Show.cshtml
@{
ViewBag.Title = "Show";
}
<h2>Show</h2>
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>
<div>
<input id="txt_Name" type="text" /><input id="Button1" type="button" value="查询" onclick="Show('first');" />
</div>
<table id="table" class="table table-bordered">
<tr>
<td>编号</td>
<td>贷款人</td>
<td>账号</td>
<td>身份证号</td>
<td>日期</td>
<td>贷款金额</td>
<td>操作</td>
</tr>
</table>
<a href="#" onclick="Show('first')">首页</a>
<a href="#" onclick="Show('prev')">上一页</a>
<a href="#" onclick="Show('next')">下一页</a>
<a href="#" onclick="Show('last')">尾页</a>
<input type="hidden" id="pageIndex" />
<input type="hidden" id="totalPage" />
<script>
Show('first');
function Show(page) {
switch (page) {
case 'first':
$('#pageIndex').val(1);
break;
case 'prev':
var num = Number($("#pageIndex").val());
if (num > 1) {
$("#pageIndex").val(num - 1);
}
else {
$("#pageIndex").val(1);
}
break;
case 'next':
var num = Number($("#pageIndex").val());
if (num < Number($("#totalPage").val())) {
$("#pageIndex").val(num + 1);
}
else {
$("#pageIndex").val($("#totalPage").val());
}
break;
case 'last':
$("#pageIndex").val($("#totalPage").val());
break;
default: break;
}
$.ajax({
url: "http://localhost:54276/api/Default/Show?pageIndex=" + $("#pageIndex").val() + "&Name=" + $("#txt_Name").val(),
type: "get",
dataType: "json",
success: function (d) {
$("#totalPage").val(d.TotalCount);
$("table tr:gt(0)").remove();
$(d.List).each(function () {
var str = '<tr>'
+ '<td>' + this.Id + '</td>'
+ '<td>' + this.Name + '</td>'
+ '<td>' + this.Zhon + '</td>'
+ '<td>' + this.identit + '</td>'
+ '<td>' + this.DDate + '</td>'
+ '<td>' + this.DMoney + '</td>'
+ '<td><input id="Button1" type="button" value="删除" onclick="Del(' + this.Id + ')"/><input id="Button1" type="button" value="修改" onclick="Upd(' + this.Id + ')"/></td>'
+ '</tr>';
$("#table tbody").append(str);
})
}
})
}
function Del(Sid) {
if (confirm("确认删除吗？")) {
$.ajax({
url: "http://localhost:54276/api/Default/Del?id=" + Sid,
type: "delete",
dataType: "json",
success: function (d) {
if (d > 0) {
alert("删除成功");
location.href = '/Default/Show';
}
else {
alert("删除失败");
}
}
})
}
}
function Upd(sid) {
location.href = '/Default/Upd?Id=' + sid;
}
</script>
Upt.html
@{
ViewBag.Title = "Upd";
}
<h2>Upd</h2>
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>
<script src="~/Content/My97DatePicker/WdatePicker.js"></script>
<input type="hidden" id="txt_Id" value="@ViewBag.Sid"/>
<table>
<tr>
<td>贷款人</td>
<td><input id="Text1" type="text" /></td>
</tr>
<tr>
<td>账号</td>
<td><input id="Text2" type="text" /></td>
</tr>
<tr>
<td>身份证号</td>
<td><input id="Text3" type="text" /></td>
</tr>
<tr>
<td>日期</td>
<td><input id="Text4" type="text" onclick="WdatePicker()"/></td>
</tr>
<tr>
<td>贷款金额</td>
<td><input id="Text5" type="text" /></td>
</tr>
<tr>
<td></td>
<td><input id="Button1" type="button" value="修改" onclick="Upd()" /></td>
</tr>
</table>
<script>
$(function () {
$.ajax({
url: "http://localhost:54276/api/Default/Getfan",
type: "get",
async: false,
data: { DId: $("#txt_Id").val() },
dataType: "json",
success: function (d) {
$("#txt_Id").val(d.Id);
$("#Text1").val(d.Name);
$("#Text2").val(d.Zhon);
$("#Text3").val(d.identit);
$("#Text4").val(d.DDate);
$("#Text5").val(d.DMoney);
}
})
})
function Upd() {
var obj = {
Id: $("#txt_Id").val(),
Name: $("#Text1").val(),
Zhon: $("#Text2").val(),
identit: $("#Text3").val(),
DDate: $("#Text4").val(),
DMoney: $("#Text5").val()
};
$.ajax({
url: "http://localhost:54276/api/Default/Upd",
type: "post",
dataType: "json",
data: obj,
contentType: "application/x-www-form-urlencoded",
success: function (d) {
if (d>0) {
alert("修改成功");
location.href = '/Default/Show';
}
else {
alert("修改失败");
}
}
})
}
</script>
Redis
heper
public class RedisHelper
{
//私有构造函数
private RedisHelper() { }
//定义静态变量
private static RedisClient _redisClient;
//私有的静态只读变量
private static object _lock = new object();
//公共静态方法
public static RedisClient GetRedisClient()
{
if (_redisClient == null)
{
//双层判断锁的单例模式
lock (_lock)
{
if (_redisClient == null)
{
_redisClient = new RedisClient("127.0.0.1",6379);
return _redisClient;
}
}
}
return _redisClient;
}
clien
public class RedisClinen
{
DDAL dal = new DDAL();
RedisClient redis = RedisHelper.GetRedisClient();
public void Show()
{
var list = dal.Show();
redis.Set("Rshow",list);
}
/// <summary>
/// 显示
/// </summary>
/// <param name="qkey"></param>
/// <returns></returns>
public List<Dai> RedisShow(string qkey)
{
if (!redis.ContainsKey("Rshow"))
{
Show();
}
var list = redis.Get<List<Dai>>("Rshow");
return list;
}
///
public int Add(string qkey,Dai d)
{
var n = dal.Add(d);
redis.Set("RAdd",n);
if (n>0)
{
Show();
}
return redis.Get<int>("RAdd");
}
控制器
[HttpGet]
[Route("Rshow")]
public List<Dai> Rshow(string qkey)
{
var list = redis.RedisShow("Rshow");
return list;
}
[HttpPost]
public int Radd(string qkey,Dai d)
{
var n = redis.Add("RAdd", d);
return n;
}
