记一次使用Asp.Net Core WebApi 5.0+Dapper+Mysql+Redis+Docker的开发过程 (3)

public async Task<Users> GetUserDetail(int id)
{
string detailSql = @"SELECT Id, Email, UserName, Mobile, Password, Age, Gender, CreateTime,Salt, IsDelete FROM Users WHERE Id=@Id";
return await Detail(id, detailSql);
}

public async Task<Users> GetUserDetailByEmail(string email)
{
string detailSql = @"SELECT Id, Email, UserName, Mobile, Password, Age, Gender, CreateTime, Salt, IsDelete FROM Users WHERE Email=@email";

using (IDbConnection conn = DataBaseConfig.GetMySqlConnection())
{
return await conn.QueryFirstOrDefaultAsync<Users>(detailSql, new { email });
}
}

public async Task<List<Users>> GetUsers()
{
string selectSql = @"SELECT * FROM Users";
return await Select(selectSql);
}

public async Task<int> AddUser(Users entity)
{
string insertSql = @"INSERT INTO Users (UserName, Gender, Avatar, Mobile, CreateTime, Password, Salt, IsDelete, Email) VALUES (@UserName, @Gender, @Avatar, @Mobile, now(),@Password, @Salt, @IsDelete,@Email);SELECT @id= LAST_INSERT_ID();";
return await Insert(entity, insertSql);
}
}
}
```

大功告成，接下来需要手动创建数据库和表结构，不能像使用EF那样自动生成了，使用Dapper基本上是要纯写SQL的，如果想像EF那样使用，就要额外的安装一个扩展 [Dapper.Contrib](https://github.com/StackExchange/dapper-dot-net/tree/master/Dapper.Contrib)。

数据库表结构如下，比较简单。

```sql
DROP TABLE IF EXISTS `Users`;
CREATE TABLE `Users` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`Email` varchar(255) DEFAULT NULL COMMENT '邮箱',
`UserName` varchar(20) DEFAULT NULL COMMENT '用户名称',
`Mobile` varchar(11) DEFAULT NULL COMMENT '手机号',
`Age` int(11) DEFAULT NULL COMMENT '年龄',
`Gender` int(1) DEFAULT '0' COMMENT '性别',
`Avatar` varchar(255) DEFAULT NULL COMMENT '头像',
`Salt` varchar(255) DEFAULT NULL COMMENT '加盐',
`Password` varchar(255) DEFAULT NULL COMMENT '密码',
`IsDelete` int(2) DEFAULT '0' COMMENT '0-正常 1-删除',
`CreateTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`Id`),
UNIQUE KEY `USER_MOBILE_INDEX` (`Mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
```

好了，数据访问层大概就这样子了，下面来看看应用层的具体实现方式。

### 应用程序层

创建一个WebApi项目，主要对外提供Api接口服务，具体结构如下。

- Autofac
- 存放IOC 依赖注入的配置项
- AutoMapper
- 存放实体对象映射关系的配置项
- Controllers
- 控制器，具体业务逻辑也将写在这
- Fliters
- 存放自定义的过滤器
- Helpers
- 存放本层中用到的一些帮助类
- Models
- 存放输入/输出/DTO等实体类


好了，结构大概就是这样。错误优先，先处理程序异常，和集成日志程序吧。

#### 自定义异常处理

在Helpers文件夹中创建一个ErrorHandingMiddleware中间件，添加扩展方法ErrorHandlingExtensions，在Startup中将会使用到。

```c#
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Threading.Tasks;

namespace CodeUin.WebApi.Helpers
{
public class ErrorHandlingMiddleware
{
private readonly RequestDelegate next;
private readonly ILogger<ErrorHandlingMiddleware> _logger;

public ErrorHandlingMiddleware(RequestDelegate next, ILogger<ErrorHandlingMiddleware> logger)
{
this.next = next;
_logger = logger;
}

public async Task Invoke(HttpContext context)
{
try
{
await next(context);
}
catch (Exception ex)
{
_logger.LogError(ex.Message);

var statusCode = 500;

await HandleExceptionAsync(context, statusCode, ex.Message);
}
finally
{
var statusCode = context.Response.StatusCode;
var msg = "";

if (statusCode == 401)
{
msg = "未授权";
}
else if (statusCode == 404)
{
msg = "未找到服务";
}
else if (statusCode == 502)
{
msg = "请求错误";
}
else if (statusCode != 200)
{
msg = "未知错误";
}
if (!string.IsNullOrWhiteSpace(msg))
{
await HandleExceptionAsync(context, statusCode, msg);
}
}
}

// 异常错误信息捕获，将错误信息用Json方式返回
private static Task HandleExceptionAsync(HttpContext context, int statusCode, string msg)
{
var result = JsonConvert.SerializeObject(new { Msg = msg, Code = statusCode });

context.Response.ContentType = "application/json;charset=utf-8";

return context.Response.WriteAsync(result);
}
}