XML と Visual Basic .NET を使って SQL Server ストアドプロシージャに値の配列を渡す方法


Author:


MVP Maur MVP

コミュニティソリューションコンテンツに関する免責事項


MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

上記


IN 句の一覧として使うには、SQL Server ストアドプロシージャに値の配列を渡す必要があります。

原因


現在のバージョンの Microsoft SQL Server には、ストアドプロシージャまたは SQL ステートメントのパラメーターとして値の配列を渡すことができる配列データ型がサポートされていません。 多くの場合、開発者は、IN 句で指定されたリストに基づいてレコードを選択するように値の配列を渡す必要があります。 場合によっては、カンマで区切られた文字列としてストアドプロシージャに渡されたパラメーターの一覧を、IN 句のパラメーターとして直接使うことはできません。また、IN 句の内部で使用できる形式に変換する必要があります。

スーパーバイザー


この問題の解決策の1つは、値の配列を XML 文字列パラメーターとしてストアドプロシージャに渡し、ストアドプロシージャの内部で OPENXML 行セットプロバイダーを使って指定された XML から値を選択することです。 OPENXML プロバイダーと Transact-sql ステートメントを併用すると、柔軟かつ簡単な方法で、渡された値の配列に基づいてデータベース内のデータを操作できます。 プロジェクトメモを作成Noteするこのサンプルには、製造レベルのアプリケーションで必要となる例外処理に対する強力な機能は含まれていません。  "受注" テーブルはノースウィンド SQL Server 2000 データベースから使用されます。 Northwind データベースでストアドプロシージャを作成するには、次のようにします。  存在する場合 (sysobjects から名前を選択します)。 "sp_SelectOrders" と入力し、「= ' P ')」と入力します sp_SelectOrdersGO PROC sp_SelectOrders @in_values nText は、XML documnetexec @hDoc sp_xml_preparedocument の出力として入力値を準備する @in_values--XMLSELECT * の値に基づいて、テーブルからデータを選択します (ここでは、[customerid からの取引先企業 (@hdoc、'/Newdataset/顧客]、1)、(CustomerID NCHAR (5))): EXEC sp_xml_removedocument @hDoc 「Microsoft Visual Studio .NET を起動して、コンソールアプリケーションを作成する」を参照してください。 既定の Module1 ファイルが作成されます。 次のようにして、Module1 ファイルの中のストロングを置き換えます。 簡単な例として、サンプルの強度では、customers テーブルからすべての顧客の一覧が選択され、ユーザーの半数のために値の配列が作成されます (限定的な選択を示すことができます)。その後、選択した顧客の Orders テーブルから注文の一覧を選択するストアドプロシージャを実行します。  環境のインポートシステムで使うために、サンプルで接続文字列を変更する必要があります。 Data. SqlClientImports システムの XmlImports loCustomers モジュール Module1 PublicSub Main () Dim of DataTable として、データベース loCustomers = GetCustomers () Console から顧客の一覧を取得します。 loCustomers = getcustomers () ("Total Customers:")。 &Rows) loCustomers Isnoプラズマ Then loOrders = GetOrders (loCustomers) ("合計受注:") EndIf ("合計注文数:") EndIf ("注文合計: &") EndIf (例: 例外本体)メッセージ) Finally () loCustomers = loCustomers () = 何もない場合は、loCustomers = loorders は Isnoん。 Dispose () loOrders = 何の EndIf Endendsub PrivateFunction GetOrders (ByVal loCustomers) を SqlParameter Dim Loorders として、Customers DataSet から loCustomers ("Customers") の文字列として、ユーザーデータセットからの XML 出力を準備することができます。列の "ColumnMapping = SqlParameter" のように指定します。次の属性は、ストアドプロシージャに XML を渡します。属性は、パラメーター Locol = New ("@in_values"、SqlDbType) のようになります。値 = loCustomers ' データベース内からの注文の一覧を取得する: GetDataFromDb ("sp_SelectOrders"、CommandType. StoredProcedure、"顧客"、Locol) "の場合、注文のリストを DataTable として返します。この場合、IsNothing = 1 の場合は、Locol を返します。テーブル (0) EndIf Catch 関数 PrivateFunction GetCustomers () は、dataset として、次のように、データセットを Dim として受け取ります。データベース loCustomers = GetDataFromDb (" 顧客からの CustomerID を選択してください ", CommandType. Text," Customers ") ' このデモでは、一部のユーザーについてのみ情報を選択していることを示すために、デモの目的の半分を削除し、loCustomers Isnoん。Rows. Count \ 2 DoWhile。行数 > i.RemoveAt (0) ループ ' 変更を承諾して、DataTable から完全に行を削除します。AcceptChanges () EndWith Else ThrowNew ApplicationException ("Customers table は存在しません") EndIf EndIf ' 顧客の戻り値を DataSet として返す loCustomers Catch ex As Exception 関数 GetDataFromDb (ByVal lcSQL AsString) ByVal は、CommandType、_ ByVal Lcsql AsString、ByValParamArray Locommandtype () を SqlParameter として dataset dim loResult として、データセット暗転の作成とオープン接続 = 新しい SqlConnection ("Security Info = False" として) を指定して、データセットを作成して、そのデータを保存します。統合セキュリティ = SSPI; データベース = Northwind; サーバー = (ローカル);接続タイムアウト = 30 ") loConnection。 Open () ' Prepare コマンドを実行して、データベースの Loconnection = New SqlCommand (lcSQL, loConnection) Loconnection からデータを選択します。 CommandType = Loconnection IfNot Loconnection Isnoプラズマ Then、Loconnection Loconnection の ForEach Loconnection。 Parameters. Add (Loconnection) 次の EndIf loAdapter = New SqlDataAdapter (Loconnection) loResult = 新しいデータセットの読み込み (loResult, Lcsql) ' データセットとしての顧客の戻り値の一覧は、例外スローとして loResult Catch ex が返されます。 Dispose () loAdapter = 空の EndIf ではありません。 loconnection Isnoして、loconnection. Dispose () loconnection = 不要になった場合は、loconnection があります。切断 () EndIf 接続。破棄 () loconnection = 何 EndIf () loConnection! F5 キーを押して、アプリケーションをコンパイルして実行します。 コンソールに結果が表示されます。