在DataGridView中实现增删改查

这几天一直在网上找如何在DataGridView中实现增删改查功能，找的真不容易啊!

现在我给做出来了，希望它能帮助需要这种功能的人

 

Imports System.Data.SqlClient
'Imports 练习.DataBase

Public Class Form1
    Dim da As New DataTable
    Dim ad As New SqlDataAdapter
    Dim conStr As String = "Data Source=JIAOPENG;Initial Catalog=MyDB;Integrated Security=True"

    '构造函数
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        AddSource()

        Me.btnUpdate.Enabled = False
    End Sub

    '查询数据
    Public Sub AddSource()
        Dim con As New SqlConnection(conStr)
        Dim comStr As String = "select * from Users"
        Dim com As New SqlCommand(comStr, con)
        con.Open()

        ad.SelectCommand = com
        ad.Fill(da)
        DataGridView1.DataSource = da

        '更改该列背景色
        DataGridView1.Columns(0).DefaultCellStyle.BackColor = Color.Aqua

        com.ExecuteScalar()
        con.Close()
    End Sub

    'DataGridView1的CellMouseEnter事件处理器
    Private Sub DataGridView1_CellMouseEnter(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles DataGridView1.CellMouseEnter
        'Header以外的单元格
        If e.ColumnIndex >= 0 And e.RowIndex >= 0 Then
            Dim dgv As DataGridView = CType(sender, DataGridView)
            '变更单元格的Style
            dgv(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.Red
            dgv(e.ColumnIndex, e.RowIndex).Style.SelectionBackColor = Color.Red
        End If
    End Sub

    'DataGridView1的CellMouseLeave事件处理器
    Private Sub DataGridView1_CellMouseLeave(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles DataGridView1.CellMouseLeave
        'Header以外的单元格
        If e.ColumnIndex >= 0 And e.RowIndex >= 0 Then
            Dim dgv As DataGridView = CType(sender, DataGridView)
            '单元格的Style返回原设定
            '如果单元格的Style删除了，设定为Null
            dgv(e.ColumnIndex, e.RowIndex).Style.BackColor = Color.Empty
            dgv(e.ColumnIndex, e.RowIndex).Style.SelectionBackColor = Color.Empty
        End If
    End Sub

    '行插入事件
    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Dim dr As DataRow = da.NewRow()
        'dr(1) = " "   '插入必须要有数据，插入到新行的第二列中
        da.Rows.InsertAt(dr, DataGridView1.CurrentRow.Index)
    End Sub

    '更新按钮事件
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        'Dim r As DataRow
        'Dim count As Integer
        'For Each r In da.Rows()
        '    count = count + 1
        '    If (r.RowState = DataRowState.Added) Then  'Datable中是否有添加新行
        '        If (DataGridView1.Rows(count - 1).Cells(1).Value.ToString() = "") Then
        '            MessageBox.Show("新行中数据为空!插入失败!", "失败!", MessageBoxButtons.OK, MessageBoxIcon.Information)
        '            DataGridView1.Rows(count - 1).Cells(1).Selected().ToString()
        '        Else
        '            UpdateSource()
        '        End If
        '    End If
        '    If (r.RowState = DataRowState.Modified) Then 'DataTable中数据是否被修改
        '        UpdateSource()
        '    End If
        'Next
        UpdateSource()
        Me.btnUpdate.Enabled = False
    End Sub

    '删除按钮事件
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If (MessageBox.Show("确定要删除吗?", "删除!", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.OK) Then
            Dim deleteSource As String = DataGridView1.CurrentRow.Cells(0).Value.ToString()
            Dim con As New SqlConnection(conStr)
            Dim com As New SqlCommand("delete from Users where ID=@id", con)
            com.Parameters.Add("@id", SqlDbType.Int, 6).Value = DataGridView1.CurrentRow.Cells(0).Value.ToString()
            con.Open()
            com.ExecuteNonQuery()
            con.Close()
            MessageBox.Show("删除成功!", "成功!", MessageBoxButtons.OK, MessageBoxIcon.Information)
            da.Clear()
            AddSource()
        End If
    End Sub

    '查询按钮事件
    Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
        da.Clear()
        AddSource()
    End Sub

    '更新数据方法
    Private Sub UpdateSource()
        If (MessageBox.Show("确定要更新数据吗?", "修改?", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) = Windows.Forms.DialogResult.OK) Then
            Dim comBuild As New SqlCommandBuilder(ad)
            Try
                ad.Update(da)
                MessageBox.Show("数据更新成功!", "成功!", MessageBoxButtons.OK, MessageBoxIcon.None)
            Catch ex As Exception
                MessageBox.Show("数据更新失败!检查输入的数据是否正确!", "失败!", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            End Try
        End If
    End Sub

    'DataGridView中单元格的数据被更改时触发的事件
    Private Sub DataGridView1_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
        Me.btnUpdate.Enabled = True
    End Sub
End Class