[大数据]ASP.NET 6 不使用EntityFramework的RazorPages MySQL增删改查

创建ASP.NET Core Web应用

创建MySQL数据库和表

CREATE DATABASE IF NOT EXISTS `searchdb`;
USE `searchdb`;

CREATE TABLE IF NOT EXISTS `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `datebirth` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sex` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

创建? cshtml和cshtml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;

namespace RazorPages_CRUD_NO_EntityFramework.Models
{
    public class Student
    {
        [Key]
        public int id { get; set; }
        public string name { get; set; }
        public string email { get; set; }
        public string datebirth { get; set; }
        public string sex { get; set; }
    }
}

@page
@model RazorPages_CRUD_NO_EntityFramework.Pages.IndexModel
@{
    ViewData["Title"] = "Home page";
}

<div class="text-center">
    <h1 class="display-4">RazorPages_CRUD_NO_EntityFramework</h1>
    <hr />
    <form method="post">
        <div class="form-group">
            姓名： <input type="text" name="name" placeholder="姓名" />
            电子邮件： <input type="email" name="email" placeholder="电子邮件" />
            出生日期： <input type="date" name="datebirth" />
            性别： <select id="sex" name="sex">
                <option value="M">男</option>
                <option value="F">女</option>
            </select>
            <input type="submit" value="增加" class="btn btn-success" />
        </div>

        <table class="table">
            <tr>
                <th>Id</th>
                <th>姓名</th>
                <th>电子邮件</th>
                <th>出生日期</th>
                <th>性别</th>
            </tr>
            @foreach (var element in Model.ListStudents)
            {
                <tr>
                    <td>@element.id</td>
                    <td>@element.name</td>
                    <td>@element.email</td>
                    <td>@element.datebirth</td>
                    <td>@element.sex</td>
                    <td><a asp-page="Edit" class="btn btn-info" asp-route-_id="@element.id">修改</a></td>
                    <td><a asp-page="Details" class="btn btn-primary" asp-route-_id="@element.id">详细信息</a></td>
                    <td><a asp-page="Delete" class="btn btn-danger" asp-route-_id="@element.id">删除</a></td>
                </tr>
            }
        </table>
    </form>
</div>

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.Sql;
using System.Data.SqlClient;
using RazorPages_CRUD_NO_EntityFramework.Models;
using MySql.Data.MySqlClient;



namespace RazorPages_CRUD_NO_EntityFramework.Pages
{
    public class IndexModel : PageModel
    {
        public IEnumerable<Student> ListStudents { get; set; }
        //private readonly ILogger<IndexModel> _logger;

        //public IndexModel(ILogger<IndexModel> logger)
        //{
        //    _logger = logger;
        //}

        public void OnGet()
        {
            ListStudents = AfficherListstudents();
        }

        public static List<Student> AfficherListstudents()
        {
            List<Student> ListStud = new List<Student>();
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                using (MySqlCommand cmd = new MySqlCommand("select * from student", con))
                {
                    con.Open();
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Student ec = new Student();
                            ec.id = Convert.ToInt32(reader["id"]);
                            ec.name = Convert.ToString(reader["name"]);
                            ec.email = Convert.ToString(reader["email"]);
                            ec.datebirth = Convert.ToString(reader["datebirth"]);
                            ec.sex = Convert.ToString(reader["sex"]);
                            ListStud.Add(ec);
                        }
                    }
                    return ListStud;
                }
            }
        }

        public IActionResult OnPostAsync(Student ec)
        {
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string req = "insert into student(name,email,datebirth,sex) values ('" + ec.name + "','" + ec.email + "','" + ec.datebirth + "','" + ec.sex + "')";
                using (MySqlCommand cmd = new MySqlCommand(req, con))
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return RedirectToPage("Index");
        }

    }
}

@page "{_id:int}"
@model RazorPages_CRUD_NO_EntityFramework.Pages.EditModel
@{
}
<h2>学生详细信息:</h2>
<form method="post">
    <table class="table">
        <tr>
            <td>ID: @Html.DisplayFor(model => model.ListStudents.id)</td>
        </tr>
        <tr>
            <td>姓名: <input type="text" name="name" placeholder="姓名" asp-for="ListStudents.name" /> </td>
        </tr>
        <tr>
            <td>电子邮件: <input type="email" name="email" placeholder="电子邮件" asp-for="ListStudents.email" /> </td>
        </tr>
        <tr>
            <td>出生日期: <input type="date" name="datebirth" asp-for="ListStudents.datebirth" /> </td>
        </tr>
        <tr>
            <td>
                性别:<select id="sex" name="sex" asp-for="ListStudents.sex">
                    <option value="M">男</option>
                    <option value="F">女</option>
                </select>
            </td>
        </tr>
    </table>
    <input type="submit" value="修改" class="btn btn-success" />
    <a asp-page="Index">学生列表</a>
