/// <summary>
/// 用户列表
/// </summary>
/// <typeparam name="UserListModel">用户列表实体</typeparam>
/// <param name="searchDto">用户列表查询实体</param>
/// <returns></returns>
public PageDataView <UserListDto> GetList(UserSearchDto searchDto)
{
PageDataView <UserListDto> result = new PageDataView <UserListDto>()
{
};
StringBuilder select = new StringBuilder();
StringBuilder where = new StringBuilder();
DynamicParameters parameters = new DynamicParameters();
select.Append("SELECT * FROM [User] ");
where.Append("WHERE 1=1 ");
if (!string.IsNullOrEmpty(searchDto.DomainAccount))
{
where.Append($"AND DomainAccount LIKE @DomainAccount ");
parameters.Add("@DomainAccount", "%" + searchDto.DomainAccount + "%");
}
if (!string.IsNullOrEmpty(searchDto.RealName))
{
where.Append($"AND RealName LIKE @RealName ");
parameters.Add("@RealName", "%" + searchDto.RealName + "%");
}
Dictionary <string, bool> ordery = new Dictionary <string, bool>();
ordery.Add("createdAt", false);
result = _unitOfWork.GetPageData <UserListDto>(select.ToString() + where.ToString(), ordery, searchDto.PageIndex, searchDto.PageSize, parameters);
return(result);
}
/// <summary>
/// 公共分页方法
/// </summary>
/// <typeparam name="T">表名</typeparam>
/// <param name="criteria">分页对象</param>
/// <returns></returns>
public static PageDataView <T> GetPageData <T>(PageCriteria criteria)
{
using (OracleConnection conn = GetConnString())
{
conn.Open();
DynamicParameters parms = new DynamicParameters();
parms.Add("@TableName", criteria.TableName, DbType.String, ParameterDirection.Input, null);
parms.Add("@PrimaryKey", criteria.PrimaryKey, DbType.String, ParameterDirection.Input, null);
parms.Add("@Fields", criteria.Fields, DbType.String, ParameterDirection.Input, null);
parms.Add("@Condition", criteria.Condition, DbType.String, ParameterDirection.Input, null);
parms.Add("@CurrentPage", criteria.CurrentPage, DbType.Int32, ParameterDirection.Input, null);
parms.Add("@PageSize", criteria.PageSize, DbType.Int32, ParameterDirection.Input, null);
parms.Add("@Sort", criteria.Sort, DbType.String, ParameterDirection.Input, null);
parms.Add("@RecordCount", criteria.RecordCount, DbType.Int32, ParameterDirection.Output, null);
string proName = "ProcGetPageData";
var pageData = new PageDataView <T>();
pageData.Items = conn.Query <T>(proName, parms, CommandType.StoredProcedure).ToList();
conn.Close();
pageData.TotalNum = parms.Get <int>("RecordCount");
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>
/// <returns>分页数据</returns>
public async Task <PageDataView <T> > GetPageDataAsync <T>(PageCriteria criteria) where T : class, new()
{
using (var conn = await DataBaseConfig.GetNpgSqlConnectionAsync(_connectionstr))
{
var p = new DynamicParameters();
var proName = "Module_Common_PagerNew";
p.Add("@tableName", criteria.TableName);
p.Add("@tableFields", criteria.Fields);
p.Add("@sqlWhere", criteria.Condition);
p.Add("@orderFields", criteria.Sort);
p.Add("@pageSize", criteria.PageSize);
p.Add("@pageIndex", criteria.CurrentPage);
p.Add("@totalPage", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@totalRecord", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <T>
{
Items = await conn.QueryAsync <T>(proName, p, commandType : CommandType.StoredProcedure),
TotalNum = p.Get <int>("@totalRecord"),
TotalPageCount = p.Get <int>("@totalPage")
};
pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount
? pageData.TotalPageCount
: criteria.CurrentPage;
return(pageData);
}
}
/// <summary>
/// 分页方法
/// </summary>
/// <typeparam name="T">查询实体</typeparam>
/// <param name="connectionString">连接字符串</param>
/// <param name="pageCriteria">查询条件</param>
/// <param name="totalNum">总数</param>
/// <returns></returns>
public static PageDataView <T> GetPageListForSQL <T>(string connectionString, PageCriteria pageCriteria)
{
var result = new PageDataView <T>();
string sql = "SELECT * from(SELECT " + pageCriteria.Fields + ",row_number() over(order by " + pageCriteria.Sort + ") rownum FROM " + pageCriteria.TableName + " where " + pageCriteria.Condition + ") t where rownum>@minrownum and rownum<=@maxrownum";
string countSql = "select count(1) from " + pageCriteria.TableName + " where " + pageCriteria.Condition;
int minrownum = (pageCriteria.CurrentPage - 1) * pageCriteria.PageSize;
int maxrownum = minrownum + pageCriteria.PageSize;
var p = new DynamicParameters();
p.Add("minrownum", minrownum);
p.Add("maxrownum", maxrownum);
if (pageCriteria.ParameterList != null)
{
foreach (var param in pageCriteria.ParameterList)
{
p.Add(param.ParamName, param.ParamValue);
}
}
var reader = MultyQuery(connectionString, sql + ";" + countSql, p);
result.Items = reader.Read <T>().ToList();
result.TotalNum = reader.Read <int>().First <int>();
result.CurrentPage = pageCriteria.CurrentPage;
result.TotalPageCount = result.TotalNum / pageCriteria.PageSize + (result.TotalNum % pageCriteria.PageSize == 0 ? 0 : 1);
return(result);
}
public virtual PageDataView <TEntity> GetPage(PageCriteria criteria, object param = null, IDbTransaction transaction = null)
{
var p = new DynamicParameters();
string proName = "ProcGetPageData";
p.Add("TableName", criteria.TableName);
p.Add("PrimaryKey", criteria.PrimaryKey);
p.Add("Fields", criteria.Fields);
p.Add("Condition", criteria.Condition);
p.Add("CurrentPage", criteria.CurrentPage);
p.Add("PageSize", criteria.PageSize);
p.Add("Sort", criteria.Sort);
p.Add("IsDesc", criteria.IsDesc);
p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <TEntity>();
if (transaction == null)
{
pageData.Items = _dbConnection.GetConnection("read").Query <TEntity>(proName, p, commandType: CommandType.StoredProcedure).ToList();
}
else
{
pageData.Items = transaction.Connection.Query <TEntity>(proName, p, commandType: CommandType.StoredProcedure).ToList();
}
pageData.TotalNum = p.Get <int>("RecordCount");
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>
/// <typeparam name="TM"></typeparam>
/// <param name="criteria"></param>
/// <param name="param"></param>
/// <returns></returns>
public PageDataView <TM> GetPageData <TM>(PageCriteria criteria, object param = null)
where TM : class, IEntityBase, new()
{
using (var conn = UnitOfWork.GetDbConnection())
{
var p = new DynamicParameters();
const string proName = "ProcGetPageData";
p.Add("TableName", criteria.TableName);
p.Add("PrimaryKey", criteria.PrimaryKey);
p.Add("Fields", criteria.Fields);
p.Add("Condition", criteria.Condition);
p.Add("CurrentPage", criteria.CurrentPage);
p.Add("PageSize", criteria.PageSize);
p.Add("Sort", criteria.Sort);
p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <TM>
{
Items = conn.Query <TM>(proName, p, commandType: CommandType.StoredProcedure).ToList(),
RecordCount = p.Get <int>("RecordCount")
};
pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.RecordCount * 1.0 / criteria.PageSize));
pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;
return(pageData);
}
}
public PageDataView <TEntity> GetPageData <TEntity>(PageCriteria criteria, object param = null) where TEntity : class
{
var p = new DynamicParameters();
string proName = "CCHRWebApiProcGetPageData";
p.Add("TableName", criteria.TableName);
p.Add("PrimaryKey", criteria.PrimaryKey);
p.Add("Fields", criteria.Fields);
p.Add("Condition", criteria.Condition);
p.Add("CurrentPage", criteria.CurrentPage);
p.Add("PageSize", criteria.PageSize);
p.Add("Sort", criteria.Sort);
p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <TEntity>();
int excuteTimes = 0;
//QUERY:
try
{
pageData.Items = Connection.Query <TEntity>(proName, p, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList();
pageData.TotalNum = p.Get <int>("RecordCount");
pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize));
pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;
}
catch (Exception e)
{
excuteTimes++;
if (e.Message.Contains("找不到存储过程 'CCHRWebApiProcGetPageData'") && excuteTimes <= 3)
{
FileStream fileStream = new FileStream("CCHRWebApiProcGetPageData.sql", FileMode.Open);
StreamReader sr = new StreamReader(fileStream);
string sql = sr.ReadToEnd();
if (fileStream != null)
{
fileStream.Close();
}
if (sr != null)
{
sr.Close();
}
if (!string.IsNullOrEmpty(sql))
{
Connection.Execute(sql);
// goto QUERY;
}
throw;
}
else if ((e.Message.Contains("远程主机强迫关闭了一个现有的连接") || e.Message.Contains("指定的网络名不再可用")) && excuteTimes <= 3)
{
System.Threading.Thread.Sleep(excuteTimes * 1000);
//goto QUERY;
}
else
{
throw;
}
}
return(pageData);
}
public PageDataView <TReturn> GetJoinPageData <TFirst, TSecond, TThird, TReturn>(PageCriteria criteria, Func <TFirst, TSecond, TThird, TReturn> map, string splitOnString) where TReturn : class
{
var p = new DynamicParameters();
string proName = "CCHRWebApiProcGetPageData";
p.Add("TableName", criteria.TableName);
p.Add("PrimaryKey", criteria.PrimaryKey);
p.Add("Fields", criteria.Fields);
p.Add("Condition", criteria.Condition);
p.Add("CurrentPage", criteria.CurrentPage);
p.Add("PageSize", criteria.PageSize);
p.Add("Sort", criteria.Sort);
p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <TReturn>();
pageData.Items = Connection.Query <TFirst, TSecond, TThird, TReturn>(proName, map, p, splitOn: splitOnString, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList();
pageData.TotalNum = p.Get <int>("RecordCount");
pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize));
pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;
return(pageData);
}
protected static PageDataView <T> GetPageData <T>(PageCriteria criteria)
{
using (var conn = new SqlConnection(ConnString))
{
var p = new DynamicParameters();
p.Add("TableName", criteria.TableName);
p.Add("PrimaryKey", criteria.PrimaryKey);
p.Add("Fields", criteria.Fields);
p.Add("Condition", criteria.Condition);
p.Add("CurrentPage", criteria.CurrentPage);
p.Add("PageSize", criteria.PageSize);
p.Add("Sort", criteria.Sort);
p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var pageData = new PageDataView <T>();
pageData.Items = conn.Query <T>("sp_GetPageData", p, commandType: CommandType.StoredProcedure).ToList();
pageData.TotalNum = p.Get <int>("RecordCount");
pageData.TotalPageCount = Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize));
pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;
return(pageData);
}
}
public PageDataView <CustomerPVListDto> GetList(CustomerPVSearchDto dto)
{
PageDataView <CustomerPVListDto> result = new PageDataView <CustomerPVListDto>()
{
};
StringBuilder select = new StringBuilder();
StringBuilder where = new StringBuilder();
DynamicParameters parameters = new DynamicParameters();
select.Append("SELECT B.* ,P.ProjectName FROM CustomerPV AS B JOIN Project AS P ON B.webMonitorId=P.KeyId ");
where.Append("WHERE 1=1 ");
if (!string.IsNullOrEmpty(dto.WebMonitorId))
{
where.Append($"AND WebMonitorId = @WebMonitorId ");
parameters.Add("@WebMonitorId", dto.WebMonitorId);
}
if (!string.IsNullOrEmpty(dto.CustomerKey))
{
where.Append($"AND (B.CustomerKey = @CustomerKey OR B.UserID = @CustomerKey) ");
parameters.Add("@CustomerKey", dto.CustomerKey);
}
if (dto.StartTime.HasValue)
{
where.Append($"AND B.createdAt >= @StartTime ");
parameters.Add("@StartTime", dto.StartTime);
}
if (dto.EndTime.HasValue)
{
where.Append($"AND B.createdAt <= @EndTime ");
parameters.Add("@EndTime", dto.EndTime);
}
Dictionary <string, bool> ordery = new Dictionary <string, bool>();
ordery.Add("createdAt", false);
result = _unitOfWork.GetPageData <CustomerPVListDto>(select.ToString() + where.ToString(), ordery, dto.PageIndex, dto.PageSize, parameters);
return(result);
}
// [Route("ddd/12.html")]
// GET: Default
public ActionResult Index()
{
//List<Contact> modelList= _contactService.GetModels(new Contact() { ID = 0 });
PageCriteria criteria = new PageCriteria();
criteria.TableName = "Contact";
criteria.Fields = "*";
criteria.PrimaryKey = "ContactID";
criteria.CurrentPage = 1;
criteria.PageSize = 5;
criteria.Sort = "ID desc ";
criteria.Condition = " ID>0";
PageDataView <Contact> pageList = _contactService.GetModelsByPage(criteria);
List <Contact> modelList = pageList.Items;
ViewBag.TotalNum = pageList.TotalNum;
ViewBag.CurrentPage = pageList.CurrentPage;
ViewBag.TotalPageCount = pageList.TotalPageCount;
return(View(modelList));
}
public PageDataView <ProjectListDto> GetList(SearchProjectDto dto)
{
PageDataView <ProjectListDto> result = new PageDataView <ProjectListDto>()
{
};
StringBuilder select = new StringBuilder();
StringBuilder where = new StringBuilder();
DynamicParameters parameters = new DynamicParameters();
select.Append("SELECT * FROM Project ");
where.Append("WHERE 1=1 ");
if (!string.IsNullOrEmpty(dto.ProjectName))
{
where.Append($"AND ProjectName LIKE @ProjectName ");
parameters.Add("@ProjectName", "%" + dto.ProjectName + "%");
}
Dictionary <string, bool> ordery = new Dictionary <string, bool>();
ordery.Add("createdAt", false);
result = _unitOfWork.GetPageData <ProjectListDto>(select.ToString() + where.ToString(), ordery, dto.PageIndex, dto.PageSize, parameters);
return(result);
}
/// <summary>
/// SQL语句分页查询
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="sqlDistinct">sql语句</param>
/// <param name="sort">排序字段集合</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">页条数</param>
/// <param name="parameters">查询参数</param>
/// <returns></returns>
public PageDataView <TEntity> GetPageData <TEntity>(string sqlDistinct, Dictionary <string, bool> sort, int?pageIndex = null, int?pageSize = null, DynamicParameters parameters = null, bool readOnly = false) where TEntity : class
{
try
{
if (string.IsNullOrEmpty(sqlDistinct))
{
throw new Exception("查询语句为空");
}
StringBuilder select = new StringBuilder();
int skipNum = 0;
if (pageIndex.HasValue && pageSize.HasValue)
{
select.AppendFormat("SELECT TOP ({0}) * ", pageSize.Value);
skipNum = pageSize.Value * (pageIndex.Value - 1);
}
else
{
select.Append("SELECT * ");
}
StringBuilder order = new StringBuilder();
foreach (string key in sort.Keys)
{
if (key.Contains(","))
{
string[] arr = key.Split(',');
foreach (string a in arr)
{
if (!string.IsNullOrEmpty(a))
{
order.AppendFormat("{0} {1},", a, sort[key] == true ? "ASC" : "DESC");
}
}
}
else
{
order.AppendFormat("{0} {1},", key, sort[key] == true ? "ASC" : "DESC");
}
}
order.Remove(order.Length - 1, 1);
//返回当前页数据
StringBuilder sql = new StringBuilder();
sql.Append(select);
sql.AppendFormat("FROM (SELECT *,row_number() OVER (ORDER BY {0}) AS row_number ", order);
sql.AppendFormat("FROM ({0}) AS t1) AS t2 ", sqlDistinct);
sql.AppendFormat("WHERE row_number>{0} ", skipNum);
sql.AppendFormat("ORDER BY {0} ", order);
var pageData = new PageDataView <TEntity>();
//查询数据
//pageData.Items = Connection.Query<TEntity>(sql.ToString(), parameters, commandType: CommandType.Text).ToList();
pageData.Items = this.GetUnitOfWork(readOnly).Query <TEntity>(sql.ToString(), parameters).ToList();
if (pageData.Items.Count > 0 && pageIndex.HasValue && pageSize.HasValue)
{
pageData.TotalNum = this.GetPageCount(sqlDistinct, parameters);
pageData.CurrentPage = pageIndex.Value;
}
return(pageData);
}
catch
{
throw;
}
}
public static MvcHtmlString PageLinks <T>(this HtmlHelper html, PageDataView <T> pageData,
Func <int, string> pageUrl, int showMaxPageNum = 20)
{
const string tplPageNumLink = "<a href=\"{0}\">{1}</a>";
const string tmplPageNumLinkCurrent = "<span class=\"current\">{0}</span>";
var result = new StringBuilder();
if (pageData.TotalPageCount > 0)
{
result.AppendFormat(@"第 {0} 页，共 {1} 页，共{2}条", (pageData.TotalPageCount < pageData.CurrentPage ? 0 : pageData.CurrentPage)
, pageData.TotalPageCount
, pageData.TotalNum);
int showStartPageNum, showEndPageNum;
if (pageData.CurrentPage <= 1)
{
showStartPageNum = 1;
showEndPageNum = showMaxPageNum;
if (showEndPageNum > pageData.TotalPageCount)
{
showEndPageNum = pageData.TotalPageCount;
}
}
else if (pageData.CurrentPage >= pageData.TotalPageCount)
{
showStartPageNum = pageData.TotalPageCount - showMaxPageNum + 1;
showEndPageNum = pageData.TotalPageCount;
if (showStartPageNum <= 0)
{
showStartPageNum = 1;
}
}
else
{
int beforeCount = Convert.ToInt32(Math.Ceiling(showMaxPageNum * 1.0 / 2));
showStartPageNum = pageData.CurrentPage - beforeCount;
if (showStartPageNum <= 0)
{
showStartPageNum = 1;
}
showEndPageNum = showStartPageNum + showMaxPageNum - 1;
if (showEndPageNum > pageData.TotalPageCount)
{
showEndPageNum = pageData.TotalPageCount;
}
if (showStartPageNum > pageData.TotalPageCount)
{
showEndPageNum = pageData.TotalPageCount;
}
}
result.AppendFormat(tplPageNumLink, pageUrl(1), "首页");
if (pageData.CurrentPage > 1)
{
result.AppendFormat(tplPageNumLink, pageUrl(pageData.CurrentPage - 1), "上一页");
}
for (int i = showStartPageNum; i <= showEndPageNum; i++)
{
if (i == pageData.CurrentPage)
{
result.AppendFormat(tmplPageNumLinkCurrent, i);
}
else
{
result.AppendFormat(tplPageNumLink, pageUrl(i), i);
}
}
if (pageData.CurrentPage < pageData.TotalPageCount)
{
result.AppendFormat(tplPageNumLink, pageUrl(pageData.CurrentPage + 1), "下一页");
}
result.AppendFormat(tplPageNumLink, pageUrl(pageData.TotalPageCount), "末页");
}
return(MvcHtmlString.Create(result.ToString()));
}
