ASP.NET Core列表增删改查

前置要求：

1. vue+element-plus实现前端静态页面

HelloWorld.vue

<template>
  <h2>hello界面</h2>
  <div class="tableList">

   <!-- 搜索框 -->
   <el-row :gutter="20">
      <el-col :span="8">
        <!-- 搜索与添加区域 -->
          <el-input placeholder="请输入内容"
          v-model="SearchVal" clearable @clear="getUserList" @keyup.enter="enterSearch">
          <template #append>
           <el-button @click="getUserList"><el-icon><search /></el-icon></el-button>
          </template>
          </el-input>
      </el-col>
      <el-col :span="2">
        <el-button type="primary" @click="openAdd">add</el-button>
      </el-col>
      <el-col :span="4">
        <el-button type="danger" @click="onDel">delete</el-button>
      </el-col>
    </el-row>

    <!-- 表单 -->
    <el-table :data="tableData" style="width: 100%">
      <el-table-column type="selection" width="55" />
    <el-table-column prop="date" label="Date" width="180" />
    <el-table-column prop="name" label="Name" width="100" />
    <el-table-column prop="oder" label="Oder" width="100" />
    <el-table-column prop="address" label="Address" />
    <!-- 操作按钮 -->
    <el-table-column label="Operations">
      <template #default="scope">
        <el-button size="small" @click="handleEdit(scope.$index, scope.row)"
          >Edit</el-button
        >
        <el-button
          size="small"
          type="danger"
          @click="handleDelete(scope.$index, scope.row)"
          >Delete</el-button
        >
      </template>
    </el-table-column>
  </el-table>
  <el-pagination background layout="prev, pager, next" :total="total"  @current-change="handleCurrentChange"/>
  </div>
</template>

<script lang="ts" setup >
import { ref } from "vue";
const SearchVal=ref("")
const total=ref(100)
const enterSearch=()=>{}
const openAdd=()=>{}
const onDel=()=>{}

const handleEdit = (index: number, row: User) => {
  console.log(index, row)
}
const handleDelete = (index: number, row: User) => {
  console.log(index, row)
}

const handleCurrentChange = (val: number) => {
  console.log(val)
}

const tableData = [
  {
    date: '2016-05-03',
    name: 'Tom',
    oder:1,
    address: 'No. 189, Grove St, Los Angeles',
  },
  {
    date: '2016-05-02',
    name: 'Tom',
    oder:2,
    address: 'No. 189, Grove St, Los Angeles',
  },
  {
    date: '2016-05-04',
    name: 'Tom',
    oder:3,
    address: 'No. 189, Grove St, Los Angeles',
  },
  {
    date: '2016-05-01',
    name: 'Tom',
    oder:4,
    address: 'No. 189, Grove St, Los Angeles',
  },
]
</script>

<style lang="scss" scoped >
.tableList{
 margin:  0 500px;
}
.el-pagination{
  margin-top: 20px;
}
</style>

1.2 创建弹框组件app.vue

<!-- 弹窗组件 -->
<template>
  <el-dialog v-model="dialogFormVisible" title="新增" with="30%">
    <el-form :model="form">
      <el-form-item label="时间" prop="date">
        <el-input v-model="form.data" type="date" placeholder="请选择一个时间" :disabledDate="disabledDate"/>
      </el-form-item>
      <el-form-item label="名称" prop="name">
        <el-input v-model="form.name"></el-input>
      </el-form-item>
      <el-form-item label="地址" prop="address">
        <el-input v-model="form.address"/>
      </el-form-item>
      <el-form-item label="排序" prop="order">
        <el-input v-model="form.oder"/>
      </el-form-item>
    </el-form>
    <template #footer>
      <span class="dialog-footer">
        <el-button @click="dialogFormVisible = false">Cancel</el-button>
        <el-button type="primary" @click="dialogFormVisible = false">
          Confirm
        </el-button>
      </span>
    </template>
  </el-dialog>
</template>
<script setup lang="ts">
import {ref} from "vue"

const dialogFormVisible=ref(false)
const form=ref()
const disabledDate=(time:any)=>{//最大时间从今天开始
    const _maxTime=Date.now()-24*60*60*1000*1
    return time.getTime()<=_maxTime
}


