Imports System.Data.OleDb   ' 导入命名空间
Public Class 例12_10
    Dim conn As OleDbConnection ' 定义Connection变量
    Dim da As OleDbDataAdapter  ' 定义DataAdapter变量
    Dim bmb As BindingManagerBase  ' 定义BindingManagerBase变量
    Const DATANAME As String = "F:\Book_manager.mdb"    ' 数据库名称   
    Const TABLENAME As String = "bookinfo"    ' 数据表名 
    Dim ds As New DataSet()   ' 创建DataSet对象
    Dim curIndex As Integer = 0   '记录当前位置
    Dim oldId As String     ' 记录原图书编号

    Private Sub OpenDataBase()  ' 打开数据库连接过程
        Dim str As String = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source =" & DATANAME '定义连接字符串
        Dim selSql As String = "SELECT * FROM bookinfo"
        conn = New OleDbConnection(str)    '创建OleDbConnection对象
        conn.Open()     '打开数据库连接
        da = New OleDbDataAdapter(selSql, conn)  '初始化DataAdapter对象da
        da.Fill(ds, TABLENAME)    '将记录集填充到ds中
        conn.Close()
        bmb = Me.BindingContext(ds, TABLENAME) '创建BindingManagerBase对象
    End Sub

    Private Sub BookManager_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        OpenDataBase()  ' 打开数据库连接
        BindData()   ' 数据绑定
        DisplayRec()          '显示当前记录号
    End Sub
    Private Sub BindData()  ' 绑定数据过程
        ' 绑定各个文本框数据
        txtID.DataBindings.Add("text", ds, "bookinfo.ID")
        txtAuthor.DataBindings.Add("text", ds, "bookinfo.Author")
        txtBookName.DataBindings.Add("text", ds, "bookinfo.BookName")
        txtDate.DataBindings.Add("text", ds, "bookinfo.PubDate")
        txtCate.DataBindings.Add("text", ds, "bookinfo.Category")
        txtISBN.DataBindings.Add("text", ds, "bookinfo.ISBN")
        txtNum.DataBindings.Add("text", ds, "bookinfo.Num")
        txtPrice.DataBindings.Add("text", ds, "bookinfo.Price")
        txtPublisher.DataBindings.Add("text", ds, "bookinfo.Publisher")
        ' 绑定数据网格DataGridView控件
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = TABLENAME
    End Sub
    Private Sub DisplayRec()
        Dim sum As Integer
        sum = bmb.Count
        Label12.Text = "当前是第" & bmb.Position + 1 & "条记录，共" & sum & "条记录"
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        '移到首记录
        bmb.Position = 0
        '显示当前记录号
        DisplayRec()
    End Sub
    Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Click
        ' 定位到单击的那一行记录
        bmb.Position = DataGridView1.CurrentRow.Index
        '显示当前记录号
        DisplayRec()
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        '移到末记录
        bmb.Position = bmb.Count - 1
        '显示当前记录号
        DisplayRec()
    End Sub

    Private Sub btnPre_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPre.Click
        ' 移至上一条记录
        bmb.Position -= 1
        '显示当前记录号
        DisplayRec()
    End Sub

    Private Sub btnAfter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAfter.Click
        ' 移至下一条记录
        bmb.Position += 1
        '显示当前记录号
        DisplayRec()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        '添加记录
        If btnAdd.Text = "增加" Then  '若当前按钮为增加状态，用户可输入新记录            
            ' 创建新记录的对象
            Dim row As DataRow
            row = ds.Tables(TABLENAME).NewRow
            ds.Tables(TABLENAME).Rows.Add(row)
            ' 刷新DataGridView控件中的内容
            DataGridView1.DataSource = ds.Tables(TABLENAME)
            bmb.Position = bmb.Count - 1
            ' 设置按钮失效
            btnDisabled()
            btnDel.Enabled = False
            btnUpdate.Enabled = False
            ' 当前按钮文本属性设置为“保存”
            btnAdd.Text = "保存"
            txtID.Focus()
        ElseIf btnAdd.Text = "保存" Then   '若当前按钮为保存状态，可保存数据至数据库
            saveRec() ' 保存数据
            DisplayRec()
            btnAdd.Text = "增加"
            ' 设置按钮失有效
            btnEnabled()
            btnDel.Enabled = True
            btnUpdate.Enabled = True
        End If
    End Sub
    Private Sub saveRec()  ' 保存记录
        Dim addSql As String
        If txtID.Text = "" Then
            MsgBox("图书编号不能为空!")
            Exit Sub
        End If
        ' 创建插入记录的SQL语句
        addSql = "INSERT INTO bookinfo  VALUES(""" _
            & txtID.Text & """,""" _
            & txtBookName.Text & """,""" _
            & txtCate.Text & """,""" _
            & txtAuthor.Text & """," _
            & txtPrice.Text & ",""" _
            & txtISBN.Text & """,""" _
            & txtPublisher.Text & """,""" _
            & txtDate.Text & """," _
            & txtNum.Text & ")"
        '创建插入记录的command对象
        da.InsertCommand = New OleDbCommand(addSql, conn)
        da.Update(ds, TABLENAME)
        ds.Clear() ' 清空数据集
        da.Fill(ds, TABLENAME)  ' 重新填充数据集
        DataGridView1.DataSource = ds.Tables(TABLENAME)  ' 刷新DataGridView控件中的内容
        bmb.Position = bmb.Count - 1
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        '删除当前记录
        Dim answer As MsgBoxResult
        answer = MsgBox("确定要删除当前记录吗？", CType(vbYesNo + vbExclamation, MsgBoxStyle), "警告")
        If answer = MsgBoxResult.Yes Then
            Dim delSql As String = "DELETE FROM bookinfo WHERE ID=""" & txtID.Text & """"
            '创建删除记录的command对象
            Dim delCommand = New OleDbCommand(delSql, conn)
            conn.Open()
            delCommand.ExecuteNonQuery()
            conn.Close()
            ds.Clear() ' 清空数据集
            da.Fill(ds, TABLENAME)  ' 重新填充数据集
            DataGridView1.DataSource = ds.Tables(TABLENAME)
            bmb.Position = 0
            DisplayRec()
        End If
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        End '退出程序
    End Sub
    Private Sub btnEnabled()
        ' 使浏览按钮及DataGridView可用
        btnAfter.Enabled = True
        btnFirst.Enabled = True
        btnPre.Enabled = True
        btnLast.Enabled = True
        DataGridView1.Enabled = True
    End Sub
    Private Sub btnDisabled()
        '使浏览按钮及DataGridView不可用
        btnAfter.Enabled = False
        btnFirst.Enabled = False
        btnPre.Enabled = False
        btnLast.Enabled = False
        DataGridView1.Enabled = False
    End Sub
End Class