</form>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Data.SqlClient;
using RazorPages_CRUD_NO_EntityFramework.Models;
using MySql.Data.MySqlClient;

namespace RazorPages_CRUD_NO_EntityFramework.Pages
{
    public class EditModel : PageModel
    {
        [BindProperty]
        public Student ListStudents { get; set; }
        public void OnGet(int _id)
        {
            Student ec = new Student();
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                using (MySqlCommand cmd = new MySqlCommand("select * from student where id='" + _id + "'", con))
                {
                    con.Open();
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {

                            ec.id = Convert.ToInt32(reader["id"]);
                            ec.name = Convert.ToString(reader["name"]);
                            ec.email = Convert.ToString(reader["email"]);
                            ec.datebirth = Convert.ToString(reader["datebirth"]);
                            ec.sex = Convert.ToString(reader["sex"]);

                        }
                    }
                    ListStudents = ec;
                }
            }
        }

        public IActionResult OnPostAsync(int _id)
        {
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string req = "update student set name ='" + ListStudents.name.ToString() + "',email='" + ListStudents.email.ToString() + "',datebirth='" + Convert.ToDateTime(ListStudents.datebirth) + "',sex='" + ListStudents.sex.ToString() + "' where id=" + _id;
                using (MySqlCommand cmd = new MySqlCommand(req, con))
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return RedirectToPage("Index");
        }
    }
}

@page "{_id:int}"
@model RazorPages_CRUD_NO_EntityFramework.Pages.DetailsModel
@{
}
<h2>学生详细信息:</h2>
<table class="table">
    <tr>
        <td>ID: @Html.DisplayFor(model => model.ListStudents.id)</td>
    </tr>
    <tr>
        <td>姓名: @Html.DisplayFor(model => model.ListStudents.name)</td>
    </tr>
    <tr>
        <td>电子邮件: @Html.DisplayFor(model => model.ListStudents.email)</td>
    </tr>
    <tr>
        <td>出生日期: @Html.DisplayFor(model => model.ListStudents.datebirth)</td>
    </tr>
    <tr>
        <td>性别: @Html.DisplayFor(model => model.ListStudents.sex)</td>
    </tr>
</table>
<a asp-page="Index">学生列表</a>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using System.Data;
using System.Data.SqlClient;
using RazorPages_CRUD_NO_EntityFramework.Models;
using MySql.Data.MySqlClient;


namespace RazorPages_CRUD_NO_EntityFramework.Pages
{
    public class DetailsModel : PageModel
    {
        [BindProperty]
        public Student ListStudents { get; set; }
        public void OnGet(int _id)
        {
            Student ec = new Student();
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                using (MySqlCommand cmd = new MySqlCommand("select * from student where id='" + _id + "'", con))
                {
                    con.Open();
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {

                            ec.id = Convert.ToInt32(reader["id"]);
                            ec.name = Convert.ToString(reader["name"]);
                            ec.email = Convert.ToString(reader["email"]);
                            ec.datebirth = Convert.ToString(reader["datebirth"]);
                            ec.sex = Convert.ToString(reader["sex"]);

                        }
                    }
                    ListStudents = ec;
                }
            }
        }
    }
}

@page "{_id:int}"
@model RazorPages_CRUD_NO_EntityFramework.Pages.DeleteModel
@{
    ViewData["Title"] = "Delete";
}

<h2>删除学生</h2>
<hr />
<h4>是否要删除学生： <b>@Html.DisplayFor(Model=>Model.ListStudents.name)</b> ?</h4>
<form method="post">
    <input type="submit" value="删除" class="btn btn-danger" />
    <a asp-page="Index" class="btn btn-primary">学生列表</a>
</form>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

using System.Data.SqlClient;
using RazorPages_CRUD_NO_EntityFramework.Models;
using MySql.Data.MySqlClient;

namespace RazorPages_CRUD_NO_EntityFramework.Pages
{
    public class DeleteModel : PageModel
    {
        [BindProperty]
        public Student ListStudents { get; set; }
        public void OnGet(int _id)
        {
            Student ec = new Student();
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                using (MySqlCommand cmd = new MySqlCommand("select * from student where id='" + _id + "'", con))
                {
                    con.Open();
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {

                            ec.id = Convert.ToInt32(reader["id"]);
                            ec.name = Convert.ToString(reader["name"]);
                            ec.email = Convert.ToString(reader["email"]);
                            ec.datebirth = Convert.ToString(reader["datebirth"]);
                            ec.sex = Convert.ToString(reader["sex"]);

                        }
                    }
                    ListStudents = ec;
                }
            }
        }
        public IActionResult OnPostAsync(int _id)
        {
            string cs = "server=localhost;user=root;database=searchdb;port=3306;password=root;sslmode=none";
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string req = "delete from student where id=" + _id;
                using (MySqlCommand cmd = new MySqlCommand(req, con))
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return RedirectToPage("Index");
        }
    }
}