/// <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); }