/// <summary>
/// 将 DataTable 填充到 List 实体集（适用于存储过程的分页）
/// </summary>
/// <typeparam name="T">List 实体集</typeparam>
/// <param name="dt">DataTable 对象</param>
/// <param name="paging">Utility.Paging 分页属性</param>
/// <returns>返回 List 实体集</returns>
public static List <T> ToList <T>(DataTable dt, ref Utility.Paging paging)
{
int recordTotal = 0;
int recordStart = 0;
int recordEnd = 0;
if (dt != null && dt.Rows.Count > 0 && dt.Columns.Contains("RecordTotal"))
{
recordTotal = Convert.ToInt32(dt.Rows[0]["RecordTotal"]);
dt.Columns.Remove("RecordTotal");
if (dt.Columns.Contains("RecordStart"))
{
recordStart = Convert.ToInt32(dt.Rows[0]["RecordStart"]);
dt.Columns.Remove("RecordStart");
}
if (dt.Columns.Contains("RecordEnd"))
{
recordEnd = Convert.ToInt32(dt.Rows[0]["RecordEnd"]);
dt.Columns.Remove("RecordEnd");
}
}
paging.SetPaging(paging.PageIndex, paging.PageSize, recordTotal, recordStart, recordEnd);
return(AX.CSF.DBHelper.ToEntity.ToList <T>(dt));
}
/// <summary>
/// 逐条执行查询命令（适用于存储过程的分页）
/// </summary>
/// <param name="storProcName">存储过程名称</param>
/// <param name="cmdParms">参数集合</param>
/// <param name="paging">Utility.Paging 分页属性</param>
/// <returns>返回 DataTable</returns>
public DataTable DataTable(string storProcName, SqlParameter[] cmdParms, ref Utility.Paging paging)
{
DataTable dt = Sql.DataTable(storProcName, cmdParms);
int recordTotal = 0;
int recordStart = 0;
int recordEnd = 0;
if (dt != null && dt.Rows.Count > 0 && dt.Columns.Contains("RecordTotal"))
{
recordTotal = Convert.ToInt32(dt.Rows[0]["RecordTotal"]);
dt.Columns.Remove("RecordTotal");
if (dt.Columns.Contains("RecordStart"))
{
recordStart = Convert.ToInt32(dt.Rows[0]["RecordStart"]);
dt.Columns.Remove("RecordStart");
}
if (dt.Columns.Contains("RecordEnd"))
{
recordEnd = Convert.ToInt32(dt.Rows[0]["RecordEnd"]);
dt.Columns.Remove("RecordEnd");
}
}
paging.SetPaging(paging.PageIndex, paging.PageSize, recordTotal, recordStart, recordEnd);
return(dt);
}
/// <summary>
/// 自动按分组分页方法
/// </summary>
/// <param name="strSql">完整sql语句</param>
/// <param name="recordCount">记录总数</param>
/// <param name="nvl">参数值</param>
/// <returns></returns>
protected System.Data.DataTable ExceuteSqlForGroupPage(string strSql, out int recordCount, System.Collections.Specialized.NameValueCollection nvl = null)
{
nvl = nvl ?? HttpContext.Current.Request.Params;
var pageIndex = 1;
var pageSize = 30;
var sort = "Id";
var order = "asc";
if (!nvl["page"].IsNullOrEmpty())
{
pageIndex = int.Parse(nvl["page"]);
}
if (!nvl["rows"].IsNullOrEmpty())
{
pageSize = int.Parse(nvl["rows"]);
}
if (!nvl["sort"].IsNullOrEmpty())
{
sort = nvl["sort"];
}
if (!nvl["order"].IsNullOrEmpty())
{
order = nvl["order"];
}
order = order.ToLower();
if (!(order == "asc" || order == "desc"))
{
throw new ArgumentException("排序类型错误!");
}
string orderSql = string.Format("(DENSE_RANK() OVER ( ORDER BY {0} {1})) AS RSNO", sort, order);
strSql = string.Format("select * from(select {0},* from ({1}) tb) t", orderSql, strSql);
var page = new Utility.Paging();
var parms = new SqlParameter[] {
new SqlParameter("@SqlStr", strSql),
new SqlParameter("@CurrentPage", pageIndex),
new SqlParameter("@PageSize", pageSize)
};
var dt = _db.DataTable("Comm_PageList", parms, ref page);
recordCount = page.RecordTotal;
return(dt);
}
/// <summary>
/// 逐条执行查询命令（适用于存储过程的分页）
/// </summary>
/// <param name="storProcName">存储过程名称</param>
/// <param name="paging">Utility.Paging 分页属性</param>
/// <returns>返回 List 实体集</returns>
public List <T> DataTable <T>(string storProcName, ref Utility.Paging paging)
{
DataTable dt = Sql.DataTable(storProcName);
return(ToEntity.ToList <T>(dt, ref paging));
}
/// <summary>
/// 逐条执行查询命令（适用于存储过程的分页）
/// </summary>
/// <param name="storProcName">存储过程名称</param>
/// <param name="cmdParms">参数集合</param>
/// <param name="paging">Utility.Paging 分页属性</param>
/// <returns>返回 List 实体集</returns>
public List <T> DataTable <T>(string storProcName, SqlParameter[] cmdParms, ref Utility.Paging paging)
{
DataTable dt = Sql.DataTable(storProcName, cmdParms);
return(ToEntity.ToList <T>(dt, ref paging));
}