C#でテーブル値パラメータ (Table-Valued Parameters - TVP)を利用した検索のコードを紹介します。
SQLで複数の値に一致する検索の場合には、IN演算子を利用すると検索できます。(IN演算子についてはこちらの記事を参照)
値の値が少ないうちは問題ないですが、プログラム等で、大量の値のリストが与えられた場合にはINを利用したクエリの大きさが大きくなることや、
SQLの作成が面倒になりjます。また、パフォーマンス面でもINに大量の候補がある場合には、IN演算子よりテーブル値パラメータ(TVP)のほうが速くなります。
この記事では、テーブル値パラメーターを作成して検索するコードを紹介します。
今回は以下のテーブルを利用します。
| id | model | name | class | category | price |
|---|---|---|---|---|---|
| 1 | C-XM01 | モーダンチェア | ホーム | チェア | 56000 |
| 2 | X-XD05 | ラージデスク | オフィス | テーブル | 87000 |
| 3 | A-DA40 | ラウンドダイニングチェア | ホーム | チェア | 28000 |
| 4 | O-XX100 | ナチュラルオフィス | オフィス | チェア | 13800 |
| 5 | R-D400 | ラウンドダイニングテーブル | ホーム | テーブル | 128000 |
| 6 | R7000 | ウッドキャビネット | オフィス | その他 | 32000 |
| 7 | B-200 | リネンベッド | ホーム | ベッド | 184500 |
| 8 | B-250 | ホワイトダブルベッド | ホーム | ベッド | 324850 |
| 9 | W-80 | ワーキングチェア | オフィス | チェア | 45000 |
| 10 | EG-10X | エルゴノミクスデスク | オフィス | テーブル | 88500 |
| 11 | NC-208 | ナチュラルウッドチェア | ホーム | チェア | 128000 |
はじめに、SQL Server 側でテーブル値型を定義します。
今回は、modelで検索することを想定しています。modelはNCHAR(64) で定義されていますので、以下のテーブル値型を作成するSQLを実行します。
CREATE TYPE dbo.ModelList AS TABLE
(
Model NCHAR(256) PRIMARY KEY
);
SQLを実行すると、SQL Serverのデータベースにユーザー定義テーブル型が作成されます。
SQL Server Management Studio で確認する場合は、オブジェクトエクスプローラーでデータベースのノードを開き、
[プログラミング] > [種類] > [ユーザー適宜テーブル型] のノード内にテーブル定義の項目が表示されます。
Windows Formアプリケーションを作成し、以下のプログラムを作成します。
テキストボックスとボタンを1つ配置したフォーム作成します。
using Microsoft.Data.SqlClient;
using static System.ComponentModel.Design.ObjectSelectorEditor;
using System.Windows.Forms;
using System.Data;
namespace TableValuedParametersQuery
{
public partial class FormMain : Form
{
public FormMain()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
List<string> qList = new List<string>() { "X-XD05", "R-D400", "B-250", "NC-208" };
string constr = @"Data Source=(DBホスト名またはIPアドレス);Initial Catalog=(データベース名);Connect Timeout=60;Persist Security Info=True;Encrypt=False;User ID=(DBユーザーID);Password=(DBパスワード)";
using (SqlConnection con = new SqlConnection(constr)) {
try {
con.Open();
string sqlstr = "SELECT * FROM ProductsF WHERE model IN(SELECT Model FROM @TVPParam)";
SqlCommand com = new SqlCommand(sqlstr, con);
// テーブル値型のデータを作成
DataTable table = new DataTable();
table.Columns.Add("Model", typeof(string));
foreach (string q in qList) {
table.Rows.Add(q);
}
// テーブル値パラメータを追加
SqlParameter tvpParam = com.Parameters.AddWithValue("@TVPParam", table);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.ModelList";
/*
//AddWithValue を利用しない書式
SqlParameter tvpParam = com.CreateParameter();
tvpParam.ParameterName = "@TVPParam";
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.Direction = ParameterDirection.Input;
tvpParam.Value = table;
tvpParam.TypeName = "dbo.ModelList";
com.Parameters.Add(tvpParam);
*/
//exec
SqlDataReader sdr = com.ExecuteReader();
while (sdr.Read() == true) {
string model = (string)sdr["model"];
string name = (string)sdr["name"];
string category = (string)sdr["category"];
decimal price = (decimal)sdr["price"];
textBox1.Text += string.Format("{0} / {1} / {2} : {3:g} \r\n", model.Trim(), name.Trim(), category.Trim(), price);
}
}
finally {
con.Close();
}
}
}
}
}
基本はこちらの記事で紹介している、SQL文を実行するプログラムと同様です。
SQL文は以下になります。IN演算子を利用し、サブクエリ（副問い合わせ）でユーザー定義テーブルの値が含まれているかを判定します。
string sqlstr = "SELECT * FROM ProductsF WHERE model IN(SELECT Model FROM @TVPParam)";
SqlCommand作成後に、テーブルパラメーターの作成をします。DataTableオブジェクトを作成し、
データベースのユーザー定義テーブル型の列名と同じ名前で、列を追加します。
Row.Addメソッドを呼び出し、検索したい値をDataTableオブジェクトに挿入します。
DataTable table = new DataTable();
table.Columns.Add("Model", typeof(string));
foreach (string q in qList) {
table.Rows.Add(q);
}
SQLパラメーターの追加も基本はこちらの記事で紹介しているコードと同様です。
テーブルパラメータ型を利用する場合は、SqlDbTypeに SqlDbType.Structured を設定します。
また、TypeName にはユーザー定義テーブル型の名称を設定します。
SqlParameter tvpParam = com.Parameters.AddWithValue("@TVPParam", table);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.ModelList";
AddWithValue を利用しない場合は以下の記述で動作します。
SqlParameter tvpParam = com.CreateParameter();
tvpParam.ParameterName = "@TVPParam";
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.Direction = ParameterDirection.Input;
tvpParam.Value = table;
tvpParam.TypeName = "dbo.ModelList";
com.Parameters.Add(tvpParam);
SQLクエリを実行して、結果を取得してテキストボックスに表示します。
SqlDataReader sdr = com.ExecuteReader();
while (sdr.Read() == true) {
string model = (string)sdr["model"];
string name = (string)sdr["name"];
string category = (string)sdr["category"];
decimal price = (decimal)sdr["price"];
textBox1.Text += string.Format("{0} / {1} / {2} : {3:g} \r\n", model.Trim(), name.Trim(), category.Trim(), price);
}
プロジェクトを実行します。下図のウィンドウが表示されます。
[button1]をクリックします。データベースを検索した結果が表示されます。
プログラムで設定した、 "X-XD05", "R-D400", "B-250", "NC-208" の項目の情報がデータベースから検索できています。
テーブル値パラメータ (Table-Valued Parameters)を利用した検索を実装できました。
