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