book.cs

基于Asp.net、MS sql sever 2000、C# 网上书店系统源码

using System;
using System.Data;
using System.Data.SqlClient;
using DataAccessLayer.DbBase;
using System.Collections ;

namespace DataAccessLayer
{
	/// <summary>
	/// Book description for book。
	/// </summary>
	public class Book:Base
	{
		public Book()
		{
			
		}
		//get all data of books
		public DataView GetBook()
		{
			String strsql;
			DataSet myDs;
			strsql="SELECT Book.*, BookType.Name AS TypeName FROM Book INNER JOIN BookType ON Book.Type = BookType.Id";
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;
		}

		//get top ten best salers
		public DataView GetBookTop10()
		{
			String strsql;
			DataSet myDs;
			strsql="select top 10 id,name from book order by sales DESC";
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;
		}

		/// <summary>
		/// add book,Operator Storeprocedure "AddBook"
		/// </summary>
		/// <param name="tempary">book detail,Kind:ArrayList</param>
		/// <returns></returns>
		public void AddBook(ArrayList tempary)
		{
			SqlConnection myCn=new SqlConnection(strConn);
			SqlCommand myCm=new SqlCommand("AddBook",myCn);
			myCm.CommandType =CommandType.StoredProcedure ;

			myCm.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,100));
			myCm.Parameters["@name"].Value =tempary[0];

			myCm.Parameters.Add(new SqlParameter("@type",SqlDbType.Int));
			myCm.Parameters["@type"].Value =tempary[1];

			myCm.Parameters.Add(new SqlParameter("@author",SqlDbType.VarChar,30 ));
			myCm.Parameters["@author"].Value =tempary[2];

			myCm.Parameters.Add(new SqlParameter("@translator",SqlDbType.VarChar,30));
			myCm.Parameters["@translator"].Value =tempary[3];

			myCm.Parameters.Add(new SqlParameter("@publisher",SqlDbType.VarChar,100));
			myCm.Parameters["@publisher"].Value =tempary[4];

			myCm.Parameters.Add(new SqlParameter("@price",SqlDbType.Decimal ));
			myCm.Parameters["@price"].Value =tempary[5];

			myCm.Parameters.Add(new SqlParameter("@discount",SqlDbType.Decimal ));
			myCm.Parameters["@discount"].Value =tempary[6];

			myCm.Parameters.Add(new SqlParameter("@cover",SqlDbType.Image ));
			myCm.Parameters["@cover"].Value =tempary[7];

			myCm.Parameters.Add(new SqlParameter("@description",SqlDbType.Text ));
			myCm.Parameters["@description"].Value =tempary[8];

			myCm.Parameters.Add(new SqlParameter("@hits",SqlDbType.Int));
			myCm.Parameters["@hits"].Value =tempary[9];

			myCm.Parameters.Add(new SqlParameter("@sales",SqlDbType.Int));
			myCm.Parameters["@sales"].Value =tempary[10];

			myCm.Parameters.Add(new SqlParameter("@status",SqlDbType.Bit) );
			myCm.Parameters["@status"].Value =tempary[11];

