第25课：CRUD：新增、读取、修改、删除｜Visual Basic 2026 简体中文教程

本课学习目标

理解 CRUD 与 INSERT、SELECT、UPDATE、DELETE 的对应关系。
使用 SqlCommand 执行 SQL。
使用 ExecuteNonQuery() 执行新增、修改和删除。
使用 ExecuteReader() 与 SqlDataReader 读取多笔记录。
根据 StudentID 精确更新和删除。
检查受影响行数 rowsAffected。
继续使用 Using 和 Try...Catch 管理数据库资源。
完成 StudentCrudManager2026。

25.1 CRUD 与 SQL

Create

INSERT

新增一笔记录。

Read

SELECT

读取一笔或多笔记录。

Update

UPDATE

修改现有记录。

Delete

DELETE

删除指定记录。

25.2 建立项目与界面

StudentCrudManager2026

继续使用 Lesson 24 的 DatabaseHelper.vb，并确认已安装 Microsoft.Data.SqlClient。

Student CRUD Manager 2026

Student ID：

姓名：

王芳

课程：

Visual Basic 2026

成绩：

操作：

新增读取全部修改删除清除

ID: 1 | 李明 | Visual Basic 2026 | 88.00 ID: 2 | 王芳 | Visual Basic 2026 | 82.00 ID: 3 | 陈伟 | Python Programming | 91.00 记录数：3

控件	Name	用途
TextBox	txtStudentID	修改和删除时输入主键。
TextBox	txtName	姓名。
TextBox	txtCourse	课程。
TextBox	txtScore	0–100。
Button	btnAdd	新增。
Button	btnLoad	读取全部。
Button	btnUpdate	修改。
Button	btnDelete	删除。
Button	btnClear	清除输入。
TextBox	txtOutput	Multiline=True，ReadOnly=True。
Label	lblStatus	显示操作状态。

25.3 SqlCommand 与执行流程

Imports Microsoft.Data.SqlClient

按钮事件用户操作

验证TryParse / If

SqlConnection连接

SqlCommandSQL

数据库结果

Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
    Using command As New SqlCommand(sql, connection)
        connection.Open()
        ' 执行 SQL
    End Using
End Using

25.4 输入验证

Private Function TryGetStudentInput(ByRef studentName As String,
                                    ByRef course As String,
                                    ByRef score As Decimal) As Boolean
    studentName = txtName.Text.Trim()
    course = txtCourse.Text.Trim()

    If String.IsNullOrWhiteSpace(studentName) Then
        MessageBox.Show("请输入学生姓名。")
        txtName.Focus()
        Return False
    End If

    If String.IsNullOrWhiteSpace(course) Then
        MessageBox.Show("请输入课程名称。")
        txtCourse.Focus()
        Return False
    End If

    If Not Decimal.TryParse(txtScore.Text, score) Then
        MessageBox.Show("请输入有效成绩。")
        txtScore.Focus()
        Return False
    End If

    If score < 0D OrElse score > 100D Then
        MessageBox.Show("成绩必须介于 0 与 100。")
        txtScore.Focus()
        Return False
    End If

    Return True
End Function

25.5 Create：INSERT

Private Function InsertStudent(studentName As String,
                               course As String,
                               score As Decimal) As Integer
    Const sql As String =
        "INSERT INTO dbo.Students (Name, Course, Score) " &
        "VALUES (@Name, @Course, @Score);"

    Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
        Using command As New SqlCommand(sql, connection)
            command.Parameters.AddWithValue("@Name", studentName)
            command.Parameters.AddWithValue("@Course", course)
            command.Parameters.AddWithValue("@Score", score)
            connection.Open()
            Return command.ExecuteNonQuery()
        End Using
    End Using
End Function

参数化 SQL 先作为安全标准使用 本课先使用 @Name、@Course、@Score，但参数化查询的原理与更严谨的参数类型会在 Lesson 27 专门讲解。

25.6 ExecuteNonQuery()

INSERT、UPDATE 和 DELETE 通常使用 ExecuteNonQuery()。它会返回受影响记录数。

Dim rowsAffected As Integer = command.ExecuteNonQuery()

25.7 Read：SELECT 与 SqlDataReader

Private Sub LoadStudents()
    Const sql As String =
        "SELECT StudentID, Name, Course, Score " &
        "FROM dbo.Students ORDER BY StudentID;"

    txtOutput.Clear()

    Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
        Using command As New SqlCommand(sql, connection)
            connection.Open()

            Using reader As SqlDataReader = command.ExecuteReader()
                Dim recordCount As Integer = 0

                While reader.Read()
                    Dim studentId As Integer = reader.GetInt32(0)
                    Dim studentName As String = reader.GetString(1)
                    Dim course As String = reader.GetString(2)
                    Dim score As Decimal = reader.GetDecimal(3)

                    txtOutput.Text &=
                        $"ID: {studentId} | {studentName} | {course} | {score:N2}" &
                        Environment.NewLine

                    recordCount += 1
                End While

                txtOutput.Text &= Environment.NewLine &
                                  $"记录数：{recordCount}"
            End Using
        End Using
    End Using

    lblStatus.Text = "学生资料读取完成。"
