'********************************************************************************
'這是數(shù)據訪問組件的源文件，主要用于和數(shù)據庫打交道,其中定義了4個類。
'DataBooks   類，專門用來處理有關書籍的操作
'DataBook    類，主要用來返回一本書的有關信息
'DataKinds   類，專門用來處理有關類別的操作
'DataKind    類，主要用來返回某一類的有關信息
'DataOrders  類，專門用來處理有關訂單的操作
'DataOrder   類，主要用來返回一個訂單的有關信息
'********************************************************************************

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration										'因為用到了ConfigurationSettings類
Imports MicroSoft.VisualBasic
Imports System.Web

NameSpace nsShop													'nsShop為自己定義的名稱空間的名稱

'----------------------------------------------------------------------------------------------------
'該類用來查詢、添加、刪除和更新記錄
Public Class DataBooks												'DataBooks是自己定義的類的名稱

	Private _strConn As String										'定義一個私有變量，用來設置數(shù)據庫連接字符串

	'建立構造函數(shù)
	Public Sub New()
		_strConn=ConfigurationSettings.AppSettings("strConn")		'將數(shù)據庫連接字符串賦值給私有變量_strConn
	End Sub

	'該函數(shù)用來返回所有書籍
	Public Function GetBook() As DataView
		Dim conn As New OleDbConnection(_strConn)					'這里使用了_strConn表示的數(shù)據庫連接字符串
		Dim strSql As String="Select * From Book"								
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "Book")											
		Return(ds.Tables("Book").DefaultView)							'返回函數(shù)值
	End Function

	'該函數(shù)用來返回一本書籍，這里和DataBook類不太一樣，這里返回了一本書，但是也是一個DataView對象
	Public Function GetOneBook(intBookId As Integer) As DataView
		Dim conn As New OleDbConnection(_strConn)						'這里使用了_strConn表示的數(shù)據庫連接字符串
		Dim strSql As String="Select * From Book Where BookId=" & intBookId								
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "Book")											
		Return(ds.Tables("Book").DefaultView)							'返回函數(shù)值
	End Function

	'返回某類書按銷量排名前10名
	Public Function GetTop10(intKindId As Integer) As DataView
		Dim conn As New OleDbConnection(_strConn)						'這里使用了_strConn表示的數(shù)據庫連接字符串
		Dim strSql As String="Select Book.BookId,Book.BookName,TableTemp.BuyTotal From (Select Top 10 Orders_particular.BookId,Sum(Orders_particular.BookNum) As BuyTotal From Orders_particular,Book Where Book.BookId=Orders_particular.BookId And Book.KindId=" & intKindId & " Group By Orders_particular.BookId Order By Sum(Orders_particular.BookNum) Desc) As TableTemp,Book Where TableTemp.BookId=Book.BookId"								
		Dim adp As New OleDbDataAdapter(strSql, conn)					
		Dim ds As New DataSet()											
		adp.Fill(ds, "Top10")											
		Return(ds.Tables("Top10").DefaultView)							'返回函數(shù)值
	End Function


	'該過程用來插入一本書
	Public Sub InsertBook(strBookName As String,strBookAuthor As String,sglBookPrice As Single,intBookNum As Integer,bytPicture() As Byte,strBookIntro As String, strBookIndex As String,intKindId As Integer,dtmSubmitDate As Date)
		Dim conn As New OleDbConnection(_strConn)					'使用了私有變量的值
		'建立Command對象，注意這里使用了含有參數(shù)的SQL語句
		Dim strSql As String="Insert Into Book(BookName,BookAuthor,BookPrice,BookNum,Picture,KindId,BookIntro,BookIndex,SubmitDate) Values(@BookName,@BookAuthor,@BookPrice,@BookNum,@picture,@KindId,@BookIntro,@BookIndex,@SubmitDate)"
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面給參數(shù)賦值
		cmd.Parameters.Add(New OleDbParameter("@BookName",OleDbType.VarWChar,50))
		cmd.Parameters("@BookName").Value=strBookName
		cmd.Parameters.Add(New OleDbParameter("@BookAuthor",OleDbType.VarWChar,50))
		cmd.Parameters("@BookAuthor").Value=strBookAuthor
		cmd.Parameters.Add(New OleDbParameter("@BookPrice",OleDbType.Single))
		cmd.Parameters("@BookPrice").Value=sglBookPrice
		cmd.Parameters.Add(New OleDbParameter("@BookNum",OleDbType.Integer))
		cmd.Parameters("@BookNum").Value=intBookNum
		cmd.Parameters.Add(New OleDbParameter("@Picture",OleDbType.VarBinary))			'注意這里是二進制數(shù)據
		cmd.Parameters("@Picture").Value=bytPicture
		cmd.Parameters.Add(New OleDbParameter("@KindId",OleDbType.Integer))
		cmd.Parameters("@KindId").Value=intKindId
		cmd.Parameters.Add(New OleDbParameter("@BookIntro",OleDbType.VarWChar,2000))
		cmd.Parameters("@BookIntro").Value=strBookIntro
		cmd.Parameters.Add(New OleDbParameter("@BookIndex",OleDbType.VarWChar,8000))
		If strBookIndex<>"" Then
			cmd.Parameters("@BookIndex").Value=strBookIndex
		Else
			cmd.Parameters("@BookIndex").Value=DBNull.Value						'賦值NULL
		End If
		cmd.Parameters.Add(New OleDbParameter("@SubmitDate",OleDbType.Date))
		cmd.Parameters("@SubmitDate").Value=dtmSubmitDate

		'下面執(zhí)行插入記錄操作
		conn.open()                             
		cmd.ExecuteNonQuery()
		conn.close()                            
	End Sub

	'該過程用來更新一本書的信息
	Public Sub UpdateBook(intBookId As Integer,strBookName As String,strBookAuthor As String,sglBookPrice As Single,intBookNum As Integer,bytPicture() As Byte,strBookIntro As String, strBookIndex As String,intKindId As Integer,dtmSubmitDate As Date)
		Dim conn As New OleDbConnection(_strConn)					'使用了私有變量的值
		'建立Command對象，注意這里使用了含有參數(shù)的SQL語句
		'這里如果客戶沒有提交新圖片，則不更換
		Dim strSql As String
		If bytPicture.Length>1 Then
			strSql="Update Book Set BookName=@BookName,BookAuthor=@BookAuthor,BookPrice=@BookPrice,BookNum=@BookNum,Picture=@Picture,KindId=@KindId,BookIntro=@BookIntro,BookIndex=@BookIndex,SubmitDate=@SubmitDate Where BookId=@BookId"
		Else
			strSql="Update Book Set BookName=@BookName,BookAuthor=@BookAuthor,BookPrice=@BookPrice,BookNum=@BookNum,KindId=@KindId,BookIntro=@BookIntro,BookIndex=@BookIndex,SubmitDate=@SubmitDate Where BookId=@BookId"
		End If
		Dim cmd As New OleDbCommand(strSql, conn)
		'下面給參數(shù)賦值
		cmd.Parameters.Add(New OleDbParameter("@BookName",OleDbType.VarWChar,50))
		cmd.Parameters("@BookName").Value=strBookName
		cmd.Parameters.Add(New OleDbParameter("@BookAuthor",OleDbType.VarWChar,50))
		cmd.Parameters("@BookAuthor").Value=strBookAuthor
		cmd.Parameters.Add(New OleDbParameter("@BookPrice",OleDbType.Single))
		cmd.Parameters("@BookPrice").Value=sglBookPrice
		cmd.Parameters.Add(New OleDbParameter("@BookNum",OleDbType.Integer))
		cmd.Parameters("@BookNum").Value=intBookNum
		If bytPicture.Length>1 Then
			cmd.Parameters.Add(New OleDbParameter("@Picture",OleDbType.VarBinary))			'注意這里是二進制數(shù)據
			cmd.Parameters("@Picture").Value=bytPicture
		End If
		cmd.Parameters.Add(New OleDbParameter("@KindId",OleDbType.Integer))
		cmd.Parameters("@KindId").Value=intKindId
		cmd.Parameters.Add(New OleDbParameter("@BookIntro",OleDbType.VarWChar,2000))
		cmd.Parameters("@BookIntro").Value=strBookIntro
		cmd.Parameters.Add(New OleDbParameter("@BookIndex",OleDbType.VarWChar,8000))
		If strBookIndex<>"" Then
			cmd.Parameters("@BookIndex").Value=strBookIndex
		Else
			cmd.Parameters("@BookIndex").Value=DBNull.Value						'賦值NULL
		End If
		cmd.Parameters.Add(New OleDbParameter("@SubmitDate",OleDbType.Date))
		cmd.Parameters("@SubmitDate").Value=dtmSubmitDate
		cmd.Parameters.Add(New OleDbParameter("@BookId",OleDbType.Integer))
		cmd.Parameters("@BookId").Value=intBookId

		'下面執(zhí)行插入記錄操作
		conn.open()                             
		cmd.ExecuteNonQuery()
		conn.close()                            
	End Sub

	'該函數(shù)用來刪除一本書
	Public Sub DeleteBook(intBookId As Integer)
		Dim conn As New OleDbConnection(_strConn) 
		Dim strSql="Delete from Book Where BookId=" & intBookId
		Dim cmd As New OleDbCommand(strSql, conn)
		conn.Open()
		cmd.ExecuteNonQuery()						
		conn.Close()
	End Sub

	'下面的函數(shù)用來根據記錄編號BookId返回一個DataBook對象,就是下面定義的第2個類
	Public Function Items(intBookId As integer) As DataBook
		'下面建立DataUser對象
		Dim db As New DataBook(intBookId)									'注意這里的傳入參數(shù)
		Return db															'返回函數(shù)值
	End Function
