新知识
vs2017 + miniUI + dapper 添加、修改、删除、查询操作
A、数据库表引用先前建立的company 公司信息表。
B、建立文件：
views 》 Home 》 Company.cshtml（新建文件） ，代码如下（直接复制即可）
@{
Layout = Layout = "~/Views/Shared/_Layout.cshtml"; ;
}
<div style="width:100%;">
<div class="mini-toolbar" style="border-bottom:0;padding:0px;">
<table style="width:100%;">
<tr>
<td style="width:100%;">
<a class="mini-button" iconCls="icon-add" onclick="add()">添加</a>
</td>
<td style="white-space:nowrap;">
<input id="key" name="key" class="mini-textbox" emptyText="请输入姓名" style="width:150px;" />
<a class="mini-button" onclick="search()">查询</a>
</td>
</tr>
</table>
</div>
</div>
<div id="datagrid1" class="mini-datagrid" style="width:100%;height:280px;" allowResize="true" pageSize="10"
url="/Home/CompanyList" idField="id" multiSelect="true" pagesize="20" showpager="true">
<div property="columns">
<!--<div type="indexcolumn"></div> -->
<div type="checkcolumn"></div>
<div field="CompanyName" width="120" headerAlign="center" allowSort="true">公司名称</div>
<div field="Address" width="120" headerAlign="center" allowSort="true">地址</div>
<div field="mobile" width="120" headerAlign="center" allowSort="true">电话</div>
<div name="action" width="100" renderer="onGenderRenderer" headerAlign="center">操作</div>
</div>
</div>
<script>
mini.parse();
var grid = mini.get("datagrid1");
grid.load();
grid.on("drawcell", function (e) {
var record = e.record,
column = e.column,
field = e.field,
value = e.value;
//action列，超连接操作按钮
if (column.name == "action") {
e.cellStyle = "text-align:center";
e.cellHtml = '<a class="mini-button" style="width:60px;" href="javascript:edit(\'' + record.id + '\')">修改</a> '
+ '<a class="mini-button" style="width:60px;" href="javascript:del(\'' + record.id + '\')" >删除</a>'
}
});
function search() {
var key = mini.get("key").value;
grid.load({ key: key });
}
$("#key").bind("keydown", function (e) {
if (e.keyCode == 13) {
search();
}
});
function del(rowid) {
if (rowid.length > 0) {
if (confirm("确定删除选中记录？")) {
grid.loading("操作中，请稍后......");
$.ajax({
url: "/Home/Delete?id=" + rowid,
type: "post",
success: function (text) {
grid.reload();
},
error: function () {
}
});
}
} else {
alert("请选中一条记录");
}
}
//添加、修改
function edit(rowid) {
mini.open({
targetWindow: window,
url: "/Home/CompanyEdit?Id=" + rowid,
title: "设置条件", width: 800, height: 600,
onload: function () {
var iframe = this.getIFrameEl();
var data = { action: "new" };
iframe.contentWindow.OpenContext = { parent: window, popwin: this };
},
ondestroy: function (action) {
loading();
grid.reload();
}
});
}
//新建
function add() {
mini.open({
targetWindow: window,
url: "/Home/CompanyEdit",
title: "添加菜单", width: 700, height: 300,
onload: function () {
var iframe = this.getIFrameEl();
var data = { action: "new" };
loading();
iframe.contentWindow.OpenContext = { parent: window, popwin: this };
},
ondestroy: function (action) {
grid.reload();
}
});
}
function loading() {
mini.mask({
el: document.body,
cls: 'mini-mask-loading',
html: '加载中...'
});
setTimeout(function () {
mini.unmask(document.body);
}, 1000);
}
</script>
views 》 Home 》CompanyEdit.cshtml（新建文件） ，代码如下（直接复制即可）
@model MiniUi_Dapper.Controllers.Company
@{
ViewBag.Title = "CompanyEdit";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<form id="form1" method="post">
<div class="mini-fit'">
<input name="Id" id="Id" class="mini-hidden" value="@Model.id" />
<table border="0" cellpadding="2" cellspacing="0" class="EditTable" style="width: 100%">
<tr>
<td class="tdLabel" style="width: 30%;"> 公司名称：</td>
<td class="tdText" style="width: 70%;"><input class="mini-textbox" id="CompanyName" name="CompanyName" style="width: 200px;" value="@Model.CompanyName"></td>
</tr>
<tr>
<td class="tdLabel" style="width: 30%;"> 地址： </td>
<td class="tdText" style="width: 70%;"> <input class="mini-textbox" id="Address" name="Address" style="width: 200px;" value="@Model.Address"> </td>
</tr>
<tr>
<td class="tdLabel" style="width: 30%;"> 电话： </td>
<td class="tdText" style="width: 70%;"> <input class="mini-textbox" id="mobile" name="mobile" style="width: 200px;" value="@Model.mobile"> </td>
</tr>
</table>
<div style="text-align:center;padding:10px;">
<a class="mini-button" onclick="onOk" style="width:60px;margin-right:20px;">确定</a>
<a class="mini-button" onclick="onCancel" style="width:60px;">取消</a>
</div>
</div>
</form>
<script type="text/javascript">
mini.parse();
var form = new mini.Form("form1");
function SaveData() {
var form = new mini.Form("#form1");
var data = form.getData(); //获取表单多个控件的数据
//var json = mini.encode(data); //序列化成JSON
$.ajax({
url: "/Home/CompanySave",
type: "post",
data: data,
success: function (text) {
//alert("提交成功，返回结果:" + text);
//grid.reload();
CloseWindow("提交成功");
}
});
}
function GetData() {
var o = form.getData();
return o;
}
function CloseWindow(action) {
if (action == "close" && form.isChanged()) {
if (confirm("数据被修改了，是否先保存？")) {
return false;
}
}
if (window.CloseOwnerWindow) return window.CloseOwnerWindow(action);
else window.close();
}
function onOk(e) {
SaveData();
}
function onCancel(e) {
CloseWindow("cancel");
}
</script>
Controllers》HomeController.cs（控制器新建方法） ，代码如下（直接复制即可）
#region 公司管理
/// <summary>
///
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="key">查询关键字，当前匹配公司名称</param>
/// <returns></returns>
[HttpPost]
public ActionResult CompanyList(int? pageIndex, int? pageSize,string key)
{
using (var conn = new MySqlConnection(ConnectionString))
{
pageIndex = pageIndex ?? 0;
pageSize = pageSize ?? 10;
string query = "";
if (key != null && key.Trim() != "")
query = "select * from Company" + " where CompanyName like '%"+ key +"%'";
else
query = "select * from Company";
var result = conn.Query<Company>(query).ToList();
var total = result.Count();
var list = result
.OrderByDescending(d => d.id).Skip((pageIndex * pageSize).Value)
.Take((pageSize).Value).Select(d => new
{
d.id,
d.CompanyName,
d.Address,
d.mobile,
d.link,
d.intodate
}).ToList();
return Json(new { total = total, data = list }, JsonRequestBehavior.AllowGet);
}
}
[HttpGet]
[Display(Description = "加载公司信息")]
public ActionResult CompanyEdit(int? id)
{
//点击编辑页面，如果id=0 则是增加操作，如果id>0则是修改操作。
using (var conn = new MySqlConnection(ConnectionString))
{
Company n;
if ((id ?? 0) == 0)
n = new Company();
else
{
n = conn.Query<Company>("select * from company where id=" + id).FirstOrDefault();
if (n == null)
return View("无公司信息");
}
return View(n);
}
}
[HttpPost]
[Display(Description = "保存公司信息")]
public ActionResult CompanySave(Company company)
{
try
{
using (var conn = new MySqlConnection(ConnectionString))
{
if (company.id == 0)
{
var query = " INSERT INTO `company`(`CompanyName`, `Address`, `mobile`, `link`) VALUES (@CompanyName, @Address, @mobile, @link) ;";
var result = conn.Query<Company>(query,company).FirstOrDefault();
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
else
{
var query = "UPDATE `company` SET `CompanyName` = @CompanyName, `Address` = @Address, `mobile` = @mobile, `link` = @link WHERE `id` = "+ company.id + ";";
var result = conn.Query<Company>(query, company).FirstOrDefault();
if (result == null)
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
else
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
}
}
catch (Exception ex)
{
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 根据表主键Id进行删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Delete(int id)
{
using (var conn = new MySqlConnection(ConnectionString))
{
conn.Execute("delete from company where id=" + id);
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
}
#endregion
A、数据库表引用先前建立的company 公司信息表。
B、建立文件：
views 》 Home 》 Company.cshtml（新建文件） ，代码如下（直接复制即可）
@{
Layout = Layout = "~/Views/Shared/_Layout.cshtml"; ;
}
<div style="width:100%;">
<div class="mini-toolbar" style="border-bottom:0;padding:0px;">
<table style="width:100%;">
<tr>
<td style="width:100%;">
<a class="mini-button" iconCls="icon-add" onclick="add()">添加</a>
</td>
<td style="white-space:nowrap;">
<input id="key" name="key" class="mini-textbox" emptyText="请输入姓名" style="width:150px;" />
<a class="mini-button" onclick="search()">查询</a>
</td>
</tr>
</table>
</div>
</div>
<div id="datagrid1" class="mini-datagrid" style="width:100%;height:280px;" allowResize="true" pageSize="10"
url="/Home/CompanyList" idField="id" multiSelect="true" pagesize="20" showpager="true">
<div property="columns">
<!--<div type="indexcolumn"></div> -->
<div type="checkcolumn"></div>
<div field="CompanyName" width="120" headerAlign="center" allowSort="true">公司名称</div>
<div field="Address" width="120" headerAlign="center" allowSort="true">地址</div>
<div field="mobile" width="120" headerAlign="center" allowSort="true">电话</div>
<div name="action" width="100" renderer="onGenderRenderer" headerAlign="center">操作</div>
</div>
</div>
<script>
mini.parse();
var grid = mini.get("datagrid1");
grid.load();
grid.on("drawcell", function (e) {
var record = e.record,
column = e.column,
field = e.field,
value = e.value;
//action列，超连接操作按钮
if (column.name == "action") {
e.cellStyle = "text-align:center";
e.cellHtml = '<a class="mini-button" style="width:60px;" href="javascript:edit(\'' + record.id + '\')">修改</a> '
+ '<a class="mini-button" style="width:60px;" href="javascript:del(\'' + record.id + '\')" >删除</a>'
}
});
function search() {
var key = mini.get("key").value;
grid.load({ key: key });
}
$("#key").bind("keydown", function (e) {
if (e.keyCode == 13) {
search();
}
});
function del(rowid) {
if (rowid.length > 0) {
if (confirm("确定删除选中记录？")) {
grid.loading("操作中，请稍后......");
$.ajax({
url: "/Home/Delete?id=" + rowid,
type: "post",
success: function (text) {
grid.reload();
},
error: function () {
}
});
}
} else {
alert("请选中一条记录");
}
}
//添加、修改
function edit(rowid) {
mini.open({
targetWindow: window,
url: "/Home/CompanyEdit?Id=" + rowid,
title: "设置条件", width: 800, height: 600,
onload: function () {
var iframe = this.getIFrameEl();
var data = { action: "new" };
iframe.contentWindow.OpenContext = { parent: window, popwin: this };
},
ondestroy: function (action) {
loading();
grid.reload();
}
});
}
//新建
function add() {
mini.open({
targetWindow: window,
url: "/Home/CompanyEdit",
title: "添加菜单", width: 700, height: 300,
onload: function () {
var iframe = this.getIFrameEl();
var data = { action: "new" };
loading();
iframe.contentWindow.OpenContext = { parent: window, popwin: this };
},
ondestroy: function (action) {
grid.reload();
}
});
}
function loading() {
mini.mask({
el: document.body,
cls: 'mini-mask-loading',
html: '加载中...'
});
setTimeout(function () {
mini.unmask(document.body);
}, 1000);
}
</script>
views 》 Home 》CompanyEdit.cshtml（新建文件） ，代码如下（直接复制即可）
@model MiniUi_Dapper.Controllers.Company
@{
ViewBag.Title = "CompanyEdit";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<form id="form1" method="post">
<div class="mini-fit'">
<input name="Id" id="Id" class="mini-hidden" value="@Model.id" />
<table border="0" cellpadding="2" cellspacing="0" class="EditTable" style="width: 100%">
<tr>
<td class="tdLabel" style="width: 30%;"> 公司名称：</td>
<td class="tdText" style="width: 70%;"><input class="mini-textbox" id="CompanyName" name="CompanyName" style="width: 200px;" value="@Model.CompanyName"></td>
</tr>
<tr>
<td class="tdLabel" style="width: 30%;"> 地址： </td>
<td class="tdText" style="width: 70%;"> <input class="mini-textbox" id="Address" name="Address" style="width: 200px;" value="@Model.Address"> </td>
</tr>
<tr>
<td class="tdLabel" style="width: 30%;"> 电话： </td>
<td class="tdText" style="width: 70%;"> <input class="mini-textbox" id="mobile" name="mobile" style="width: 200px;" value="@Model.mobile"> </td>
</tr>
</table>
<div style="text-align:center;padding:10px;">
<a class="mini-button" onclick="onOk" style="width:60px;margin-right:20px;">确定</a>
<a class="mini-button" onclick="onCancel" style="width:60px;">取消</a>
</div>
</div>
</form>
<script type="text/javascript">
mini.parse();
var form = new mini.Form("form1");
function SaveData() {
var form = new mini.Form("#form1");
var data = form.getData(); //获取表单多个控件的数据
//var json = mini.encode(data); //序列化成JSON
$.ajax({
url: "/Home/CompanySave",
type: "post",
data: data,
success: function (text) {
//alert("提交成功，返回结果:" + text);
//grid.reload();
CloseWindow("提交成功");
}
});
}
function GetData() {
var o = form.getData();
return o;
}
function CloseWindow(action) {
if (action == "close" && form.isChanged()) {
if (confirm("数据被修改了，是否先保存？")) {
return false;
}
}
if (window.CloseOwnerWindow) return window.CloseOwnerWindow(action);
else window.close();
}
function onOk(e) {
SaveData();
}
function onCancel(e) {
CloseWindow("cancel");
}
</script>
Controllers》HomeController.cs（控制器新建方法） ，代码如下（直接复制即可）
#region 公司管理
/// <summary>
///
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="key">查询关键字，当前匹配公司名称</param>
/// <returns></returns>
[HttpPost]
public ActionResult CompanyList(int? pageIndex, int? pageSize,string key)
{
using (var conn = new MySqlConnection(ConnectionString))
{
pageIndex = pageIndex ?? 0;
pageSize = pageSize ?? 10;
string query = "";
if (key != null && key.Trim() != "")
query = "select * from Company" + " where CompanyName like '%"+ key +"%'";
else
query = "select * from Company";
var result = conn.Query<Company>(query).ToList();
var total = result.Count();
var list = result
.OrderByDescending(d => d.id).Skip((pageIndex * pageSize).Value)
.Take((pageSize).Value).Select(d => new
{
d.id,
d.CompanyName,
d.Address,
d.mobile,
d.link,
d.intodate
}).ToList();
return Json(new { total = total, data = list }, JsonRequestBehavior.AllowGet);
}
}
[HttpGet]
[Display(Description = "加载公司信息")]
public ActionResult CompanyEdit(int? id)
{
//点击编辑页面，如果id=0 则是增加操作，如果id>0则是修改操作。
using (var conn = new MySqlConnection(ConnectionString))
{
Company n;
if ((id ?? 0) == 0)
n = new Company();
else
{
n = conn.Query<Company>("select * from company where id=" + id).FirstOrDefault();
if (n == null)
return View("无公司信息");
}
return View(n);
}
}
[HttpPost]
[Display(Description = "保存公司信息")]
public ActionResult CompanySave(Company company)
{
try
{
using (var conn = new MySqlConnection(ConnectionString))
{
if (company.id == 0)
{
var query = " INSERT INTO `company`(`CompanyName`, `Address`, `mobile`, `link`) VALUES (@CompanyName, @Address, @mobile, @link) ;";
var result = conn.Query<Company>(query,company).FirstOrDefault();
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
else
{
var query = "UPDATE `company` SET `CompanyName` = @CompanyName, `Address` = @Address, `mobile` = @mobile, `link` = @link WHERE `id` = "+ company.id + ";";
var result = conn.Query<Company>(query, company).FirstOrDefault();
if (result == null)
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
else
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
}
}
catch (Exception ex)
{
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 根据表主键Id进行删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Delete(int id)
{
using (var conn = new MySqlConnection(ConnectionString))
{
conn.Execute("delete from company where id=" + id);
return Json(new { @return = 1 }, JsonRequestBehavior.AllowGet);
}
}
#endregion
修改左菜单导航的json文件
到此完成数据库的增删改查操作。运行一下看看结果。