End Sub

ExecuteReader() 返回 SqlDataReader；reader.Read() 每次移动到下一笔记录。

25.8 StudentID 验证

Private Function TryGetStudentId(ByRef studentId As Integer) As Boolean
    If Not Integer.TryParse(txtStudentID.Text, studentId) Then
        MessageBox.Show("请输入有效 Student ID。")
        txtStudentID.Focus()
        Return False
    End If

    If studentId <= 0 Then
        MessageBox.Show("Student ID 必须大于 0。")
        Return False
    End If

    Return True
End Function

25.9 Update：修改记录

Private Function UpdateStudent(studentId As Integer,
                               studentName As String,
                               course As String,
                               score As Decimal) As Integer
    Const sql As String =
        "UPDATE dbo.Students " &
        "SET Name=@Name, Course=@Course, Score=@Score " &
        "WHERE StudentID=@StudentID;"

    Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
        Using command As New SqlCommand(sql, connection)
            command.Parameters.AddWithValue("@Name", studentName)
            command.Parameters.AddWithValue("@Course", course)
            command.Parameters.AddWithValue("@Score", score)
            command.Parameters.AddWithValue("@StudentID", studentId)
            connection.Open()
            Return command.ExecuteNonQuery()
        End Using
    End Using
End Function

UPDATE 一定要检查 WHERE 如果没有 WHERE 条件，可能会修改整张表的全部记录。

25.10 Delete：删除记录

Private Function DeleteStudent(studentId As Integer) As Integer
    Const sql As String =
        "DELETE FROM dbo.Students WHERE StudentID=@StudentID;"

    Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
        Using command As New SqlCommand(sql, connection)
            command.Parameters.AddWithValue("@StudentID", studentId)
            connection.Open()
            Return command.ExecuteNonQuery()
        End Using
    End Using
End Function

DELETE 没有 WHERE 会删除全部记录 删除前应确认 StudentID，并用 MessageBox Yes/No 让用户再次确认。

25.11 完整 Form1.vb

Imports Microsoft.Data.SqlClient

