Dapper.Contrib拓展及資料庫生成實體

dapper對實體操作，並返回DataSet和DataTable ...

1.關於Dapper.Contrib

　　Dapper.Contrib是Dapper的一個拓展類庫，Dapper是一個輕量級ORM框架，這個不瞭解的請自行收集資料，本文主要講講，資料庫生成實體，並通過實體進行CURD

2.首先，我們瞭解下Dapper.Contrib需要實體的格式：

using Dapper.Contrib.Extensions;
    [Table("StudentSet")]
    public partial class StudentSet
    {
    	[Key]
        public int Id { get; set; }
    	
        public string StudentName { get; set; }
    	
        public string LoginName { get; set; }
    	
        public string Password { get; set; }
    	
        public int Age { get; set; }
    	
        public string Sex { get; set; }
    	
        public string ClassID { get; set; }
    	
        public string RuleID { get; set; }
    	
        public string Status { get; set; }
    	
        public string UpdateTime { get; set; }
    	
        public string CreateTime { get; set; }
    }

　　可以看出，這裡，需要註明表名，主鍵欄位，並需要引用Dapper.Contrib.Extensions;的命名空間

3.通過EF來從資料庫映射出實體

　　在第二點中，我們發現，定義一個實體來符合Dapper.Contrib要求是非常麻煩的，那麼，我們來看看如何利用EF框架生成

（3.1）想要生成與資料庫對應的實體，就要用到EF的DB First，首先右鍵你的項目，添加新建項，數據，選擇ADO.NET 實體數據模型

添加，來自資料庫EF設計器，之後就是連接你的資料庫，選擇表/視圖/存儲過程/函數等，之後，我們就能生成一個與資料庫對應的實體

這是我隨意創建的一個，我們看一下生成的實體，發現，沒有資料庫註釋說明，也不符合Dapper.Contrib的需要

那麼，我們來改動Model2.tt這個文件，這裡，需要下載一個文件，很小，十幾k，GetSummery.ttinclude下載完了後，放到與edmx文件同級目錄下：

然後，在配置文件添加資料庫連接字串：

<add name="MyDbContext" connectionString="Data Source=yourserver;Initial Catalog=yourdatabase;Integrated Security=True;" providerName="System.Data.SqlClient" />

　　改動這個文件：

包含到我們的Model2.tt文件，這是個T4模板文件

<#@ include file="GetSummery.ttinclude" #>

搜索<#=codeStringGenerator.UsingDirectives(inHeader: false)#>
添加

using System.ComponentModel.DataAnnotations;
/// <summary>
/// <#= getTableSummery(code.Escape(entity)) #>
/// </summary>
在之下
搜索<#=codeStringGenerator.Property(edmProperty)#>
添加
/// <summary>
/// <#= getColumnSummery(code.Escape(entity),code.Escape(edmProperty)) #>
/// </summary>

<#=entity.KeyMembers.Contains(edmProperty.Name)?"[Key]":""#>
在上方
保存

這時候查看，嗯，我們的資料庫欄位說明已經出來了並且符合我們的Dapper.Contrib的需要，這時候可能會報錯，前面說過，你需要using Dapper.Contrib.Extensions;

然後，我們就可以愉快的使用Dapper.Contrib了，這裡不多說，貼出我自己寫的DapperHelper類

using Dapper;
using Dapper.Contrib.Extensions;
using FK.Common;
using FK.Data.Entities;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;

namespace FK.Web.Data
{
    public class DapperHelper
    {
        public static IDbConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DbConnString"].ConnectionString.DESDecrypt());

        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();

        #region 增添記錄

        /// <summary>
        /// 添加一個實體
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="TableName"></param>
        /// <param name="fieldlist"></param>
        /// <param name="key"></param>
        /// <param name="o"></param>
        /// <returns></returns>
        public static int AddByEntity<T>(string TableName, string fieldlist, string key, object o)
        {
            string ColumnsName = string.Empty;
            //fildlist=="*"時,通過實體獲取列名
            if (fieldlist.Trim().Equals("*") || string.IsNullOrEmpty(fieldlist))
            {
                PropertyInfo[] plist = typeof(T).GetProperties();
                string str = "";
                foreach (PropertyInfo p in plist)
                {
                    if (!p.PropertyType.IsGenericType && !p.Name.Equals(key))
                        str += p.Name + ",";

                }
                str = str.Substring(0, str.Length - 1);
                fieldlist = str;
            }
            //根據列名拼接參數名
            string[] arrstr = fieldlist.Split(',');
            foreach (string s in arrstr)
            {
                ColumnsName += "@" + s + ",";
            }
            ColumnsName = ColumnsName.Substring(0, ColumnsName.Length - 1);
            fieldlist = "(" + fieldlist + ")";
            string sql = "Insert into " + TableName + " " + fieldlist + " values (" + ColumnsName + ")";
            //執行插入
            var result = connection.Execute(sql, o);
            return result;
        }

