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;

最终效果：

主页：

 新增(提交自动跳转)：

 修改：