</script>

<style scoped lang="scss">
.dialog-footer button:first-child {
  margin-right: 10px;
}
.el-button--text {
  margin-right: 15px;
}
.el-select {
  width: 300px;
}
.el-input {
  width: 300px;
}
.dialog-footer button:first-child {
  margin-right: 10px;
}
</style>

完善方法组件的代码：组件的引用，组件的传值prop，计算属性，事件监听、子触发父组件的事件

HelloWorld.vue

<template>
  <h2>hello界面</h2>
  <div class="tableList">

   <!-- 搜索框 -->
   <el-row :gutter="20">
      <el-col :span="8">
        <!-- 搜索与添加区域 -->
          <el-input placeholder="请输入内容"
          v-model="SearchVal" clearable @clear="getUserList" @keyup.enter="enterSearch">
          <template #append>
           <el-button @click="getUserList"><el-icon><search /></el-icon></el-button>
          </template>
          </el-input>
      </el-col>
      <el-col :span="2">
        <el-button type="primary" @click="openAdd">add</el-button>
      </el-col>
      <el-col :span="4">
        <el-button type="danger" @click="onDel">delete</el-button>
      </el-col>
    </el-row>

    <!-- 表单 -->
    <el-table :data="tableData" style="width: 100%">
      <el-table-column type="selection" width="55" />
    <el-table-column prop="date" label="Date" width="180" />
    <el-table-column prop="name" label="Name" width="100" />
    <el-table-column prop="order" label="Order" width="100" />
    <el-table-column prop="address" label="Address" />
    <!-- 操作按钮 -->
    <el-table-column label="Operations">
      <template #default="scope">
        <el-button size="small" @click="handleEdit(scope.$index, scope.row)"
          >Edit</el-button
        >
        <el-button
          size="small"
          type="danger"
          @click="handleDelete(scope.$index, scope.row)"
          >Delete</el-button
        >
      </template>
    </el-table-column>
  </el-table>
  <el-pagination background layout="prev, pager, next" :total="total"  @current-change="handleCurrentChange"/>
  </div>
  <addVue :isShow="isShow" :info="info" @closeAdd="closeAdd" @success="success"></addVue>
</template>

<script lang="ts" setup >
import { ref } from "vue";
import User from "../class/User.ts"
import addVue from "../components/add.vue"
const SearchVal=ref("")
const total=ref(100)

const isShow=ref(false)
//定义info
const info=ref<User>(new User())

const enterSearch=()=>{}
const openAdd=()=>{
  isShow.value=true
}
const onDel=()=>{}

const handleEdit = (index: number, row: User) => {
  console.log(index, row)
  info.value=row
  isShow.value=true
}
const handleDelete = (index: number, row: User) => {
  console.log(index, row)
}

const handleCurrentChange = (val: number) => {
  console.log(val)
}
const closeAdd=()=>{
  isShow.value=false
  info.value=new User()
}
const success=()=>{
  isShow.value=false
  info.value=new User()
  EIMessage.success(message)
}



const tableData = [
  {
    date: '2016-05-03',
    name: 'Tom',
    order:1,
    address: 'No. 189, Grove St, Los Angeles',
  },
  {
    date: '2016-05-02',
    name: 'Tom',
    order:2,
    address: 'No. 189, Grove St, Los Angeles',
  },
  {
    date: '2016-05-04',
    name: 'Tom',
    order:3,
    address: 'No. 189, Grove St, Los Angeles',
  },
  {
    date: '2016-05-01',
    name: 'Tom',
    order:4,
    address: 'No. 189, Grove St, Los Angeles',
  },
]
</script>

<style lang="scss" scoped >
.tableList{
 margin:  0 500px;
}
.el-pagination{
  margin-top: 20px;
}
</style>

app.vue

作用：利用三元表达式，使用同一个组件展示不同的弹框内容

