/// <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>
/// ���ݲ���categoryId����ȡͼ�������ϸ��Ϣ
/// </summary>
/// <param name="topicID">ͼ�����ID</param>
public void LoadData(int categoryId)
{
Database db=new Database(); //ʵ����һ��Database��
string sql="";
sql="Select * from [Category] where CategoryId = "+ categoryId ;
DataRow dr=db.GetDataRow(sql); //����Database���GetDataRow������ѯ�û�����
//���ݲ�ѯ�õ������ݣ��Գ�Ա��ֵ
if(dr!=null)
{
this._categoryId=GetSafeData.ValidateDataRow_N(dr,"CategoryId");
this._categoryName=GetSafeData.ValidateDataRow_S(dr,"CategoryName");
this._exist=true;
}
else
{
this._exist=false;
}
}
/// <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);
}
/// <summary>
/// �����ݿ����һ���û�
/// </summary>
/// <param name="htUserInfo">�û���Ϣ��ϣ��</param>
public void Add(Hashtable userInfo)
{
Database db=new Database(); //ʵ����һ��Database��
db.Insert("[User]",userInfo ); //����Database���GetDataRow������ѯ�û�����
}
/// <summary>
/// ���ݲ���bookID����ȡͼ����ϸ��Ϣ
/// </summary>
/// <param name="topicID">ͼ��ID</param>
public void LoadData(int bookID)
{
Database db=new Database(); //ʵ����һ��Database��
string sql="";
sql="Select * from [Book] where BookID = "+ bookID ;
DataRow dr=db.GetDataRow(sql); //����Database���GetDataRow������ѯ�û�����
//���ݲ�ѯ�õ������ݣ��Գ�Ա��ֵ
if(dr!=null)
{
this._bookId=GetSafeData.ValidateDataRow_N(dr,"BookID");
this._bookName=GetSafeData.ValidateDataRow_S(dr,"BookName");
this._categoryId=GetSafeData.ValidateDataRow_N(dr,"CategoryId");
this._price=GetSafeData.ValidateDataRow_F(dr,"Price");
this._publisher=GetSafeData.ValidateDataRow_S(dr,"Publisher");
this._publishDate=GetSafeData.ValidateDataRow_T(dr,"PublishDate");
this._author=GetSafeData.ValidateDataRow_S(dr,"Author");
this._pageNum=GetSafeData.ValidateDataRow_N(dr,"PageNum");
this._pictureUrl=GetSafeData.ValidateDataRow_S(dr,"PictureUrl");
this._description=GetSafeData.ValidateDataRow_S(dr,"Description");
this._saleCount=GetSafeData.ValidateDataRow_N(dr,"SaleCount");
this._exist=true;
}
else
{
this._exist=false;
}
}
/// <summary>
/// ���ݲ���userId����ȡ��������ϸ��Ϣ
/// </summary>
/// <param name="userId">�û�ID</param>
public void LoadData(int userId)
{
Database db=new Database(); //ʵ����һ��Database��
string sql="";
sql="Select * from [Cart] where UserID = "+ userId ;
SqlDataReader reader=db.GetDataReader(sql); //����Database���GetDataRow������ѯ�û�����
//���ݲ�ѯ�õ������ݣ��Գ�Ա��ȫ��ֵ
while(reader.Read())
{
if(this._cartId==0)
{
this._cartId=GetSafeData.ValidateDataReader_N(reader,"CartId");
this._userId=GetSafeData.ValidateDataReader_N(reader,"UserId");
}
this._books.Add(GetSafeData.ValidateDataReader_N(reader,"BookId"));
this._amount.Add(GetSafeData.ValidateDataReader_N(reader,"Amount"));
this._exist=true;
}
if(this._cartId==0)
{
this._exist=false;
}
}
/// <summary>
/// ɾ������������
/// </summary>
public void Delete()
{
Database db=new Database();
string strSql="Delete From [Cart] Where CartID = "+this._cartId;
db.ExecuteSQL(strSql);
}
/// <summary>
/// �����ݿ����һ���������е�һ��ͼ��
/// </summary>
/// <param name="topicInfo">�������е�һ��ͼ�����Ϣ��ϣ��</param>
public void Add(Hashtable cartInfo)
{
Database db=new Database(); //ʵ����һ��Database��
db.Insert("[Cart]",cartInfo ); //����Database���Inser��������������
}
/// <summary>
/// ���ݲ���loginName����ȡ�û���ϸ��Ϣ
/// </summary>
/// <param name="userId">�û�ID</param>
public void LoadData(int userId)
{
Database db=new Database(); //ʵ����һ��Database��
string sql="";
sql="Select * from [User] where UserId = "+userId.ToString();
DataRow dr=db.GetDataRow(sql); //����Database���GetDataRow������ѯ�û�����
//���ݲ�ѯ�õ������ݣ��Գ�Ա��ֵ
if(dr!=null)
{
this._userID=GetSafeData.ValidateDataRow_N(dr,"UserID");
this._loginName=GetSafeData.ValidateDataRow_S(dr,"loginName");
this._userName=GetSafeData.ValidateDataRow_S(dr,"UserName");
this._password=GetSafeData.ValidateDataRow_S(dr,"PassWord");
this._address=GetSafeData.ValidateDataRow_S(dr,"Address");
this._zip=GetSafeData.ValidateDataRow_S(dr,"Zip");
this._exist=true;
}
else
{
this._exist=false;
}
}
/// <summary>
/// �����ݿ����һ��ͼ��
/// </summary>
/// <param name="topicInfo">ͼ����Ϣ��ϣ��</param>
public void Add(Hashtable bookInfo)
{
Database db=new Database(); //ʵ����һ��Database��
db.Insert("[Book]",bookInfo ); //����Database���Inser��������������
}
/// <summary>
/// ɾ����������ij��ͼ��
/// </summary>
/// <param name="cartId">��Ҫɾ������������Ŀ���</param>
public void RemoveBook(int cartId)
{
Database db=new Database();
string strSql="Delete From [Cart] Where cartId = "+cartId.ToString();
db.ExecuteSQL(strSql);
}
/// <summary>
/// ɾ��ͼ��
/// </summary>
public void Delete()
{
Database db=new Database();
string strSql="Delete From [Book] Where BookID = "+this._bookId;
db.ExecuteSQL(strSql);
}
/// <summary>
/// �Ĺ������е�һ��ͼ������
/// </summary>
/// <param name="newCartInfo">�µĹ�������Ϣ��ϣ��</param>
/// <param name="condition">Update��Where�Ӿ�</param>
public void Update(Hashtable newCartInfo,int cartId)
{
Database db=new Database();
string condition = "Where CartID = "+cartId.ToString();
db.Update("[Cart]",newCartInfo,condition);
}
/// <summary>
/// ��ͼ������
/// </summary>
/// <param name="newBookInfo">�µ�ͼ����Ϣ��ϣ��</param>
/// <param name="condition">Update��Where�Ӿ�</param>
public void Update(Hashtable newBookInfo)
{
Database db=new Database();
string condition = "Where BookID = "+this._bookId;
db.Update("[Book]",newBookInfo,condition);
}
/// <summary>
/// �ж��Ƿ���ڵ�¼��ΪloginName���û�
/// </summary>
/// <param name="loginName">�û���¼��</param>
/// <returns>������ڣ�����true��������false</returns>
public static bool HasUser(string loginName)
{
Database db=new Database();
string sql="";
sql="Select * from [User] where [LoginName] = "
+SqlStringConstructor.GetQuotedString(loginName);
DataRow row=db.GetDataRow(sql);
if(row!=null)
return true;
else
return false;
}