        /// <summary>
        /// 添加一個實體
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="criteria"></param>
        /// <param name="o"></param>
        /// <returns></returns>
        public static int AddByEntity<T>(PageCriteria criteria, object o)
        {
            string ColumnsName = string.Empty;
            //fildlist=="*"時,通過實體獲取列名
            if (criteria.Fields.Trim().Equals("*") || string.IsNullOrEmpty(criteria.Fields))
            {
                PropertyInfo[] plist = typeof(T).GetProperties();
                string str = "";
                foreach (PropertyInfo p in plist)
                {
                    if (!p.PropertyType.IsGenericType && !p.Name.Equals(criteria.PrimaryKey))
                        str += p.Name + ",";

                }
                str = str.Substring(0, str.Length - 1);
                criteria.Fields = str;
            }
            //根據列名拼接參數名
            string[] arrstr = criteria.Fields.Split(',');
            foreach (string s in arrstr)
            {
                ColumnsName += "@" + s + ",";
            }
            ColumnsName = ColumnsName.Substring(0, ColumnsName.Length - 1);
            criteria.Fields = "(" + criteria.Fields + ")";
            string sql = "Insert into " + criteria.TableName + " " + criteria.Fields + " values (" + ColumnsName + ")";
            //執行插入
            var result = connection.Execute(sql, o);
            return result;
        }

        /// <summary>
        /// 事務操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqllist"></param>
        /// <param name="objlist"></param>
        /// <returns></returns>
        public static int TranByEntity<T>(IList<string> sqllist, IList<T> objlist)
        {
            if (sqllist.Count != objlist.Count) return -100;    //參數數量不一致
            connection.Open();
            IDbTransaction transaction = connection.BeginTransaction();
            try
            {
                for (int i = 0; i < sqllist.Count; i++)
                {
                    connection.Execute(sqllist[i], objlist[i], transaction);
                }
                transaction.Commit();
                return 1;
            }
            catch
            {
                transaction.Rollback();
                return -1;
            }
        }

        #endregion

        #region 更新記錄

        /// <summary>
        /// 更新記錄
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="UpdateString">更新的列和值</param>
        /// <param name="WhereString">條件</param>
        /// <param name="Parameters">參數對象</param>
        /// <returns></returns>
        public static int Update(string TableName, string UpdateString, string WhereString, object Parameters = null)
        {
            string sql = "update " + TableName + " set " + UpdateString + " where 1=1 " + WhereString;
            var result = connection.Execute(sql, Parameters);
            return result;
        }

        /// <summary>
        /// 更新記錄
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="UpdateString"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static int Update(PageCriteria criteria,  object Parameters = null)
        {
            string sql = "update " + criteria.TableName + " set " + criteria.Fields + " where 1=1 " + criteria.Condition;
            var result = connection.Execute(sql, Parameters);
            return result;
        }

        /// <summary>
        /// 更新實體
        /// </summary>
        /// <typeparam name="T">實體類型</typeparam>
        /// <param name="newt">實體對象，主鍵不能缺少</param>
        /// <returns></returns>
        public static int UpdateEntity<T>(T newt)
        {
            string TableName = typeof(T).Name;
            PropertyInfo[] plist = typeof(T).GetProperties();
            var type = typeof(T);
            var keyProperties = KeyPropertiesCache(type).ToList();
            string condition = "";
            foreach (var item in keyProperties)
            {
                if (!item.PropertyType.IsGenericType)
                    condition = item.Name + "=@" + item.Name + " and";
            }
            string field = "";
            foreach (PropertyInfo p in plist)
            {
                if (!p.PropertyType.IsGenericType)
                    field += p.Name + ",";
            }
            field = field.Substring(0, field.Length - 1);
            condition = condition.Substring(0, condition.Length - 4);
            T oldt = GetModel<T>(TableName, field, "where 1=1 " + condition, newt);
            if (newt == null || oldt == null) return 0;
            if (newt.Equals(oldt)) return 0;
            string objVal1 = string.Empty;
            string objVal2 = string.Empty;
            string updatestr = "";
            foreach (PropertyInfo p in plist)
            {
                objVal1 = p.GetValue(newt, null) == null ? string.Empty : p.GetValue(newt, null).ToString();
                objVal2 = p.GetValue(oldt, null) == null ? string.Empty : p.GetValue(oldt, null).ToString();
                if (objVal1 != objVal2)
                    updatestr += p.Name + "=@" + p.Name + ",";
            }
            updatestr = updatestr.Substring(0, updatestr.Length - 1);
            string sql = "update " + TableName + " set " + updatestr + " where 1=1 " + condition;
            var result = connection.Execute(sql, newt);
            return result;
        }