<!-- 弹窗组件 -->
<template>
  <el-dialog v-model="dialogFormVisible" :title="info?.name?'修改':'新增'" with="30%" @close="$emit('closeAdd')" draggable="true">
    <!-- 使用三元表达式控制标题展示 -->
    <el-form :model="form">
      <el-form-item label="时间" prop="date">
        <el-input v-model="form.date" type="date" placeholder="请选择一个时间" :disabledDate="disabledDate"/>
      </el-form-item>
      <el-form-item label="名称" prop="name">
        <el-input v-model="form.name"></el-input>
      </el-form-item>
      <el-form-item label="地址" prop="address">
        <el-input v-model="form.address"/>
      </el-form-item>
      <el-form-item label="排序" prop="order">
        <el-input v-model="form.order"/>
      </el-form-item>
    </el-form>
    <template #footer>
      <span class="dialog-footer">
        <el-button @click="closeAdd()">取消</el-button>
        <el-button type="primary" @click="save()">
          确认
        </el-button>
      </span>
    </template>
  </el-dialog>
</template>
<script setup lang="ts">
import {ref,Ref,computed,watch} from "vue"
import User from "../class/User"
const prop=defineProps({//内置的传参工具  接收使用prop传递过来的数据
    isShow:Boolean,//对于接收的类型进行控制
    info:User
})


const disabledDate=(time:any)=>{//最大时间从今天开始
    const _maxTime=Date.now()-24*60*60*1000*1
    return time.getTime()<=_maxTime
}
const dialogFormVisible=computed(()=>prop.isShow)//表单的展示与隐藏绑定在isShow上
const form:Ref<User>=ref<User>({//声明使用User类
    id:"",
    date:"",
    name:"",
    address:"",
    order:0
})
watch(()=>prop.info,(newInfo)=>{
    if(newInfo){
        form.value={
            id:newInfo.id,
            date:newInfo.date,
            name:newInfo.name,
            address:newInfo.address,
            order:newInfo.order 
        }
    }
})
const emits=defineEmits(["closeAdd","success"])
const closeAdd=()=>{
   emits("closeAdd")
}
const save=()=>{
    emits("success")
}

</script>

<style scoped lang="scss">
.dialog-footer button:first-child {
  margin-right: 10px;
}
.el-button--text {
  margin-right: 15px;
}
.el-select {
  width: 300px;
}
.el-input {
  width: 300px;
}
.dialog-footer button:first-child {
  margin-right: 10px;
}
</style>

2、后端接口的创建与开发

安装nuget：sqlSugarCore

Program.cs

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.MapGet("/test", () =>//方法（"/路径",()=>{}）
{
    return "ok";
});


app.Run();

创建Model->User.cs

using SqlSugar;

namespace WebApplication1.Model
{
    public class User
    {
        [SugarColumn(IsPrimaryKey = true)]//使用sqlSugar：配置主键
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime Date { get; set; }
        public string Address { get; set; }
        public int Order { get; set; }

    }
}

创建Data->SqlSugarHelper.cs

using SqlSugar;

namespace WebApplication1.Data
{
    public class SqlSugarHelper
    {
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig() 
        {
            ConnectionString= "Data Source=.;Initial Catalog=BookMS;Persist Security Info=True;User ID=sa;Password=123456;Trust Server Certificate=True",
            DbType=DbType.SqlServer,//数据库类型
            IsAutoCloseConnection=true,//不手动colse 自动关闭连接
        },
            db =>
            {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    Console.WriteLine(sql);//输出sql返回的数据，不影响性能开发阶段可以使用
                };
            }
        );
    }
}

完善SqlSugarHelper.cs实现增删改查数据库的执行语句

using SqlSugar;
using System.Reflection;
using WebApplication1.Model;

