MVC日记的增删改方法实现
这里我就不过多介绍数据库了,根据实体模型层自行设计数据库就好
首先创建实体模型层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MVC.Notes.DataModel
{
public class NotesDataModel
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int CId { get; set; }
public DateTime Times { get; set; }
public bool Gk { get; set; }
public string CFl { get; set; }
}
}
创建ado调用连接数据库的帮助类,这里你需要自行改动你自己创建的数据库名字,及登录账号及密码,话不多说,直接上代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Net;
using System.Net.Sockets;
namespace MVC.Notes.DAL
{
public class DBHelper
{
private SqlConnection conn = null;
/// <summary>
/// 构造函数
/// </summary>
public DBHelper()
{
if (conn == null)
{
conn = new SqlConnection("Data Source=.;Initial Catalog=NotesDB;Integrated Security=True");
}
}
/// <summary>
/// 返回DataTable查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public DataTable GetTable(string sql, SqlParameter[] par = null)
{
try
{
SqlCommand com = new SqlCommand(sql, conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
SqlDataAdapter ada = new SqlDataAdapter(com);
DataTable dt = new DataTable();
ada.Fill(dt);
ada.Dispose();
if (conn.State == ConnectionState.Open)
{
this.Close();
}
return dt;
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
this.Close();
}
throw;
}
}
/// <summary>
/// 返回List查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public List<T> GetToList<T>(string sql, SqlParameter[] par = null)
{
List<T> li = DataTableToList<T>(GetTable(sql));
return li;
}
/// <summary>
/// 返回查询结果首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, SqlParameter[] par = null)
{
try
{
this.Open();
SqlCommand com = new SqlCommand(sql, conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
return com.ExecuteScalar();
}
catch (Exception ex)
{
this.Close();
throw;
}
}
/// <summary>
/// 返回执行结果受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, SqlParameter[] par = null)
{
try
{
this.Open();
SqlCommand com = new SqlCommand(sql,conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
return com.ExecuteNonQuery();
}
catch (Exception ex)
{
this.Close();
throw;
}
}
/// <summary>
/// Table转list集合
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private static List<T> DataTableToList<T>(DataTable dt)
{
//初始化值
List<T> result = new List<T>();
for (int i = 0; i < dt.Rows.Count; i++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pro in propertys)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (pro.Name.Equals(dt.Columns[j].ColumnName))
{
if (dt.Rows[i][j] != DBNull.Value)
{
pro.SetValue(_t, dt.Rows[i][j], null);
}
else
{
pro.SetValue(_t, null, null);
}
break;
}
}
}
result.Add(_t);
}
return result;
}
/// <summary>
/// 打开数据库链接
/// </summary>
private void Open()
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
/// <summary>
/// 关闭数据库链接
/// </summary>
private void Close()
{
if (conn.State != ConnectionState.Closed)
{
conn.Dispose();
}
}
}
}
写的sql语句调用帮助类操作数据库,dal层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MVC.Notes.DataModel;
namespace MVC.Notes.DAL
{
public class NotesDAL
{
//实例化DBHlper
DBHelper db = new DBHelper();
//下拉
public List<NotesDataModel> BindSel()
{
return db.GetToList<NotesDataModel>("select * from Category");
}
//添加笔记
public int Add(NotesDataModel nm)
{
string sql = $"insert into NoteBook values ('{nm.Title}','{nm.Content}',{nm.CId},'{nm.Times}',{(nm.Gk ?1:0)})";
return db.ExecuteNonQuery(sql);
}
//显示
public List<NotesDataModel> Show()
{
return db.GetToList<NotesDataModel>("select * from Category c join NoteBook n on c.CId=n.CId");
}
//删除 单删/批删
public int Del(string id)
{
string sql = $"delete from NoteBook where Id in ({id})";
return db.ExecuteNonQuery(sql);
}
}
}
放在控制器里的方法,一个方法用来加载页面,一个用来执行后台调用的代码,有些特定的方式只有一个方法,自行理解,不懂得可以问我
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVC.Notes.BLL;
using MVC.Notes.DataModel;
using Webdiyer.WebControls.Mvc;
namespace MVC.Notes.UI.Controllers
{
public class NotesController : Controller
{
//实例化bll层
NotesBLL nb = new NotesBLL();
// GET: Notes
//下拉框
public void BindSel()
{
ViewBag.sl = new SelectList(nb.BindSel(), "CId", "CFl");
}
//添加笔记页面
public ActionResult AddNotes()
{
BindSel();
return View();
}
//添加笔记功能
[HttpPost]
public int AddNotes(NotesDataModel nm)
{
BindSel();
int i = nb.Add(nm);
return i;
}
//显示页面
public ActionResult Show(int index=1)
{
BindSel();
return View(nb.Show().ToPagedList(index,3));
}
//查询功能
[HttpPost]
public ActionResult Show(int id=0,string title="",int index=1)
{
BindSel();
if (id!=0)
{
return PartialView("_PartialShow", nb.Show().Where(m => m.CId == id & m.Title.Contains(title)).ToPagedList(index,3));
}
else
{
return PartialView("_PartialShow", nb.Show().Where(m => m.Title.Contains(title)).ToPagedList(index, 3));
}
}
//删除功能
public ActionResult Del(string id,int index=1)
{
BindSel();
int i = nb.Del(id);
if (i>0)
{
Response.Write("<script>alert('删除成功!')</script>");
}
else
{
Response.Write("<script>alert('删除失败!')</script>");
}
return PartialView("_PartialShow", nb.Show().ToPagedList(index,3));
}
}
}
还需要在mvc项目下的文件夹models里创建实体视图模型,用于在mvc中显示,这里我就不多多解释了
添加视图页面中需要写的代码,我们这里用的方法是ajax方法,也可以不用ajax书写,个人比较喜欢使用ajax方法
@{
ViewBag.Title = "AddNotes";
}
@using MVC.Notes.UI.Models;
@model NotesViewModel
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
<h2>添加笔记页面</h2>
@using (Ajax.BeginForm("AddNotes",new AjaxOptions { @OnSuccess="suc"}))
{
<table class="table-bordered table">
<tr>
<td>@Html.LabelFor(m => m.Title)</td>
<td>@Html.TextBoxFor(m => m.Title)</td>
</tr>
<tr>
<td>@Html.LabelFor(m => m.Content)</td>
<td>@Html.TextBoxFor(m => m.Content)</td>
</tr>
<tr>
<td>@Html.LabelFor(m => m.CId)</td>
<td>@Html.DropDownListFor(m => m.CId, ViewBag.sl as SelectList, "==请选择==")</td>
</tr>
<tr>
<td>@Html.LabelFor(m => m.Times)</td>
<td>@Html.TextBoxFor(m => m.Times)</td>
</tr>
<tr>
<td>@Html.LabelFor(m => m.Gk)</td>
<td>
@Html.RadioButtonFor(m => m.Gk, true)是
@Html.RadioButtonFor(m => m.Gk, false, new { @checked = true })否
</td>
</tr>
<tr>
<td colspan="2">
<input id="Submit1" type="submit" value="添加笔记" />
</td>
</tr>
</table>
}
<script>
function suc(d) {
if (d > 0) {
alert("添加笔记成功!");
location.href = "/Notes/Show";
}
else {
alert("添加笔记失败!");
}
}
</script>
显示视图中的代码,因为我们用的是ajax方法,所以显示或者进行查询或者删除时,需要一个分部视图来实现我们的功能,我下面写的是用于显示页面的代码
@{
ViewBag.Title = "Show";
}
@using MVC.Notes.DataModel;
@using Webdiyer.WebControls.Mvc;
@model PagedList<NotesDataModel>
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
<h2>显示页面</h2>
@using (Ajax.BeginForm("Show", new AjaxOptions { UpdateTargetId = "divData" }))
{
@Html.DropDownList("id", ViewBag.sl as SelectList, "==请选择==")
@Html.TextBox("title")
<input id="Submit1" type="submit" value="搜索" />
<input id="Button1" type="button" value="添加笔记" onclick="location.href='/Notes/AddNotes'" />
}
<div id="divData">
@Html.Partial("_PartialShow", Model)
</div>
分部视图的代码如下
@using MVC.Notes.DataModel;
@using Webdiyer.WebControls.Mvc;
@model PagedList<NotesDataModel>
<table class="table table-bordered">
<thead>
<tr style="background-color:pink">
<td><input id="ckall" type="checkbox" onclick="Qx()" />全选</td>
<td>标题</td>
<td>内容</td>
<td>分类</td>
<td>发布时间</td>
<td>是否公开</td>
<td>操作</td>
</tr>
</thead>
<tbody>
@{
if (Model != null)
{
foreach (var ss in Model)
{
<tr>
<td><input class="ck" value="@ss.Id" type="checkbox" /></td>
<td>@ss.Title</td>
<td>@(ss.Gk ? "*******" : "**********")</td>
<td>@ss.CFl</td>
<td>@ss.Times</td>
<td>@(ss.Gk ? "是" : "否")</td>
<td>@Ajax.ActionLink("删除", "Del", new { @id = ss.Id }, new AjaxOptions { UpdateTargetId = "divData", Confirm = "确认删除吗?" })</td>
</tr>
}
}
}
</tbody>
</table>
<input id="Submit1" type="submit" value="批量删除" onclick="return Ps()" />
<script>
//全选
function Qx() {
if ($("#ckall").prop("checked")) {
$(".ck").prop("checked", true);
}
else {
$(".ck").prop("checked", false);
}
}
//批量删除
function Ps() {
//定义一个数组 存储id
var st = [];
if (confirm("确认删除吗?")) {
$(".ck:checked").each(function () {
st.push(this.value);
})
if (st.length == 0) {
alert("请您至少选择一个数据");
return;
}
$.ajax({
url: "/Notes/Del?id=" + st.toString(),
success:
function (d) {
$("#divData").html(d);
}
})
return true;
}
else {
return false;
}
}
</script>
@*//分页*@
<div class="text-center">
<span style="display:inline-block; position:relative;top:-30px;">共 @Model.TotalPageCount 页 @Model.TotalItemCount 条记录，当前为第 @Model.CurrentPageIndex 页</span>
@Ajax.Pager(Model, new PagerOptions
{
/*
* 设置分页显示的样式
*/
ActionName = "Show",
FirstPageText = "首页",
LastPageText = "最后一页",
NextPageText = "下一页",
PrevPageText = "上一页",
PageIndexParameterName = "index",
ContainerTagName = "ul",
CssClass = "pagination",
CurrentPagerItemTemplate = "<li class=\"active\"><a href=\"#\">{0}</a></li>",
DisabledPagerItemTemplate = "<li class=\"disabled\"><a>{0}</a></li>",
PagerItemTemplate = "<li>{0}</li>"
},
new MvcAjaxOptions
{
HttpMethod = "post",
UpdateTargetId = "divData",//更新数据的标签ID
DataFormId = "form0" //分页对应的条件搜索表单ID
})
</div>
第一次发技术文章,还望大家多多指教,逻辑有点不清晰,有什么不懂得可以问我,觉得我写的还可以的话,点点赞,关注一下,小学生在此先谢过了
