.net core使用Dapper

一、记录一下.NET core API下使用dapper的方法。

1. 引入两个Nuget包：

Dapper 这个是dapper的主要引用包
System.Data.SqlClient  这个包主要是用来使用SQL Server的时候使用的，如果是使用MySQL，就不能使用这个。

2. 添加配置文件：

"ConnectionStrings": {"DefaultConnection": "Server=106.55.75.66,1433;database=StudentID;uid=sa;pwd=qwe20211114;Pooling=True;Max Pool Size=13772000;Connect Timeout=300;"
  },

3. 创建读取Config类

using Microsoft.Extensions.Configuration;

namespace zhcx.Common
{
    public class DataBaseConfig
    {
        private string sqlserverconnectionstring;
        public string SqlServiceconnectionstring
        {
            get { return sqlserverconnectionstring; }
        }
        public DataBaseConfig(IConfiguration Configuration)
        {
            sqlserverconnectionstring = Configuration.GetConnectionString("DefaultConnection");
        }
    }
}

4. 封装Dapper使用类

using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace zhcx.Common
{
    public class SQLDapperHelper : IDapper
    {
        public static IConfiguration _Configuration { get; set; }
        public string _connectionString;
        public SQLDapperHelper(IConfiguration Configuration)
        {
            _Configuration = Configuration;
            /// 数据库连接字符串
            _connectionString = new DataBaseConfig(_Configuration).SqlServiceconnectionstring;
        }
        private IDbConnection _connection { get; set; }
        public IDbConnection Connection
        {
            get
            {
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    _connection = new SqlConnection(_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();
                }
            }
        }
    }
}

5. 提供对外的接口

using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace zhcx.Common
{
    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;
    }
}

6. 最最最重要的注入

 services.AddControllers();
            #region dapper
            services.AddScoped<IDapper, SQLDapperHelper>();
            #endregion

7. 使用Dapper

//程序中注入 
private readonly IConnectionMultiplexer _redis;
        public IDapper _IDapper;
        public StatisticalServices(IConnectionMultiplexer redis, IDapper IDapper)
        {
            _redis = redis;
            _IDapper = IDapper;
        }

// 方法中使用
string getmaxIdsql = "SELECT max(Id) maxid FROM RB_Amazon_product_base(NOLOCK) WHERE CustomerID=1 AND IsOffShelf=0 AND IsDelete=0 ";
            var maxdts = _IDapper.QueryList<string>(getmaxIdsql, null);

 

以上就是相关的使用，谢谢学习！！！共同进步