ASP.NET Core：使用Dapper和SwaggerUI来丰富你的系统框架

一、概述

1、用VS2017创建如下图的几个.NET Standard类库，默认版本为1.4，你可以通过项目属性进行修改，最高支持到1.6，大概五月份左右会更新至2.0，API会翻倍，很期待！

排名分先后，这里简要说下我对各个类库职责的一个理解。 Light.Model：存放实际项目中你用到的所有实体集合，包括数据库表映射实体，请求实体，响应实体，视图显示实体以及一些公共实体类等，同时你还可以根据自己业务的模块功能进行更细致的划分。 Light.IRepository：数据库仓储接口，定义你操作数据库的所有动作，包括简单的C（Create）R（Retrive）U（Update）D（Delete），也就是我们经常说的增查改删。对于上层服务而言，我只要暴露出来操作数据库的接口就可以了，而不需要关注具体的实现，这样一来，不管我使用Dapper还是EF Core，抑或是SQLServer或者MySql，对调用者来说，不会有任何影响。 Light.Repository：各种请求数据库数据的方法都在这里，你可以选择自己喜欢的工具，自己喜欢的语言来丰富它，sql也好，linq也罢，只要你高兴，随时可以搞起来。 Light.IBusiness和Light.Business：业务逻辑接口定义和实现，理解了上面说的, 这两层其实含义是差不多的。复杂的业务逻辑，验证或者判断等等，你都在Business里边实现好，至于以后是web api来调用还是普通的web来调用，这些都交由IBusiness来做就可以了 Light.DependencyInjection：依赖注入。从此解放我们的双手，再也不用new来new去了，代码也整洁了，同时也解耦了，况且它已经被集成到.net core中，何乐而不为呢。 那么剩下如果后续有需要Common层或者Web层的，向解决方案里边添加就好，这俩就不用过多解释什么了，你懂的

 

2、当然了，你还可以通过.NET Core Tool的cli命令来创建，前提是你的电脑上安装了.NET Core SDK。

dotnet new classlib -n Light.Repository //创建一个名字为Light.Repository的.NET Standard类库

更多创建类型请键入如下命令进行查看

dotnet new -all

红框内自上而下依次表示：

• 控制台应用程序
• 类库
• 微软自带的单元测试项目
• 引入XUnit框架的单元测试项目
• 空的ASP.NET Core
• ASP.NET Core MVC项目
• ASP.NET Core WebAPI项目
• 空的解决方案

二、引入Dapper

1、准备创建用户表的SQL脚本

USE [Light]
GO

