dapper的简单封装

   /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <typeparam name="T">要获取实体</typeparam>
        /// <param name="pageIndex">要获取的页数</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="model">分页参数</param>
        /// <param name="totalCount">数据总数量</param>
        /// <returns></returns>
        public static List<T> GetPageList<T>(int pageIndex, int pageSize, PageModel model, out int totalCount)
        {
            totalCount = 0;
            List<T> result = new List<T>();
            var param = new DynamicParameters();

            using (var conn = CreateConnection())
            {
                conn.Open();
                param.Add("@Tables", model.Tables, dbType: DbType.String);
                param.Add("@PK", model.PKey, dbType: DbType.String);
                param.Add("@Sort", model.Sort, dbType: DbType.String);
                param.Add("@PageNumber", pageIndex, dbType: DbType.Int32);
                param.Add("@PageSize", pageSize, dbType: DbType.Int32);
                param.Add("@Fields", model.Fields, dbType: DbType.String);
                param.Add("@Filter", model.Filter, dbType: DbType.String);
                param.Add("@isCount", model.IsCount, dbType: DbType.Boolean);
                param.Add("@Total", dbType: DbType.Int32, direction: ParameterDirection.Output);

                result = conn.Query<T>("Proc_CommonPagingStoredProcedure", param, commandType: CommandType.StoredProcedure).ToList();
                totalCount = param.Get<int>("@Total");
            }

            return result;
        }

    }

    public class PageModel
    {
        public PageModel()
        {
            PageSize = 10;
            IsCount = true;
        }

        /// <summary>
        /// 表名,多表请使用 tableA a inner join tableB b On a.AID = b.AID
        /// </summary>
        public string Tables { get; set; }

        /// <summary>
        /// 主键，可以带表头 a.AID
        /// </summary>
        public string PKey { get; set; }

        /// <summary>
        /// 排序字段
        /// </summary>
        public string Sort { get; set; }

        /// <summary>
        /// 开始页码即要查询的页
        /// </summary>
        public int PageIndex { get; set; }

        /// <summary>
        /// 页大小
        /// </summary>
        public int PageSize { get; set; }

        /// <summary>
        /// 读取字段
        /// </summary>
        public string Fields { get; set; }

        /// <summary>
        /// Where条件
        /// </summary>
        public string Filter { get; set; }

        /// <summary>
        /// 是否获得总记录数
        /// </summary>
        public bool IsCount { get; set; }
    }

 

 /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(S_ForwardRate model)
        {
            int result = 0;
            var conn = DBHelper.CreateConnection();
            conn.Open();
            var tran = conn.BeginTransaction();


            try
            {
                StringBuilder strSql = new StringBuilder();
                var param = new DynamicParameters();

                #region 远期利率
                strSql.Append("insert into [S_ForwardRate](");
                strSql.Append("Name,UserId,CreateTime)");

                strSql.Append(" values (");
                strSql.Append("@Name,@UserId,@CreateTime)");
                strSql.Append(";SELECT @returnid=SCOPE_IDENTITY()");

                param.Add("@Name", model.Name, dbType: DbType.String);
                param.Add("@UserId", model.UserId, dbType: DbType.Int32);
                param.Add("@CreateTime", model.CreateTime, dbType: DbType.DateTime);

                param.Add("@returnid", dbType: DbType.Int32, direction: ParameterDirection.Output);
                conn.Execute(strSql.ToString(), param, tran);
                result = param.Get<int>("@returnid");
                #endregion

                #region 远期利率值
                if (model.Values != null && model.Values.Count > 0)
                {
                    foreach (var item in model.Values)
                    {
                        strSql.Clear();
                        param = new DynamicParameters();

                        strSql.Append("insert into S_ForwardRateValue(");
                        strSql.Append("FID,TYPE,VALUE1,VALUE2,VALUE3,VALUE4,[ORDER])");
                        strSql.Append(" values (");
                        strSql.Append("@FID,@TYPE,@VALUE1,@VALUE2,@VALUE3,@VALUE4,@ORDER)");
                        param.Add("@FID", result, dbType: DbType.Int32);
                        param.Add("@TYPE", item.Type, dbType: DbType.String);//枚举转字符串
                        param.Add("@VALUE1", item.Value1, dbType: DbType.Decimal);
                        param.Add("@VALUE2", item.Value2, dbType: DbType.Decimal);
                        param.Add("@VALUE3", item.Value3, dbType: DbType.Decimal);
                        param.Add("@VALUE4", item.Value4, dbType: DbType.Decimal);
                        param.Add("@ORDER", item.Order, dbType: DbType.Int32);

                        conn.Execute(strSql.ToString(), param, tran);
                    }
                }

                #endregion

                tran.Commit();
            }
            catch (Exception ex)
            {
                LogHelper.Log.WriteError("[回滚]新增远期利率出错", ex);
                tran.Rollback();
            }
            finally
            {
                if (tran != null)
                    tran.Dispose();
                if (conn != null)
                    conn.Close();
            }
            return result;
        }
        #endregion

        #region  更新
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(S_ForwardRate model)
        {
            int result = 0;
            var conn = DBHelper.CreateConnection();
            conn.Open();
            var tran = conn.BeginTransaction();


            try
            {
                StringBuilder strSql = new StringBuilder();
                var param = new DynamicParameters();

                #region 远期利率
                strSql.Append("update [S_ForwardRate] set ");
                strSql.Append("Name=@Name");

                strSql.Append(" where Id=@Id ");

                param.Add("@Id", model.Id, dbType: DbType.Int32);
                param.Add("@Name", model.Name, dbType: DbType.String);

                result = conn.Execute(strSql.ToString(), param, tran);
                #endregion

                #region 远期利率值
                if (model.Values != null && model.Values.Count > 0)
                {
                    foreach (var item in model.Values)
                    {
                        strSql.Clear();
                        param = new DynamicParameters();

                        strSql.Append(@"update S_ForwardRateValue set 
                        TYPE=@TYPE, VALUE1=@VALUE1, VALUE2=@VALUE2, VALUE3=@VALUE3, VALUE4=@VALUE4, 
                        [ORDER]=@ORDER where FID=@FID and Id=@Id;");

                        param.Add("@FID", model.Id, dbType: DbType.Int32);
                        param.Add("@Id", item.Id, dbType: DbType.Int32);
                        param.Add("@TYPE", item.Type, dbType: DbType.String);//枚举转字符串
                        param.Add("@VALUE1", item.Value1, dbType: DbType.Decimal);
                        param.Add("@VALUE2", item.Value2, dbType: DbType.Decimal);
                        param.Add("@VALUE3", item.Value3, dbType: DbType.Decimal);
                        param.Add("@VALUE4", item.Value4, dbType: DbType.Decimal);
                        param.Add("@ORDER", item.Order, dbType: DbType.Int32);

                        conn.Execute(strSql.ToString(), param, tran);
                    }
                }

                #endregion

                tran.Commit();
            }
            catch (Exception ex)
            {
                LogHelper.Log.WriteError("[回滚]修改远期利率出错", ex);
                tran.Rollback();
            }
            finally
            {
                if (tran != null)
                    tran.Dispose();
                if (conn != null)
                    conn.Close();
            }
            return result > 0;
        }

        #endregion

        #region  删除
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {


            int result = 0;
            using (var conn = DBHelper.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                try
                {
                    StringBuilder strSql = new StringBuilder();
                    var param = new DynamicParameters();
                    strSql.Append("delete from [S_ForwardRateValue] ");
                    strSql.Append(" where RId=@Id ");
                    param.Add("@Id", Id, dbType: DbType.Int32);//答案
                    conn.Execute(strSql.ToString(), param, tran);

                    strSql.Clear();

                    strSql.Append("delete from [S_ForwardRate] ");
                    strSql.Append(" where Id=@Id ");
                    param.Add("@Id", Id, dbType: DbType.Int32);//删除远期利率
                    result = conn.Execute(strSql.ToString(), param, tran);

                    tran.Commit();
                }
                catch (Exception ex)
                {
                    LogHelper.Log.WriteError("[回滚]修改远期利率出错", ex);
                    tran.Rollback();
                }
                finally
                {
                    if (tran != null)
                        tran.Dispose();
                    if (conn != null)
                        conn.Close();
                }

            }
            return result > 0;
        }

        #endregion

        #region  获取实体
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public S_ForwardRate GetModel(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [S_ForwardRate] where Id=@Id; ");
            strSql.Append("select * from [S_ForwardRateValue] where FID=@Id; ");

            S_ForwardRate model = null;
            var param = new DynamicParameters();
            using (var conn = DBHelper.CreateConnection())
            {
                conn.Open();
                param.Add("@Id", Id, dbType: DbType.Int32);
                using (var multi = conn.QueryMultiple(strSql.ToString(), param))
                {
                    model = multi.Read<S_ForwardRate>().FirstOrDefault();
                    model.Values = multi.Read<S_ForwardRate.S_ForwardRateValue>().ToList();

                }
            }
            return model;
        }



        #endregion

        #region  根据查询条件获取列表
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<S_ForwardRate> GetList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.Id,a.Name,a.UserId,a.CreateTime, ");
            strSql.Append("b.Type,b.Value1, b.Value2,b.Value3,b.Value4,b.[Order],b.Id,b.FId");
            strSql.Append(" FROM [S_ForwardRate] a ");
            strSql.Append(" left JOIN [S_ForwardRateValue] b on a.Id = b.FId ");


            List<S_ForwardRate> list = new List<S_ForwardRate>();
            using (var conn = DBHelper.CreateConnection())
            {
                conn.Open();
                var infos = conn.Query<S_ForwardRate, S_ForwardRate.S_ForwardRateValue, S_ForwardRate>(strSql.ToString(), (s, v) =>
                {

                    var f = list.Find(a => a.Id == s.Id);
                    if (f == null)
                    {
                        s.Values = new List<S_ForwardRate.S_ForwardRateValue>();
                        s.Values.Add(v);
                        list.Add(s);
                        return s;
                    }
                    else
                    {
                        f.Values.Add(v);
                        return f;
                    }
                }, "Type");
            }
            return list;
        }



        #endregion

        #region  获取分页参数
        /// <summary>
        /// 获取分页参数
        /// </summary>
        public PageModel GetPage()
        {
            PageModel model = new PageModel();
            model.Tables = "[S_ForwardRate]";
            model.PKey = "Id";
            model.Sort = "CreateTime DESC";
            model.Fields = "Id,Name,UserId,CreateTime";
            return model;
        }

        #endregion

