?? databook.vb
字號:
'********************************************************************************
'這是數據訪問組件的源文件，主要用于和數據庫打交道,其中定義了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")
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -