首页 > 代码库 > ASP.NET Core:使用Dapper和SwaggerUI来丰富你的系统框架
ASP.NET Core:使用Dapper和SwaggerUI来丰富你的系统框架
一、概述
1、用VS2017创建如下图的几个.NET Standard类库,默认版本为1.4,你可以通过项目属性进行修改,最高支持到1.6,大概五月份左右会更新至2.0,API会翻倍,很期待!
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脚本
1 USE [Light] 2 GO 3 4 /****** Object: Table [dbo].[User] Script Date: 2017/3/27 22:40:08 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO10 11 CREATE TABLE [dbo].[User](12 [Id] [int] IDENTITY(10000,1) NOT NULL,13 [UserName] [nvarchar](50) NOT NULL,14 [Password] [nvarchar](50) NOT NULL,15 [Gender] [bit] NOT NULL,16 [Birthday] [datetime] NOT NULL,17 [CreateUserId] [int] NOT NULL,18 [CreateDate] [datetime] NOT NULL,19 [UpdateUserId] [int] NOT NULL,20 [UpdateDate] [datetime] NOT NULL,21 [IsDeleted] [bit] NOT NULL,22 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 23 (24 [Id] ASC25 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]26 ) ON [PRIMARY]27 28 GO29 30 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_UserName] DEFAULT (‘‘) FOR [UserName]31 GO32 33 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_Password] DEFAULT (‘‘) FOR [Password]34 GO35 36 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_Gender] DEFAULT ((0)) FOR [Gender]37 GO38 39 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_Birthday] DEFAULT (getdate()) FOR [Birthday]40 GO41 42 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CreateUserId] DEFAULT ((0)) FOR [CreateUserId]43 GO44 45 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CreateDate] DEFAULT (getdate()) FOR [CreateDate]46 GO47 48 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_UpdateUserId] DEFAULT ((0)) FOR [UpdateUserId]49 GO50 51 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_UpdateDate] DEFAULT (getdate()) FOR [UpdateDate]52 GO53 54 ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]55 GO56 57 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‘58 GO59 60 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‘61 GO62 63 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‘64 GO65 66 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‘67 GO68 69 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‘70 GO71 72 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‘73 GO74 75 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‘76 GO77 78 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‘79 GO80 81 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‘82 GO83 84 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‘85 GO
2、添加对应的User表实体
1 /// <summary> 2 /// 用户实体 3 /// </summary> 4 public class User : BaseModel 5 { 6 /// <summary> 7 /// 用户名 8 /// </summary> 9 public string UserName { get; set; }10 11 /// <summary>12 /// 密码13 /// </summary>14 public string Password { get; set; }15 16 /// <summary>17 /// 性别(0女,1男)18 /// </summary>19 public bool Gender { get; set; }20 21 /// <summary>22 /// 出生年月日23 /// </summary>24 public DateTime Birthday { get; set; }25 }
3、使用泛型针对每一个表定义基本的CRUD接口,然后IUserRepository继承它,如果需要其他的接口,后续往里边定义即可
1 /// <summary> 2 /// 基类业务接口定义 3 /// </summary> 4 public interface IBaseBusiness<T> where T : class 5 { 6 /// <summary> 7 /// 添加一个实体 8 /// </summary> 9 /// <param name="entity">要创建的实体</param>10 /// <param name="connectionString">链接字符串</param>11 /// <returns></returns>12 bool CreateEntity(T entity, string connectionString = null);13 14 /// <summary>15 /// 根据主键Id获取一个实体16 /// </summary>17 /// <param name="id">主键Id</param>18 /// <param name="connectionString">链接字符串</param>19 /// <returns></returns>20 T RetriveOneEntityById(int id, string connectionString = null);21 22 /// <summary>23 /// 获取所有实体24 /// </summary>25 /// <param name="connectionString">链接字符串</param>26 /// <returns></returns>27 IEnumerable<T> RetriveAllEntity(string connectionString = null);28 29 /// <summary>30 /// 修改一个实体31 /// </summary>32 /// <param name="entity">要修改的实体</param>33 /// <param name="connectionString">链接字符串</param>34 /// <returns></returns>35 bool UpdateEntity(T entity, string connectionString = null);36 37 /// <summary>38 /// 根据主键Id删除一个实体39 /// </summary>40 /// <param name="id">主键Id</param>41 /// <param name="connectionString">链接字符串</param>42 /// <returns></returns>43 bool DeleteEntityById(int id, string connectionString = null);44 }
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
1 /// <summary> 2 /// 数据库配置 3 /// </summary> 4 public class DataBaseConfig 5 { 6 #region SqlServer链接配置 7 /// <summary> 8 /// 默认的Sql Server的链接字符串 9 /// </summary>10 private static string DefaultSqlConnectionString = @"Data Source=.;Initial Catalog=Light;User ID=sa;Password=sa;";11 public static IDbConnection GetSqlConnection(string sqlConnectionString = null)12 {13 if (string.IsNullOrWhiteSpace(sqlConnectionString))14 {15 sqlConnectionString = DefaultSqlConnectionString;16 }17 IDbConnection conn = new SqlConnection(sqlConnectionString);18 conn.Open();19 return conn;20 }21 #endregion22 }
b)、实现IUserRepository定义的接口,使用Dapper进行操作数据
1 /// <summary> 2 /// 用户仓储 3 /// </summary> 4 /// <typeparam name="User"></typeparam> 5 public class UserRepository : IUserRepository 6 { 7 /// <summary> 8 /// 创建一个用户 9 /// </summary> 10 /// <param name="entity">用户</param> 11 /// <param name="connectionString">链接字符串</param> 12 /// <returns></returns> 13 public bool CreateEntity(User entity, string connectionString = null) 14 { 15 using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) 16 { 17 string insertSql = @"INSERT INTO [dbo].[User] 18 ([UserName] 19 ,[Password] 20 ,[Gender] 21 ,[Birthday] 22 ,[CreateUserId] 23 ,[CreateDate] 24 ,[UpdateUserId] 25 ,[UpdateDate] 26 ,[IsDeleted]) 27 VALUES 28 (@UserName 29 ,@Password 30 ,@Gender 31 ,@Birthday 32 ,@CreateUserId 33 ,@CreateDate 34 ,@UpdateUserId 35 ,@UpdateDate 36 ,@IsDeleted)"; 37 return conn.Execute(insertSql, entity) > 0; 38 } 39 } 40 41 /// <summary> 42 /// 根据主键Id删除一个用户 43 /// </summary> 44 /// <param name="id">主键Id</param> 45 /// <param name="connectionString">链接字符串</param> 46 /// <returns></returns> 47 public bool DeleteEntityById(int id, string connectionString = null) 48 { 49 using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) 50 { 51 string deleteSql = @"DELETE FROM [dbo].[User] 52 WHERE Id = @Id"; 53 return conn.Execute(deleteSql, new { Id = id }) > 0; 54 } 55 } 56 57 /// <summary> 58 /// 获取所有用户 59 /// </summary> 60 /// <param name="connectionString">链接字符串</param> 61 /// <returns></returns> 62 public IEnumerable<User> RetriveAllEntity(string connectionString = null) 63 { 64 using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) 65 { 66 string querySql = @"SELECT [Id] 67 ,[UserName] 68 ,[Password] 69 ,[Gender] 70 ,[Birthday] 71 ,[CreateUserId] 72 ,[CreateDate] 73 ,[UpdateUserId] 74 ,[UpdateDate] 75 ,[IsDeleted] 76 FROM [dbo].[User]"; 77 return conn.Query<User>(querySql); 78 } 79 } 80 81 /// <summary> 82 /// 根据主键Id获取一个用户 83 /// </summary> 84 /// <param name="id">主键Id</param> 85 /// <param name="connectionString">链接字符串</param> 86 /// <returns></returns> 87 public User RetriveOneEntityById(int id, string connectionString = null) 88 { 89 using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) 90 { 91 string querySql = @"SELECT [Id] 92 ,[UserName] 93 ,[Password] 94 ,[Gender] 95 ,[Birthday] 96 ,[CreateUserId] 97 ,[CreateDate] 98 ,[UpdateUserId] 99 ,[UpdateDate]100 ,[IsDeleted]101 FROM [dbo].[User]102 WHERE Id = @Id";103 return conn.QueryFirstOrDefault<User>(querySql, new { Id = id });104 }105 }106 107 /// <summary>108 /// 修改一个用户109 /// </summary>110 /// <param name="entity">要修改的用户</param>111 /// <param name="connectionString">链接字符串</param>112 /// <returns></returns>113 public bool UpdateEntity(User entity, string connectionString = null)114 {115 using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString))116 {117 string updateSql = @"UPDATE [dbo].[User]118 SET [UserName] = @UserName119 ,[Password] = @Password120 ,[Gender] = @Gender121 ,[Birthday] = @Birthday122 ,[UpdateUserId] = @UpdateUserId123 ,[UpdateDate] = @UpdateDate124 ,[IsDeleted] = @IsDeleted125 WHERE Id = @Id";126 return conn.Execute(updateSql, entity) > 0;127 }128 }129 }
上面的代码所执行的方法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服务容器进行管理,我们只需要将接口和它的实现类进行注册就行了
1 /// <summary> 2 /// 注入业务逻辑层 3 /// </summary> 4 public class BusinessInjection 5 { 6 public static void ConfigureBusiness(IServiceCollection services) 7 { 8 services.AddSingleton<IUserBusiness, UserBusiness>(); 9 }10 }
1 /// <summary> 2 /// 注入仓储层 3 /// </summary> 4 public class RepositoryInjection 5 { 6 public static void ConfigureRepository(IServiceCollection services) 7 { 8 services.AddSingleton<IUserRepository, UserRepository>(); 9 }10 }
三、引入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中间件并将他们注入到应用程序的管道中,同时也将我们自定义的服务加进去
1 /// <summary> 2 /// This method gets called by the runtime. Use this method to add services to the container. 3 /// </summary> 4 /// <param name="services"></param> 5 public void ConfigureServices(IServiceCollection services) 6 { 7 RepositoryInjection.ConfigureRepository(services); 8 BusinessInjection.ConfigureBusiness(services); 9 services.AddMvc();10 services.AddSwaggerGen();11 }12 13 /// <summary>14 /// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.15 /// </summary>16 /// <param name="app"></param>17 /// <param name="env"></param>18 /// <param name="loggerFactory"></param>19 public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)20 {21 loggerFactory.AddConsole(Configuration.GetSection("Logging"));22 loggerFactory.AddDebug();23 24 app.UseMvc();25 app.UseSwagger();26 app.UseSwaggerUi();27 }
3、打开launchSettings文件,修改launchUrl为“swagger/ui”
1 "profiles": { 2 "IIS Express": { 3 "commandName": "IISExpress", 4 "launchBrowser": true, 5 "launchUrl": "swagger/ui", 6 "environmentVariables": { 7 "ASPNETCORE_ENVIRONMENT": "Development" 8 } 9 },10 "Light.Api": {11 "commandName": "Project",12 "launchBrowser": true,13 "launchUrl": "swagger/ui",14 "environmentVariables": {15 "ASPNETCORE_ENVIRONMENT": "Development"16 },17 "applicationUrl": "http://localhost:62116"18 }19 }
4、添加一个UserController,针对每一个服务增加一个REST风格的API接口。注意使用的HttpMethod方法。HttpGet表示获取对象,HttpPost表示创建对象,HttpPut表示修改对象,HttpDelete表示删除对象
1 /// <summary> 2 /// 用户控制器 3 /// </summary> 4 [Route("api/[controller]")] 5 public class UserController : Controller 6 { 7 private readonly IUserBusiness iUserBusiness; 8 /// <summary> 9 /// 构造函数注入服务10 /// </summary>11 /// <param name="userBusiness"></param>12 public UserController(IUserBusiness userBusiness)13 {14 iUserBusiness = userBusiness;15 }16 17 /// <summary>18 /// 获取所有用户19 /// </summary>20 /// <returns></returns>21 [HttpGet]22 //[Route("AllUser")]23 public IEnumerable<User> GetAllUser()24 {25 return iUserBusiness.RetriveAllEntity();26 }27 28 /// <summary>29 /// 根据主键Id获取一个用户30 /// </summary>31 /// <param name="id">主键Id</param>32 /// <returns></returns>33 [HttpGet("{id}")]34 public User GetOneUser(int id)35 {36 return iUserBusiness.RetriveOneEntityById(id);37 }38 39 /// <summary>40 /// 新增用户41 /// </summary>42 /// <param name="user">用户实体</param>43 /// <returns></returns>44 [HttpPost]45 public bool CreateUser([FromBody]User user)46 {47 return iUserBusiness.CreateEntity(user);48 }49 50 /// <summary>51 /// 修改用户52 /// </summary>53 /// <param name="id">主键Id</param>54 /// <param name="user">用户实体</param>55 /// <returns></returns>56 [HttpPut("{id}")]57 public bool UpdateUser(int id, [FromBody]User user)58 {59 user.Id = id;60 return iUserBusiness.UpdateEntity(user);61 }62 63 /// <summary>64 /// 根据主键Id删除一个用户65 /// </summary>66 /// <param name="id">主键Id</param>67 /// <returns></returns>68 [HttpDelete("{id}")]69 public bool DeleteUser(int id)70 {71 return iUserBusiness.DeleteEntityById(id);72 }73 }
5、大功告成,Ctrl+F5运行你的程序,并试着执行添加,修改和查询的Api,体验Swagger带给你的方便之处。
写在最后:这篇文章只是介绍了它们俩最简单最基本的用法,如果其中说法有误的,还望各位前辈多多指点,更多的内容还需要后续的继续积累和学习,加油!
ASP.NET Core:使用Dapper和SwaggerUI来丰富你的系统框架