databook.vb

电子商务买书的网站

Imports System.Data
Imports System.Data.OleDb
'----------------------------------------------------------------------------------------------------
'该类用来查询、添加、删除和更新记录
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(ByVal 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(ByVal 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(ByVal strBookName As String, ByVal strBookAuthor As String, ByVal sglBookPrice As Single, ByVal intBookNum As Integer, ByVal bytPicture() As Byte, ByVal strBookIntro As String, ByVal strBookIndex As String, ByVal intKindId As Integer, ByVal 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(ByVal intBookId As Integer, ByVal strBookName As String, ByVal strBookAuthor As String, ByVal sglBookPrice As Single, ByVal intBookNum As Integer, ByVal bytPicture() As Byte, ByVal strBookIntro As String, ByVal strBookIndex As String, ByVal intKindId As Integer, ByVal 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(ByVal 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(ByVal 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(ByVal 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")
        conn.Close()
    End Sub

End Class

'----------------------------------------------------------------------------------------------------
'第3个类，用来处理跟类别有关的操作
Public Class DataKinds