---------------------------------------------------
--  desc: 通用分页存储过程            
---------------------------------------------------

CREATE PROCEDURE [dbo].[Proc_CommonPagingStoredProcedure]
@Tables nvarchar(1000),            --表名,多表请使用 tableA a inner join tableB b On a.AID = b.AID
@PK nvarchar(100),                --主键，可以带表头 a.AID
@Sort nvarchar(200) = '',        --排序字段
@PageNumber int = 1,            --开始页码
@PageSize int = 10,                --页大小
@Fields nvarchar(1000) = '*',    --读取字段
@Filter nvarchar(1000) = NULL,    --Where条件
@isCount bit = 0  ,   --1        --是否获得总记录数
@Total    int output
AS

DECLARE @strFilter nvarchar(2000)
declare @sql Nvarchar(max)
IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
   SET @strFilter = ' WHERE 1=1 ' + @Filter + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ' '
  END
if @isCount = 1 --获得记录条数
    begin
        Declare @CountSql Nvarchar(max) 
        Set @CountSql = 'SELECT @TotalCount= Count(1) FROM ' + @Tables + @strFilter 
        Execute sp_executesql @CountSql,N'@TotalCount int output',@TotalCount= @Total Output 
        -- 针对groupby后无数据时，@Total会变为null
        if @Total is null
            begin
                set @Total = 0
            end
    end
    
if @Sort is null or @Sort = ''''
  set @Sort = @PK + ' DESC '

IF @PageNumber < 1
  SET @PageNumber = 1

if @PageNumber = 1 --第一页提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort 
end 
else
  begin   
    DECLARE @START_ID varchar(50)
    DECLARE @END_ID varchar(50) 


    SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
    SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
    set @sql =  ' SELECT * '+
   'FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
     '+@Fields+ '
      FROM '+@Tables+ @strFilter +' ) AS D
   Where rownum >= '+@START_ID+' AND  rownum <=' +@END_ID +' ORDER BY '+substring(@Sort,charindex('.',@Sort)+1,len(@Sort)-charindex('.',@Sort))
  END
 

EXEC(@sql)