book.cs

来自「这是一个编好的网上书店系统」· CS 代码 · 共 604 行 · 第 1/2 页

			}

			try
			{
				currentConn.Open() ;
				currentCmd.ExecuteNonQuery() ;
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new  Exception(e.Message);
			}
			finally
			{
				currentCmd.Dispose() ;
				currentConn.Close() ;
			}
		}


		/// <summary>
		/// Delete Book by BookID
		/// </summary>
		/// <param name="BookID">int</param>
		public void DeleteBookByID(int BookID)
		{
			string strSql = "Delete from BookInfo where ID=" + BookID;
			try
			{
				ExecuteSQLCmd(strSql);
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
		}

		/// <summary>
		/// Get book's message in detail
		/// </summary>
		/// <param name="ClassInfo">int</param>
		/// <returns></returns>
		public SqlDataReader GetBookMessage(int ClassInfo)
		{
			string strSql;
			SqlDataReader result;
			try
			{
				SqlConnection currentConn = new SqlConnection(strConn);
				strSql = "Select * from QuickBookSearchView";
				currentConn.Open();
				SqlCommand currentCmd = new SqlCommand(strSql, currentConn);
				result = currentCmd.ExecuteReader();
				return result;
			} 
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
		}
		
		/// <summary>
		/// Get the top 10 book by the hitnumber
		/// </summary>
		/// <returns>SqlDataReader</returns>
		public SqlDataReader GetBookByHitNumber()
		{
			string strSql;
			SqlDataReader result;
			try
			{
				SqlConnection currentConn = new SqlConnection(strConn);
				strSql = "select top 10 * from QuickBookSearchView order by HitNumber DESC";
				SqlCommand currentCmd = new SqlCommand(strSql, currentConn);
				currentConn.Open();
				result = currentCmd.ExecuteReader();
				return result;
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
		}
		
		/// <summary>
		/// Get Book's subtype
		/// </summary>
		/// <param name="nSuperType"></param>
		/// <returns></returns>
		public SqlDataReader GetBookSubType(int nSuperType)
		{
			string strSql;
			SqlDataReader result;
			try
			{		
				SqlConnection currentConn = new SqlConnection(strConn);
				strSql = "select * from [BookType] where SuperBookType=" + nSuperType;
				SqlCommand currentCmd = new SqlCommand(strSql, currentConn);
				currentConn.Open();
				result = currentCmd.ExecuteReader();
				return result;
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
		}
		
		/// <summary>
		/// Get one book's detail information
		/// </summary>
		/// <param name="BookID">DataRow</param>
		/// <returns></returns>
		public DataRow GetBookDetail(int BookID)
		{
			string strSql;
			DataSet currentDS;
			try
			{
				//strSql = "select * from BookDetail where ID=" + BookID;
				strSql = "SELECT BookType.TypeName, BookInfo.ID, BookInfo.BookName,BookInfo.PublishingHouse, BookInfo.Author," +
					"BookInfo.Translator,BookInfo.ISBN, BookInfo.Pages, BookInfo.PublishDate,BookInfo.Price, BookInfo.HitNumber, "+
					"BookInfo.SpecialPrice, BookInfo.Discount, BookInfo.Description,img=case when(not BookInfo.Cover is null) then " +
					"' <img src=ReadBookCover.aspx?id='+cast(BookInfo.id as varchar(10))+' Border=1 width=100 height=125> '" + 
					"else ' <img src=img/NoCover.jpg border=1 width=100 height=125>'  end FROM BookInfo INNER JOIN BookType " + 
					"ON BookInfo.ClassInfo = BookType.BookType where ID=" + BookID;
				currentDS = ExecuteSQLForDS(strSql);
				return currentDS.Tables[0].Rows[0];
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
		}
		
		public DataRow GetTypeDetail(int nSubType)
		{
			string strSql;
			DataSet currentDS;
			try
			{
				strSql = "select * from BookType where BookType=" + nSubType;
				currentDS = ExecuteSQLForDS(strSql);
				return currentDS.Tables[0].Rows[0];
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
		}
		/// <summary>
		/// Add one to HitNumber
		/// </summary>
		/// <param name="BookID">int</param>
		public void AddHitNumber(int BookID)
		{
			string strSql;
			strSql = "Update BookInfo set HitNumber = HitNumber + 1 where ID = " + BookID;
			try
			{
				ExecuteSQLForDS(strSql);
			}
			catch
			{
				throw new Exception("Add HitNumber failed");
			}
		}

		/// <summary>
		/// Search with keyword for BookName or PublishingHouse or Author
		/// </summary>
		/// <param name="searchStr">string</param>
		/// <returns></returns>
		public DataSet SearchBook(string searchStr)
		{
			string strSql;
			strSql = "select * from BookInfo where BookName like '%" + searchStr + "%' or PublishingHouse like '%" 
					+ searchStr + "%' or Author like '%" + searchStr +"%'";
			DataSet currentDS;
			currentDS = ExecuteSQLForDS(strSql);
			return currentDS;		
		}
		
		/// <summary>
		/// Get book order detail
		/// </summary>
		/// <param name="UserName">string</param>
		/// <param name="OrderID">int</param>
		/// <returns></returns>
		public DataSet GetOrderDetail(string UserName, int OrderID)
		{
			string strSql;
			strSql = "select * from OrderDetail where UserName = " + UserName + "and ID = " + OrderID;
			DataSet currentDS;
			currentDS = ExecuteSQLForDS(strSql);
			return currentDS;
		}

		public DataView GetBookDetailBySubType(int nSubType, string strOrder)
		{
			string strSql = "select * from BookInfo where ClassInfo=" + nSubType  + "order by " + strOrder + " desc";
			DataSet currentDS;
			try
			{
				currentDS = ExecuteSQLForDS(strSql);
				return currentDS.Tables[0].DefaultView;
			}
			catch
			{
				throw new Exception("得到子类型书籍操作失败！");
			}
		}
		/// <summary>
		/// Get QuickType // Maybe no use later.
		/// </summary>
		/// <returns></returns>
		public static DataView GetQuickType()
		{
			string strSql = "select BookType, TypeName from BookType";
			DataSet currentDS;
			try
			{
				currentDS = ExecuteSQLForDS(strSql);
				return currentDS.Tables[0].DefaultView;
			}
			catch
			{
				throw new Exception("Type search failed");
			}
		}

		#endregion
	
	}


	public class Comment : DBBaseClass
	{
		/// <summary>
		/// Get all the comment of one book by id
		/// </summary>
		/// <param name="BookID">int</param>
		/// <returns></returns>
		public DataView GetBookComment(int BookID)
		{
			string strSql;
			DataSet currentDS;
			strSql = "select * from [BookComment] where BookID = " + BookID;
			currentDS = ExecuteSQLForDS(strSql);
			return currentDS.Tables[0].DefaultView;
		}

		/// <summary>
		/// Insert user's comment to database
		/// </summary>
		/// <param name="tmpArrayList">ArrayList</param>
		public void InsertComment(ArrayList tmpArrayList)
		{
			SqlConnection currentConn = new SqlConnection(strConn);
			SqlCommand currentCmd = new SqlCommand("InsertComment", currentConn);
			currentCmd.CommandType = CommandType.StoredProcedure;

			currentCmd.Parameters.Add(new SqlParameter("@BookID", SqlDbType.Int));
			currentCmd.Parameters["@BookID"].Value = tmpArrayList[0];

			currentCmd.Parameters.Add(new SqlParameter("@Caption", SqlDbType.VarChar, 80));
			currentCmd.Parameters["@Caption"].Value = tmpArrayList[1];

			currentCmd.Parameters.Add(new SqlParameter("@CommentInfo",SqlDbType.Text));
			currentCmd.Parameters["@CommentInfo"].Value = tmpArrayList[2];

			currentCmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 40));
			currentCmd.Parameters["@UserName"].Value = tmpArrayList[3];

			currentCmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 60));
			currentCmd.Parameters["@Email"].Value = tmpArrayList[4];

			currentCmd.Parameters.Add(new SqlParameter("@Rank",SqlDbType.TinyInt));
			currentCmd.Parameters["@Rank"].Value = tmpArrayList[5];

			currentCmd.Parameters.Add(new SqlParameter("@PublishDate", SqlDbType.DateTime));
			currentCmd.Parameters["@PublishDate"].Value = tmpArrayList[6];

			try
			{
				currentConn.Open();
				currentCmd.ExecuteNonQuery();
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
			finally
			{
				currentCmd.Dispose();
				currentConn.Close();
			}
		}
	}


}