        /// <summary>
        /// 更新實體，需要舊的實體對象
        /// </summary>
        /// <typeparam name="T">實體類型</typeparam>
        /// <param name="oldt">舊的實體對象</param>
        /// <param name="newt">新的實體對象</param>
        /// <returns></returns>
        public static int UpdateEntity<T>(T oldt, T newt)
        {
            string TableName = typeof(T).Name;
            PropertyInfo[] plist = typeof(T).GetProperties();
            var type = typeof(T);
            var keyProperties = KeyPropertiesCache(type).ToList();
            if (newt == null || oldt == null) return 0;
            if (newt.Equals(oldt)) return 0;
            string objVal1 = string.Empty;
            string objVal2 = string.Empty;
            string updatestr = "";
            foreach (PropertyInfo p in plist)
            {
                objVal1 = p.GetValue(newt, null) == null ? string.Empty : p.GetValue(newt, null).ToString();
                objVal2 = p.GetValue(oldt, null) == null ? string.Empty : p.GetValue(oldt, null).ToString();
                if (objVal1 != objVal2&& !p.PropertyType.IsGenericType)
                    updatestr += p.Name + "=@" + p.Name + ",";
            }
            updatestr = updatestr.Substring(0, updatestr.Length - 1);
            string condition = "";
            foreach (var item in keyProperties)
            {
                if (!item.PropertyType.IsGenericType)
                    condition = item.Name + "=@" + item.Name + " and";
            }
            condition = condition.Substring(0, condition.Length - 4);
            string sql = "update " + TableName + " set " + updatestr + " where 1=1 " + condition;
            var result = connection.Execute(sql, newt);
            return result;
        }

        #endregion

        #region 刪除記錄

        /// <summary>
        /// 刪除記錄
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="WhereString">條件</param>
        /// <param name="Parameters">參數對象</param>
        /// <returns></returns>
        public static int Delete(string TableName, string WhereString, object Parameters = null)
        {
            string sql = "delete from " + TableName + " where 1=1 " + WhereString;
            var result = connection.Execute(sql, Parameters);
            return result;
        }

        /// <summary>
        /// 刪除
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static int Delete(PageCriteria criteria, object Parameters = null)
        {
            string sql = "delete from " + criteria.TableName + " where 1=1 " + criteria.Condition;
            var result = connection.Execute(sql, Parameters);
            return result;
        }

        #endregion

        #region 查詢操作

        /// <summary>
        /// 查詢返回DataSet
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="FieldString"></param>
        /// <param name="WhereString"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static DataSet QueryGetDS(string TableName, string FieldString, string WhereString, object Parameters = null)
        {
            DataSet ds = new XDataSet();
            string sql = "select " + FieldString + " from " + TableName + " where 1=1 " + WhereString;
            var result = connection.ExecuteReader(sql, Parameters);
            try
            {
                ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { });
            }
            finally { result.Close(); }
            return ds;
        }