End Class

'----------------------------------------------------------------------------------------------------
'第2個類僅僅用來返回一條記錄的各個字段值
Public Class DataBook														'DataBBS是自己定義的類的名稱
	Private _strConn As String													'定義一個私有變量，用來設置數(shù)據庫連接字符串

	'下面定義了幾個屬性，用來返回對應字段值
	Public BookId As Integer
	Public BookName As String
	Public BookAuthor As String
	Public BookPrice As Single
	Public BookNum As Integer
	Public Picture() As Byte
	Public KindId As Integer
	Public BookIntro As String
	Public BookIndex As String
	Public SubmitDate As Date

	
	'這是構造函數(shù)，在其中給每一個屬性賦值
	Public Sub New(intBookId As Integer)
		_strConn=ConfigurationSettings.AppSettings("strConn")							'將數(shù)據庫連接字符串賦值給私有變量_strConn
		Dim conn As New OleDbConnection(_strConn)										'建立Connection對象
		Dim strSql As String="Select * From Book Where BookId=" & intBookId				'建立SQL語句
		Dim cmd As New OleDbCommand(strSql, conn)										'建立Command對象
		conn.Open()                                            
		Dim dr As OleDbDataReader = cmd.ExecuteReader()									'建立DataReader對象
		'下面讀取數(shù)據，并給屬性賦值
		dr.Read()		
		BookId=dr.Item("BookId")
		BookName=dr.Item("BookName")
		BookAuthor=dr.Item("BookAuthor")
		BookPrice=dr.Item("BookPrice")
		BookNum=dr.Item("BookNum")
		Picture=dr.Item("Picture")
		KindId=dr.Item("KindId")
		BookIntro=dr.Item("BookIntro")
		BookIndex=dr.Item("BookIndex").ToString()
		SubmitDate=dr.Item("SubmitDate")