public PagingDataSet <PointLogs> GetPointLogs(PointLogsQuery pointLogsQuery, int pageIndex, int pageSize) { int totalCount = 0; int totalPage = 0; string whereSql = string.Empty; string orderBy = string.Empty; HandleOrderByEunm(pointLogsQuery.PointLogsSortBy, out orderBy); object pars = new object(); whereSql = HandleQueryBySqlable(pointLogsQuery, out pars); return(GetPageListByCache(pageIndex, pageSize, out totalCount, out totalPage, whereSql, pars, orderBy, i => i.Id)); }
/// <summary> /// 使用Sqlable处理SQL拼接(支持参数化) /// </summary> /// <param name="pointLogsQuery"></param> /// <param name="pars"></param> /// <returns></returns> private string HandleQueryBySqlable(PointLogsQuery pointLogsQuery, out object pars) { Dictionary <string, object> paramsDictionary = new Dictionary <string, object>(); var sqlTable = new Sqlable(); sqlTable.Sql = new StringBuilder(); if (pointLogsQuery.IsIncome.HasValue) { sqlTable = sqlTable.Where("is_income = @IsIncome"); paramsDictionary.Add("IsIncome", pointLogsQuery.IsIncome); } if (!string.IsNullOrWhiteSpace(pointLogsQuery.UserName)) { sqlTable = sqlTable.Where("userid in (select userid from sc_user where username LIKE @UserName)"); paramsDictionary.Add("UserName", $"%{pointLogsQuery.UserName}%"); } if (!string.IsNullOrWhiteSpace(pointLogsQuery.Itemsname)) { sqlTable = sqlTable.Where("itemsname LIKE @Itemsname"); paramsDictionary.Add("itemsname", $"%{pointLogsQuery.Itemsname}%"); } if (pointLogsQuery.SexyPoints.HasValue) { sqlTable = sqlTable.Where("sexy_points = @SexyPoints"); paramsDictionary.Add("SexyPoints", pointLogsQuery.SexyPoints); } if (pointLogsQuery.ExperiencePoints.HasValue) { sqlTable = sqlTable.Where("experience_points = @ExperiencePoints"); paramsDictionary.Add("ExperiencePoints", pointLogsQuery.ExperiencePoints); } pars = paramsDictionary; foreach (var item in sqlTable.Where) { sqlTable.Sql.Append(item); } return(sqlTable.Sql.ToString().TrimStart(" AND".ToCharArray())); }
public PagingDataSet <PointLogs> GetPointLogs(PointLogsQuery pointLogsQuery, int pageIndex, int pageSize) { return(pointLogsRepository.GetPointLogs(pointLogsQuery, pageIndex, pageSize)); }