用NetCore 和Dapper 和mySql做一个简单的实例，

一准备工作

1：VS2017+windos系统，也可以用其他的操作系统和工具

2：一台Cenetos的虚拟机或者虚拟机

二：开始

1：用微软官方的netCore的ToDo项目改造，项目的主体结构如下图，源连接

https://docs.microsoft.com/zh-cn/aspnet/core/tutorials/first-web-api

 

1：打开Nuget控制台，安装 MySQL官方.NET Core驱动,并且支持 EF Core

 >Install-Package SapientGuardian.MySql.Data -Pre

2：打开Nuget控制台，安装Dapper

>Install-Package Dapper -Pre

3：在Models文件夹下新建ToDoItem 的实体

public class ToDoItem    {        [Required]        public string ID { get; set; }        [Required]        public string Name { get; set; }        [Required]        public string Notes { get; set; }        public bool Done { get; set; }    }

4：在appsettings.json里面配置MySql的数据库连接字符串，然后点开appsettings.json的属性将“复制到输出目录”项的值改为“始终复制”，

SslMode=none的作用是解决连接的时候报SSL错误的

"ConnectionStrings": {    "SqlServerConnection": "Server=(LocalDb)\\MSSQLLocalDB, Database=test",    "MySqlConnection": "Server=自己的服务器;Database=test;User ID=root;Password=111111;SslMode=none"  }

 5：在Common下面新建一个AppConfigurtaionServices的类，用于读取appsettings.json里面的连接字符串(appsettings.json属性如果没有选中始终复制，在代码运行的时候，

IConfiguration 会报错

)

public class AppConfigurtaionServices    {        public static IConfiguration Configuration { get; set; }        static AppConfigurtaionServices()        {            Configuration = new ConfigurationBuilder()            .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })            .Build();        }    }

6：新建一个Interfaces文件夹，然后下面建一个IToDoRepository的接口类

public interface IToDoRepository    {        bool DoesItemExist(string id);        IEnumerable
     
       All();        ToDoItem Find(string id);        int Insert(ToDoItem item);        int Update(ToDoItem item);        int Delete(string id);    }
     

　　

7：新建一个Services然后下面新建ToDoRepository类 是我们的数据仓储类 记得要引用(using Dapper;和 using MySql.Data.MySqlClient;)

public class ToDoRepository : IToDoRepository    {        private  static  string DefaultSqlConnectionString = "";        public ToDoRepository()        {            DefaultSqlConnectionString = AppConfigurtaionServices.Configuration.GetConnectionString("MySqlConnection");             }        public static IDbConnection GetSqlConnection(string sqlConnectionString = null)        {            if (string.IsNullOrWhiteSpace(sqlConnectionString))            {                sqlConnectionString = DefaultSqlConnectionString;            }            IDbConnection conn = new MySqlConnection(sqlConnectionString);            conn.Open();            return conn;        }        /// 
        /// 获取全部        /// 
        /// 
             public IEnumerable
     
       All()        {            using (IDbConnection conn = GetSqlConnection())            {                string strsql = "select * from ToDoItem";                return conn.Query
      
       (strsql, null);            }        }        /// 
       
        /// 查询是否存在        /// 
        /// 
               /// 
               public bool DoesItemExist(string id)        {            using (IDbConnection conn = GetSqlConnection())            {                int cout = conn.Query
       
        ("select count(*) from ToDoItem where  ID=@ID", new { ID = id }).FirstOrDefault();                if (cout > 0)                    return true;                else                    return false;            }        }        /// 
        
        /// 删除        /// 
        /// 
                /// 
                public int Delete(string id)        {            using (IDbConnection conn = GetSqlConnection())            {                string strsql = "DELETE from ToDoItem where ID=@ID";                return conn.Execute(strsql, new { ID = id });            }        }        /// 
        
        /// 查询整个对象        /// 
        /// 
                /// 
                public ToDoItem Find(string id)        {            using (IDbConnection conn = GetSqlConnection())            {                string strsql = "select * from  ToDoItem where ID=@ID";                return conn.Query
        
         (strsql, new { ID = id }).FirstOrDefault();            }        }        /// 
         
        /// 新增项        /// 
        /// 
                 /// 
                 public int Insert(ToDoItem item)        {            using (IDbConnection conn = GetSqlConnection())            {                string strsql = "INSERT into ToDoItem(ID,Name,Notes,Done)values(@ID,@Name,@Notes,@Done)";                return conn.Execute(strsql, item);            }        }        /// 
         
        /// 修改        /// 
        /// 
                 /// 
                 public int Update(ToDoItem item)        {            using (IDbConnection conn = GetSqlConnection())            {                string strsql = " UPDATE ToDoItem SET Name=@Name,Notes=@Notes,Done=@Done where ID=@ID";                return conn.Execute(strsql, item);            }        }         }
        
       
      
     

 8：在Controller下面新增一个ToDoItemsController的控制器，记得添加相关的命名空间