Public Class Form1

    Private Function TryGetStudentInput(ByRef studentName As String,
                                        ByRef course As String,
                                        ByRef score As Decimal) As Boolean
        studentName = txtName.Text.Trim()
        course = txtCourse.Text.Trim()

        If String.IsNullOrWhiteSpace(studentName) Then
            MessageBox.Show("请输入学生姓名。")
            txtName.Focus()
            Return False
        End If

        If String.IsNullOrWhiteSpace(course) Then
            MessageBox.Show("请输入课程名称。")
            txtCourse.Focus()
            Return False
        End If

        If Not Decimal.TryParse(txtScore.Text, score) Then
            MessageBox.Show("请输入有效成绩。")
            txtScore.Focus()
            Return False
        End If

        If score < 0D OrElse score > 100D Then
            MessageBox.Show("成绩必须介于 0 与 100。")
            txtScore.Focus()
            Return False
        End If

        Return True
    End Function

    Private Function TryGetStudentId(ByRef studentId As Integer) As Boolean
        If Not Integer.TryParse(txtStudentID.Text, studentId) OrElse studentId <= 0 Then
            MessageBox.Show("请输入有效 Student ID。")
            txtStudentID.Focus()
            Return False
        End If
        Return True
    End Function

    Private Function InsertStudent(studentName As String,
                                   course As String,
                                   score As Decimal) As Integer
        Const sql As String =
            "INSERT INTO dbo.Students (Name, Course, Score) " &
            "VALUES (@Name, @Course, @Score);"

        Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
            Using command As New SqlCommand(sql, connection)
                command.Parameters.AddWithValue("@Name", studentName)
                command.Parameters.AddWithValue("@Course", course)
                command.Parameters.AddWithValue("@Score", score)
                connection.Open()
                Return command.ExecuteNonQuery()
            End Using
        End Using
    End Function

    Private Sub LoadStudents()
        Const sql As String =
            "SELECT StudentID, Name, Course, Score " &
            "FROM dbo.Students ORDER BY StudentID;"

        txtOutput.Clear()

        Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
            Using command As New SqlCommand(sql, connection)
                connection.Open()

                Using reader As SqlDataReader = command.ExecuteReader()
                    Dim recordCount As Integer = 0

                    While reader.Read()
                        txtOutput.Text &=
                            $"ID: {reader.GetInt32(0)} | " &
                            $"{reader.GetString(1)} | " &
                            $"{reader.GetString(2)} | " &
                            $"{reader.GetDecimal(3):N2}" &
                            Environment.NewLine
                        recordCount += 1
                    End While

                    txtOutput.Text &= Environment.NewLine &
                                      $"记录数：{recordCount}"
                End Using
            End Using
        End Using

        lblStatus.Text = "学生资料读取完成。"
    End Sub

    Private Function UpdateStudent(studentId As Integer,
                                   studentName As String,
                                   course As String,
                                   score As Decimal) As Integer
        Const sql As String =
            "UPDATE dbo.Students SET Name=@Name, Course=@Course, Score=@Score " &
            "WHERE StudentID=@StudentID;"

        Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
            Using command As New SqlCommand(sql, connection)
                command.Parameters.AddWithValue("@Name", studentName)
                command.Parameters.AddWithValue("@Course", course)
                command.Parameters.AddWithValue("@Score", score)
                command.Parameters.AddWithValue("@StudentID", studentId)
                connection.Open()
                Return command.ExecuteNonQuery()
            End Using
        End Using
    End Function

    Private Function DeleteStudent(studentId As Integer) As Integer
        Const sql As String =
            "DELETE FROM dbo.Students WHERE StudentID=@StudentID;"

        Using connection As New SqlConnection(DatabaseHelper.ConnectionString)
            Using command As New SqlCommand(sql, connection)
                command.Parameters.AddWithValue("@StudentID", studentId)
                connection.Open()
                Return command.ExecuteNonQuery()
            End Using
        End Using
    End Function

    Private Sub ClearInputFields()
        txtStudentID.Clear()
        txtName.Clear()
        txtCourse.Clear()
        txtScore.Clear()
        txtName.Focus()
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        Dim studentName As String = ""
        Dim course As String = ""
        Dim score As Decimal
        If Not TryGetStudentInput(studentName, course, score) Then Exit Sub

        Try
            Dim rowsAffected As Integer = InsertStudent(studentName, course, score)
            lblStatus.Text = $"新增完成，影响 {rowsAffected} 笔记录。"
            ClearInputFields()
            LoadStudents()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
        Try
            LoadStudents()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim studentId As Integer
        Dim studentName As String = ""
        Dim course As String = ""
        Dim score As Decimal
        If Not TryGetStudentId(studentId) Then Exit Sub
        If Not TryGetStudentInput(studentName, course, score) Then Exit Sub

        Try
            Dim rowsAffected As Integer = UpdateStudent(studentId, studentName, course, score)
            If rowsAffected = 0 Then
                MessageBox.Show("找不到这个 Student ID。")
            Else
                lblStatus.Text = "学生资料修改成功。"
                ClearInputFields()
                LoadStudents()
            End If
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim studentId As Integer
        If Not TryGetStudentId(studentId) Then Exit Sub

        Dim result As DialogResult = MessageBox.Show(
            $"确定要删除 Student ID {studentId} 吗？",
            "确认删除",
            MessageBoxButtons.YesNo,
            MessageBoxIcon.Warning)

        If result <> DialogResult.Yes Then Exit Sub

        Try
            Dim rowsAffected As Integer = DeleteStudent(studentId)
            If rowsAffected = 0 Then
                MessageBox.Show("找不到这个 Student ID。")
            Else
                lblStatus.Text = "学生资料删除成功。"
                ClearInputFields()
                LoadStudents()
            End If
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        ClearInputFields()
        lblStatus.Text = "输入栏已经清除。"
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            LoadStudents()
        Catch ex As SqlException
            lblStatus.Text = "启动时无法读取数据库。"
            MessageBox.Show(ex.Message, "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

End Class

25.12 三种 Execute 方法

方法	适合	返回
ExecuteNonQuery()	INSERT / UPDATE / DELETE	受影响行数。
ExecuteReader()	SELECT 多行多列	SqlDataReader。
ExecuteScalar()	COUNT / MAX 等单值	第一行第一列。

25.13 测试 CRUD

新增一位学生，确认记录数增加。
点击读取全部，确认新记录出现。
输入 StudentID 和新资料执行修改。
输入 StudentID 执行删除并确认。
关闭再重新运行应用，确认变更仍在数据库。

25.14 常见错误

错误 1

UPDATE 没有 WHERE

可能修改全部记录。

错误 2

DELETE 没有 WHERE

可能删除整张表的数据。

错误 3

不检查 rowsAffected

不存在的 StudentID 可能影响 0 行。

错误 4

所有数据库代码塞进 Click

应拆成可重用方法。

25.15 为什么下一课才用 DataGridView？

本课重点是 SQL、SqlCommand、ExecuteNonQuery 和 SqlDataReader。下一课会把 SELECT 结果真正绑定到 DataGridView，并学习点击选中行后把 StudentID、Name、Course、Score 带回输入框。

25.16 小练习

增加“查询指定 ID”按钮。
使用 ExecuteScalar() 显示学生总数。
尝试不存在的 StudentID，确认 Update/Delete 显示 0 行结果。
新增一个 Search by Name 查询，但先保持参数写法。
为 Products 表设计同样的四个 CRUD 方法。

本课复习

CRUD 四个字母分别是什么？
SqlConnection 与 SqlCommand 职责有什么不同？
ExecuteNonQuery 适合哪些 SQL？
ExecuteReader 返回什么？
reader.Read() 做什么？
为什么 Update/Delete 使用 StudentID？
rowsAffected 为什么重要？
为什么删除前需要确认？

上一课← 第 24 课：创建与连接数据库下一课第 26 课：使用 DataGridView 显示数据 →