オブジェクトの複雑なタイプリストをSQL Serverに送る方法

おいコミュニティ、私は戻っています!今回は、SQL ServerとCとの関係で問題を解決しました.

問題
だから、同じチャネル上のすべてのあなたの取得を背景に少し.
この特定のプロジェクトのために、私はSQL ServerとASPを使用しています.NET MVC 5.ADOを介してデータベース接続を管理します.ネット私は主にデータベースにデータを操作する負荷を処理するためにストアドプロシージャによって動作します.複数行を変更する必要があったとき、私はいつも私が作業したいIDをコンマ区切りの文字列を送信して終了しました.
ここではどのように変更するためにIDのリストを送信する方法の例です.

CREATE PROCEDURE 
    /*This will contain a comma separated list e.g 1,2,3,4,5 and so on*/
    @idList varchar(MAX)
AS
BEGIN
     --Do something useful with @idList
END

しかし、今回はIDだけでなく複雑なタイプの要素のリストを送る必要がありました.私はかつてこれをしました、しかし、その時、私はデータベースにストリングとしてそれを送ることができるために私の複雑なタイプをXMLに直列化しました、そして、データベースで一度、私はXMLを逆シリアル化しました必要です.それは仕事の権利の多くのように聞こえる?それで、私は何か他のものが働くのがより簡単であるならば、捜すことに決めました.

解決策:テーブル値パラメータレスキュー!
ソリューションを検索しながら、この手順でSQL Serverにパラメーターを送信する方法を発見しました.しかし、テーブル値パラメータは何ですか?
テーブル値パラメータは、その名前が暗黙のうちに定義されているような特定の構造テーブルを保持できるストアドプロシージャに送信できる特別なパラメーターです.基本的に、テーブルを送ることができます、あなたは以前に定義されました.
さもなければ、ストリングまたは複雑なタイプがXMLに変換されて、それからストリングとして彼らを送るために直列化されるように送られるパラメタまたはIDリストのそれらの長いリストにさよなら!

どうやって使うの?
テーブル値パラメータを使用するには、次の3つの手順を実行する必要があります.

ユーザー定義のテーブル型を作成します.

テーブル値パラメータを使用して、最近作成したユーザー定義のテーブル型を使用するストアドプロシージャを作成します.

ADOの設定パラメータをパラメータとして送信します.

各々のステップを壊しましょう.私たちは、単一の操作で映画を別の映画館に割り当てることができます映画のWebアプリで動作している想像してみましょう.

テーブル定義の作成
これはSQL Serverにどのようにしてテーブル値のパラメータで送信したいかを示します.
どのように構造化する必要があるか見てみましょう.

CREATE TYPE <schema>.<name of the type> AS TABLE
(
   <ColumnName> <Colum type>
)
GO

例は

CREATE TYPE dbo.MovieAssignment AS TABLE
(
    MovieId INT,
    Order TINYINT,
    MovieTheaterId INT
)
GO

テーブル値パラメータを使用する場合、ストアドプロシージャを作成します
それから、我々はテーブル値パラメタを送るために、ちょうど作成されたユーザ定義のテーブルタイプをSPで使用する必要があります.構造は通常のSPと同じです.

CREATE PROCEDURE <schema>.<name of the type>
(
    @<parameter name> <user-defined table type> READONLY
)
AS 
BEGIN
   -- SP's body
END

ここで重要な注意点は、我々はREADONLY SQL Serverに指示するパラメータ型の後のキーワードはテーブル値パラメータです.
私たちの映画のアプリの例に戻るには、次のようにSPを定義することができます.

CREATE PROCEDURE dbo.PersistMovieAssignments
(
    @MovieAssignments dbo.MovieAssignment READONLY
)
AS 
BEGIN
   -- SP's body
END

ADOの設定パラメータをパラメータとして送信する
この作業をする最後のステップは、私たちが送っているパラメタが特別なテーブル値のパラメタであるということを知らせるためにCのCount - codeでSPに我々の呼び出しを構成することです.
最初に考慮する必要があるのは、パラメータをDataTableとして送信する必要があり、構造体としてパラメーター型をマークする必要があるためです.このパラメータはテーブル値のパラメータです.
例を見ましょう.

using (var sqlConnection = new SqlConnection(/*Our connection string*/))
{
   sqlConnection.Open();
   /*Create the SqlCommand object to tell SQL what we want to execute, in our case is a stored procedure.*/
   var sqlCommand = new SqlCommand("dbo.PersistMovieAssignments", sqlConnection)
   {
      CommandType = CommandType.StoredProcedure
   };
   /*Then we create DataTable we want to send as table-valued parameter*/
   var movieAssignments = new DataTable();
   movieAssignments.Columns.Add("MovieId", typeof(int));
   movieAssignments.Columns.Add("Order", typeof(int));
   movieAssignments.Columns.Add("MovieTheaterId", typeof(int));

   /*Add the data to the DataTable object*/
   movieAssignments.Rows.Add(1, 1, 1);
   movieAssignments.Rows.Add(1, 2, 2);
   movieAssignments.Rows.Add(1, 5, 3);

   /*Then we create the SqlParameter object where we are going to assign the DataTable containing the data we want to send to SQL Server*/
   SqlParameter movieAssignmentsParameter = sqlCommand.Parameters.AddWithValue("@MovieAssignments", movieAssignments);
   movieAssignmentsParameter.SqlDbType = SqlDbType.Structured;

   /*Then we just execute our command*/
   sqlCommand.ExecuteReader();
}

我々のならばPersistMovieAssignments 我々が実行する結果を格納するべきであるどんな種類の価値でも、我々は帰りますExecuteReader いくつかの変数でメソッドを処理し、それに応じて値を抽出します.

重要な注意
私は、私があなたに知らせる価値があると思うように、ポストへの最新版の一部としてこのセクションを加えました.テーブル値のパラメータを使用しているときに注意する必要があります.

ADOの使用.テーブル値のパラメータを指定した場合、データベースに送信しているデータテーブルの列の順序は、ユーザー定義のテーブル型で定義されている列の順序に一致する必要があります.(この重要な注意のためにZohharに感謝します).

テーブル値のパラメータはTransact - SQLコードで読み取り専用です.テーブル値のパラメータの列の列値を更新できず、行を挿入または削除できません.ストアドプロシージャまたはパラメーター化されたステートメントに渡されたデータをテーブル値パラメーターで変更するには、データを一時テーブルまたはテーブル変数に挿入する必要があります.

ALTER TABLE文を使用すると、テーブル値のパラメーターのデザインを変更できません.

制限の詳細については、一般的にテーブル値のパラメータについて知りたい場合はdocumentation .

結論
私がこれを知っている前に、私はいつも私が以前に述べたように、データを複数の行をデータにXMLに直列化して、SQL Serverで再びそれを逆シリアル化するために必要とするとき、苦労していました.これは間違っていませんが、これはSerialize/Designialize部分のバグを挿入することができます.私は個人的にこの方法をはるかに強いタイプを見つけたので、コンパイル時にバグをたくさんキャッチできるので良いことです.
それで?これは知っていましたか.これを使ったことがありますか.これまでにSQL Serverにオブジェクトの複雑なタイプのリストを送信する必要が直面している?どうやってその問題に取り組みましたか.
私は、これが誰でも読むのに役立つことができることを望みます.コメントがあれば下のコメント欄に残してください.私はそれを感謝します!
読んで、次のポストでお会いしていただきありがとうございます!