一、Dapper
Dapper 是一个轻量级ORM框架，在项目中如果对性能比较看中，Dapper是一个不错的选择。接下来我们就来看看如何在项目中使用Dapper。
首先先安装Dapper的Nuget包：
下载Nuget包.png
二、使用Dapper
2.1、获取连接信息
数据库连接字符写在appsettings.json文件中：
"ConnectionStrings": {
"DefaultConnection": "Server=127.0.0.1; Port=3306; Database=huangdemo; user=root; password=123456; charset=utf8; Convert Zero Datetime=True"
}
新建一个DataBaseConfig类获取连接信息：
public class DataBaseConfig
{
private string mysqlconnectionstring;
public string Mysqlconnectionstring {
get { return mysqlconnectionstring; }
}
public DataBaseConfig(IConfiguration Configuration)
{
mysqlconnectionstring = Configuration.GetConnectionString("DefaultConnection");
}
}
2.2、定义一个操作接口IDapper
写了如下几个操作接口:
public interface IDapper
{
/// 获取数据列表
List<T> QueryList<T>(string cmd, object param, CommandType? commandType = null, bool beginTransaction = false) where T : class;
///异步获取数据列表
Task<IEnumerable<T>> QueryAsync<T>(string cmd, object param = null, CommandType? commandType = null, IDbTransaction transaction = null) where T : class;
}
再定义一个接口实现类SQLDapperHelper，实现IDapper。然后SQLDapperHelper构造方法里注入IConfiguration对象用来读取数据库连接信息 ；类里还声明一个IDbConnection类型的数据库连接对象Connection，用来操作数据库。
public class SQLDapperHelper : IDapper
{
public static IConfiguration _Configuration { get; set; }
public string _connectionString;
public SQLDapperHelper(IConfiguration Configuration)
{
_Configuration = Configuration;
/// 数据库连接字符串
_connectionString = new DataBaseConfig(_Configuration).Mysqlconnectionstring;
}
private IDbConnection _connection { get; set; }
public IDbConnection Connection
{
get
{
if (_connection == null || _connection.State == ConnectionState.Closed)
{
_connection = new MySqlConnection(_connectionString);
}
return _connection;
}
}
/// 获取数据列表
public List<T> QueryList<T>(string cmd, object param, CommandType? commandType = null, bool beginTransaction = false) where T : class
{
try
{
return Execute((conn, dbTransaction) =>
{
return conn.Query<T>(cmd, param, dbTransaction, commandType: commandType ?? CommandType.Text).ToList();
}, beginTransaction);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// 异步获取数据列表
public async Task<IEnumerable<T>> QueryAsync<T>(string cmd, object param = null, CommandType? commandType = null, IDbTransaction transaction = null) where T : class
{
try
{
using (IDbConnection conn = Connection)
{
return await conn.QueryAsync<T>(cmd, param, transaction, commandType: commandType ?? CommandType.Text);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private T Execute<T>(Func<IDbConnection, IDbTransaction, T> func, bool beginTransaction = false, bool disposeConn = true)
{
IDbTransaction dbTransaction = null;
if (beginTransaction)
{
Connection.Open();
dbTransaction = Connection.BeginTransaction();
}
try
{
T reslutT = func(Connection, dbTransaction);
dbTransaction?.Commit();
return reslutT;
}
catch (Exception ex)
{
dbTransaction?.Rollback();
Connection.Dispose();
throw ex;
}
finally
{
if (disposeConn)
{
Connection.Dispose();
}
}
}
}
最后别忘了在startup文件中绑定：
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddScoped<IDapper, SQLDapperHelper>();
}
2.3使用
在Controller中测试一下
[ApiController]
[Route("api/[controller]")]
public class MydemoController : ControllerBase
{
public IDapper _IDapper;
public MydemoController(IDapper Idapper)
{
_IDapper = Idapper;
}
[HttpGet]
[Route("FirstApi")]
public async Task<ActionResult<string>> FirstApi(int age)
{
var aaa = _IDapper.QueryList<object>( "select * from stu where age =@age;",new { age=age});
}
}
image.png
image.png
