.net core webapi dapper 使用案例

​1、使用nuget下载对应的dapper 包，如下：​

Packages	NuGet
Dapper.Extension.AspNetCore	​​​
Dapper.Extension.AspNetCore.MySql	​​​

​2、在Startup 类中的  ConfigureServices 方法中加入如下代码：​

services.AddDapperForMySql(options =>
            {
                options.ConnectionString = Configuration.GetConnectionString("MySql");
            });

​3、在appsetting.json文件加入数据库连接配置，如下：​

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
     "MySql": "server=192.168.1.238;database=rfid_db;user id=root;password=123456;port=3306;"
  }

}

​4、 添加SysUser model 如下：​

[Table("SysUsers")]
    public class SysUser
    {
        [Key]
        public int uId { get; set; }

        public string userName { get; set; }

        public string loginName { get; set; }

        public string pwd { get; set; }
    }

​5、添加 ISysUserService 接口：​

public interface ISysUserService
    {
        List<SysUser> GetList(int pageIndex, int pageSize);

        long Add(SysUser user);

        bool Update(SysUser user);

        bool Del(SysUser user);

        bool TestTran(SysUser user);

    }

​6、添加对ISysUserService  的实现类 SysUserService ​

using coreTest.IService;
using coreTest.Model;
using coreTest.Service.Mysql;
using Dapper.Extension.AspNetCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace coreTest.Service
{
    public class SysUserService : ISysUserService
    {

        private readonly IDapper dapper;

        public SysUserService(IDapper _dapper)
        {
            dapper = _dapper;
        }


        /// <summary>
        /// 返回值是对应表添加的对应记录的主键值
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public long Add(SysUser user)
        {
            var res = dapper.Insert<SysUser>(user);
            return res;

        }

        public bool Del(SysUser user)
        {
            return dapper.Delete<SysUser>(user);

        }

        public List<SysUser> GetList(int pageIndex, int pageSize)
        {
            var result = dapper.QueryPage<SysUser>(SysUserSql.getCountSql, SysUserSql.getListSql, pageIndex, pageSize);
            return result.Contents;
        }

        public bool Update(SysUser user)
        {
            return dapper.Update<SysUser>(user);
        }

        public bool TestTran(SysUser user)
        {
            bool result = false;

            using (var transaction = dapper.BeginTransaction())
            {
                try
                {
                    int b = 0;
                    dapper.Insert<SysUser>(user);
                    int z = 1 / b;
                    dapper.Update<SysUser>(user);
                    transaction.Commit();

                    result = true;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    transaction.Rollback();
                    result = false;

                }
            }
            return result;
        }
    }
}

​7、添加SysUserController 控制器：​

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using coreTest.IService;
using coreTest.Model;
using Dapper.Extension.AspNetCore;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;

namespace coreTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class SysUserController : ControllerBase
    {
        private readonly ILogger<SysUserController> logger;
        private readonly IDapper dapper;
        private readonly ISysUserService sysUserService;

        public SysUserController(ILogger<SysUserController> _logger, IDapper _dapper, ISysUserService _sysUserService)
        {

            logger = _logger;
            dapper = _dapper;
            sysUserService = _sysUserService;
        }

        [HttpGet]
        [Route("Index")]
        public JsonResult Index(int pageIndex = 1, int pageSize = 10)
        {
            var result = sysUserService.GetList(pageIndex, pageSize);
            return new JsonResult(result);
        }

        [HttpGet]
        [Route("Del")]
        public JsonResult Del(int uId)
        {
            SysUser user = new SysUser()
            {
                uId = uId
            };
            var result = sysUserService.Del(user);
            return new JsonResult(result);
        }


        [HttpPost]
        [Route("Add")]
        public JsonResult Add(SysUser sysUser)
        {

            var result = sysUserService.Add(sysUser);
            return new JsonResult(result);
        }



        [HttpPost]
        [Route("Update")]
        public JsonResult Update(SysUser sysUser)
        {

            var result = sysUserService.Update(sysUser);
            return new JsonResult(result);
        }

        /// <summary>
        /// 测试事务
        /// </summary>
        /// <param name="sysUser"></param>
        /// <returns></returns>

        [HttpPost]
        [Route("TestTran")]
        public JsonResult TestTran(SysUser sysUser)
        {
            //测试事务
            var result = sysUserService.TestTran(sysUser);
            return new JsonResult(result);
        }

    }
}

​8、在Startup 类中的 ConfigureServices 方法中添加如下代码：​

services.AddTransient<IModuleService, ModuleService>();
            services.AddTransient<ISysUserService, SysUserService>();