面试题：Visual Basic 中如何实现对数据库的增删改查操作

Question

假设你要使用 Visual Basic 连接 SQL Server 数据库，并且对其中一张名为 'Employees' 的表进行增删改查操作。请描述具体的步骤，包括如何引用相关的库，建立数据库连接，以及编写增删改查的 SQL 语句并在 Visual Basic 代码中执行。

Answer 1

1. 引用相关库

在 Visual Basic 项目中，需要引用 System.Data.SqlClient 库来连接 SQL Server 数据库。

• 在 Visual Studio 中，右键点击项目，选择“添加引用”。
• 在“引用管理器”中，展开“框架”，找到并勾选 System.Data.SqlClient，然后点击“确定”。

2. 建立数据库连接

Imports System.Data.SqlClient

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim connectionString As String = "Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD"
        Using connection As New SqlConnection(connectionString)
            Try
                connection.Open()
                Console.WriteLine("Connected to the database!")
            Catch ex As Exception
                Console.WriteLine("Error: " & ex.Message)
            End Try
        End Using
    End Sub
End Class

在上述代码中，需要将 YOUR_SERVER_NAME 替换为实际的 SQL Server 服务器名称，YOUR_DATABASE_NAME 替换为实际的数据库名称，YOUR_USERNAME 和 YOUR_PASSWORD 替换为有效的登录凭据。

3. 增删改查操作

插入数据（增）

Dim insertQuery As String = "INSERT INTO Employees (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)"
Using command As New SqlCommand(insertQuery, connection)
    command.Parameters.AddWithValue("@FirstName", "John")
    command.Parameters.AddWithValue("@LastName", "Doe")
    command.Parameters.AddWithValue("@Age", 30)
    Try
        command.ExecuteNonQuery()
        Console.WriteLine("Data inserted successfully!")
    Catch ex As Exception
        Console.WriteLine("Error: " & ex.Message)
    End Try
End Using

删除数据（删）

Dim deleteQuery As String = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID"
Using command As New SqlCommand(deleteQuery, connection)
    command.Parameters.AddWithValue("@EmployeeID", 1)
    Try
        command.ExecuteNonQuery()
        Console.WriteLine("Data deleted successfully!")
    Catch ex As Exception
        Console.WriteLine("Error: " & ex.Message)
    End Try
End Using

更新数据（改）

Dim updateQuery As String = "UPDATE Employees SET Age = @NewAge WHERE EmployeeID = @EmployeeID"
Using command As New SqlCommand(updateQuery, connection)
    command.Parameters.AddWithValue("@NewAge", 31)
    command.Parameters.AddWithValue("@EmployeeID", 1)
    Try
        command.ExecuteNonQuery()
        Console.WriteLine("Data updated successfully!")
    Catch ex As Exception
        Console.WriteLine("Error: " & ex.Message)
    End Try
End Using

查询数据（查）

Dim selectQuery As String = "SELECT * FROM Employees"
Using command As New SqlCommand(selectQuery, connection)
    Try
        Dim reader As SqlDataReader = command.ExecuteReader()
        While reader.Read()
            Console.WriteLine(reader("FirstName") & " " & reader("LastName") & " - Age: " & reader("Age"))
        End While
        reader.Close()
    Catch ex As Exception
        Console.WriteLine("Error: " & ex.Message)
    End Try
End Using