[Route("api/[controller]")]    public class ToDoItemsController : Controller    {        private readonly IToDoRepository _toDoRepository;        public ToDoItemsController(IToDoRepository toDoRepository)        {            _toDoRepository = toDoRepository;        }        /// 
        /// 获取数据        /// 
        /// 
             [HttpGet]        public IActionResult List()        {            return Ok(_toDoRepository.All());        }        /// 
        /// 新增项        /// 
        ///         /// 
             [HttpPost]        public IActionResult Create([FromBody] ToDoItem item)        {            try            {                if (item == null || !ModelState.IsValid)                {                    return BadRequest("没有通过验证");                }                bool itemExists = _toDoRepository.DoesItemExist(item.ID);                if (itemExists)                {                    return StatusCode(StatusCodes.Status409Conflict, "已经存在此项");                }                _toDoRepository.Insert(item);            }            catch (Exception)            {                return BadRequest("创建失败");            }            return Ok(item);        }        /// 
        /// 修改项        /// 
        ///         /// 
             [HttpPut]        public IActionResult Edit([FromBody] ToDoItem item)        {            try            {                if(item==null || !ModelState.IsValid)                    return BadRequest("没有通过必填验证");                var existingItem = _toDoRepository.Find(item.ID);                if(existingItem==null)                {                    return NotFound("没有发现此记录");                }                _toDoRepository.Update(item);            }            catch(Exception)            {                return BadRequest("修改失败");            }            return NoContent();        }        /// 
        /// 删除        /// 
        ///         /// 
             [HttpDelete("{id}")]        public IActionResult Delete(string id)        {            try            {                var item = _toDoRepository.Find(id);                if (item == null)                    return NotFound("没有此记录");                _toDoRepository.Delete(id);            }            catch(Exception )            {                return BadRequest("删除失败");            }            return NoContent();        }         }

　9：在ConfigureServices里配置IToDoRepository 和ToDoRepository服务

services.AddSingleton
     
      ();
     

 10：配置Swagger(丝袜哥)具体Swagger的基础知识可以连接到

https://docs.microsoft.com/zh-cn/aspnet/core/tutorials/web-api-help-pages-using-swagger?tabs=visual-studio

10.1：在Nuget控制台添加引用

   >Install-Package Swashbuckle.AspNetCore

10.2:在Startup类中配置Swagger

10.2.1：在ConfigureServices方法里面添加Swagger服务

//添加Swagger服务            services.AddSwaggerGen(c =>            {                c.SwaggerDoc("v1", new Info                {                    Version = "v1",                    Title = "ToDo API",                    Description = "A simple example ASP.NET Core Web API",                    TermsOfService = "None",                    Contact = new Contact { Name = "Shayne Boyer", Email = "", Url = "https://twitter.com/spboyer" },                    License = new License { Name = "Use under LICX", Url = "https://example.com/license" }                });                var basePath = PlatformServices.Default.Application.ApplicationBasePath;                var xmlPath = Path.Combine(basePath, "ToDoApi.xml");                c.IncludeXmlComments(xmlPath);            });

  10.2.2：在Configure配置Swagger服务

app.UseSwagger();//配置Swagger服务app.UseSwaggerUI(c =>{ c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");});

 10.3：最终的Startup类如下

using Microsoft.AspNetCore.Builder;using Microsoft.AspNetCore.Hosting;using Microsoft.Extensions.Configuration;using Microsoft.Extensions.DependencyInjection;using Microsoft.Extensions.PlatformAbstractions;using Swashbuckle.AspNetCore.Swagger;using System.IO;using ToDoApi.Interfaces;namespace ToDoApi{    public class Startup    {        public Startup(IConfiguration configuration)        {            Configuration = configuration;        }        public IConfiguration Configuration { get; }        //添加服务        public void ConfigureServices(IServiceCollection services)        {            services.AddMvc();               //添加Swagger服务            services.AddSwaggerGen(c =>            {                c.SwaggerDoc("v1", new Info                {                    Version = "v1",                    Title = "ToDo API",                    Description = "A simple example ASP.NET Core Web API",                    TermsOfService = "None",                    Contact = new Contact { Name = "Shayne Boyer", Email = "", Url = "https://twitter.com/spboyer" },                    License = new License { Name = "Use under LICX", Url = "https://example.com/license" }                });                var basePath = PlatformServices.Default.Application.ApplicationBasePath;                var xmlPath = Path.Combine(basePath, "ToDoApi.xml");                c.IncludeXmlComments(xmlPath);            });            services.AddSingleton
     
      ();        }        // 配置服务        public void Configure(IApplicationBuilder app, IHostingEnvironment env)        {            app.UseSwagger();            //配置Swagger服务            app.UseSwaggerUI(c =>            {                c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");            });            //配置开发环境            if (env.IsDevelopment())            {                app.UseDeveloperExceptionPage();            }            app.UseMvc();        }    }}
     

10.4：启动项设置为swagger启动 

在launchSettings.json做如下修改

这样你的项目用IIS Express启动来就是swagger界面

11：最终效果

11.2：测试获取数据的方法

12:对NetCore的理解还很浅，只是做了一个简单的demo希望能帮到你，只是写了mysql的，如果是要用SqlServer,则修改读取SqlServerConnection的数据连接然后把数据仓储里面的 MySql.Data.MySqlClien改成Sql server的就可以了

 13：demo连接：

https://files.cnblogs.com/files/gouguo/ToDoApi.rar