- 安装NuGet包:Dapper和MySql.Data,如果是sql server数据库则安装System.Data.SqlClient
- 新建Dapper文件夹,里面新建BaseDBContext上下文类
代码内容:
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication1.Dapper
{
public class BaseDBContext
{
private string connectionString;
public IDbConnection Connection { get; set; }
//mysql数据库
public BaseDBContext(string settings)
{
connectionString = settings;
Connection = new MySqlConnection(connectionString);
}
//sql server数据库
//public BaseDBContext(string settings)
//{
// connectionString = settings;
// Connection = new SqlConnection(connectionString);
//}
}
}
- 新建Models文件夹,在里面新建一个表类:
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace WebApplication1.Models
{
public class City
{
[Key]
public int Id { get; set; }
[Column(TypeName = "nvarchar(100)")]
public string Name { get; set; }
[Column(TypeName = "nvarchar(100)")]
public string AreaCode { get; set; }
}
}
在数据库也准备属性表名一样的表:
4. 新建接口文件夹IRepository用于存放接口,新建IBaseRepository类,封装基础增删改查:
namespace WebApplication1.IRepository
{
public interface IBaseRepository<T>
{
Task Insert(T entity, string insertSql);
Task Update(T entity, string updateSql);
Task Delete(int Id, string deleteSql);
Task<List<T>> SelectAsync(string selectSql);
Task<T> GetOne(int Id, string selectOneSql);
Task<List<T>> GetList(int Id, string selectSql);
}
}
再新建ICityRepository表接口类,继承IBaseRepository:
using WebApplication1.Models;
namespace WebApplication1.IRepository
{
public interface ICityRepository:IBaseRepository<City>
{
//查询所有数据
public Task<List<City>> query();
}
}
- 新建实现接口的文件夹Repository用于存放实现接口的类,在下面新建两个类BaseRepository,CityRepository用于实现刚才声明的接口
BaseRepository继承IBaseRepository,实现IBaseRepository的接口,代码如下:
using Dapper;
using System.Data;
using WebApplication1.Dapper;
using WebApplication1.IRepository;
namespace WebApplication1.Repository
{
public class BaseRepository<T> : IBaseRepository<T> where T : class, new()
{
public BaseDBContext _baseDB;
public BaseRepository(BaseDBContext baseDB)
{
_baseDB = baseDB;
}
public async Task Delete(int Id, string deleteSql)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
await dbConnection.ExecuteAsync(deleteSql, new { Id = Id });
}
}
public async Task<T> GetOne(int Id, string selectOneSql)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
var result = await dbConnection.QueryFirstOrDefaultAsync<T>(selectOneSql, new { Id = Id });
return result;
}
}
public async Task<List<T>> GetList(int Id, string selectSql)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
return await Task.Run(() => dbConnection.Query<T>(selectSql, new { Id = Id }).ToList());
}
}
public async Task Insert(T entity, string insertSql)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
await dbConnection.QueryFirstOrDefaultAsync<T>(insertSql, entity);
}
}
public async Task<List<T>> SelectAsync(string selectSql)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
return await Task.Run(() => dbConnection.Query<T>(selectSql).ToList());
}
}
public async Task Update(T entity, string updateSql)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
await dbConnection.ExecuteAsync(updateSql, entity);
}
}
public async Task<List<T>> GetByDynamicParams(string sqlText, DynamicParameters dynamicParams)
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
return await Task.Run(() => dbConnection.Query<T>(sqlText, dynamicParams).ToList());
}
}
}
}
CityRepository继承ICityRepository,实现ICityRepository的接口,代码如下:
using Dapper;
using System.Data;
using WebApplication1.Dapper;
using WebApplication1.IRepository;
using WebApplication1.Models;
namespace WebApplication1.Repository
{
public class CityRepository : BaseRepository<City>, ICityRepository
{
public BaseDBContext _baseDB;
public CityRepository(BaseDBContext baseDB) : base(baseDB)
{
_baseDB = baseDB;
}
public async Task<List<City>> query()
{
using (IDbConnection dbConnection = _baseDB.Connection)
{
dbConnection.Open();
return await Task.Run(() => dbConnection.Query<City>("select * from City").ToList());
}
}
}
}
- 在appsettings.json中添加数据库连接字符串,下面我用的是mysql的连接串:
"ConnectionStrings": {
"DbConnectionString": " Data Source=localhost;Database=efcodetest;AllowLoadLocalInfile=true;User ID=root;Password=123456;allowPublicKeyRetrieval=true;pooling=true;CharSet=utf8;port=3306;sslmode=none;"
}
6. 依赖注入,在Program.cs里面添加刚才声明实现几个类的依赖注入:
builder.Services.AddSingleton(sp => new BaseDBContext(
builder.Configuration.GetConnectionString("DbConnectionString")
));
builder.Services.AddScoped<ICityRepository, CityRepository>();
7. 新建api测试:
代码:
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using WebApplication1.IRepository;
namespace WebApplication1.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class CityController : ControllerBase
{
private ICityRepository Repository;
public CityController(ICityRepository _repository)
{
Repository = _repository;
}
[HttpGet("query")]
public async Task<object> query() => await Repository.query();
}
}
请求结果和数据库对比: