MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

Visual Basic ADO.NET深入讲解

2022-06-305.0k 阅读

一、ADO.NET 概述

ADO.NET 是一组向 .NET Framework 程序员公开数据访问服务的类。它提供了一种断开式、与数据源无关的数据访问模型，使得开发人员能够轻松地与各种数据源（如关系数据库、XML 数据等）进行交互。

在 Visual Basic 中使用 ADO.NET，开发人员可以执行以下常见操作：

  1. 连接到数据源：如 SQL Server、Oracle 等数据库。
  2. 执行 SQL 命令：查询数据、插入数据、更新数据、删除数据等。
  3. 管理数据：在内存中对数据进行处理、排序、筛选等。
  4. 将数据绑定到用户界面控件：如 DataGridView 等，以便用户直观地查看和操作数据。

二、ADO.NET 核心对象

  1. Connection 对象
    • 作用：用于建立与数据源的连接。不同的数据源有不同的 Connection 对象，例如，对于 SQL Server 数据库，使用 SqlConnection；对于 Oracle 数据库，使用 OracleConnection。
    • 示例代码：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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 SqlException
                Console.WriteLine("Error connecting to the database: " & ex.Message)
            End Try
        End Using
    End Sub
End Module
  • 说明：上述代码使用 SqlConnection 连接到 SQL Server 数据库。Using 语句确保在代码块结束时正确关闭和释放连接资源，提高资源管理的安全性。
  1. Command 对象
    • 作用：用于对数据源执行 SQL 命令或存储过程。同样，针对不同的数据源有不同的 Command 对象，如 SqlCommand 用于 SQL Server。
    • 示例代码 - 执行查询命令：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            connection.Open()
            Dim query As String = "SELECT * FROM Customers"
            Using command As New SqlCommand(query, connection)
                Try
                    Dim reader As SqlDataReader = command.ExecuteReader()
                    While reader.Read()
                        Console.WriteLine(reader("CustomerName").ToString())
                    End While
                    reader.Close()
                Catch ex As SqlException
                    Console.WriteLine("Error executing query: " & ex.Message)
                End Try
            End Using
        End Using
    End Sub
End Module

  • 说明：这段代码在连接打开后，创建了一个 SqlCommand 对象来执行一个简单的查询。ExecuteReader 方法返回一个 SqlDataReader 对象，用于逐行读取查询结果。

  • 示例代码 - 执行插入命令：

Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            connection.Open()
            Dim insertQuery As String = "INSERT INTO Customers (CustomerName, ContactName, Country) VALUES (@CustomerName, @ContactName, @Country)"
            Using command As New SqlCommand(insertQuery, connection)
                command.Parameters.AddWithValue("@CustomerName", "New Customer")
                command.Parameters.AddWithValue("@ContactName", "New Contact")
                command.Parameters.AddWithValue("@Country", "USA")
                Try
                    Dim rowsAffected As Integer = command.ExecuteNonQuery()
                    Console.WriteLine(rowsAffected & " row(s) inserted.")
                Catch ex As SqlException
                    Console.WriteLine("Error inserting data: " & ex.Message)
                End Try
            End Using
        End Using
    End Sub
End Module
  • 说明：此代码展示了如何使用 SqlCommand 执行插入操作。通过 Parameters.AddWithValue 方法为 SQL 语句中的参数赋值，这样可以防止 SQL 注入攻击。ExecuteNonQuery 方法用于执行不返回结果集的命令，如插入、更新和删除操作，并返回受影响的行数。
  1. DataReader 对象
    • 作用：提供一种快速、只读、向前的方式来读取来自数据源的数据。它在读取数据时保持与数据源的连接，适用于处理大量数据且不需要对数据进行复杂操作的场景。
    • 特点：
      • 性能高效：由于其只读和向前的特性，占用内存少，读取速度快。
      • 连接依赖性：必须在连接打开的状态下使用。
    • 示例代码（续上面查询示例）：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            connection.Open()
            Dim query As String = "SELECT * FROM Customers"
            Using command As New SqlCommand(query, connection)
                Dim reader As SqlDataReader = command.ExecuteReader()
                While reader.Read()
                    Console.WriteLine("Customer Name: " & reader("CustomerName").ToString())
                    Console.WriteLine("Contact Name: " & reader("ContactName").ToString())
                    Console.WriteLine("Country: " & reader("Country").ToString())
                End While
                reader.Close()
            End Using
        End Using
    End Sub
