/// <summary>
/// ���ݲ�ѯ������ϣ��,��ѯ����
/// </summary>
/// <param name="queryItems">��ѯ������ϣ��</param>
/// <returns>��ѯ�������DataTable</returns>
public static DataTable Query(Hashtable queryItems)
{
string where=SqlStringConstructor.GetConditionClause(queryItems);
string sql="Select * From [Category]"+where;
Database db = new Database();
return db.GetDataTable(sql);
}
/// <summary>
/// ��ij�����ԣ�������߽����ȡ���е�ͼ����Ϣ
/// </summary>
/// <param name="sortedColumn">��������</param>
/// <param name="sortType">��ȡ{desc,asc}���ֱ�����������</param>
/// <returns>��ѯ�����</returns>
public static DataTable QueryBooks(Hashtable queryItems,string sortedColumn,string sortType)
{
if(sortType.ToUpper()!="DESC" && sortType.ToUpper()!="ASC" && sortType!="")
return null;
string where=SqlStringConstructor.GetConditionClause(queryItems);
string constrains=" Order By "+sortedColumn+" "+sortType;
string sql="Select * From [Book] "+where+constrains;
Database db = new Database();
return db.GetDataTable(sql);
}
/// <summary>
/// ���ճ������ѯͼ���������
/// </summary>
/// <returns></returns>
public static DataTable GetSaleCountByPublisher()
{
string sql="Select Publisher,sum(SaleCount) as SaleCount From [Book]"
+"Group By Publisher";
Database db=new Database();
DataTable dt=db.GetDataTable(sql);
return dt;
}
/// <summary>
/// ���ռ۸��ѯͼ���������
/// </summary>
/// <returns></returns>
public static DataTable GetSaleCountByPrice()
{
string sql=
"Select SUM(SaleCount) AS SaleCount,'1~30' AS PriceGrade from [Book] where price < 30"
+" UNION "
+"Select SUM(SaleCount) AS SaleCount,'30~50' AS PriceGrade from [Book] where price >= 30 and price < 50"
+" UNION "
+"Select SUM(SaleCount) AS SaleCount,'50+' AS PriceGrade from [Book] where price >= 50 ";
Database db=new Database();
DataTable dt=db.GetDataTable(sql);
return dt;
}
/// <summary>
/// ��������ѯͼ���������
/// </summary>
/// <returns></returns>
public static DataTable GetSaleCountByCategory()
{
string sql="Select [Category].CategoryName,sum(SaleCount) as SaleCount From [Book],[Category] "
+"Where [Book].CategoryId=[Category].CategoryId "
+"Group By [Category].CategoryName";
Database db=new Database();
DataTable dt=db.GetDataTable(sql);
return dt;
}
/// <summary>
/// ��ѯuserId�Ĺ������е���Ϣ������DataTable����
/// </summary>
/// <param name="userId">�û����</param>
/// <returns>��������ϢDataTable</returns>
public static DataTable Query(int userId)
{
string sql="";
sql="Select * From [Cart],[Book] Where [Cart].UserId = "+userId
+" And [Cart].bookId=[Book].bookId";
Database db=new Database();
return db.GetDataTable(sql);
}
