databook.vb

《ASP.NET C#程序设计案例教程》

'********************************************************************************
'这是数据访问组件的源文件，主要用于和数据库打交道,其中定义了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										'定义一个私有变量，用来设置数据库连接字符串

	'建立构造函数
	Public Sub New()
		_strConn=ConfigurationSettings.AppSettings("strConn")		'将数据库连接字符串赋值给私有变量_strConn
	End Sub

	'该函数用来返回所有书籍
	Public Function GetBook() As DataView
		Dim conn As New OleDbConnection(_strConn)					'这里使用了_strConn表示的数据库连接字符串
		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)							'返回函数值
	End Function

	'该函数用来返回一本书籍，这里和DataBook类不太一样，这里返回了一本书，但是也是一个DataView对象
	Public Function GetOneBook(intBookId As Integer) As DataView
		Dim conn As New OleDbConnection(_strConn)						'这里使用了_strConn表示的数据库连接字符串
		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)							'返回函数值
	End Function

	'返回某类书按销量排名前10名
	Public Function GetTop10(intKindId As Integer) As DataView
		Dim conn As New OleDbConnection(_strConn)						'这里使用了_strConn表示的数据库连接字符串
		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)							'返回函数值
	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对象，注意这里使用了含有参数的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)
		'下面给参数赋值
		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))			'注意这里是二进制数据
		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

		'下面执行插入记录操作
		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对象，注意这里使用了含有参数的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)
		'下面给参数赋值
		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))			'注意这里是二进制数据
			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

		'下面执行插入记录操作
		conn.open()                             
		cmd.ExecuteNonQuery()
		conn.close()                            
	End Sub

	'该函数用来删除一本书
	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

	'下面的函数用来根据记录编号BookId返回一个DataBook对象,就是下面定义的第2个类
	Public Function Items(intBookId As integer) As DataBook
		'下面建立DataUser对象
		Dim db As New DataBook(intBookId)									'注意这里的传入参数
		Return db															'返回函数值
	End Function
End Class

'----------------------------------------------------------------------------------------------------
'第2个类仅仅用来返回一条记录的各个字段值
Public Class DataBook														'DataBBS是自己定义的类的名称
	Private _strConn As String													'定义一个私有变量，用来设置数据库连接字符串

	'下面定义了几个属性，用来返回对应字段值
	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

	
	'这是构造函数，在其中给每一个属性赋值
	Public Sub New(intBookId As Integer)
		_strConn=ConfigurationSettings.AppSettings("strConn")							'将数据库连接字符串赋值给私有变量_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对象
		'下面读取数据，并给属性赋值
		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")