End Module
  • 说明：While reader.Read() 循环逐行读取数据，reader("ColumnName") 可以获取指定列的数据。读取完成后，需要调用 reader.Close() 方法关闭 DataReader，以释放相关资源。
  1. DataAdapter 对象
    • 作用：充当数据源和数据集（DataSet）之间的桥梁，用于填充数据集和更新数据源。它包含四个主要的 Command 属性：SelectCommand、InsertCommand、UpdateCommand 和 DeleteCommand，分别用于查询、插入、更新和删除操作。
    • 示例代码 - 填充数据集：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            Dim query As String = "SELECT * FROM Customers"
            Using adapter As New SqlDataAdapter(query, connection)
                Dim dataSet As New DataSet()
                adapter.Fill(dataSet, "Customers")
                Dim dataTable As DataTable = dataSet.Tables("Customers")
                For Each row As DataRow In dataTable.Rows
                    Console.WriteLine("Customer Name: " & row("CustomerName").ToString())
                Next
            End Using
        End Using
    End Sub
End Module

  • 说明：代码创建了一个 SqlDataAdapter 对象，并使用其 Fill 方法将查询结果填充到 DataSet 中的名为 “Customers” 的 DataTable 中。然后遍历 DataTable 的行来输出客户名称。

  • 示例代码 - 更新数据源：

Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            Dim query As String = "SELECT * FROM Customers"
            Using adapter As New SqlDataAdapter(query, connection)
                Dim dataSet As New DataSet()
                adapter.Fill(dataSet, "Customers")
                Dim dataTable As DataTable = dataSet.Tables("Customers")
                Dim newRow As DataRow = dataTable.NewRow()
                newRow("CustomerName") = "New Customer 2"
                newRow("ContactName") = "New Contact 2"
                newRow("Country") = "UK"
                dataTable.Rows.Add(newRow)
                Dim builder As New SqlCommandBuilder(adapter)
                adapter.Update(dataSet, "Customers")
            End Using
        End Using
    End Sub
End Module
  • 说明：此代码在填充数据集后，创建了一个新的数据行并添加到 DataTable 中。SqlCommandBuilder 自动生成用于更新数据源的 InsertCommand、UpdateCommand 和 DeleteCommand。adapter.Update 方法将 DataSet 中的更改应用回数据源。
  1. DataSet 对象
    • 作用：是 ADO.NET 断开式数据访问模型的核心组件，它是一个内存中的数据缓存，类似于一个小型的关系数据库，包含一个或多个 DataTable 对象，以及这些表之间的关系（DataRelation）。
    • 优点：
      • 断开连接操作：可以在没有与数据源保持实时连接的情况下处理数据，提高了系统的灵活性和可扩展性。
      • 数据一致性维护：DataSet 可以维护数据的完整性约束，如主键、外键等关系。
    • 示例代码 - 多表操作：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            Dim customersQuery As String = "SELECT * FROM Customers"
            Dim ordersQuery As String = "SELECT * FROM Orders"
            Using customersAdapter As New SqlDataAdapter(customersQuery, connection)
                Using ordersAdapter As New SqlDataAdapter(ordersQuery, connection)
                    Dim dataSet As New DataSet()
                    customersAdapter.Fill(dataSet, "Customers")
                    ordersAdapter.Fill(dataSet, "Orders")
                    Dim relation As New DataRelation("CustomerOrders", dataSet.Tables("Customers").Columns("CustomerID"), dataSet.Tables("Orders").Columns("CustomerID"))
                    dataSet.Relations.Add(relation)
                    For Each customerRow As DataRow In dataSet.Tables("Customers").Rows
                        Console.WriteLine("Customer: " & customerRow("CustomerName").ToString())
                        Dim childRows() As DataRow = customerRow.GetChildRows(relation)
                        For Each orderRow As DataRow In childRows
                            Console.WriteLine("  Order: " & orderRow("OrderDate").ToString())
                        Next
                    Next
                End Using
            End Using
        End Using
    End Sub
End Module
  • 说明：代码从数据库中获取 Customers 和 Orders 表的数据并填充到 DataSet 中。然后创建了一个 DataRelation 对象来建立两个表之间基于 CustomerID 的关系。通过 GetChildRows 方法可以获取某个客户对应的所有订单数据。

三、连接字符串

  1. 连接字符串的组成 连接字符串包含了连接到数据源所需的各种信息，不同的数据源连接字符串格式有所不同，但通常包括以下常见部分：

    • 数据源地址：如 SQL Server 的服务器名称（Data Source）。
    • 数据库名称：要连接的具体数据库（Initial Catalog 或 Database）。
    • 身份验证信息：用户名（User ID）和密码（Password），或者使用集成身份验证（Integrated Security = True）。
    • 其他选项：如连接超时时间（Connection Timeout）等。

  2. SQL Server 连接字符串示例

    • 基本示例：
