VB.net实现通讯录

基于command对象的数据访问

Imports System.Data.OleDb

Public Class Form1

    Public DatabasePath As String = "F:\www\研一下\通讯录\通讯录.accdb" '定义数据库的具体路径
    Public ConnectionString As String = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & DatabasePath '定义数据库连接字符串
    Public DatabaseConnection As OleDbConnection = New OleDbConnection(ConnectionString) '定义数据库连接对象，通过该连接对象执行与数据库的连接通断
    Public DataCommand As OleDbCommand '数据库SQL语句操作命令对象。通过该对象调用SQL语句的执行方法
    Public SQLstring As String 'SQL语句载体

    '刷新通讯录
    Private Sub FreshButton_Click(sender As Object, e As EventArgs) Handles FreshButton.Click
        Dim DatabaseSet As DataSet = New DataSet("通讯录") '定义数据库DataSet对象，其相当于驻存在内存中的数据库副本
        Dim DatabaseAdapter As OleDbDataAdapter '定义数据适配器DataAdapter

        DatabaseConnection.Open() '与数据库连接

        SQLstring = "select * from 通讯录 " '定义“选取数据库中所有记录”的SQL命令语句
        DatabaseAdapter = New OleDbDataAdapter(SQLstring, DatabaseConnection) '数据库适配器对象中SelectCommand属性select命令执行
        DatabaseAdapter.Fill(DatabaseSet, "通讯录") '数据适配器的Fill方法将SelectCommand的执行结果返回到DataSet中
        DataGridView1.DataSource = DatabaseSet.Tables("通讯录") 'DataBaseSet中的“通讯录”表在DataGridView中进行展示

        DatabaseConnection.Close() '与数据库断开

    End Sub
    '添加联系人信息
    Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click

        DatabaseConnection.Open()

        SQLstring = "insert into 通讯录(姓名,性别,年龄,籍贯,政治面貌,电话,邮箱) values('" & TextBox1.Text & "','" & ComboBox1.Text & "', " & TextBox3.Text & ",'" & TextBox4.Text & "','" & ComboBox2.Text & "'," & TextBox6.Text & ",'" & TextBox7.Text & "')"
        '定义”向通讯录中所有字段列插入相应记录“的SQL语句
        DataCommand = New OleDbCommand(SQLstring, DatabaseConnection) '建立DataCommand对象以对数据库进行访问
        DataCommand.ExecuteNonQuery() '执行SQL语句

        DatabaseConnection.Close()
    End Sub
    '删除指定联系人信息
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DatabaseConnection.Open()

        SQLstring = "delete * from 通讯录 where  姓名='" & TextBox2.Text & "'"
        DataCommand = New OleDbCommand(SQLstring, DatabaseConnection)
        DataCommand.ExecuteNonQuery()

        DatabaseConnection.Close()

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: 这行代码将数据加载到表“通讯录DataSet.通讯录”中。您可以根据需要移动或删除它。
        Me.通讯录TableAdapter.Fill(Me.通讯录DataSet.通讯录)

    End Sub

End Class

基于断开式连接的数据访问

Imports System.Data.OleDb

Public Class Form1
    Public DatabasePath As String = "F:\www\研一下\通讯录\通讯录.accdb" '数据库文件的全路径
    Public ConString As String = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & DatabasePath '数据库连接字符串
    Public DatabaseConnection As OleDbConnection = New OleDbConnection(ConString) '定义数据库连接对象
    Public DatabaseCommand As OleDbCommand = New OleDbCommand '定义对数据库SQL语句操作执行的对象
    Public SQLstring As String '定义SQL语句载体
    Public DataAdapter As OleDbDataAdapter '定义数据适配器
    Dim DataSet As DataSet = New DataSet() '定义DataSet对象，其可以理解为内存中的关系模型数据库，数据库的数据来源于外界物理数据库

    '保存联系人
    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        DatabaseConnection.Open() '数据库连接开启

        SQLstring = "select * from 通讯录"
        DatabaseCommand.Connection = DatabaseConnection '指定数据库SQL语句操作执行的对象与目标数据库之间的连接
        DatabaseCommand.CommandText = SQLstring '指定数据库SQL语句操作执行的对象的命令文本
        DataAdapter = New OleDbDataAdapter(DatabaseCommand) '指定数据适配器执行目标SQL命令的条件（connection+SQLstring)
        DataSet.Clear() '清空Dataset中的内容
        DataAdapter.Fill(DataSet, "通讯录")

        Dim DatasetTableRow As DataRow
        DatasetTableRow = DataSet.Tables("通讯录").NewRow()
        DatasetTableRow("姓名") = NameTextBox.Text
        DatasetTableRow("性别") = GenderComboBox.Text
        DatasetTableRow("年龄") = AgeTextBox.Text
        DatasetTableRow("籍贯") = HomeTextBox.Text
        DatasetTableRow("政治面貌") = StateComboBox.Text
        DatasetTableRow("电话") = PhoneTextBox.Text
        DatasetTableRow("邮箱") = EmailTextBox.Text
        DataSet.Tables("通讯录").Rows.Add(DatasetTableRow)

        Dim DatasetBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
        DataAdapter.Update(DataSet, "通讯录") '数据适配器将执行之后的结果集更新至物理数据库

        DataGridView1.DataSource = DataSet.Tables("通讯录")
        MsgBox("添加成功!")

        DatabaseConnection.Close() '数据库连接关闭
    End Sub

    '刷新通讯录
    Private Sub FreshButton_Click(sender As Object, e As EventArgs) Handles FreshButton.Click
        DatabaseConnection.Open()

        SQLstring = "select * from 通讯录"
        DataAdapter = New OleDbDataAdapter(SQLstring, DatabaseConnection)
        DataSet.Clear()
        DataAdapter.Fill(DataSet, "通讯录")

        DataGridView1.DataSource = DataSet.Tables("通讯录")
        MsgBox("刷新成功")

        DatabaseConnection.Close()
    End Sub

    '删除指定联系人信息
    Private Sub DeleteButton_Click(sender As Object, e As EventArgs) Handles DeleteButton.Click
        DatabaseConnection.Open()

        SQLstring = "select * from 通讯录"
        DataAdapter = New OleDbDataAdapter(SQLstring, DatabaseConnection)
        DataSet.Clear()
        DataAdapter.Fill(DataSet, "通讯录")

        Dim RowCollection As DataRowCollection = DataSet.Tables("通讯录").Rows
        DataSet.Tables("通讯录").PrimaryKey = New DataColumn() {DataSet.Tables("通讯录").Columns("姓名")}
        If RowCollection.Contains(AimDeleteTextBox.Text) Then
            Dim AimRows As DataRow = RowCollection.Find(AimDeleteTextBox.Text)
            RowCollection.Remove(AimRows)
            MsgBox("删除成功")
            Dim DatasetBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
            DatasetBuilder.GetDeleteCommand()
            DataAdapter.Update(DataSet, "通讯录")
            DataGridView1.DataSource = DataSet.Tables("通讯录")
        Else
            MsgBox("删除失败")
        End If

        DatabaseConnection.Close()
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: 这行代码将数据加载到表“通讯录DataSet.通讯录”中。您可以根据需要移动或删除它。
        Me.通讯录TableAdapter.Fill(Me.通讯录DataSet.通讯录)

    End Sub
End Class

运行结果