.net Dapper 实践系列(5) ---事务编辑(layui+mvc5+Dapper+MySQL)
写在前面
上一小节，我们总结了根据Id查询多表数据，最后返回Json对象给前台的例子。接下来，在这一小节我们要实现多表编辑的操作。
实践步骤
因为上一小节以及创建了Edit视图以及数据的显示。所以，在这里不在重复。主要介绍实现多表编辑的操作。
1.在BorrowBookController控制器中，创建 Edits_Op 方法 实现数据的编辑
/// <summary>
/// 多表编辑
/// </summary>
/// <param name="Jsons">前台接收参数</param>
/// <returns>成功返回1，失败返回0</returns>
public int Edits_Op(sys_Book_Type Jsons)
{
int result = 0;
var para = new List<Tuple<string, object>>();
Tuple<string, object> temp1;
var id = Jsons.book_Type_id;
//表1数据
sys_Book_Type bty = new sys_Book_Type { };
bty.book_Type_id = id;
bty.book_Type_Name = Jsons.book_Type_Name;
bty.book_Type_Des = Jsons.book_Type_Des;
bty.create_Time = DateTime.Now;
//表2数据
var Jsonbook = Jsons.books;
List<sys_Borrow_Book> listbooks = new List<sys_Borrow_Book> { };
foreach (var item in Jsonbook)
{
sys_Borrow_Book bk = new sys_Borrow_Book();
bk.book_Id = item.book_Id;
bk.book_Num = item.book_Name;
bk.book_Name = item.book_Name;
bk.book_Desc = item.book_Desc;
bk.borow_Time = DateTime.Now;
bk.book_Type_id = id;
bk.user_Id = "ba33a34eb98411e9870700155d08c915";
listbooks.Add(bk);
}
string sql_bookty = "update sys_Book_Type set book_Type_Name=?book_Type_Name,book_Type_Des=?book_Type_Des, ";
sql_bookty += "create_Time=?create_Time where book_Type_id=?book_Type_id";
temp1 = new Tuple<string, object>(sql_bookty, bty);
para.Add(temp1);
string sql_book = "update sys_Borrow_Book set book_Num=?book_Num,book_Name=?book_Name,";
sql_book += " book_Desc=?book_Desc,borow_Time=?borow_Time,book_Type_id=?book_Type_id,user_Id=?book_Type_id ";
sql_book += " where book_Id=?book_Id ";
temp1 = new Tuple<string, object>(sql_book, listbooks);
para.Add(temp1);
result = DapperDBContext.ExecuteTransaction(para).Item1;
if(result==true){result=1;}
return result;
}
2.在Edit视图中 创建 Edit视图
<div class="layui-container">
<div class="layui-col-md12">
<h2 style="margin-top:10px;">编辑</h2>
<hr class="layui-bg-green">
<div class="layui-form " style="padding:20px;margin:10px 10px 10px 10px;border:1px solid #ddd;" id="" >
<div class="layui-form-item">
<label class="layui-form-label">书类型名称</label>
<div class="layui-input-block">
<input type="text" id="book_Type_Id" style="display:none;">
<input type="text" name="book_Type_Name" id="book_Type_Name" placeholder="请输入标题" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-form-item layui-form-text">
<label class="layui-form-label">书类型描述</label>
<div class="layui-input-block">
<textarea class="layui-textarea" name="book_Type_Des" id="book_Type_Des" placeholder="请输入书籍类型描述"></textarea>
</div>
</div>
</div>
<div class="layui-form layui-form-pane" style="padding:20px;margin:10px 10px 10px 10px;border:1px solid #ddd;" id="" lay-filter="">
<button class="layui-btn layui-btn-sm" id="btnAddRow">新增</button>
<table class="layui-table" id="tab">
<tr>
<th>借书编号</th>
<th>书名</th>
<th>书简介</th>
<th>操作</th>
</tr>
<tr class="list">
<td><input type="text" name="book_Num" class="layui-input" /></td>
<td><input type="text" name="book_Name" class="layui-input" /></td>
<td><input type="text" name="book_Desc" class="layui-input" /></td>
<td><button class="layui-btn layui-btn-danger layui-btn-sm btnDelRow">删除</button></td>
</tr>
</table>
</div>
<div style="width:144px;margin:0 auto">
<div style="width:144px;margin:0 auto;">
<button class="layui-btn layui-btn-normal" id="btnSave">保存</button>
<button class="layui-btn layui-btn-primary" id="btnCancel">取消</button>
</div>
</div>
</div>
</div>
3.在Edit视图中 操作dom
<script type="text/javascript">
$(function () {
$("#btnSave").click(function () {
var bookType = {
book_Type_Id: "" + $("#book_Type_Id").val() + "",
book_Type_Name: "" + $("#book_Type_Name").val() + "",
book_Type_Des: "" + $("#book_Type_Des").val() + "",
books: []
};
var tr = $("#tab tr:gt(0)");
for (var i = 0; i < tr.length; i++) {
var td = $(tr[i]);
var id = td.children().children().attr("id");
var aa = td.children().children().val();
var bb = td.children().next().children().val();
var cc = td.children().next().next().children().val();
bookType.books.push({ book_Id: id, book_Num: "" + aa + "", book_Name: "" + bb + "", book_Desc: "" + cc + "" });
}
$.ajax({
url: "../BorrowBook/Edits_Op",
type: "post",
dataType: "json",
contentType: "application/json",
data: JSON.stringify(bookType)
}).done(function (msg) {
alert(msg);
if (msg == 0) {
parent.layer.msg("保存成功!", { icon: 1 });
var index = parent.layer.getFrameIndex(window.name); //先得到当前iframe层的索引
parent.layer.close(index); //再执行关闭
}
else {
parent.layer.msg("保存失败!", { icon: 2 });
return;
}
}).fail(function (e) { });
});
});
</script>
注意：
此刻我们会发现，执行Edits_Op的方法只能编辑原来有的数据，而无法通过点击增行来编辑数据的。所以，我们对Edits_Op这个方法进行改造一下。如果要实现编辑的同时也可以添加数据。我们就必须先获取到两张表中有联系的Id。在操作时先删除表，然后再往里面查询编辑好的数据。我称之为变相的编辑。
4.重构Edits_Op方法
/// <summary>
/// 多表编辑
/// </summary>
/// <param name="Jsons">前台接收参数</param>
/// <returns>成功返回1，失败返回0</returns>
public int Edits_Op(sys_Book_Type Jsons)
{
int result = 0;
var para = new List<Tuple<string, object>>();
Tuple<string, object> temp1;
var Id = Guid.NewGuid().ToString();
sys_Book_Type bty = new sys_Book_Type { };
bty.book_Type_id = Id;
bty.book_Type_Name = Jsons.book_Type_Name;
bty.book_Type_Des = Jsons.book_Type_Des;
bty.create_Time = DateTime.Now;
//表2数据
var Jsonbook = Jsons.books;
List<sys_Borrow_Book> listbooks = new List<sys_Borrow_Book> { };
foreach (var item in Jsonbook)
{
sys_Borrow_Book bk = new sys_Borrow_Book();
bk.book_Id = Guid.NewGuid().ToString();
bk.book_Num = item.book_Num;
bk.book_Name = item.book_Name;
bk.book_Desc = item.book_Desc;
bk.borow_Time = DateTime.Now;
bk.book_Type_id = Id;
bk.user_Id = "ba33a34eb98411e9870700155d08c915";
listbooks.Add(bk);
}
string sql_del = "delete from sys_Book_Type where book_Type_id=?id";
string sql_dels = "delete from sys_Borrow_Book where book_Type_id=?id";
string sql_bookty = "insert into sys_Book_Type(book_Type_id,book_Type_Name,book_Type_Des,create_Time)";
sql_bookty += " values (?book_Type_id,?book_Type_Name,?book_Type_Des,?create_Time)";
string sql_book = "insert into sys_Borrow_Book(book_Id,book_Num,book_Name,book_Desc,borow_Time,book_Type_id,user_Id)";
sql_book += " values (?book_Id,?book_Num,?book_Name,?book_Desc,?borow_Time,?book_Type_id,?user_Id)";
DynamicParameters p1 = new DynamicParameters();
p1.Add("?Id", Jsons.book_Type_id);
temp1 = new Tuple<string, object>(sql_del, p1);
para.Add(temp1);
temp1 = new Tuple<string, object>(sql_dels, p1);
para.Add(temp1);
temp1 = new Tuple<string, object>(sql_bookty, bty);
para.Add(temp1);
temp1 = new Tuple<string, object>(sql_book, listbooks);
para.Add(temp1);
bool boolresult = DapperDBContext.ExecuteTransaction(para).Item1;
if (boolresult == true) { result = 1; }
return result;
}
最终效果：