Dim connectionString As String = "Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD"
  • 集成身份验证示例：
Dim connectionString As String = "Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;Integrated Security=True"
  1. 连接字符串的安全性
    • 避免硬编码：不要在代码中直接硬编码连接字符串，尤其是包含敏感信息（如密码）的字符串。可以将连接字符串存储在配置文件（如 App.config 或 Web.config）中，然后在代码中读取。
    • 加密：对于包含敏感信息的连接字符串，可以考虑对配置文件中的连接字符串进行加密，以增加安全性。

四、数据绑定

  1. 简单数据绑定 简单数据绑定是将一个控件（如 TextBox）绑定到一个数据源中的单个值。
    • 示例代码：
Imports System.Data.SqlClient

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        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)
            Dim query As String = "SELECT CustomerName FROM Customers WHERE CustomerID = 1"
            Using command As New SqlCommand(query, connection)
                connection.Open()
                Dim customerName As String = CStr(command.ExecuteScalar())
                TextBox1.DataBindings.Add("Text", Nothing, customerName)
            End Using
        End Using
    End Sub
End Class
  • 说明：上述代码从数据库中获取一个客户名称，并将其绑定到 TextBox1 的 Text 属性上。ExecuteScalar 方法用于执行查询并返回结果集中的第一行第一列的值。
  1. 复杂数据绑定 复杂数据绑定是将一个控件（如 DataGridView）绑定到一个数据源中的多个数据项，通常是一个 DataTable 或 DataView。
    • 示例代码：
Imports System.Data.SqlClient

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        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)
            Dim query As String = "SELECT * FROM Customers"
            Using adapter As New SqlDataAdapter(query, connection)
                Dim dataSet As New DataSet()
                adapter.Fill(dataSet, "Customers")
                DataGridView1.DataSource = dataSet.Tables("Customers")
            End Using
        End Using
    End Sub
End Class
  • 说明：此代码将 Customers 表的数据填充到 DataSet 中，并将 DataGridView1 的数据源设置为 DataSet 中的 Customers 表，从而在 DataGridView 中显示客户数据。

五、错误处理

  1. 捕获 SqlException 在使用 ADO.NET 与 SQL Server 交互时，最常见的异常是 SqlException。可以使用 Try - Catch 块来捕获并处理这些异常。
    • 示例代码：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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()
                Dim query As String = "SELECT * FROM NonExistentTable"
                Using command As New SqlCommand(query, connection)
                    Dim reader As SqlDataReader = command.ExecuteReader()
                End Using
            Catch ex As SqlException
                Console.WriteLine("SQL Server error: " & ex.Message)
                For Each error As SqlError In ex.Errors
                    Console.WriteLine("Error Number: " & error.Number)
                    Console.WriteLine("Line Number: " & error.LineNumber)
                Next
            End Try
        End Using
    End Sub
End Module
  • 说明：上述代码尝试查询一个不存在的表，会引发 SqlException。在 Catch 块中，不仅输出了异常消息，还遍历了 ex.Errors 集合，获取了更详细的错误信息，如错误编号和行号等。
  1. 通用异常处理 除了 SqlException，还可能遇到其他类型的异常，如 InvalidOperationException（例如在连接未打开时执行命令）。可以在 Try - Catch 块中添加通用的 Catch 子句来处理这些异常。
    • 示例代码：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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
                Dim query As String = "SELECT * FROM Customers"
                Using command As New SqlCommand(query, connection)
                    Dim reader As SqlDataReader = command.ExecuteReader()
                End Using
            Catch ex As SqlException
                Console.WriteLine("SQL Server error: " & ex.Message)
            Catch ex As Exception
                Console.WriteLine("General error: " & ex.Message)
            End Try
        End Using
    End Sub
End Module
  • 说明：此代码在捕获 SqlException 后，添加了一个通用的 Catch 子句来捕获其他类型的异常，并输出相应的错误消息。

