使用 XML 和 Visual Basic .NET 将值数组传递到SQL Server存储过程
本文介绍如何使用 XML 和 Visual Basic .NET 将值数组传递到SQL Server存储过程。
原始产品版本: Visual Basic
原始 KB 编号: 555266
症状
需要将值数组传递到SQL Server存储过程,以将其用作子句的列表IN
。
原因
当前版本的 Microsoft SQL Server不支持允许将值数组作为存储过程或 SQL 语句的参数传递的数组数据类型。 通常,开发人员需要传递值数组,以便根据子句中 IN
提供的列表选择记录。 在某些情况下,以逗号分隔的字符串形式传递给存储过程的参数列表,此分隔字符串不能直接用作 IN 子句的参数,并且必须转换为在 子句内 IN
使用时可接受的形式。
解决方案
此问题的解决方案之一是将值的数组作为 XML 字符串参数传递给存储过程,并在存储过程内部使用 OPENXML rowset
提供程序从提供的 XML 中选择值。 OPENXML
将 提供程序与 Transact-SQL 语句结合使用可提供灵活且简单的方法来根据传递的值数组操作数据库中的数据。
创建项目
注意
此示例不包含生产级应用程序中所需的异常处理代码。
我们将使用 SQL Server 数据库中的 Northwind
Orders 表。 使用以下强在数据库中创建存储过程 Northwind
。
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_SelectOrders'
AND type = 'P')
DROP PROCEDURE sp_SelectOrders
GO
CREATE PROC sp_SelectOrders @in_values nText AS
DECLARE @hDoc int
--Prepare input values as an XML documnet
exec sp_xml_preparedocument @hDoc OUTPUT, @in_values
--Select data from the table based on values in XML
SELECT * FROM Orders WHERE CustomerID IN (
SELECT CustomerID FROM OPENXML (@hdoc, '/NewDataSet/Customers', 1)
WITH (CustomerID NCHAR(5)))
EXEC sp_xml_removedocument @hDoc
GO
启动 Visual Studio .NET 并创建控制台应用程序。 默认情况下,将创建 Module1.vb 文件。
将 Module1.vb 文件中的 强 替换为以下文件。 为了简化和示例,sample strong 从“客户”表中选择所有客户的列表,仅为一半 (的客户的 XML 字符串准备值数组,以演示) 有限选择,然后运行存储过程,从所选客户的“订单”表中选择订单列表。
注意
需要修改示例中的强连接字符串才能在环境中使用它
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Text
Module Module1
PublicSub Main()
Dim loCustomers As DataSet
Dim loOrders As DataTable
Try
'Get list of the customers from the database
loCustomers = GetCustomers()
Console.WriteLine("Total customers: " & loCustomers.Tables(0).Rows.Count.ToString)
If Not loCustomers Is Nothing Then
loOrders = GetOrders(loCustomers)
Console.WriteLine("Total orders: " & loOrders.Rows.Count.ToString)
EndIf
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
If Not loCustomers Is Nothing Then
loCustomers.Dispose()
loCustomers = Nothing
EndIf
If Not loOrders Is Nothing Then
loOrders.Dispose()
loOrders = Nothing
EndIf
EndTry
EndSub
Private Function GetOrders(ByVal loCustomers As DataSet) As DataTable
Dim loOrders As DataSet
Dim loParameter As SqlParameter
Dim loCol As DataColumn
Try
'Prepare XML output from the Customers DataSet as a string
ForEach loCol In loCustomers.Tables("Customers").Columns
loCol.ColumnMapping = System.Data.MappingType.Attribute
Next
'Pass XML into the stored procedure as a parameter
loParameter = New SqlParameter("@in_values", System.Data.SqlDbType.NText)
loParameter.Value = loCustomers.GetXml
'Get list of the orders from the database
loOrders = GetDataFromDb("sp_SelectOrders", CommandType.StoredProcedure, "Customers", loParameter)'Return list of the orders as a DataTable
If (Not loOrders Is Nothing) AndAlso loOrders.Tables.Count = 1 Then
Return loOrders.Tables(0)
EndIf
Catch ex As Exception
Throw ex
EndTry
EndFunction
Private Function GetCustomers() As DataSet
Dim loCustomers As DataSet
Dim i As Int32
Try
'Get list of the customers from the database
loCustomers = GetDataFromDb("SELECT CustomerID FROM Customers", CommandType.Text, "Customers")'Remove half of the customers for the demo purposes to show that we select info just for some of them
If Not loCustomers Is Nothing Then
If loCustomers.Tables.Contains("Customers") Then
With loCustomers.Tables("Customers")
i = .Rows.Count \ 2
DoWhile .Rows.Count > i
.Rows.RemoveAt(0)
Loop
'Accept changes to remove the rows completely from the DataTable
.AcceptChanges()
EndWith
Else
ThrowNew ApplicationException("Customers table does not exist")
EndIf
EndIf
'Return list of the customers as a DataSet
Return loCustomers
Catch ex As Exception
Throw ex
EndTry
EndFunction
Private Function GetDataFromDb(ByVal lcSQL AsString, ByVal loCommandType As CommandType, _
ByVal lcTableName AsString, ByValParamArray loParameters() As SqlParameter) As DataSet
Dim loResult As DataSet
Dim loConnection As SqlConnection
Dim loCommand As SqlCommand
Dim loAdapter As SqlDataAdapter
Dim i As Int32
Dim loParameter As SqlParameter
Try
'Create and open connection to the Northwind database
loConnection = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=(local);Connect Timeout=30")
loConnection.Open()'Prepare command and to select data from the database
loCommand = New SqlCommand(lcSQL, loConnection)
loCommand.CommandType = loCommandType
IfNot loParameters IsNothingThen
ForEach loParameter In loParameters
loCommand.Parameters.Add(loParameter)
Next
EndIf
loAdapter = New SqlDataAdapter(loCommand)
loResult = New DataSet
loAdapter.Fill(loResult, lcTableName)'Return list of the customers as a DataSet
Return loResult
Catch ex As Exception
Throw ex
Finally
'Clean resources
If Not loAdapter Is Nothing Then
loAdapter.Dispose()
loAdapter = Nothing
EndIf
If Not loCommand Is Nothing Then
loCommand.Dispose()
loCommand = Nothing
EndIf
If Not loConnection Is Nothing Then
If loConnection.State = ConnectionState.Open Then
loConnection.Close()
EndIf
loConnection.Dispose()
loConnection = Nothing
EndIf
EndTry
EndFunction
EndModule
按 F5 编译并运行应用程序。 控制台将显示结果。
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