ASP.NET MVC基本增删改查(MySql)
效果
- 使用工具有VS2015、mariadb-10.3.7-winx64.msi、Google Chrome
- Controllers：
using MVC.Models; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using System.Web.Mvc; namespace MVC.Controllers { public class StudentController : Controller { // GET: Student public ActionResult List() { DataTable dt = MySqlHelper.SelectSQL("select * from student"); //StudentModel[] ls = new StudentModel[data.Rows.Count]; //for (int i = 0; i < data.Rows.Count; i++) //{ // ls[i]=new StudentModel() // { // Id = Convert.ToInt16(data.Rows[i]["ID"]), // Name = data.Rows[i]["Name"].ToString(), // Age = Convert.ToInt16(data.Rows[i]["Age"]), // ClassName = data.Rows[i]["ClassName"].ToString() // }; //} List<StudentModel> ls = new List<StudentModel>(); for (int i = 0; i < dt.Rows.Count; i++) { ls.Add(new StudentModel() { Id = Convert.ToInt16(dt.Rows[i]["ID"]), Name = dt.Rows[i]["Name"].ToString(), Age = Convert.ToInt16(dt.Rows[i]["Age"]), ClassName = dt.Rows[i]["ClassName"].ToString() }); } //string datas = Newtonsoft.Json.JsonConvert.SerializeObject(dt); //datas = datas.Replace("'{ ", "'{").Replace("}", "}'"); ViewBag.Data = ls; //TempData["Student"] = datas; return View(); } [HttpGet] public ActionResult AddStudent() { return View(); } [HttpPost] public ActionResult AddStudent(FormCollection collection) { string name = collection["Name"]; string age = collection["Age"]; string classname = collection["ClassName"]; MySqlHelper.ExecSQL("insert into student(name,age,classname)values(@name,@age,@classname)", new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="@name",Value=name }, new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="@age",Value=age }, new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="@classname",Value=classname } }); return Redirect("~/Student/list"); } [HttpGet] public ActionResult EditStudent(int ID) { DataTable dt = MySqlHelper.SelectSQL($"select * from student WHERE ID={ID}"); StudentModel model = new StudentModel() { Id = Convert.ToInt16(dt.Rows[0]["ID"]), Name = dt.Rows[0]["Name"].ToString(), ClassName = dt.Rows[0]["ClassName"].ToString(), Age = Convert.ToInt16(dt.Rows[0]["Age"]) }; return View(model); } [HttpPost] public ActionResult EditStudent(FormCollection fc) { string name = fc["Name"]; int age = Convert.ToInt16(fc["Age"]); string classname =fc["ClassName"]; int id = Convert.ToInt16(fc["id"]); string sql = "update student set Name=@name,age=@age,classname=@classname where id=@id"; MySqlHelper.ExecSQL(sql,new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="name",Value=name }, new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="classname",Value=classname }, new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="age",Value=age }, new MySql.Data.MySqlClient.MySqlParameter() { ParameterName="id",Value=id }, }); return Redirect("~/Student/list"); } public ActionResult DELETEStudent(int ID) { string sql = "DELETE FROM student where id=@id"; MySqlHelper.ExecSQL(sql, new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter() { ParameterName = "id", Value = ID } }); return Redirect("~/Student/list"); } public ActionResult ImportStudent() { return View(); } public ActionResult Upload(HttpPostedFileBase Upload) { if (Upload != null) { string FilePath = Server.MapPath($@"~/{Upload.FileName}"); Upload.SaveAs(FilePath); return Content("保存成功:路径=>" + FilePath); } return Content("未读取到文件"); } } }
Model：
namespace MVC.Models { public class StudentModel { public int Id { get; set; } public string Name { get; set; } public string ClassName { get; set; } public int Age{ get; set; } } }
SqlHelper：
class MySqlHelper { private static readonly string connationString = Properties.Settings.Default.Connection; static MySqlConnection con; private static MySqlConnection Open() { con= new MySqlConnection(connationString); con.Open(); return con; } /// <summary> /// 执行sql /// </summary> /// <param name="sql"></param> /// <param name="mySqls"></param> /// <returns></returns> public static int ExecSQL(string sql,params MySqlParameter[] mySqls) { using (MySqlCommand cmd = MySqlHelper.Open().CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(mySqls); return cmd.ExecuteNonQuery(); } } /// <summary> /// 查询SQL /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DataTable SelectSQL(string sql) { DataTable dt = new DataTable(); using (MySqlCommand cmd = MySqlHelper.Open().CreateCommand()) { cmd.CommandText = sql; var reader = cmd.ExecuteReader(); dt.Load(reader); return dt; } } }
Views：（需要引入JS:jquery.min.js）
查询主页
@using MVC.Models; <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <title>List</title> <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script> <script> function EditData() { console.log("进入--EditData--"); var data = document.getElementById('tad'); var find = false; var rowdata = null; for (var i = 0; i < data.rows.length; i++) { if (data.rows[i].style.background == "blue") { find = true; datarow = data.rows[i]; rowdata = datarow.cells[0].innerHTML; //for (var j = 0; j < datarow.cells.length; j++) { // alert("第" + (i + 1) + "行，第" + (j + 1) + "列的值是:" + datarow.cells[j].innerHTML); //} break; } } if (find) { window.location.href = "/Student/EditStudent?ID=" + rowdata /* $.ajax({ type: "get", url: "~/Student/EditStudent?ID=" + rowdata });*/ } else { alert("请选择一行数据!"); } } function DelData() { console.log("进入--EditData--"); var data = document.getElementById('tad'); var find = false; var rowdata = null; for (var i = 0; i < data.rows.length; i++) { if (data.rows[i].style.background == "blue") { find = true; datarow = data.rows[i]; rowdata = datarow.cells[0].innerHTML; //for (var j = 0; j < datarow.cells.length; j++) { // alert("第" + (i + 1) + "行，第" + (j + 1) + "列的值是:" + datarow.cells[j].innerHTML); //} break; } } if (find) { console.log('datarow==' + datarow); window.location.href = "/Student/DELETEStudent?ID=" + rowdata } else { alert("请选择一行数据!"); } } function getrow(obj){ if(event.srcElement.tagName=="TD"){ curRow = event.srcElement.parentElement; if (curRow.style.background != "blue") { curRow.style.background = "yellow"; } } } function backrow(obj){ if(event.srcElement.tagName=="TD"){ curRow = event.srcElement.parentElement; if (curRow.style.background != "blue") { curRow.style.background = "#f2f2f2"; } } } function selectRow(obj) { if (event.srcElement.tagName == "TD") { curRow = event.srcElement.parentElement; if (curRow.style.background == "blue") { curRow.style.background = "#f2f2f2" } else { curRow.style.background = "blue"; } } } </script> </head> <body> <div> <form id="list" action="/" method="post"> <input type="button" id="add" value="新增" onclick="location.href='@Url.Action("AddStudent")'" /> <input type="button" id="edit" value="修改" onclick="EditData()"/> <input type="button" id="del" value="删除" onclick="DelData()"/> <input type="button" id="imp" value="导出" onclick="location.href='@Url.Action("ImportStudent")'"/> </form> <form action="~/Student/Upload" method="post" enctype="multipart/form-data"> <input type="file" name="Upload" value="上传"/> <input type="submit" /> </form> </div> <div> <table width="100%" height="100px" border="1px" id="tad" onmouseover="getrow(this)" onmouseout="backrow(this)" onclick="selectRow(this)"> <tr><th>编号</th><th>姓名</th><th>年龄</th><th>班级</th></tr> @foreach (var item in @ViewBag.Data as List<StudentModel>) { <tr> <td>@item.Id</td> <td>@item.Name</td> <td>@item.Age</td> <td>@item.ClassName</td> <td><a href="~/Student/EditStudent?ID=@item.Id">编辑</a></td> <td>@Html.ActionLink("删除", "DELETEStudent", new { id = @item.Id }) </td> </tr> } </table> </div> </body> </html>
新增页面：
<!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>AddStudent</title> </head> <body> <div> <form action="~/Student/AddStudent" method="post"> 姓名: <input name="Name" type="text" placeholder="请输入姓名" /> <br /> 年纪： <input name="Age" type="text" placeholder="请输入年龄" /><br /> 班级： <input name="ClassName" type="text" placeholder="请输入班级" /> <br /> <input type="submit" /> </form> </div> </body> </html>
修改页面
@model MVC.Models.StudentModel <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>EditStudent</title> </head> <body> <div> <form action="~/Student/EditStudent" method="post"> ID: <input name="ID" type="text" readonly="readonly" placeholder="ID" value=@Model.Id /> <br /> 姓名: <input name="Name" type="text" placeholder="请输入姓名" value=@Model.Name /> <br /> 年纪： <input name="Age" type="text" placeholder="请输入年龄" value=@Model.Age /> <br /> 班级： <input name="ClassName" type="text" placeholder="请输入班级" value=@Model.ClassName /> <br /> <input type="submit" /> </form> </div> </body> </html>
- 数据库脚本
DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `Age` int(11) DEFAULT NULL, `ClassName` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, PRIMARY KEY (`Id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '张三', 22, '一年级'); INSERT INTO `student` VALUES (2, '李四', 23, '二年级'); INSERT INTO `student` VALUES (3, '王五', 24, '三年级'); INSERT INTO `student` VALUES (4, '赵六', 25, '四年级'); INSERT INTO `student` VALUES (5, '张无忌', 13, '二年级'); INSERT INTO `student` VALUES (6, '老大', 32, '二年级'); INSERT INTO `student` VALUES (7, '孙琦', 12, '五年级'); INSERT INTO `student` VALUES (8, '老王', 23, '二年级'); SET FOREIGN_KEY_CHECKS = 1;
最终效果：
主页：
新增(提交自动跳转)：
修改：
