.net core 使用Dapper

一、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