        /// <summary>
        /// 查詢返回DataSet
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static DataSet QueryGetDS(PageCriteria criteria, object Parameters = null)
        {
            DataSet ds = new XDataSet();
            string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition;
            var result = connection.ExecuteReader(sql, Parameters);
            try
            {
                ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { });
            }
            finally { result.Close(); }
            return ds;
        }

        /// <summary>
        /// 查詢返回DataTable
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="FieldString"></param>
        /// <param name="WhereString"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static DataTable QueryGetDT(string TableName, string FieldString, string WhereString, object Parameters = null)
        {
            DataTable dt = new DataTable();
            string sql = "select " + FieldString + " from " + TableName + " where 1=1 " + WhereString;
            var result = connection.ExecuteReader(sql, Parameters);
            try
            {
                dt.Load(result);
            }
            finally { result.Close(); }
            return dt;
        }

        /// <summary>
        /// 查詢返回DataTable
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static DataTable QueryGetDT(PageCriteria criteria, object Parameters = null)
        {
            DataTable dt = new DataTable();
            string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition;
            var result = connection.ExecuteReader(sql, Parameters);
            try
            {
                dt.Load(result);
            }
            finally { result.Close(); }
            return dt;
        }

        /// <summary>
        /// 查詢查詢返回list
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="FieldString"></param>
        /// <param name="WhereString"></param>
        /// <param name="Parameters"></param>
        /// <returns>對象集</returns>
        public static List<T> Query<T>(string TableName, string FieldString, string WhereString, object Parameters = null)
        {
            string sql = "select " + FieldString + " from " + TableName + " where 1=1 " + WhereString;
            var result = connection.Query<T>(sql, Parameters).ToList();
            return result;
        }

        /// <summary>
        /// 查詢返回list
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static List<T> Query<T>(PageCriteria criteria, object Parameters = null)
        {
            string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition;
            var result = connection.Query<T>(sql, Parameters).ToList();
            return result;
        }

        /// <summary>
        /// 獲取模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="TableName"></param>
        /// <param name="FieldString"></param>
        /// <param name="WhereString"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static T GetModel<T>(string TableName, string FieldString, string WhereString, object Parameters = null)
        {
            string sql = "select top 1 " + FieldString + " from " + TableName + " where 1=1 " + WhereString;
            var result = connection.Query<T>(sql, Parameters).FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 獲取模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="criteria"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public static T GetModel<T>(PageCriteria criteria, object Parameters = null)
        {
            string sql = "select " + criteria.Fields + " from " + criteria.TableName + " where 1=1 " + criteria.Condition;
            var result = connection.Query<T>(sql, Parameters).FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 獲取實體通過實體
        /// </summary>
        /// <typeparam name="T">實體類型</typeparam>
        /// <param name="t">實體對象</param>
        /// <param name="condition">and field1=@field1 and field2=@field2</param>
        /// <returns></returns>
        public static T GetModel<T>(T t, string condition=null)
        {
            string TableName = typeof(T).Name;
            PropertyInfo[] plist = typeof(T).GetProperties();
            var type = typeof(T);
            var keyProperties = KeyPropertiesCache(type).ToList();
            if (string.IsNullOrEmpty(condition)){
                foreach (var item in keyProperties)
                {
                    if (!item.PropertyType.IsGenericType)
                         condition =" and " + item.Name + "=@" + item.Name ;
                }
            }
            string field = "";
            foreach (PropertyInfo p in plist)
            {
                if (!p.PropertyType.IsGenericType)
                    field += p.Name + ",";
            }
            field = field.Substring(0, field.Length - 1);
            string sql = "select top 1 " + field + " from " + TableName + " where 1=1 " + condition;
            var result = connection.Query<T>(sql, t).FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 通用分頁獲取數據
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="criteria"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static PageDataView<T> GetPageModelList<T>(PageCriteria criteria)
        {
            var p = new DynamicParameters();
            string proName = "Fgly_Public_Pagination";
            p.Add("TableName", criteria.TableName);
            p.Add("PrimaryKey", criteria.PrimaryKey);
            p.Add("Fields", criteria.Fields);
            p.Add("Filter", criteria.Condition);
            p.Add("CurrentPage", criteria.CurrentPage);
            p.Add("PageSize", criteria.PageSize);
            p.Add("Order", criteria.Sort);
            p.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

            connection.Open();
            var pageData = new PageDataView<T>
            {
                Items = connection.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList()
            };
            connection.Close();
            pageData.TotalNum = p.Get<int>("TotalCount");
            pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize));
            pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;
            return pageData;
        }

        /// <summary>
        /// 分頁獲取數據
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static DataTable GetPageProc(PageCriteria criteria,ref int TotalCount)
        {
            var p = new DynamicParameters();
            string proName = "Fgly_Public_Pagination";
            p.Add("TableName", criteria.TableName);
            p.Add("PrimaryKey", criteria.PrimaryKey);
            p.Add("Fields", criteria.Fields);
            p.Add("Filter", criteria.Condition);
            p.Add("CurrentPage", criteria.CurrentPage);
            p.Add("PageSize", criteria.PageSize);
            p.Add("Order", criteria.Sort);
            p.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            DataTable dt = new DataTable();
            //connection.Open();
            var result = connection.ExecuteReader(proName, p, commandType: CommandType.StoredProcedure);
            try
            {
                dt.Load(result);
            }
            finally { result.Close(); }
            connection.Close();
            TotalCount = p.Get<int>("TotalCount");
            return dt;
        }

        /// <summary>
        /// 分頁獲取數據
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static IList<T> GetPageProc<T>(PageCriteria criteria, ref int TotalCount)
        {
            var p = new DynamicParameters();
            string proName = "Fgly_Public_Pagination";
            p.Add("TableName", criteria.TableName);
            p.Add("PrimaryKey", criteria.PrimaryKey);
            p.Add("Fields", criteria.Fields);
            p.Add("Filter", criteria.Condition);
            p.Add("CurrentPage", criteria.CurrentPage);
            p.Add("PageSize", criteria.PageSize);
            p.Add("Order", criteria.Sort);
            p.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            connection.Open();
            var result = connection.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList();
            connection.Close();
            TotalCount = p.Get<int>("TotalCount");
            return result;
        }
        #endregion

        #region 執行存儲過程
        /// <summary>
        /// 執行存儲過程並返回List
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static List<T> ExtProcGetList<T>(string proName, object p = null)
        {
            var result = connection.Query<T>(proName, p, commandType: CommandType.StoredProcedure).ToList();
            return result;
        }

        /// <summary>
        /// 執行存儲過程並返回DataSet
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExtProcGetDS(string proName, object p=null)
        {
            DataSet ds = new XDataSet();
            var result = connection.ExecuteReader(proName, p, commandType: CommandType.StoredProcedure);
            try
            {
                ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { });
            }
            finally { result.Close(); }
            return ds;
        }

        /// <summary>
        /// 執行存儲過程並返回DataTable
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataTable ExtProcGetDT(string proName, object p = null)
        {
            DataTable dt = new DataTable();
            var result = connection.ExecuteReader(proName, p, commandType: CommandType.StoredProcedure);
            try
            {
                dt.Load(result);
            }
            finally { result.Close(); }
            return dt;
        }

        /// <summary>
        /// 執行存儲過程返回受影響的行數
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static int IntExtProc(string proName,object p = null)
        {
            int result = connection.Execute(proName, p, commandType: CommandType.StoredProcedure);
            return result;
        }

        /// <summary>
        /// 執行存儲過程返回一個值
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static object ExtProcScale(string proName, object p = null)
        {
            object result = connection.ExecuteScalar(proName, p, commandType: CommandType.StoredProcedure);
            return result;
        }

        #endregion

        #region 執行Sql語句

        /// <summary>
        /// 執行sql返回list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static List<T> ExtSqlGetList<T>(string sql,object p = null)
        {
            var result = connection.Query<T>(sql, p).ToList();
            return result;
        }

        /// <summary>
        /// 執行Sql語句返回模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static T ExtSqlGetModel<T>(string sql, object parameter)
        {
            var result = connection.Query<T>(sql, parameter).ToList().FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 執行sql返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExtSqlGetDS(string sql,object p = null)
        {
            DataSet ds = new XDataSet();
            var result = connection.ExecuteReader(sql, p);
            try
            {
                ds.Load(result, LoadOption.OverwriteChanges, null, new DataTable[] { });
            }
            finally { result.Close(); }
            return ds;
        }

        /// <summary>
        /// 執行SQL返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataTable ExtSqlGetDT(string sql, object p = null)
        {
            DataTable dt = new DataTable();
            var result = connection.ExecuteReader(sql, p);
            try
            {
                dt.Load(result);
            }
            finally { result.Close(); }
            return dt;
        }

        /// <summary>
        /// 執行SQL返回受影響的行數
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static int IntExtSql(string sql, object p = null)
        {
            int result = connection.Execute(sql, p);
            return result;
        }

        /// <summary>
        /// 執行SQL返回一個值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static object ExtSqlScale(string sql,object p = null)
        {
            object result = connection.ExecuteScalar(sql, p);
            return result;
        }
        #endregion

        #region 擴展需要方法
        private static List<PropertyInfo> KeyPropertiesCache(Type type)
        {
            var allProperties = TypePropertiesCache(type);
            var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is Dapper.Contrib.Extensions.KeyAttribute)).ToList();

            if (keyProperties.Count == 0)
            {
                var idProp = allProperties.Find(p => string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase));
                if (idProp != null && !idProp.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute))
                {
                    keyProperties.Add(idProp);
                }
            }

            KeyProperties[type.TypeHandle] = keyProperties;
            return keyProperties;
        }

        private static List<PropertyInfo> TypePropertiesCache(Type type)
        {
            var properties = type.GetProperties().Where(IsWriteable).ToArray();
            TypeProperties[type.TypeHandle] = properties;
            return properties.ToList();
        }

        private static bool IsWriteable(PropertyInfo pi)
        {
            var attributes = pi.GetCustomAttributes(typeof(WriteAttribute), false).AsList();
            if (attributes.Count != 1) return true;

            var writeAttribute = (WriteAttribute)attributes[0];
            return writeAttribute.Write;
        }

        #endregion

    }

    public class XLoadAdapter : DataAdapter
    {
        public XLoadAdapter()
        {
        }

        public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords)
        {
            return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
        }
    }

    public class XDataSet : DataSet
    {
        public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)
        {
            XLoadAdapter adapter = new XLoadAdapter
            {
                FillLoadOption = loadOption,
                MissingSchemaAction = MissingSchemaAction.AddWithKey
            };
            if (handler != null)
            {
                adapter.FillError += handler;
            }
            adapter.FillFromReader(this, reader, 0, 0);
            if (!reader.IsClosed && !reader.NextResult())
            {
                reader.Close();
            }
        }
    }
}

