/// <summary>
/// 传递参数分别是：表名，主键，页面大小，分页码，条件，查询总数,参数
/// </summary>
/// <param name="tableName"></param>
/// <param name="primaryKey"></param>
/// <param name="selectValue"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="where"></param>
/// <param name="orderBy"></param>
/// <param name="recordCount"></param>
/// <param name="spr"></param>
/// <returns></returns>
public DataSet GetPagerInfo(string tableName, string primaryKey, string selectValue, int pageSize, int pageIndex, string where, string orderBy, ref int recordCount, List <SqlParameter> spr)
{
where = string.IsNullOrEmpty(where) ? "1=1" : where;
orderBy = string.IsNullOrEmpty(orderBy) ? (" Order By " + primaryKey + " ASC") : (" Order By " + orderBy);
string cmdText = "select count(" + primaryKey + ") from " + tableName + " where " + where;
recordCount = SqlConvertHelper.GetInstallSqlHelper().ExecuteScalar(cmdText, spr).ToInt();
//当页码为1，或小于1时
if (pageIndex <= 1)
{
cmdText = "select top " + pageSize + " " + selectValue + " from " + tableName + " where " + where + " " + orderBy;
}
else
{
string pageCmdText = @"
SELECT {0} FROM (
SELECT ROW_NUMBER() OVER ( {5} ) rowindex,{0}
FROM {1}
WHERE {2}
)_t WHERE rowindex BETWEEN {3} AND {4} ORDER BY rowindex
";
int StartIndex = pageSize * (pageIndex - 1);
int EndIndex = pageSize * pageIndex;
cmdText = string.Format(pageCmdText, selectValue, tableName, where, StartIndex, EndIndex, orderBy);
}
return(SqlConvertHelper.GetInstallSqlHelper().GetDataSet(cmdText, spr));
}
/// <summary>
/// 不分页查询,基础方法
/// </summary>
/// <param name="count">显示总数，空则全部显示</param>
/// <param name="selectFields">查询字段</param>
/// <param name="express">表达式</param>
/// <param name="orderBy">排序,为空则不排序</param>
/// <returns></returns>
private List <TEntity> Search(int?count, string selectFields, List <Expression> express, string orderBy)
{
//获取参数和条件
CoreFrameworkEntity CoreFrameworkEntity = GetParaListAndWhere(express);
//条件
string where = CoreFrameworkEntity.where;
//参数列表
List <SqlParameter> listPara = CoreFrameworkEntity.paraList;
selectFields = selectFields == "" ? "*" : selectFields; //查询字段
orderBy = orderBy == "" ? "" : "order by " + orderBy; //排序
string topStr = count.HasValue == false ? "" : " top " + count + " ";
string cmdText = "select " + topStr + selectFields + " from " + tableName + " where " + where + " " + orderBy;
IDataReader sdr = new SqlConvertHelper().ExecuteReader(cmdText, listPara);
return(DynamicBuilder <TEntity> .GetList(sdr, columnAttrList));
}
/// <summary>
/// 传递参数分别是：表名，主键，页面大小，分页码，条件，查询总数,参数
/// </summary>
/// <param name="TableName"></param>
/// <param name="PrimaryKey"></param>
/// <param name="selectValue"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="Where"></param>
/// <param name="OrderBy"></param>
/// <param name="recordCount"></param>
/// <param name="spr"></param>
/// <returns></returns>
public DataSet GetPagerInfo(string TableName, string PrimaryKey, string selectValue, int PageSize, int PageIndex, string Where, string OrderBy, ref int recordCount, List <SqlParameter> spr)
{
Where = string.IsNullOrEmpty(Where) ? "1=1" : Where;
OrderBy = string.IsNullOrEmpty(OrderBy) ? "" : (" Order By " + OrderBy);
string cmdText = "select count(" + PrimaryKey + ") from " + TableName + " where " + Where;
recordCount = new SqlConvertHelper().ExecuteScalar(cmdText, spr).ToInt();
//当页码为1，或小于1时
if (PageIndex <= 1)
{
cmdText = "select top " + PageSize + " " + selectValue + " from " + TableName + " where " + Where + " " + OrderBy;
}
else
{
cmdText = "select top " + PageSize + " " + selectValue + " from " + TableName + " where " + Where + " and " + PrimaryKey
+ " not in (select top " + PageSize * (PageIndex - 1) + " " + PrimaryKey + " from " + TableName + " where " + Where + " " + OrderBy + ") " + OrderBy;
}
return(new SqlConvertHelper().GetDataSet(cmdText, spr));
}
/// <summary>
/// 传递参数分别是：表名，主键，页面大小，分页码，条件，查询总数,参数
/// </summary>
/// <param name="TableName"></param>
/// <param name="PrimaryKey"></param>
/// <param name="selectValue"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="Where"></param>
/// <param name="OrderBy"></param>
/// <param name="recordCount"></param>
/// <param name="spr"></param>
/// <returns></returns>
public DataSet GetPagerInfo(string TableName, string PrimaryKey, string selectValue, int PageSize, int PageIndex, string Where, string OrderBy, ref int recordCount, List <SqlParameter> spr)
{
Where = string.IsNullOrEmpty(Where) ? "1=1" : Where;
OrderBy = string.IsNullOrEmpty(OrderBy) ? "" : (" Order By " + OrderBy);
string cmdText = "select count(" + PrimaryKey + ") from " + TableName + " where " + Where;
recordCount = new SqlConvertHelper().ExecuteScalar(cmdText, spr).ToInt();
cmdText = "select top 0 " + selectValue + " from " + TableName;
DataSet ds = new SqlConvertHelper().GetDataSet(cmdText, spr);
//当页码为1，或小于1时
if (PageIndex <= 1)
{
cmdText = "select top " + PageSize + " " + selectValue + " from " + TableName + " where " + Where + " " + OrderBy;
}
else
{
cmdText = "select top " + PageSize + " " + selectValue + " from " + TableName + " where " + Where + " and " + PrimaryKey
+ " not in (select top " + PageSize * (PageIndex - 1) + " " + PrimaryKey + " from " + TableName + " where " + Where + " " + OrderBy + ") " + OrderBy;
}
IDataReader reader = new SqlConvertHelper().ExecuteReader(cmdText, spr);
while (reader.Read())
{
DataRow dr = ds.Tables[0].NewRow();
DataColumnCollection collection = ds.Tables[0].Columns;
foreach (DataColumn column in ds.Tables[0].Columns)
{
dr[column.ColumnName] = reader[column.ColumnName];
}
ds.Tables[0].Rows.Add(dr);
}
return(ds);
}