/****** Object:  Table [dbo].[User]    Script Date: 2017/3/27 22:40:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(10000,1) NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,
    [Password] [nvarchar](50) NOT NULL,
    [Gender] [bit] NOT NULL,
    [Birthday] [datetime] NOT NULL,
    [CreateUserId] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [UpdateUserId] [int] NOT NULL,
    [UpdateDate] [datetime] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_UserName]  DEFAULT ('') FOR [UserName]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Password]  DEFAULT ('') FOR [Password]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Gender]  DEFAULT ((0)) FOR [Gender]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Birthday]  DEFAULT (getdate()) FOR [Birthday]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_CreateUserId]  DEFAULT ((0)) FOR [CreateUserId]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_UpdateUserId]  DEFAULT ((0)) FOR [UpdateUserId]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_UpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'UserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Password'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别（0女，1男）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Gender'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生年月日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Birthday'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'CreateUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'UpdateUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'UpdateDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标志是否删除（0未删除，1已删除）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'IsDeleted'
GO

 2、添加对应的User表实体

/// <summary>
    /// 用户实体
    /// </summary>
    public class User : BaseModel
    {
        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        /// 密码
        /// </summary>
        public string Password { get; set; }

        /// <summary>
        /// 性别（0女，1男）
        /// </summary>
        public bool Gender { get; set; }

        /// <summary>
        /// 出生年月日
        /// </summary>
        public DateTime Birthday { get; set; }
    }

3、使用泛型针对每一个表定义基本的CRUD接口，然后IUserRepository继承它，如果需要其他的接口，后续往里边定义即可

/// <summary>
    /// 基类业务接口定义
    /// </summary>
    public interface IBaseBusiness<T> where T : class
    {
        /// <summary>
        /// 添加一个实体
        /// </summary>
        /// <param name="entity">要创建的实体</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        bool CreateEntity(T entity, string connectionString = null);

        /// <summary>
        /// 根据主键Id获取一个实体
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        T RetriveOneEntityById(int id, string connectionString = null);

        /// <summary>
        /// 获取所有实体
        /// </summary>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        IEnumerable<T> RetriveAllEntity(string connectionString = null);

        /// <summary>
        /// 修改一个实体
        /// </summary>
        /// <param name="entity">要修改的实体</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        bool UpdateEntity(T entity, string connectionString = null);

        /// <summary>
        /// 根据主键Id删除一个实体
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        bool DeleteEntityById(int id, string connectionString = null);
    }

 4、主角进场。通过Nuget引入Dapper到Repository中，目前的版本为1.50.2

Install-Package Dapper

• Dapper是目前一款简洁、高效并且开源的ORM(实体对象关系映射)框架之一。
  传送门：https://github.com/StackExchange/Dapper
• 它不仅支持Sql Server，还适用于MySql，SqlLite，Oracle，PostgreSql等
• 默认使用参数化进行查询或者新增
• 支持存储过程
• 支持级联映射和多表映射
• 默认缓冲你所执行的SQL

a)、设置你的链接字符串和DbConnection

    /// <summary>
    /// 数据库配置
    /// </summary>
    public class DataBaseConfig
    {
        #region SqlServer链接配置
        /// <summary>
        /// 默认的Sql Server的链接字符串
        /// </summary>
        private static string DefaultSqlConnectionString = @"Data Source=.;Initial Catalog=Light;User ID=sa;Password=sa;";
        public static IDbConnection GetSqlConnection(string sqlConnectionString = null)
        {
            if (string.IsNullOrWhiteSpace(sqlConnectionString))
            {
                sqlConnectionString = DefaultSqlConnectionString;
            }
            IDbConnection conn = new SqlConnection(sqlConnectionString);
            conn.Open();
            return conn;
        }
        #endregion
    }

b)、实现IUserRepository定义的接口，使用Dapper进行操作数据

    /// <summary>
    /// 用户仓储
    /// </summary>
    /// <typeparam name="User"></typeparam>
    public class UserRepository : IUserRepository
    {
        /// <summary>
        /// 创建一个用户
        /// </summary>
        /// <param name="entity">用户</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        public bool CreateEntity(User entity, string connectionString = null)
        {
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
            {
                string insertSql = @"INSERT INTO [dbo].[User]
                                           ([UserName]
                                           ,[Password]
                                           ,[Gender]
                                           ,[Birthday]
                                           ,[CreateUserId]
                                           ,[CreateDate]
                                           ,[UpdateUserId]
                                           ,[UpdateDate]
                                           ,[IsDeleted])
                                     VALUES
                                           (@UserName
                                           ,@Password
                                           ,@Gender
                                           ,@Birthday
                                           ,@CreateUserId
                                           ,@CreateDate
                                           ,@UpdateUserId
                                           ,@UpdateDate
                                           ,@IsDeleted)";
                return conn.Execute(insertSql, entity) > 0;
            }
        }

        /// <summary>
        /// 根据主键Id删除一个用户
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        public bool DeleteEntityById(int id, string connectionString = null)
        {
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
            {
                string deleteSql = @"DELETE FROM [dbo].[User]
                                            WHERE Id = @Id";
                return conn.Execute(deleteSql, new { Id = id }) > 0;
            }
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        public IEnumerable<User> RetriveAllEntity(string connectionString = null)
        {
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
            {
                string querySql = @"SELECT [Id]
                                          ,[UserName]
                                          ,[Password]
                                          ,[Gender]
                                          ,[Birthday]
                                          ,[CreateUserId]
                                          ,[CreateDate]
                                          ,[UpdateUserId]
                                          ,[UpdateDate]
                                          ,[IsDeleted]
                                      FROM [dbo].[User]";
                return conn.Query<User>(querySql);
            }
        }

        /// <summary>
        /// 根据主键Id获取一个用户
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        public User RetriveOneEntityById(int id, string connectionString = null)
        {
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
            {
                string querySql = @"SELECT [Id]
                                          ,[UserName]
                                          ,[Password]
                                          ,[Gender]
                                          ,[Birthday]
                                          ,[CreateUserId]
                                          ,[CreateDate]
                                          ,[UpdateUserId]
                                          ,[UpdateDate]
                                          ,[IsDeleted]
                                      FROM [dbo].[User]
                                     WHERE Id = @Id";
                return conn.QueryFirstOrDefault<User>(querySql, new { Id = id });
            }
        }

        /// <summary>
        /// 修改一个用户
        /// </summary>
        /// <param name="entity">要修改的用户</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        public bool UpdateEntity(User entity, string connectionString = null)
        {
            using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))
            {
                string updateSql = @"UPDATE [dbo].[User]
                                       SET [UserName] = @UserName
                                          ,[Password] = @Password
                                          ,[Gender] = @Gender
                                          ,[Birthday] = @Birthday
                                          ,[UpdateUserId] = @UpdateUserId
                                          ,[UpdateDate] = @UpdateDate
                                          ,[IsDeleted] = @IsDeleted
                                     WHERE Id = @Id";
                return conn.Execute(updateSql, entity) > 0;
            }
        }
    }

上面的代码所执行的方法Execute，Query，QueryFirstOrDefault其实都是IDbConnection的扩展方法，转到定义你也可以清楚的看到默认buffered缓存是开启的，并且都支持事务提交，同时还都对应各自的async异步方法

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?));

 这样一来，再也不用DataReader去一个一个属性赋值了，也不用SqlParameter一个一个去参数化了，Dapper全部帮你做了，你只需要关注你的业务SQL语句以及上下文的对象即可，是不是很清爽！

c)、接下来就是完成对上一部分仓储数据的调用。一方面是IBusiness的服务接口定义，另一方面则是Business的简单实现，因为暂时只是涉及到一个表的增查改删，所以具体的代码就不上了

d)、至于依赖注入模块，我直接使用的是Microsoft.Extensions.DependencyInjection，所有的服务由ServiceCollection服务容器进行管理，我们只需要将接口和它的实现类进行注册就行了

    /// <summary>
    /// 注入业务逻辑层
    /// </summary>
    public class BusinessInjection
    {
        public static void ConfigureBusiness(IServiceCollection services)
        {
            services.AddSingleton<IUserBusiness, UserBusiness>();
        }
    }

    /// <summary>
    /// 注入仓储层
    /// </summary>
    public class RepositoryInjection
    {
        public static void ConfigureRepository(IServiceCollection services)
        {
            services.AddSingleton<IUserRepository, UserRepository>();
        }
    }

三、引入SwaggerUI

简介：SwaggerUI是一款针对WebApi的可视化工具，同样我也称它是一款全自动的接口描述文档，并且可以很方便的帮助你测试你的接口，接下来我们一步一步的来把它添加进去。

1、向ASP.NET Core WebApi项目中添加Swashbuckle组件，打开包管理控制台，输入：

Install-Package Swashbuckle -Pre

记得加上后边的-Pre，表示安装最新版本，否则的话会默认下载稳定版本5.5.3（不支持Core）

当然了，你也可以包管理器进行安装，记得勾选“包括预发行版”，然后选择6.0.0-beta902进行安装

2、打开Startup文件，添加Swagger中间件并将他们注入到应用程序的管道中，同时也将我们自定义的服务加进去

        /// <summary>
        /// This method gets called by the runtime. Use this method to add services to the container.
        /// </summary>
        /// <param name="services"></param>
        public void ConfigureServices(IServiceCollection services)
        {
            RepositoryInjection.ConfigureRepository(services);
            BusinessInjection.ConfigureBusiness(services);
            services.AddMvc();
            services.AddSwaggerGen();
        }

        /// <summary>
        /// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        /// </summary>
        /// <param name="app"></param>
        /// <param name="env"></param>
        /// <param name="loggerFactory"></param>
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            app.UseMvc();
            app.UseSwagger();
            app.UseSwaggerUi();
        }    

 3、打开launchSettings文件，修改launchUrl为“swagger/ui”

  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "launchUrl": "swagger/ui",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "Light.Api": {
      "commandName": "Project",
      "launchBrowser": true,
      "launchUrl": "swagger/ui",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      },
      "applicationUrl": "http://localhost:62116"
    }
  }

4、添加一个UserController，针对每一个服务增加一个REST风格的API接口。注意使用的HttpMethod方法。HttpGet表示获取对象，HttpPost表示创建对象，HttpPut表示修改对象，HttpDelete表示删除对象

    /// <summary>
    /// 用户控制器
    /// </summary>
    [Route("api/[controller]")]
    public class UserController : Controller
    {
        private readonly IUserBusiness iUserBusiness;
        /// <summary>
        /// 构造函数注入服务
        /// </summary>
        /// <param name="userBusiness"></param>
        public UserController(IUserBusiness userBusiness)
        {
            iUserBusiness = userBusiness;
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        //[Route("AllUser")]
        public IEnumerable<User> GetAllUser()
        {
            return iUserBusiness.RetriveAllEntity();
        }

        /// <summary>
        /// 根据主键Id获取一个用户
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public User GetOneUser(int id)
        {
            return iUserBusiness.RetriveOneEntityById(id);
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="user">用户实体</param>
        /// <returns></returns>
        [HttpPost]
        public bool CreateUser([FromBody]User user)
        {
            return iUserBusiness.CreateEntity(user);
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <param name="user">用户实体</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public bool UpdateUser(int id, [FromBody]User user)
        {
            user.Id = id;
            return iUserBusiness.UpdateEntity(user);
        }

        /// <summary>
        /// 根据主键Id删除一个用户
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public bool DeleteUser(int id)
        {
            return iUserBusiness.DeleteEntityById(id);
        }
    }

5、大功告成，Ctrl+F5运行你的程序，并试着执行添加，修改和查询的Api，体验Swagger带给你的方便之处。

写在最后：这篇文章只是介绍了它们俩最简单最基本的用法，如果其中说法有误的，还望各位前辈多多指点，更多的内容还需要后续的继续积累和学习，加油！