　　這個類，包含了對實體操作，執行sql語句，執行存儲過程，返回list<T>,DataSet,DataTable,int,object等操作，可能需要你手動添加一些引用，這邊在貼出我的條件類，分頁類，在這個類中有用到：

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

namespace FK.Data.Entities
{
    public class PageCriteria
    {
        private string _TableName;
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }
        private string _Fileds = "*";
        /// <summary>
        /// 欄位（逗號隔開，預設*）
        /// </summary>
        public string Fields
        {
            get { return _Fileds; }
            set { _Fileds = value; }
        }
        private string _PrimaryKey = "ID";
        /// <summary>
        /// 主鍵名
        /// </summary>
        public string PrimaryKey
        {
            get { return _PrimaryKey; }
            set { _PrimaryKey = value; }
        }
        private int _PageSize = 10;
        /// <summary>
        /// 每頁記錄數，預設10
        /// </summary>
        public int PageSize
        {
            get { return _PageSize; }
            set { _PageSize = value; }
        }
        private int _CurrentPage = 1;
        /// <summary>
        /// 當前頁
        /// </summary>
        public int CurrentPage
        {
            get { return _CurrentPage; }
            set { _CurrentPage = value; }
        }
        private string _Sort = string.Empty;
        /// <summary>
        /// 排序
        /// </summary>
        public string Sort
        {
            get { return _Sort; }
            set { _Sort = value; }
        }
        private string _Condition = string.Empty;
        /// <summary>
        /// 條件
        /// </summary>
        public string Condition
        {
            get { return _Condition; }
            set { _Condition = value; }
        }
        private int _RecordCount;
        /// <summary>
        /// 記錄數
        /// </summary>
        public int RecordCount
        {
            get { return _RecordCount; }
            set { _RecordCount = value; }
        }
    }
}

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

namespace FK.Data.Entities
{
    public class PageDataView<T>
    {
        private int _TotalNum;
        public PageDataView()
        {
            this._Items = new List<T>();
        }
        /// <summary>
        /// 記錄數
        /// </summary>
        public int TotalNum
        {
            get { return _TotalNum; }
            set { _TotalNum = value; }
        }
        private IList<T> _Items;
        /// <summary>
        /// 列表
        /// </summary>
        public IList<T> Items
        {
            get { return _Items; }
            set { _Items = value; }
        }
        /// <summary>
        /// 當前頁
        /// </summary>
        public int CurrentPage { get; set; }
        /// <summary>
        /// 總頁數,輸出參數
        /// </summary>
        public int TotalPageCount { get; set; }
    } 
}

　　好了，還有什麼疑問，或者建議，請在評論中與我討論，感謝你的閱讀，再見