搭建WebAPI项目，EF Core访问数据库

1. 新建项目，搭建架构，Api 采用 .NET Core 5.0 ，类库采用 .NET 5.0

项目引用顺序：表现层引用业务层，业务层引用数据访问层、公共类，公共类引用扩展类

2. 采用数据库先行模式，实现模型层

Nuget（注意版本兼容）：

　　Pomelo.EntityFrameworkCore.MySql（根据数据库不同而不同，这里采用的是MYSQL）

　　Microsoft.EntityFrameworkCore.Design

　　Microsoft.EntityFrameworkCore.Tools

设置数据访问层为启动项目，打开程序包管理器控制台，执行上下文脚手架命令：

Scaffold-DbContext "Server=localhost;User Id=root;Password=密码;Database=数据库名称;TreatTinyAsBoolean=true;" "Pomelo.EntityFrameworkCore.MySql" -OutputDir  Models -force -UseDatabaseNames

生成效果：

另外添加 AppDbContext.cs ，代替******Context.cs

3. 添加 IHospitalServices类，实现接口层（仅医院表，下同）

public interface IHospitalServices
{
    /// <summary>
    /// 获取数据集合
    /// </summary>
    /// <returns></returns>
    Task<IEnumerable<hospital>> GetListAsync();

    /// <summary>
    /// 通过主键ID获取数据
    /// </summary>
    Task<hospital> GetInfoAsync(string id);

    /// <summary>
    /// 添加数据
    /// </summary>
    Task<int> AddAsync(hospital entity);
    /// <summary>
    /// 添加数据集合
    /// </summary>
    Task<int> AddListAsync(List<hospital> list);

    /// <summary>
    /// 更新一条数据
    /// </summary>
    Task<int> UpdateAsync(hospital entity);
    /// <summary>
    /// 修改集合，修改指定的列
    /// </summary>
    /// <param name="list">集合</param>
    /// <param name="column">指定的列，参考 id,name。要修改的列名传入数组</param>
    /// <returns></returns>
    Task<int> UpdateListAsync(List<hospital> list, string[] column);

    /// <summary>
    /// 删除数据
    /// </summary>
    Task<int> DeleteAsync(string id);
    /// <summary>
    /// 删除集合
    /// </summary>
    Task<int> DeleteListAsync(List<hospital> list);

    /// <summary>
    /// 条件查询
    /// </summary>
    /// <param name="where">过滤条件</param>
    Task<IEnumerable<hospital>> GetAnyAsync(Expression<Func<hospital, bool>> where);

    /// <summary>
    /// 分页查询
    /// </summary>
    /// <param name="where">过滤条件</param>
    /// <param name="courrentPage">当前页</param>
    /// <param name="pageSize">页大小</param>
    /// <returns></returns>
    Task<page_data<hospital>> GetAnyAsync<TOrder>(Expression<Func<hospital, bool>> where, Expression<Func<hospital, TOrder>> order, int pageIndex = 1, int pageSize = 15, bool isDesc = false);
}

View Code

需要扩展模型类，用于接收 Api 的请求参数和分页返回数据

/// <summary>
/// 分页查询接收
/// </summary>
public class page_info<T> where T : class
{
    /// <summary>
    /// 第几页
    /// </summary>
    public int page_index { get; set; }
    /// <summary>
    /// 单页数
    /// </summary>
    public int page_size { get; set; }
    /// <summary>
    /// 数据
    /// </summary>
    public T data { get; set; }
}

/// <summary>
/// 分页查询返回
/// </summary>
public class page_data<T> where T : class
{
    /// <summary>
    /// 总页数
    /// </summary>
    public int total_page { get; set; }
    /// <summary>
    /// 总数
    /// </summary>
    public int total_count { get; set; }
    /// <summary>
    /// 数据
    /// </summary>
    public IEnumerable<T> data { get; set; }
}

/// <summary>
/// 修改/删除方法的接收
/// </summary>
public class receive_data
{
    /// <summary>
    /// 值，以逗号分割
    /// </summary>
    public string ids { get; set; }

    /// <summary>
    /// 修改状态 -1删除 0禁用 1启用
    /// </summary>
    public int? status { get; set; }
}

View Code

4. 添加 HospitalServices类，实现业务逻辑层

public class HospitalServices : IHospitalServices
{
    private readonly AppDbContext context;

    public HospitalServices(AppDbContext context)
    {
        this.context = context;
    }

    public async Task<IEnumerable<hospital>> GetListAsync()
    {
        return await context.hospital.ToListAsync();
    }

    public async Task<hospital> GetInfoAsync(string id)
    {
        return await context.hospital.FindAsync(new Guid(id));
    }

    public async Task<int> AddAsync(hospital entity)
    {
        await context.hospital.AddAsync(entity);
        return await context.SaveChangesAsync();
    }
    public async Task<int> AddListAsync(List<hospital> list)
    {
        await context.hospital.AddRangeAsync(list);
        return await context.SaveChangesAsync();
    }

