dapper的简单封装 _简单

【dapper的简单封装】当筵意气临九霄，星离雨散不终朝。这篇文章主要讲述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); } }#endregiontran.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("[email protected]"); strSql.Append(" where [email protected] "); 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 [email protected], [email protected], [email protected], [email protected], [email protected], [ORDER][email protected] where [email protected] and [email protected]; "); 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); } }#endregiontran.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 [email protected] "); param.Add("@Id", Id, dbType: DbType.Int32); //答案 conn.Execute(strSql.ToString(), param, tran); strSql.Clear(); strSql.Append("delete from [S_ForwardRate] "); strSql.Append(" where [email protected] "); 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 [email protected]; "); strSql.Append("select * from [S_ForwardRateValue] where [email protected]; "); 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--是否获得总记录数 @Totalint output ASDECLARE @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 endif @Sort is null or @Sort = ‘‘‘‘ set @Sort = @PK + ‘ DESC ‘IF @PageNumber < 1 SET @PageNumber = 1if @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+‘ ANDrownum < =‘ +@END_ID +‘ ORDER BY ‘+substring(@Sort,charindex(‘.‘,@Sort)+1,len(@Sort)-charindex(‘.‘,@Sort)) END EXEC(@sql)