閱讀英文

共用方式為

使用 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 中選取值。 搭配 Transact-SQL 語句使用 OPENXML 提供者，可根據傳遞的值陣列，提供彈性且簡單的方法來操作資料庫中的數據。

建立專案

注意

此範例不包含生產層級應用程式中所需的例外狀況處理程序代碼。

我們將使用 SQL Server 資料庫中的 Northwind Orders 數據表。 使用下列強式在資料庫中建立預存程式 Northwind 。

SQL 
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 檔案內的強式取代為下列檔案。 為了簡化和範例，範例強式會從 Customers 數據表中選取所有客戶的清單、準備值陣列，做為一半客戶的 XML 字串（以示範有限的選取專案），然後執行預存程式，從所選客戶的 Orders 數據表中選取訂單清單。

注意

您必須在強式範例中修改 連接字串，才能在您的環境中使用它

vbnet 
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 編譯和執行應用程式。 主控台會顯示結果。