    public async Task<int> UpdateAsync(hospital entity)
    {
        var entry = this.context.hospital.Update(entity);
        return await context.SaveChangesAsync();
    }
    public async Task<int> UpdateListAsync(List<hospital> list, string[] column)
    {
        foreach (var entity in list)
        {
            var entry = context.Entry(entity);
            entry.State = EntityState.Modified;
            foreach (var item in column)
            {
                entry.Property(item).IsModified = true;
            }

            var listP = entry.Properties.ToList();
            listP.RemoveAll(w => w.Metadata.IsPrimaryKey());
            foreach (var item in listP)
            {
                if (column.Contains(item.Metadata.Name))//字段名称
                    continue;
                item.IsModified = false;
            }
        }
        return await context.SaveChangesAsync();
    }

    public async Task<int> DeleteAsync(string id)
    {
        var entity = await context.hospital.FindAsync(new Guid(id));
        if (entity != null)
        {
            //先删除关联表数据
            //……
            context.hospital.Remove(entity);
            return await context.SaveChangesAsync();
        }
        return 0;
    }
    public async Task<int> DeleteListAsync(List<hospital> list)
    {
        //先删除关联表数据
        //……
        context.hospital.RemoveRange(list);
        return await context.SaveChangesAsync();
    }

    public async Task<IEnumerable<hospital>> GetAnyAsync(Expression<Func<hospital, bool>> where)
    {
        return await context.hospital.AsNoTracking().Where(where).ToArrayAsync();
    }

    public async Task<page_data<hospital>> GetAnyAsync<TOrder>(Expression<Func<hospital, bool>> where, Expression<Func<hospital, TOrder>> order, int pageIndex = 1, int pageSize = 15, bool isDesc = false)
    {
        page_data<hospital> data = new page_data<hospital>();
        int totalCount = await context.hospital.AsNoTracking().Where(where).CountAsync();
        if (isDesc)
            data.data = await context.hospital.AsNoTracking().Where(where).OrderByDescending(order).Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToArrayAsync();
        else
            data.data = await context.hospital.AsNoTracking().Where(where).OrderBy(order).Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToArrayAsync();

        data.total_page = (totalCount + pageSize - 1) / pageSize;
        data.total_count = totalCount;
        return data;
    }
}

View Code

5. 添加 HospitalController控制器，实现表现层

/// <summary>
/// 医院管理
/// </summary>
[Route("api/[controller]")]
[ApiController]
public class HospitalController : ControllerBase
{
    private readonly IHospitalServices idal;
    Result result = new Result();

    public HospitalController(IHospitalServices idal)
    {
        this.idal = idal;
    }

    /// <summary>
    /// 获取医院详细信息
    /// </summary>
    /// <param name="id">医院ID</param>
    /// <returns></returns>
    [HttpGet]
    [Route("getInfo")]
    public async Task<Result> Get(string id)
    {
        try
        {
            result.Data = await idal.GetInfoAsync(id);
            return result;
        }
        catch (Exception ex)
        {
            result.Code = -99; result.Message = ex.Message;
            return result;
        }
    }

    /// <summary>
    /// 添加医院信息
    /// </summary>
    /// <param name="json">{"name":"第一测试医院", "hospital_code":"00280099", "areacode":"778899"}</param>
    /// <returns></returns>
    [HttpPost]
    [Route("addInfo")]
    public async Task<Result> AddInfo(object json)
    {
        try
        {
            var model = json.ToString().ToObject<hospital>();
            model.id = Guid.NewGuid();
            model.acronym = MsPinYinHelper.FirstPinYin(model.name);
            model.create_time = DateTime.Now;
            model.status = 1;

            int count = await idal.AddAsync(model);
            if (count > 0)
            {
                result.Message = "添加成功！";
                result.Data = count;
            }
            else
            {
                result.Code = -1;
                result.Message = "添加失败！";
            }
            return result;
        }
        catch (Exception ex)
        {
            result.Code = -99; result.Message = ex.Message;
            return result;
        }
    }

    /// <summary>
    /// -1删除/0禁用/1启用
    /// </summary>
    /// <param name="json">{"ids":"1d235515-b464-4de4-8777-ebcd6053e1cf,cd402518-1338-4f18-84d3-9423e06f6637","status":1}</param>
    /// <returns></returns>
    [HttpPost]
    [Route("updateStatus")]
    public async Task<Result> UpdateStatus(object json)
    {
        try
        {
            receive_data model = json.ToString().ToObject<receive_data>();
            string message = "";
            List<hospital> list = new List<hospital>();
            foreach (var id in model.ids.Split(","))
            {
                var entity = await idal.GetInfoAsync(id);
                if (entity != null)
                {
                    switch (model.status)
                    {
                        case -1:
                            message = "删除";
                            entity.status = -1;
                            break;
                        case 1:
                            message = "启用";
                            entity.status = 1;
                            break;
                        default:
                            message = "禁用";
                            entity.status = 0;
                            break;
                    }
                    list.Add(entity);
                }
            }
            int count = await idal.UpdateListAsync(list, new string[] { "status" });
            if (count > 0)
            {
                result.Message = message + "成功！";
                result.Data = count;
            }
            else
            {
                result.Code = -1;
                result.Message = message + "失败！";
            }
            return result;
        }
        catch (Exception ex)
        {
            result.Code = -99; result.Message = ex.Message;
            return result;
        }
    }