namespace WebApplication1.Data
{
    public class SqlSugarHelper
    {
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig() 
        {
            ConnectionString= "Data Source=.;Initial Catalog=BookMS;Persist Security Info=True;User ID=sa;Password=123456;Trust Server Certificate=True",
            DbType=DbType.SqlServer,//数据库类型
            IsAutoCloseConnection=true,//不手动colse 自动关闭连接
        },
            db =>
            {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    Console.WriteLine(sql);//输出sql返回的数据，不影响性能开发阶段可以使用
                };
            });

        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <returns></returns>
        public static string InitDateBase()//自动创建表使用sqlSuger的配置
        {
            try
            {
                //创建数据库
                Db.DbMaintenance.CreateDatabase();
                //创建表
                string nspace = "WebApplication1.Model";
                Type[] ass=Assembly.LoadFrom(AppContext.BaseDirectory+ "WebApplication1.dll").GetTypes().Where(p=>p.Namespace == nspace).ToArray();
                Db.CodeFirst.SetStringDefaultLength(200).InitTables(ass);
                //初始化数据
                Db.Deleteable<User>().ExecuteCommand();//清空数据
                List<User> list = new List<User>();
                for(int i = 1; i <= 5; i++)
                {
                    list.Add(new User()
                    {
                        Id = Guid.NewGuid().ToString(),
                        Name="Tom"+i,
                        Date=DateTime.Now,
                        Address="北京路138号",
                        Order=i,
                    });
                }
                for (int i = 6; i <= 10; i++)
                {
                    list.Add(new User()
                    {
                        Id = Guid.NewGuid().ToString(),
                        Name = "Tom" + i,
                        Date = DateTime.Now,
                        Address = "北京路138号",
                        Order = i,
                    });
                }
                for (int i = 11; i <= 30; i++)
                {
                    list.Add(new User()
                    {
                        Id = Guid.NewGuid().ToString(),
                        Name = "Tom" + i,
                        Date = DateTime.Now,
                        Address = "北京路138号",
                        Order = i,
                    });
                }
                Db.Insertable(list).ExecuteCommand();
                return "ok";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        ///<summary>
        ///1.读取用户列表
        ///</summary>
        public static Result GetUsers(Model req)
        {
            Result result = new Result();
            int total = 0;
            result.Res = Db.Queryable<User>()
                .WhereIF(!string.IsNullOrEmpty(req.KeyWord), s => s.Name.Contains(req.KeyWord) || s.Address.Contains(req.KeyWord))//过滤数据
                .OrderBy(s => s.Order)//排序
                .ToOffsetPage(req.PageIndex, req.PageSize, ref total);//分页
            result.Total = total;
            return result;
        }
        ///<summary>
        ///2.新增
        ///</summary>
        public static bool Add(AddReq req)
        {
            User info =new User()
            {
                Id=Guid.NewGuid().ToString(),
                Name = req.Name,
                Date=req.Date,
                Address=req.Address,
                Order=req.Order
            };
            if (Db.Queryable<User>().Any(p => p.Name == req.Name))
            {
                return false;
            }
            return Db.Insertable(info).ExecuteCommand()>0;
        }
        ///<summary>
        ///3.编辑
        ///</summary>
        public static bool Edit(User req)
        {
            User info =Db.Queryable<User>().First(p=>p.Id==req.Id);
            if (info == null)
            {
                return false;
            }
            info.Name = req.Name;
            info.Date = req.Date;
            info.Address = req.Address;
            info.Order = req.Order;
            return Db.Updateable(info).ExecuteCommand() > 0;//差不多是返回影响行数的意思
            
        }
        ///<summary>
        ///4.删除方法（单个及批量都可以）
        ///</summary>
        public static bool Del(string ids)
        {
            return Db.Ado.ExecuteCommand($"DELETE [User] WHERE Id IN({ids})")>0;
        }
    }

    public class Model//分页
    {
        public string KeyWord { get; set; }
        public int PageIndex { get; set; }
        public int PageSize { get; set; }
        public int Total {  get; set; }
    }
    public class Result//结果
    {
        public int Total { get; set; }
        public object Res { get; set; }
    }
    public class AddReq//添加的请求参数
    {
        //public string Id { get; set; }
        public string Name { get; set; }
        public DateTime Date { get; set; }
        public string Address { get; set; }
        public int Order { get; set; }
    }
}

在Program.cs网络请求中追加代码

app.MapGet("/codefrist", () =>
{
    return SqlSugarHelper.InitDateBase();
});
app.MapPost("/list", (Model req) =>
{
    return SqlSugarHelper.GetUsers(req);
});
app.MapPost("/add", (AddReq req) =>
{
    return SqlSugarHelper.Add(req);
});
app.MapPost("/edit", (User req) =>
{
    return SqlSugarHelper.Edit(req);
});
app.MapDelete("/delete", (string ids) => 
{
    return SqlSugarHelper.Del(ids);
});

实现对数据库的增删改查