在 Visual Basic .NET 中使用存储过程(1)
2024-07-10 13:02:43
供稿：网友
在 visual basic .net 中使用存储过程
billy hollis
2002年9月14日
从 msdn code center 下载 storedprocvb.net.exe 示例文件（英文）。(http://msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/026/002/872/msdncompositedoc.xml)
摘要：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")
此代码与上一个示例中的代码非常相似，不同之处在于创建 command 对象之后，为其配置了 parameter 对象并将此对象添加到 command 的参数集合中。在此示例中（更接近于演示软件）将对客户 id 进行硬编码，参数的 value 属性通常会设置为某些用户输入数据。但是，参数的其他属性可以完全象此示例中那样设置。
此示例中的所有参数设置都是显式设置。某些开发人员喜欢这种样式，因为它便于说明。但某些开发人员喜欢使用代码行较少的等价方法：
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
cmdorders.parameters.add(new _
sqlparameter("@customerid", sqldbtype.varchar, 5))
cmdorders.parameters("@customerid").value = "alfki"
dim dagetorders as new sqldataadapter(cmdorders)
dim dsorders as new dataset()
dagetorders.fill(dsorders, "orders")
datagrid1.datasource = dsorders.tables("orders")
此代码与上一示例的作用完全相同。但每个参数只需要两行代码，而不是六行。如果存储过程包含大量参数（如后面某些示例所示），所需代码行的多少就会有明显区别，因此在后面部分，我们将使用此表单。
使用存储过程更新数据库
以上示例使用存储过程从数据库中提取信息。在复杂应用程序中使用存储过程更新、插入和删除记录也很常见。让我们看看如何使用 ado.net 完成该操作。
在第一个示例中，我们将使用 visual studio® .net 中的向导编写一个存储过程集合，并创建使用这些过程的代码。尽管我们只需在此示例中编写最少量的代码，但检查向导创建的代码有助于我们理解除获取数据以外，有关与存储过程交互操作的过程。
在此示例中，我们将使用 northwind 示例数据库中的 customers 表。安装后的 northwind 数据库中不包含用于更新、插入或删除客户的存储过程，但 visual studio .net 中的 dataadapter configuration wizard（数据适配器配置向导）可轻松地为我们编写一些存储过程。
启动新的 windows application（windows 应用程序）项目。在空白的 form1 上，放置一个 datagrid 和两个按钮。和先前一样，更改 datagrid 的 anchor 属性使之锚定到全部四个边。将按钮命名为 btnfill 和 btnupdate，并分别将其 text 属性更改为 fill 和 update。
转到 toolbox（工具箱）的 data（数据）选项卡，将 sqldataadapter 控件拖动到窗体上，然后释放鼠标。这将启动 dataadapter configuration wizard（数据适配器配置向导）。单击 next（下一步）按钮开始向向导中输入信息。
首先，需要选择一个到 northwind 数据库的连接；如果列表中未显示所需连接，则单击 new connection（新建连接）按钮创建一个连接。然后单击 next（下一步）按钮。
下一屏幕上将出现三种数据访问方法。其外观与图 2 类似。
图 2：选择用于 dataadapter 的数据访问类型
此时，多数演示软件示例选择第一个选项来使用 sql 语句。但是，我们将使用第二个选项，并让向导为我们生成一些存储过程。选择 create new stored procedures（创建新存储过程）选项，然后单击 next（下一步）按钮。
下一屏幕将请求 sql 语句，指示最初从数据库中提取的数据。但并不直接使用此 sql 语句。sql 语句中的信息将用于构造存储过程，以便执行实际数据访问。为使示例简单起见，请输入 sql 语句 select * from customers，然后按 next（下一步）按钮。
此时，向导会请求要创建的存储过程的名称。操作共有四种 - select、update、insert 和 delete 操作。按以下方法对其命名：
select：msdnselectcustomers
update：msdnupdatecustomer
insert：msdninsertcustomer
delete：msdndeletecustomer
选择 yes, create them in the database for me（是的，在数据库中创建它们。）选项。此时，向导屏幕应该如图 3 所示。
图 3：命名要由 dataadapter 向导创建的存储过程
单击 next（下一步）按钮。向导将创建存储过程并在状态栏屏幕上指示其进度。完成后，可单击 finish（完成）按钮退出向导。
向导创建了配置完整的 dataadapter，但未创建 dataset 来存放数据。这是我们下一步要做的。在 toolbox（工具框）的 data（数据）选项卡中，拖动 dataset 控件。出现配置屏幕时，选择 untyped dataset（无类型的数据集）。
现在我们准备使用 dataadapter 填充数据集。在 btnfill 的 click 事件中，放入以下两行代码：
sqldataadapter1.fill(dataset1, "customers")
datagrid1.datasource = dataset1.tables("customers")
在 btnupdate 的 click 事件中，放入以下代码行：
sqldataadapter1.update(dataset1, "customers")
现在我们有了一段使用存储过程进行数据访问的有效演示软件。可以运行程序并单击 fill 按钮获取网格中的客户列表。然后，可在窗格中编辑客户记录并选择 update 按钮将更改放回到数据库中。
注意：编辑第一列（即 customerid）时将出现异常，因为在 sql server 中不能更新数据库记录中的主键。
查看由向导生成的代码会有所帮助，所有这些代码最初都隐藏在 windows form designer generated code（windows 窗体设计器生成的代码）区域中。单击该区域的加号可展开代码。注意以下代码，它对所需的 sqldataadapter 和四个命令对象进行了实例化：
me.sqldataadapter1 = new system.data.sqlclient.sqldataadapter()
me.sqlselectcommand1 = new system.data.sqlclient.sqlcommand()
me.sqlinsertcommand1 = new system.data.sqlclient.sqlcommand()
me.sqlupdatecommand1 = new system.data.sqlclient.sqlcommand()
me.sqldeletecommand1 = new system.data.sqlclient.sqlcommand()
此后的代码配置每个命令对象并为其创建参数集合。此代码与上一个示例相似，它们都使用带参数的存储过程。但向导生成的代码使用参数的某些附加属性，以使其与更改数据的存储过程协同工作。例如，用于创建 sqlinsertcommand1 的 companyname 参数的代码：
me.sqlinsertcommand1.parameters.add(new _
system.data.sqlclient.sqlparameter("@companyname", _
system.data.sqldbtype.nvarchar, 40, "companyname"))
在上一个示例中，我们只为参数名称、数据类型和长度等设置了属性。此代码还会将参数的 sourcecolumn 属性设置为值 companyname。该属性指示 dataset 的 customers datatable 中与此参数对应的字段。这使 datatable 中的值在插入操作期间自动插入到参数的 value 属性中。让我们来详细介绍一下。
调用 sqldataadapter 的 update 方法时，该方法将更新 dataset 中的单个 datatable。当逐行检查 datatable 时，会查找需要更新、插入或删除的行。当找到需要插入到数据库中的行后，sqldataadapter 将使用由其 insertcommand 属性设置的 command 对象。这种情况下，command 对象将访问 msdninsertcustomer 存储过程。
在该存储过程运行前，每个参数的 value 属性都必须从插入的行中导入。配置 sqldataadapter1 的代码将存储过程的每个参数与 datatable 中的相应字段相关联。这样，新 datatable 行中的数据将自动传输到存储过程的参数。
其他存储过程参数的配置方法与此相似。但有一个不同之处值得注意。其他存储过程传入 datatable 中数据的原始值，这些值用于检查数据是否在您不知情的情况下发生了更改。也就是说，如果您提取了某些数据，而其他人在您尝试更新前更改了该数据，您将收到并发异常。启动以上程序、提取客户，然后使用工具（例如 sql enterprise manager）更改记录中的内容，便可以看到这种情况的发生。如果您在示例程序中更改同一记录并尝试进行更新，则会收到并发异常。