    /// <summary>
    /// 修改医院信息
    /// </summary>
    /// <param name="json">{"id":"cd402518-1338-4f18-84d3-9423e06f6637","hospital_id":"00280099","name":"第二测试医院","acronym":"decsyy","create_time": "2021-05-31T15:52:08","status": 1}</param>
    /// <returns></returns>
    [HttpPost]
    [Route("updateInfo")]
    public async Task<Result> UpdateInfo(object json)
    {
        try
        {
            var entity = json.ToString().ToObject<hospital>();
            Expression<Func<hospital, bool>> filter = u => u.id == entity.id;
            var model = idal.GetAnyAsync(filter).Result.FirstOrDefault();
            if (model != null)
            {
                PropertyTrade.Voluation(model, entity);
                int count = await idal.UpdateAsync(model);
                if (count > 0)
                {
                    result.Message = "修改成功！";
                    result.Data = count;
                }
                else
                {
                    result.Code = -1;
                    result.Message = "修改失败！";
                }
            }
            else
            {
                result.Code = -1;
                result.Message = "找不到该数据！";
            }
            return result;
        }
        catch (Exception ex)
        {
            result.Code = -99; result.Message = ex.Message;
            return result;
        }
    }

    /// <summary>
    /// 分页查询医院列表信息
    /// </summary>
    /// <param name="json">{"page_index":1,"page_size":10,"data":{"name":"医院","status":1}}</param>
    /// <returns></returns>
    [HttpPost]
    [Route("getAny")]
    public async Task<Result> GetAny(object json)
    {
        try
        {
            page_info<hospitalDro> data = json.ToString().ToObject<page_info<hospitalDro>>();
            Expression<Func<hospital, bool>> filter = u => u.status != -1;
            if (data.data != null)
            {
                if (!string.IsNullOrEmpty(data.data.name))
                    filter = filter.And(c => c.name.Contains(data.data.name));
                if (data.data.status != null)
                    filter = filter.And(c => c.status == data.data.status);
            }
            Expression<Func<hospital, DateTime>> order = u => u.create_time;
            result.Data = await idal.GetAnyAsync(filter, order, data.page_index, data.page_size, true);
            return result;
        }
        catch (Exception ex)
        {
            result.Code = -99; result.Message = ex.Message;
            return result;
        }
    }

    public partial class hospitalDro
    {
        public string name { get; set; }
        public sbyte? status { get; set; }
    }
}

View Code

需要扩展模型类，用于 Api 返回数据

public class Result
{
    /// <summary>
    /// 返回状态 0正常 -1失败 -99程序异常
    /// </summary>
    public int Code { get; set; }

    /// <summary>
    /// 提示信息，失败提示失败，成功提示成功（除查询），异常提醒异常信息
    /// </summary>
    public string Message { get; set; }

    /// <summary>
    /// 返回数据，正常时返回
    /// </summary>
    public object Data { get; set; }
}

View Code

还需添加 ExpressionExtensions类和 PropertyTrade类，

详见 https://www.cnblogs.com/Allofus/p/14771510.html，https://www.cnblogs.com/Allofus/p/14848681.html

6. 在 appsettings.json 中配置数据库连接字符串

"AllowedHosts": "*",
"ConnectionStrings": {
  "DBConnection": "server=localhost;user=root;pwd=123456;database=数据库名"
}

7. 在 Startup类 ConfigureServices方法中进行依赖注入

//EF Core DbContext类
var connectionString = Configuration.GetConnectionString("DBConnection");
ServerVersion serverVersion = ServerVersion.AutoDetect(connectionString);
services.AddDbContextPool<AppDbContext>(
        options => options.UseMySql(connectionString, serverVersion)
    );

services.AddScoped<IHospitalServices, HospitalServices>();

完成，这样就可以操作数据表里面的数据了：（下述用的是其他图片，所以不是Hospital而是User）

8. 没有注释不友好？设置项目属性--生成，勾选XML文档文件，如下图：

再在ConfigureServices方法中进行配置：

services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new OpenApiInfo { Title = "API", Version = "v1" });
    // 为 Swagger 设置xml文档注释路径
    var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
    var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
    // 添加控制器层注释，true表示显示控制器注释
    c.IncludeXmlComments(xmlPath, true);
});

最后在控制器上添加注释：

/// <summary>
/// 获取用户列表
/// </summary>
[HttpGet]
[Route("getUserList")]
public IEnumerable<User> Get()
{
    return _user.GetAllUsers();
}

效果如下图：

参考上传文件 WebApi_阶段一.zip

弊端：修改时，int字段会为0

posted @ 2021-03-24 19:33 kueizheng 阅读(567) 评论(0) 收藏举报

刷新页面返回顶部

kueizheng

搭建WebAPI项目，EF Core访问数据库

公告