/// <summary> /// ��ҳ��ѯ����������� /// PROCEDURE GetRecordFromSingleTableByPage /// @tblName nvarchar(255), -- ���� /// @priKeyName nvarchar(50), -- �����л��ʾ�� /// @fldNames nvarchar(1000), -- �ֶ���,����ֶ�ͨ�����ŷָ� /// @PageSize int = 0, -- ҳ�ߴ� /// @PageIndex int = 1, -- ҳ�� /// @OrderType nvarchar(200) = '', -- ��������'':û������Ҫ�� 0���������� 1���������� �ַ������û��Զ���������� /// @strWhere nvarchar(2000) = '', -- ��ѯ���� (ע��: ��Ҫ�� where) /// @TotalItem int output, /// @TotalPage int output /// </summary> /// <param name="pageInfo">��ѯ����</param> /// <returns> /// ���ز�ѯ�Ľ��ΪDataSet,Tables[0]Ϊ��ҳ��ѯ�����ѯ�� /// Tables[1]Ϊͳ���ֶν��,��totalfldsΪ��ʱ��û�д����� /// </returns> public static DataSet GetDataFromSingleTableByPage(SearchPageInfo pageInfo) { string spName = "UP_GetRecordFromSingleTableByPage"; DbCommand comm = CommDataAccess.DbReader.GetStoredProcCommand(spName); CommDataAccess.DbReader.AddInParameter(comm, "@tblName", DbType.String, pageInfo.TableName); CommDataAccess.DbReader.AddInParameter(comm, "@priKeyName", DbType.String, pageInfo.PriKeyName); CommDataAccess.DbReader.AddInParameter(comm, "@fldNames", DbType.String, pageInfo.FieldNames); CommDataAccess.DbReader.AddInParameter(comm, "@PageSize", DbType.Int32, pageInfo.PageSize); CommDataAccess.DbReader.AddInParameter(comm, "@PageIndex", DbType.Int32, pageInfo.PageIndex); CommDataAccess.DbReader.AddInParameter(comm, "@OrderType", DbType.String, pageInfo.OrderType); CommDataAccess.DbReader.AddInParameter(comm, "@strWhere", DbType.String, pageInfo.StrWhere); CommDataAccess.DbReader.AddOutParameter(comm, "@TotalItem", DbType.Int32, 4); CommDataAccess.DbReader.AddOutParameter(comm, "@TotalPage", DbType.Int32, 4); DataSet ds = CommDataAccess.DbReader.ExecuteDataSet(comm); pageInfo.TotalItem = Convert.ToInt32(CommDataAccess.DbReader.GetParameterValue(comm, "@TotalItem")); pageInfo.TotalPage = Convert.ToInt32(CommDataAccess.DbReader.GetParameterValue(comm, "@TotalPage")); return ds; }
public DataTable GetList(int PageIndex, int PageSize, string Condition, out int RecordCount) { SearchPageInfo info = new SearchPageInfo(); info.FieldNames = "*"; info.OrderType = " seproductid desc"; info.PageIndex = PageIndex; info.PageSize = PageSize; info.PriKeyName = "seproductid"; info.StrJoin = ""; info.StrWhere = " 1=1 " + Condition; info.TableName = "mwSecondhandProduct"; info.TotalFieldStr = ""; DataTable dt = CommDataHelper.GetDataFromSingleTableByPage(info).Tables[0]; RecordCount = info.TotalItem; return dt; }
/// <summary> /// 分页查询单个表的数据 /// PROCEDURE GetRecordFromSingleTableByPage /// @tblName nvarchar(255), -- 表名 /// @priKeyName nvarchar(50), -- 主键列或标示列 /// @fldNames nvarchar(1000), -- 字段名,多个字段通过逗号分割 /// @PageSize int = 0, -- 页尺寸 /// @PageIndex int = 1, -- 页码 /// @OrderType nvarchar(200) = '', -- 设置排序,'':没有排序要求 0:主键升序 1:主键降序 字符串:用户自定义排序规则 /// @strWhere nvarchar(2000) = '', -- 查询条件 (注意: 不要加 where) /// @TotalItem int output, /// @TotalPage int output /// </summary> /// <param name="pageInfo">查询条件</param> /// <returns> /// 返回查询的结果为DataSet,Tables[0]为分页查询结果查询, /// Tables[1]为统计字段结果,当totalflds为空时,没有此数据 /// </returns> public static DataSet GetDataFromSingleTableByPage(SearchPageInfo pageInfo) { string spName = "UP_GetRecordFromSingleTableByPage"; DbCommand comm = CommDataAccess.DbReader.GetStoredProcCommand(spName); CommDataAccess.DbReader.AddInParameter(comm, "@tblName", DbType.String, pageInfo.TableName); CommDataAccess.DbReader.AddInParameter(comm, "@priKeyName", DbType.String, pageInfo.PriKeyName); CommDataAccess.DbReader.AddInParameter(comm, "@fldNames", DbType.String, pageInfo.FieldNames); CommDataAccess.DbReader.AddInParameter(comm, "@PageSize", DbType.Int32, pageInfo.PageSize); CommDataAccess.DbReader.AddInParameter(comm, "@PageIndex", DbType.Int32, pageInfo.PageIndex); CommDataAccess.DbReader.AddInParameter(comm, "@OrderType", DbType.String, pageInfo.OrderType); CommDataAccess.DbReader.AddInParameter(comm, "@strWhere", DbType.String, pageInfo.StrWhere); CommDataAccess.DbReader.AddOutParameter(comm, "@TotalItem", DbType.Int32, 4); CommDataAccess.DbReader.AddOutParameter(comm, "@TotalPage", DbType.Int32, 4); DataSet ds = CommDataAccess.DbReader.ExecuteDataSet(comm); pageInfo.TotalItem = Convert.ToInt32(CommDataAccess.DbReader.GetParameterValue(comm, "@TotalItem")); pageInfo.TotalPage = Convert.ToInt32(CommDataAccess.DbReader.GetParameterValue(comm, "@TotalPage")); return(ds); }
public DataTable GetProductList(int CategoryID, int PageIndex, int BrandID, decimal[] PriceRange, int OrderType, out int RecordCount, out int PageCount) { SearchPageInfo pageinfo = new SearchPageInfo(); string CategoryPath = Convert.ToString(GetCategoryInfo(CategoryID)["catepath"]); string where = String.Format(" catepath like '{0}%'", CategoryPath); where += " and status = " + (int)ProductStatus.上架; if (BrandID != 0) where += " and pdproduct.brandid=" + BrandID; if (PriceRange != null && PriceRange.Length == 2) { if (PriceRange[1] == 0) { where += String.Format(" and pdproduct.merchantprice >= {0} ", PriceRange[0]); } else { where += String.Format(" and pdproduct.merchantprice >= {0} and pdproduct.merchantprice <= {1}", PriceRange[0], PriceRange[1] + 0.99M); } } pageinfo.FieldNames = "[ProductId],[ProductName],[ProductCode],[CatePath],[CateId],[TradePrice],[MerchantPrice],[ReducePrice],[Stock],[SmallImage],[MediumImage],[LargeImage],[Keywords],[Brief],[PageView],[InsertTime],[ChangeTime],[Status],[SortValue],[Score]"; pageinfo.OrderType = "SortValue asc"; pageinfo.PageIndex = PageIndex; pageinfo.PageSize = Config.ListPageSize; pageinfo.PriKeyName = "ProductId"; pageinfo.StrWhere = where; pageinfo.TableName = "pdproduct"; pageinfo.OrderType = GetOrderString(OrderType); pageinfo.TotalFieldStr = ""; pageinfo.TotalItem = 0; pageinfo.TotalPage = 0; DataTable dt = CommDataHelper.GetDataFromSingleTableByPage(pageinfo).Tables[0]; RecordCount = pageinfo.TotalItem; PageCount = pageinfo.TotalPage; return dt; }
public DataTable GetProductList(int CategoryID, int PageIndex,int BrandID,decimal[] PriceRange,int OrderType,Hashtable Parameters, out int RecordCount, out int PageCount) { string where = String.Empty; int i = 0; if(Parameters!=null) foreach (string key in Parameters.Keys) { CategoryParaModel para = new CategoryParaModelBll().GetModel(Convert.ToInt32(key), CategoryID); if (i == Parameters.Count - 1) where += String.Format(" and (pdproductpara.paraid = {0} and pdproductpara.paravalue like '%{1}%') ", key, para.ParaValues.Split(',')[Convert.ToInt32(Parameters[key])]); else where += String.Format(" and (pdproductpara.paraid = {0} and pdproductpara.paravalue like '%{1}%') ", key, para.ParaValues.Split(',')[Convert.ToInt32(Parameters[key])]); i++; } if (BrandID != 0) where += " and pdproduct.brandid=" + BrandID; if (PriceRange != null && PriceRange.Length == 2) where += String.Format(" and pdproduct.merchantprice >= {0} and pdproduct.merchantprice <= {1}", PriceRange[0], PriceRange[1] + 0.99M); string CategoryPath = Convert.ToString(GetCategoryInfo(CategoryID)["catepath"]); where += String.Format(" and pdproduct.catepath like '{0}%'", CategoryPath); where += " and pdproduct.status = 1"; SearchPageInfo pageInfo = new SearchPageInfo(); pageInfo.TableName = "pdproductpara"; pageInfo.PriKeyName = "productid"; pageInfo.FieldNames = "pdproduct.productid,paraid,paravalue,productname,pdcategory.cateid,pdcategory.catepath,tradeprice,merchantprice,reduceprice,stock,smallimage,mediumimage,largeimage,keywords,brief,pageview,inserttime,changetime,pdproduct.status,score"; pageInfo.TotalFieldStr = ""; pageInfo.PageSize = Config.ListPageSize; pageInfo.PageIndex = PageIndex; pageInfo.OrderType = GetOrderString(OrderType); pageInfo.StrWhere = "1=1 " + where; pageInfo.StrJoin = " inner join pdproduct on pdproduct.productid=pdproductpara.productid inner join pdcategory on pdproduct.cateid=pdcategory.cateid "; DataTable dt = CommDataHelper.GetDataFromMultiTablesByPage(pageInfo).Tables[0]; RecordCount = pageInfo.TotalItem; PageCount = pageInfo.TotalPage; return dt; }
public DataSet GetList(int PageIndex, int PageSize, string Condition, out int RecordCount) { SearchPageInfo info = new SearchPageInfo(); info.FieldNames = "*"; info.OrderType = " showorder desc"; info.PageIndex = PageIndex; info.PageSize = PageSize; info.PriKeyName = "brandid"; info.StrJoin = ""; info.StrWhere = " 1=1 " + Condition; info.TableName = "pdbrand"; info.TotalFieldStr = ""; DataSet ds = CommDataHelper.GetDataFromSingleTableByPage(info); RecordCount = info.TotalItem; return ds; }
public DataTable GetNewsList(int PageIndex, int PageSize, int CategoryID, out int RecordCount,out int PageCount) { SearchPageInfo pageinfo = new SearchPageInfo(); string where = String.Format(" dbo.GetNewsCategoryPath(cateid)+'/' like dbo.GetNewsCategoryPath({0})+'/%'", CategoryID); pageinfo.FieldNames = "*"; pageinfo.OrderType = "newsid desc"; pageinfo.PageIndex = PageIndex; pageinfo.PageSize = PageSize; pageinfo.PriKeyName = "newsid"; pageinfo.StrWhere = where; pageinfo.TableName = "nenews"; pageinfo.TotalFieldStr = ""; pageinfo.TotalItem = 0; pageinfo.TotalPage = 0; DataTable dt = CommDataHelper.GetDataFromSingleTableByPage(pageinfo).Tables[0]; RecordCount = pageinfo.TotalItem; PageCount = pageinfo.TotalPage; return dt; }