在 Visual Basic .NET 中使用存储过程

　　
　　摘要：Billy Hollis 解释了在复杂系统中使用存储过程的好处，使存储过程超出了演示软件的范畴，并提供了有关如何访问存储过程并在您自己的应用程序中开始使用这些存储过程的实用示例。
　　
　　我们作者通常将软件分为两类 - 真实软件和演示软件。真实软件是在真实环境中使用的软件。演示软件用于阐释编程概念。
　　
　　您在文章和书籍中看到的大部分代码都是演示软件。它必须比真实软件简单，否则读者将为那些与阐释的概念无关的细节而费神。但有时演示软件又过于极端。追求简单会忽略开发人员编写真实软件所需的细节。
　　
　　最近我在数据访问方面就曾遇到这样一个问题。实际上，我见过的每个数据访问示例都使用 SQL 语句从关系数据库（例如，Microsoft SQL Server™）中读取或向其中写入。然而，在真实环境中，除了对有限的小型系统适用以外，这是很不可取的编程方法。结构合理的 n 层应用程序使用存储过程代替 SQL 语句进行数据访问。
　　
　　存储过程在概念上类似于程序中的函数。它们获取输入参数，以黑盒模式运行并返回相应信息。与函数不同的是，存储过程由数据库引擎执行，而不是在程序中执行。也就是说，将信息输入到存储过程或从中输出信息都必须通过与数据库交互的技术来完成。在 Microsoft Visual Basic® 6.0 中，该技术就是传统的 ADO。在 Visual Basic .NET 中，我们可以使用 ADO.NET 完成该任务。
　　
　　对于许多编程任务而言，Visual Basic .NET 使得通过存储过程访问数据比使用 Visual Basic 6.0 容易得多。其中有一些用来帮助该过程的向导，一旦您学会如何避免一些错误之后，即使使用 ADO.NET 从头编写这些逻辑也并不复杂。
　　
　　本文介绍了一些在 ADO.NET 中使用存储过程的基本技巧，并从只读操作开始，一直到如何使用存储过程进行数据插入、删除和更新。
　　
　　您无需精通存储过程的编写也可从本文受益。许多大型编程小组的开发人员需要使用他人编写的存储过程。我们的示例之一需要将存储过程插入到示例数据库中，但我们将逐步介绍这个任务。
　　
　　ADO.NET 简介
　　本文假设您已经了解了 ADO.NET 的基础知识。如果您在工作中从未使用过 ADO.NET 中的 DataAdapter、DataSet 和 Command 对象，则应阅读一些介绍 ADO.NET 的文章，包括 Rocky 为本专栏撰写的名为 ADO.NET 与您一文。
　　
　　简而言之，DataSet 在 ADO.NET 中用作数据容器，并在与数据库断开连接时使用。DataSet 包含一个或多个 DataTable，每个 DataTable 都包含行集合。对于那些熟悉传统 ADO 环境的用户来说，DataTable 可被看作是断开连接的 Recordset。
　　
　　DataAdapter 在连接到数据库时工作。单个 DataAdapter 的作用是使用数据库中的数据填充某个 DataTable，或将 DataTable 中的更改写回到数据库，或者二者兼而有之。
　　
　　DataAdapter 要求 Command 对象执行各种数据库操作。Command 对象存放 SQL 语句或指定数据访问实现方法的存储过程名称。每个 DataAdapter 有四个属性，指定用于四种数据访问类型之一的命令对象。
　　
　　SelectCommand：此 Command 对象用于从数据库中选择数据。
　　UpdateCommand：此 Command 对象用于更新数据库中的现有记录。
　　InsertCommand：此 Command 对象用于向数据库中插入新记录。
　　DeleteCommand：此 Command 对象用于删除数据库中的现有记录。
　　图 1 阐释了这些对象及其关系。
　　　
　　图 1：用于访问存储过程的主要 ADO.NET 类以及它们之间的关系
　　
　　到目前为止，您所看到的演示软件示例可能将其 Command 对象配置为使用 SQL 语句进行数据访问。实际上，某些示例可能完全跳过了 Command 对象的创建，这是因为 DataAdapter 的某个构造函数允许 Command 对象选择后台创建的数据。在使用存储过程之前，让我们运行这样一个示例进行比较。
　　
　　本文中的所有示例都使用 SQL Server 附带的 Northwind 示例数据库。我们还使用专门为 SQL Server 创建的 ADO.NET 类，而不是普通的 OLE DB 类。为了便于访问这些 SQL Server 类，所有示例都需要在应用程序的代码顶部加上以下代码行：
　　
　　Imports System.Data.SQLClient
　　
　　现在，让我们看看不使用存储过程执行数据访问的第一个示例。在此示例中，我们将在 Northwind 数据库 Products 表中检索所有产品。创建一个新 Windows 应用程序，在出现的空白 Form1 上，放置一个按钮和一个 DataGrid。将 DataGrid 的 Anchor 属性设置为全部四个边，使之随表单的扩展而扩展。在按钮的 Click 事件中，放置以下代码：
　　
　　Dim sConnectionString As String = _
　　"server=localhost;uid=sa;pwd=;database=Northwind"
　　Dim sSQL As String = "SELECT * FROM Products"
　　Dim daGetProducts As New SqlDataAdapter(sSQL, sConnectionString)
　　Dim dsProducts As New DataSet()
　　daGetProducts.Fill(dsProducts, "Products")
　　DataGrid1.DataSource = dsProducts.Tables("Products")
　　
　　根据计算机配置的不同，可能需要更改连接字符串。建立数据库连接后，其余代码应该可以正常运行。此演示软件说明了填入和使用 DataSet 的最简单方法。
　　
　　请注意，代码并不创建 Connection 对象或 Command 对象。事实上，没有这些对象，ADO.NET 便无法工作，但它们是在后台创建并使用的。实例化 SqlDataAdapter 的代码行传入 SQL 字符串（用于配置后台 Command 对象）和连接字符串（用于配置后台 Connection 对象）。
　　
　　我们可以将此代码更改为使用显式 Connection 和 Command 对象，以便稍稍远离演示软件。在表单上再放置一个按钮，并将以下代码放到 Click 事件中：
　　
　　Dim sConnectionString As String = _
　　"server=localhost;uid=sa;pwd=;database=Northwind"
　　Dim sSQL As String = "SELECT * FROM Products"
　　
　　Dim cnNorthwind As New SqlConnection(sConnectionString)
　　Dim cmdProducts As New SqlCommand(sSQL, cnNorthwind)
　　
　　Dim daGetProducts As New SqlDataAdapter(cmdProducts)
　　Dim dsProducts As New DataSet()
　　daGetProducts.Fill(dsProducts, "Products")
　　DataGrid1.DataSource = dsProducts.Tables("Products")
　　
　　此代码通过显式创建 Connection 和 Command 对象，并将这些对象附加到 DataAdapter，说明了 DataAdapters 的常用性。通过在实例化 DataAdapter 时传入 cmdProducts，DataAdapter 的 SelectCommand 将自动设置。然后，可以立即使用 DataAdapter 访问数据库。
　　
　　此代码的结果与前一示例中的结果相同。尽管它有点接近真实软件，但由于数据访问是通过 SQL 语句实现的，因此仍然属于演示软件。
　　
　　使用简单存储过程获取数据
　　如何将此演示软件更改为使用存储过程？只需更改几行代码。在表单上再放置一个按钮，并将以下代码放到 Click 事件中：
　　
　　Dim sConnectionString As String = _
　　"server=localhost;uid=sa;pwd=;database=Northwind"
　　Dim cnNorthwind As New SqlConnection(sConnectionString)
　　Dim cmdProducts As New _
　　SqlCommand("十件最贵的产品", cnNorthwind)
　　cmdProducts.CommandType = CommandType.StoredProcedure
　　
　　Dim daGetProducts As New SqlDataAdapter(cmdProducts)
　　Dim dsProducts As New DataSet()
　　daGetProducts.Fill(dsProducts, "Products")
　　DataGrid1.DataSource = dsProducts.Tables("Products")
　　
　　实例化 Command 对象时，此代码不使用 SQL 语句并替换为要使用的存储过程名称。此外，Command 对象的 CommandType 属性必须设置为 StoredProcedure。
　　
　　此后的代码与上一个示例非常相似，但它返回不同的数据。存储过程查找十件最贵的产品，并只返回每个产品的名称和价格。
　　
　　带输入参数的存储过程
　　此示例很简单，因为存储过程不需要任何输入参数。也就是说，查找十件最贵的产品不需要任何外部信息。无需外界帮助，存储过程即可完成此操作。然而，多数存储过程都需要输入参数来执行其功能。在下一个示例中，让我们看看如何向存储过程传递输入参数。我们将使用 CustomerID 来获取相关客户的所有订单，并使用名为 CustOrderHist 的存储过程（已存在于 Northwind 数据库中）。
　　
　　在已使用的表单上再创建一个按钮，并将以下代码行放到按钮的 Click 事件后面：
　　
　　Dim sConnectionString As String = _
　　"server=localhost;uid=sa;pwd=;database=Northwind"
　　Dim cnNorthwind As New SqlConnection(sConnectionString)
　　Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
　　cmdOrders.CommandType = CommandType.StoredProcedure
　　' 为存储过程设置参数
　　Dim prmCustomerID As New SqlParameter()
　　prmCustomerID.ParameterName = "@CustomerID"
　　prmCustomerID.SqlDbType = SqlDbType.VarChar
　　prmCustomerID.Size = 5
　　prmCustomerID.Value = "ALFKI"
　　
　　cmdOrders.Parameters.Add(prmCustomerID)
　　
　　Dim daGetOrders As New SqlDataAdapter(cmdOrders)
　　Dim dsOrders As New DataSet()
　　daGetOrders.Fill(dsOrders, "Orders")
　　DataGrid1.DataSource = dsOrders.Tables("Orders")
　　
　　此代码与上一