			try
			{
				myCn.Open() ;
				myCm.ExecuteNonQuery() ;
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new  Exception(er.Message);
			}
			finally
			{
				myCm.Dispose() ;
				myCn.Close() ;
			}
		}



		/// <summary>
		/// Delete Book By Bookid
		/// </summary>
		/// <param name="BookId"></param>
		public void DeleteBookById(int BookId)
		{
			strSQL = "Delete From book Where id="+BookId;
			
			try
			{
				ExecuteSql(strSQL);				
			}
			catch
			{
				throw new Exception("删除失败!");
			}
		}

		
		/// <summary>
		/// Get Book Cover By BookId
		/// </summary>
		/// <param name="BookId">BookId</param>
		/// <returns></returns>
		/// 
		public DataView GetBookCover(int BookId)
		{
			String strsql;
			DataSet myDs;
			strsql="select cover from book where id="+BookId;
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;
		}



		/// <summary>
		/// Get Book Message By BookType
		/// </summary>
		/// <param name="id">BookType'Id</param>
		/// <returns></returns>
		public SqlDataReader GetBookMessage(int BookTypeId)
		{
			string strsql;
			SqlDataReader result;
			try
			{
				SqlConnection myCn=new SqlConnection(strConn);
				strsql="select id,name,discount,author,price,type ,image=case when(not Cover is null) then ' <img src=ReadBookCover.aspx?id='+cast(id as varchar(10))+' Border=1 width=80 height=120>' else ' <img src=img/pic.jpg border=1 width=80 height=120>' end from book where type="+BookTypeId;
				SqlCommand myCm=new SqlCommand(strsql,myCn);
				myCn.Open ();
				result=myCm.ExecuteReader(CommandBehavior.CloseConnection);
				return result;
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new Exception(er.Message);
			}

		}


		public DataView GetNewBook()
		{
			String strsql;
			DataSet myDs;
			strsql="select top 10 id,name from book order by id desc";
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;
		}


		public SqlDataReader GetBookByHits()
		{
			string strsql;
			SqlDataReader result;
			try
			{
				SqlConnection myCn=new SqlConnection(strConn);
				strsql="select top 10 id,name,descr='&nbsp;&nbsp;'+SUBSTRING(description,0,100)+'......',image=case when(not Cover is null) then ' <img src=ReadBookCover.aspx?id='+cast(id as varchar(10))+' Border=1 width=70 height=100>' else ' <img src=img/pic.jpg border=1 width=70 height=100>' end from book order by hits DESC";
				SqlCommand myCm=new SqlCommand(strsql,myCn);
				myCn.Open ();
				result=myCm.ExecuteReader(CommandBehavior.CloseConnection);
				return result;
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new Exception(er.Message);
			}

		}

		public DataRow GetBookDetail(int BookId)
		{
			string strsql;
			DataSet myDs;
			try
			{
				strsql="select BookType.Name as BookTypeName,book.id,book.name,author,price,type,publisher,Description,translator,discount,hits,status,sales,image=case when(not Cover is null) then ' <img src=ReadBookCover.aspx?id='+cast(book.id as varchar(10))+' Border=1 width=80 height=120>' else ' <img src=img/pic.jpg border=1 width=80 height=120>' end from book join BookType on book.type=booktype.id where Book.id="+BookId;
				myDs=ExecuteSql4Ds(strsql);
				return myDs.Tables[0].Rows[0];
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new Exception(er.Message);
			}

		}



		
		/// <summary>
		/// Add  book hits
		/// </summary>
		/// <param name="bookId">book ID</param>
		/// <returns></returns>
		public static void Hits(string bookId)
		{
			strSQL =  "Update Book set hits=hits+1 Where id= " + bookId;
			try
			{
				ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("点击失败!!");
			}
		}



		/// <summary>
		/// Search book
		/// </summary>
		/// <param name="strCondition">search condition</param>
		/// <returns></returns>
		public static DataSet Search(string strCondition)
		{
			strSQL =  "Select * From BookV Where 1=1 " + strCondition + " Order by id";
			try
			{
				return ExecuteSql4Ds(strSQL);				
			}
			catch
			{
				throw new Exception("搜索失败!!");
			}
		}
		

		/// <summary>
		/// Search Book By name,author,translator,publisher
		/// </summary>
		/// <param name="Msg"></param>
		/// <returns></returns>
		public DataSet SearchBook(string Msg)
		{
			string str;

			str="select * from book where name like '%" + Msg + "%' or author like '%" + Msg + "%' or translator like '%" + Msg + "%' or publisher like '%" + Msg + "%' ";
			
			DataSet myDs;

			myDs=ExecuteSql4Ds(str);
			
			return myDs;

		}


		/// <summary>
		/// 
		/// </summary>
		/// <param name="name"></param>
		/// <param name="pwd"></param>
		/// <param name="OrderId"></param>
		/// <returns></returns>

		public DataSet GetOrderDetail(string name,string pwd,int OrderId)
		{
			string str;
			str=" SELECT Orders.Id, OrderDetails.Quantity, Book.Name,Book.Price,Book.Discount" +
				" FROM UserInfo INNER JOIN Orders ON  UserInfo.Id =  Orders.UserId INNER JOIN" +
				" OrderDetails INNER JOIN Book ON  OrderDetails.BookId =  .Book.Id ON " +
				" Orders.Id = OrderDetails.OrderId where UserInfo.name='"+name+"' and UserInfo.Password='"+
				pwd+"' and Orders.id="+OrderId;
			DataSet myDs;
			myDs=ExecuteSql4Ds(str);
			return myDs;
		}
	}
	public class BookType:Base
	{
		public BookType()
		{
			
		}
		//get all data of book type
		public DataView GetBookType()
		{
			String strsql;
			DataSet myDs;
			strsql="select * from booktype order by id";
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;
		}

		/// <summary>
		/// add types
		/// </summary>
		/// <param name="name">name</param>
		/// <param name="memo">memo</param>
		/// <returns></returns>
		public  void Add(string name,string memo)
		{
			if(IfExist(name))
			{
				throw new Exception("type existed");
			}
			else
			{
				strSQL = "Insert into BookType (Name,memo) Values("				
					+ "'" + name + "',"
					+ "'" + memo + "')"	;			
				try
				{
					ExecuteSql(strSQL);				
				}
				catch
				{
					throw new Exception("注册失败！");
				}			
			}					
		}

		/// <summary>
		/// delete type
		/// </summary>
		/// <param name="id">jbook_id</param>
		public void delete(int id)
		{
			strSQL = "Delete From booktype Where id="+id;
			
			try
			{
				ExecuteSql(strSQL);				
			}
			catch
			{
				throw new Exception("delete failed!");
			}
		}
		/// <summary>
		/// if book existed
		/// </summary>
		/// <param name="BTName"></param>
		/// <returns></returns>
		public bool IfExist(string BTName)
		{
			strSQL = "Select Id from booktype Where Name='"
				+ BTName + "'";

			try
			{

				ExecuteSql4Value(strSQL);
				return true;
			}
			catch
			{
				return false;
			}
		}
	}

	public class Comment:Base
	{


		/// <summary>
		/// Get Book Comment By BookId
		/// </summary>
		/// <param name="BookId"></param>
		/// <returns></returns>
		public DataView GetCommentByBookId(int BookId)
		{
			string strsql;
			DataSet myDs;
			strsql="select * from comment where bookid="+BookId;
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;

		}


		public DataView GetCommentTop10()
		{
			string strsql;
			DataSet myDs;
			strsql="select top 10 bookid,  CommentCaption=UserName+':'+SUBSTRING(Caption,0,50) from comment ";
			myDs=ExecuteSql4Ds(strsql);
			return myDs.Tables[0].DefaultView ;
		}


		/// <summary>
		/// Insert Book's Comment
		/// </summary>
		/// <param name="arr"></param>
		public void InsertComment(ArrayList arr)
		{
			SqlConnection myCn=new SqlConnection(strConn);
			SqlCommand myCm=new SqlCommand("InsertComment",myCn);
			myCm.CommandType =CommandType.StoredProcedure ;

			myCm.Parameters.Add(new  SqlParameter("@BookId",SqlDbType.Int));
			myCm.Parameters["@BookId"].Value =arr[0];

			myCm.Parameters.Add(new  SqlParameter("@UserName",SqlDbType.VarChar,50));
			myCm.Parameters["@UserName"].Value =arr[1];

			myCm.Parameters.Add(new  SqlParameter("@Email",SqlDbType.VarChar,50));
			myCm.Parameters["@Email"].Value =arr[2];

			myCm.Parameters.Add(new  SqlParameter("@Caption",SqlDbType.VarChar,100));
			myCm.Parameters["@Caption"].Value =arr[3];

			myCm.Parameters.Add(new  SqlParameter("@Content",SqlDbType.Text ));
			myCm.Parameters["@Content"].Value =arr[4];

			myCm.Parameters.Add(new  SqlParameter("@PublishDate",SqlDbType.DateTime  ));
			myCm.Parameters["@PublishDate"].Value =arr[5];

			try
			{
				myCn.Open() ;
				myCm.ExecuteNonQuery() ;
			}
			catch(System.Data.SqlClient.SqlException er)
			{
				throw new  Exception(er.Message);
			}
			finally
			{
				myCm.Dispose() ;
				myCn.Close() ;
			}



		}
	}
}