KSO-.NETCore中使用安装dapper和Dapper增删改查的二次封装

1.Dapper简介与优势

Dapper是小巧玲珑的ORM框架
Dapper 是一个简单的 .NET 对象映射器，在速度方面拥有微型 ORM之王的称号，几乎与使用原始 ADO.NET 数据读取器一样快。ORM是一个对象关系映射器，负责数据库和编程语言之间的映射。
速度快。最接近ADO.NET的性能的ORM框架
高性能、易排查、易运维、灵活可控
缺点
和EF相比，手写sql当修改表结构不易发现bug。
习惯了EF后再来使用Dapper，会很难适应那种没有了强类型的安全感。不过可以用单元测和心细来避免。
支持多种数据库，包括Postgresql,SqlServer,sqlite,oracle, MySQL,

2.引用包

Dapper 通过 NuGet 安装：https ://www.nuget.org/packages/Dapper

PM> Install-Package Dapper

 
PM> Install-Package MySql.Data

3.配置文件进行添加链接字符串

Data Source=60.205.178.29;Database=DB;AllowLoadLocalInfile=true;User ID=root;Password=。;allowPublicKeyRetrieval=true;pooling=true;CharSet=utf8;port=3306;sslmode=none;

4.在项目Common层增加DapperHelp帮助类

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

using Dapper;

namespace dappers.DapperHelp
{
    public static class DbContext
    {
        // 获取开启数据库的连接
        private static IDbConnection Db
        {
            get
            {
                //创建单一实例
                DapperHelper.GetInstance();
                return DapperHelper.OpenCurrentDbConnection();
            }
        }

        /// <summary>
        /// 查出一条记录的实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault<T>(string sql, object param = null)
        {
            return Db.QueryFirstOrDefault<T>(sql, param);
        }

        public static Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null)
        {
            return Db.QueryFirstOrDefaultAsync<T>(sql, param);
        }
        /// <summary>
        /// 查出多条记录的实体泛型集合
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        public static IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }

        public static Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }

        public static int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.Execute(sql, param, transaction, commandTimeout, commandType);
        }

        public static Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
        }

        public static T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
        }

        public static Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 同时查询多张表数据（高级查询）
        /// "select *from K_City;select *from K_Area";
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlMapper.GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.QueryMultiple(sql, param, transaction, commandTimeout, commandType);
        }
        public static Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.QueryMultipleAsync(sql, param, transaction, commandTimeout, commandType);
        }
    }
}

单例创建Dapper实例

 
namespace dappers.DapperHelp
{
    public class DapperContext
    {
        /// 数据库连接名
        private static string _connection = string.Empty;

        /// 获取连接名        
        private static string Connection
        {
            get { return _connection; }
            //set { _connection = value; }
        }

        /// 返回连接实例        
        private static IDbConnection dbConnection = null;

        /// 静态变量保存类的实例        
        private static DapperContext uniqueInstance;

        /// 定义一个标识确保线程同步        
        private static readonly object locker = new object();
        /// <summary>
        /// 私有构造方法，使外界不能创建该类的实例，以便实现单例模式
        /// </summary>
        private DapperContext()
        {
            // 这里为了方便演示直接写的字符串，实例项目中可以将连接字符串放在配置文件中，再进行读取。
            _connection = @"server=.;uid=sa;pwd=sasasa;database=Dapper";
        }

        /// <summary>
        /// 获取实例，这里为单例模式，保证只存在一个实例
        /// </summary>
        /// <returns></returns>
        public static DapperContext GetInstance()
        {
            // 双重锁定实现单例模式，在外层加个判空条件主要是为了减少加锁、释放锁的不必要的损耗
            if (uniqueInstance == null)
            {
                lock (locker)
                {
                    if (uniqueInstance == null)
                    {
                        uniqueInstance = new DapperContext();
                    }
                }
            }
            return uniqueInstance;
        }


        /// <summary>
        /// 创建数据库连接对象并打开链接
        /// </summary>
        /// <returns></returns>
        public static IDbConnection OpenCurrentDbConnection()
        {
            if (dbConnection == null)
            {
                dbConnection = new SqlConnection(Connection);
            }
            //判断连接状态
            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }
            return dbConnection;
        }
    }
}

获取实例

DapperContext.GetInstance();
获取之后，就是进行封装了，首先进行创建仓储层，也就是IBasicRepository ， BasicRepository，
这两个进行接口与实现的具体封装，
请参考上面得DapperHelper 帮组类库，进行封装，也可以DapperHelper 
做成静态类，直接在需要的时候进行使用，大家根据项目中的实际情况进行分析