六、性能优化

  1. 使用连接池 ADO.NET 支持连接池功能，它可以重用现有的数据库连接，而不是每次都创建新的连接，从而提高性能。默认情况下，连接池是启用的。

    • 注意事项：
      • 连接字符串一致性：使用相同连接字符串的连接会被放入同一个连接池。因此，确保在整个应用程序中使用一致的连接字符串，以充分利用连接池。
      • 连接关闭：及时关闭不再使用的连接，使其返回连接池供其他请求使用。使用 Using 语句可以方便地管理连接的生命周期。

  2. 优化 SQL 命令

    • 参数化查询：如前文所示，使用参数化查询不仅可以防止 SQL 注入攻击，还能提高查询性能。数据库可以缓存参数化查询的执行计划，避免每次都重新编译查询。
    • 避免不必要的查询：只获取应用程序实际需要的数据，避免使用 SELECT *，而是明确指定所需的列。

  3. 减少往返次数

    • 批量操作：如果需要执行多个相似的 SQL 命令（如多次插入操作），可以将这些命令批量执行，减少与数据库的往返次数。例如，在 SqlCommand 中，可以使用 ExecuteNonQuery 方法执行多个 SQL 语句组成的字符串。
    • 使用存储过程：存储过程在数据库服务器端预编译并存储，执行效率高。而且通过一次调用存储过程可以完成复杂的业务逻辑，减少与数据库的交互次数。

七、高级主题

  1. 事务处理 事务是一组作为单个逻辑工作单元执行的操作。在 ADO.NET 中，可以使用 Transaction 对象来管理事务。
    • 示例代码 - 简单事务：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            connection.Open()
            Using transaction As SqlTransaction = connection.BeginTransaction()
                Try
                    Dim insertQuery1 As String = "INSERT INTO Table1 (Column1) VALUES ('Value1')"
                    Using command1 As New SqlCommand(insertQuery1, connection, transaction)
                        command1.ExecuteNonQuery()
                    End Using
                    Dim insertQuery2 As String = "INSERT INTO Table2 (Column2) VALUES ('Value2')"
                    Using command2 As New SqlCommand(insertQuery2, connection, transaction)
                        command2.ExecuteNonQuery()
                    End Using
                    transaction.Commit()
                    Console.WriteLine("Transaction committed.")
                Catch ex As SqlException
                    Console.WriteLine("Error in transaction: " & ex.Message)
                    transaction.Rollback()
                    Console.WriteLine("Transaction rolled back.")
                End Try
            End Using
        End Using
    End Sub
End Module
  • 说明：上述代码在一个事务中执行两个插入操作。如果其中任何一个操作失败，Catch 块会捕获异常并回滚事务，确保数据的一致性。
  1. XML 数据处理 ADO.NET 与 XML 紧密集成。DataSet 可以将数据以 XML 格式保存，也可以从 XML 文件或流中加载数据。
    • 示例代码 - 将 DataSet 保存为 XML：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        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)
            Dim query As String = "SELECT * FROM Customers"
            Using adapter As New SqlDataAdapter(query, connection)
                Dim dataSet As New DataSet()
                adapter.Fill(dataSet, "Customers")
                dataSet.WriteXml("Customers.xml")
            End Using
        End Using
    End Sub
End Module
  • 示例代码 - 从 XML 加载到 DataSet：
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim dataSet As New DataSet()
        dataSet.ReadXml("Customers.xml")
        Dim dataTable As DataTable = dataSet.Tables("Customers")
        For Each row As DataRow In dataTable.Rows
            Console.WriteLine("Customer Name: " & row("CustomerName").ToString())
        Next
    End Sub
End Module
  • 说明：第一个代码示例将 Customers 表的数据填充到 DataSet 并保存为 XML 文件。第二个示例从 XML 文件加载数据到 DataSet 并遍历显示客户名称。
  1. 异步操作 在 ADO.NET 中，可以使用异步方法来执行数据库操作，以避免阻塞主线程，提高应用程序的响应性。
    • 示例代码 - 异步查询：
Imports System.Data.SqlClient
Imports System.Threading.Tasks

Module Module1
    Async Sub Main()
        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)
            Await connection.OpenAsync()
            Dim query As String = "SELECT * FROM Customers"
            Using command As New SqlCommand(query, connection)
                Dim reader As SqlDataReader = Await command.ExecuteReaderAsync()
                While reader.Read()
                    Console.WriteLine(reader("CustomerName").ToString())
                End While
                reader.Close()
            End Using
        End Using
    End Sub
End Module
  • 说明：上述代码使用 Async 和 Await 关键字来异步打开连接并执行查询，在查询执行期间主线程不会被阻塞，可以继续执行其他任务。

通过对上述内容的学习，开发人员可以深入掌握 Visual Basic 中 ADO.NET 的使用，从而开发出高效、稳定的数据访问应用程序。无论是小型桌面应用还是大型企业级 Web 应用，ADO.NET 都提供了强大的数据访问功能。