.netCore 引用第三方ORM中間件-Dapper

十年河東，十年河西，莫欺少年窮

學無止境，精益求精

可惡的NetCore對DataTable支援的不好，這對于喜歡直接寫SQL的童鞋來說非常難受，是以，在NetCore項目中有必要引入優秀的第三方資料庫中間件，目前市場上比較優秀的第三方EF架構有很多，例如：Sqlsugar、Dapper、

FreeSql 等，當然，微軟也有自己的一套EF架構，EntityFrm 和 EfCore，就效率而言，第三方的架構要完勝微軟的EF架構，真不知道微軟是幹啥吃的、

今天，我們使用Dapper這個輕量級的第三方EF架構。

1.安裝Dapper

　　這裡直接使用Nuget安裝。

 安裝完成後，我們需要對Dapper進行擴充，如下：

using Microsoft.Extensions.Configuration;
using System;
using System.Data;
using System.Data.SqlClient;
using WuAnCommon;

namespace WuAnSqlService
{
    public class DapperHelper
    {
        /// 擷取連接配接字元串        
        private static string Connection= ConfigCommon.Get("WuAnDBContext");
      

        /// 傳回連接配接執行個體        
        private static IDbConnection dbConnection = null;

        /// 靜态變量儲存類的執行個體        
        private static DapperHelper uniqueInstance;

        /// 定義一個辨別確定線程同步        
        private static readonly object locker = new object();
        /// <summary>
        /// 私有構造方法，使外界不能建立該類的執行個體，以便實作單例模式
        /// </summary>
        private DapperHelper()
        {
            // 這裡為了友善示範直接寫的字元串，執行個體項目中可以将連接配接字元串放在配置檔案中，再進行讀取。
           
        }

        /// <summary>
        /// 擷取執行個體，這裡為單例模式，保證隻存在一個執行個體
        /// </summary>
        /// <returns></returns>
        public static DapperHelper GetInstance()
        {
            // 雙重鎖定實作單例模式，在外層加個判空條件主要是為了減少加鎖、釋放鎖的不必要的損耗
            if (uniqueInstance == null)
            {
                lock (locker)
                {
                    if (uniqueInstance == null)
                    {
                        uniqueInstance = new DapperHelper();
                    }
                }
            }
            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;
        }
    }
}      

View Code

上述代碼完成Dapper對資料庫的連接配接，下面還需要Dapper操作資料庫的公共類，如下：

public static class DapperDbContext
    {
        // 擷取開啟資料庫的連接配接
        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);
        }
    }      

public BaseResponse<List<WuAnTestModel>> GetDataDapper()
        {
            try
            {
               return CommonBaseResponse.SetResponse<List<WuAnTestModel>>(DapperDbContext.Query<WuAnTestModel>("select DeviceNo,DeviceSecret from Yk_Device").ToList(),true);
            }
            catch (Exception ex)
            {
                throw ExceptionHelper.GetAggregateException("WuAnService.WuAnTestService", "GetDataDapper", "", ex);
            }
        }