ASP.NET Core WebApi Dapper使用

Dapper使用

1、安装包

VS CODE

#dapper
dotnet add package Dapper --version 2.1.35

#mysql和EF Core混用不用安装
dotnet add package MySql.Data --version 8.3.0

#sqlserver和EF Core混用不用安装
dotnet add package System.Data.SqlClient --version 4.8.6

#sqlite
dotnet add package Microsoft.Data.Sqlite --version 6.0.28

VS 2022

#dapper
NuGet\Install-Package Dapper -Version 2.1.35

#mysql和EF Core混用不用安装
NuGet\Install-Package MySql.Data -Version 8.3.0

#sqlserver和EF Core混用不用安装
NuGet\Install-Package System.Data.SqlClient -Version 4.8.6

#sqlite
NuGet\Install-Package Microsoft.Data.Sqlite -Version 6.0.28

2、案例

sqlserver

//sqlserver
string sqlConne = "Data Source=127.0.0.1;Initial Catalog=MyData;Persist Security Info=True;User ID=sa;Password=Yang@0871,.;Connect Timeout=500;";

using IDbConnection db =new SqlConnection(sqlConne);

db.Open();
var models=db.Query("select  * from persons").ToList();
foreach (var model in models)
{
    Console.WriteLine(model);
}

mysql

//mysql
dotnet add package MySql.Data --version 8.3.0
NuGet\Install-Package MySql.Data -Version 8.3.0
string mysqlConne = "Server=127.0.0.1;port=33060;Database=netcoredev; User=root;Password=123456";
using IDbConnection mydb = new MySqlConnection(mysqlConne);
mydb.Open();
//查询

 var m=mydb.Query("select * from Sys_Log limit 10").ToList();
//var m=mydb.Query<SysLog>("select * from Sys_Log limit 10").ToList();
foreach (var model in m)
{
    Console.WriteLine(model.Id);
}


//插入
mydb.Execute("INSERT INTO Sys_City(CityId,CityCode,CityName,ProvinceCode)VALUES(@CityId,@CityCode,@CityName,@ProvinceCode)", new { CityId=530871, CityCode=5308, CityName="昆明", ProvinceCode=11 });
//更新
mydb.Execute("UPDATE Sys_City SET CityName=@CityName,ProvinceCode=@ProvinceCode Where CityId=@CityID", new { CityId=530871,CityName="昆明市", ProvinceCode=53});

sqlite

//NuGet\Install-Package Microsoft.Data.Sqlite

//sqlite
string sqlConne = "Data Source=test.db;";
using IDbConnection mydb = new SqliteConnection(sqlConne);
mydb.Open();

var m = mydb.Query("select * from Persons limit 10").ToList();

foreach (var model in m)
{
    Console.WriteLine(model);
}

3、依赖注入

注入依赖使用哪种数据库就注入哪一个数据库，不要全部注入，全部注入做下判断，否则会引起冲突

Program中注入

#region sqlserver

//EF Core配置
builder.Services.AddDbContext<StudyContext>(x =>
{
    x.UseSqlServer(builder.Configuration.GetConnectionString("StudyContext"));
});

//Dapper配置
builder.Services.AddTransient<IDbConnection>(x => new SqlConnection(builder.Configuration.GetConnectionString("StudyContext")));

#endregion

#region sqlite

//EF Core配置
builder.Services.AddDbContext<StudyContext>(x =>
{
    x.UseSqlite(builder.Configuration.GetConnectionString("StudyContext"));
});
//Dapper配置
builder.Services.AddTransient<IDbConnection>(x => new SqliteConnection(builder.Configuration.GetConnectionString("StudyContext")));
#endregion

#region mysql


#pragma warning disable CS8602 // 解引用可能出现空引用。
//mysql版本获取
string[] version = builder.Configuration.GetConnectionString("VerSion").Split(new char[] { '.' });
#pragma warning restore CS8602 // 解引用可能出现空引用。
//EF Core
builder.Services.AddDbContext<StudyContext>(option =>
option.UseMySql(builder.Configuration.GetConnectionString("StudyContext"), new MySqlServerVersion(new Version(Convert.ToInt32(version[0]), Convert.ToInt32(version[1]), Convert.ToInt32(version[2])))));

//Dapper配置
builder.Services.AddTransient<IDbConnection>(x => new MySqlConnection(builder.Configuration.GetConnectionString("StudyContext")));
#endregion

StartUp中注入

#region sqlserver

//EF Core配置
services.AddDbContext<StudyContext>(x =>
{
    x.UseSqlServer(Configuration.GetConnectionString("StudyContext"));
});

//Dapper配置
services.AddTransient<IDbConnection>(x => new SqlConnection(Configuration.GetConnectionString("StudyContext")));

#endregion

#region sqlite

//EF Core配置
services.AddDbContext<StudyContext>(x =>
{
    x.UseSqlite(builder.Configuration.GetConnectionString("StudyContext"));
});
//Dapper配置
services.AddTransient<IDbConnection>(x => new SqliteConnection(Configuration.GetConnectionString("StudyContext")));
#endregion

#region mysql


#pragma warning disable CS8602 // 解引用可能出现空引用。
//mysql版本获取
string[] version = Configuration.GetConnectionString("VerSion").Split(new char[] { '.' });
#pragma warning restore CS8602 // 解引用可能出现空引用。
//EF Core
services.AddDbContext<StudyContext>(option =>
option.UseMySql(Configuration.GetConnectionString("StudyContext"), new MySqlServerVersion(new Version(Convert.ToInt32(version[0]), Convert.ToInt32(version[1]), Convert.ToInt32(version[2])))));

//Dapper配置
services.AddTransient<IDbConnection>(x => new MySqlConnection(Configuration.GetConnectionString("StudyContext")));
#endregion

4、在控制器中使用

using BaseEntity;
using BaseWebAPI.Controllers;
using Dapper;
using IServices;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Caching.Memory;
using System.Data;
using System.Text.Json;

namespace BaseWebApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class HomeController : Controller
    {
        private readonly IDbConnection db;
        public PersonsController(IDbConnection _db) 
        {
            db = _db;
    }
        /// <summary>
        /// Dapper查询前10条
        /// </summary>
        /// <returns></returns>
        [HttpGet, Route("GetDapperQuery")]
        [AllowAnonymous]
        public  async Task<IActionResult> GetDapperQuery()
        {
            var list = await db.QueryAsync<TEntity>("select*from person limit 10");
            return Json(new { code = "200", message = "成功", data = list.Result });
        }
        /// <summary>
        /// 传入SQL查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        [HttpGet, Route("GetDapperQuerys")]
        [AllowAnonymous]
        public  async Task<IActionResult> GetDapperQuerys(string sql)
        {
           var list = await db.QueryAsync<TEntity>(sql);
            return Json(new { code = "200", message = "成功", data = list.Result });
        }       
        /// <summary>
        /// 按照ID查找
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        [HttpGet, Route("FindDapperQuery")]
        [AllowAnonymous]
        public  async Task<IActionResult> FindDapperQuery(int Id)
        {
            var list = await db.QueryFirstOrDefaultAsync<TEntity>($"select*from person where ID={Id}");
            return Json(new { code = "200", message = "成功", data = list.Result });
        }        
        /// <summary>
        /// 按照删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        [HttpGet, Route("DapperSqlExecute")]
        [AllowAnonymous]
        public async Task<IActionResult> DapperSqlExecute(int Id)
        {
            var list = await db.ExecuteAsync($"delete from person where ID={Id}");
            return Json(new { code = "200", message = "成功", data = list.Result });
        }
    }
}