C# – ADO.NET、Dapper、および EF Core でテーブル値パラメーター (TVP) を使用する方法

テーブル値パラメーター (TVP) を使用すると、複数行のデータをパラメーターとして SQL クエリに送信できます。

TVP は柔軟です。これらは、未加工の SQL クエリとストアド プロシージャの両方で使用でき、挿入から選択まで、あらゆる種類のクエリで使用できます。

この記事では、一括挿入を行って TVP を使用する方法を示します。これ以外にも使用できますが、これは最も一般的なシナリオの 1 つです。 ADO.NET、Dapper、EF Core を使用した例を紹介します。最後に、TVP と SqlBulkCopy を使用した一括挿入のパフォーマンス比較を示します。

注:すべての例で、DataReader ストリーミング アプローチではなく、DataTable アプローチを使用しています。

1 – データベースに TVP タイプを作成します

テーブル値パラメーターを渡すことができるようにするには、次のようにデータベースにテーブル型を作成する必要があります:

CREATE TYPE TVP_People AS TABLE
(
	[Id] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[BirthDate] [datetime] NULL,
	[FavoriteMovie] [nvarchar](50) NULL,
	[FavoriteNumber] [int] NULL
)
Code language: SQL (Structured Query Language) (sql)

これには任意の数の列を含めることができます。

2 – DataTable に入力する

コードから TVP を挿入するには、まず次のように DataTable を作成して入力する必要があります:

DataTable tvpPeople = new DataTable();

tvpPeople.Columns.Add(nameof(Person.Id), typeof(string));
tvpPeople.Columns.Add(nameof(Person.Name), typeof(string));
tvpPeople.Columns.Add(nameof(Person.BirthDate), typeof(DateTime));
tvpPeople.Columns.Add(nameof(Person.FavoriteMovie), typeof(string));
tvpPeople.Columns.Add(nameof(Person.FavoriteNumber), typeof(int));

foreach (var person in people)
{
	var row = tvpPeople.NewRow();
	row[nameof(Person.Id)] = person.Id;
	row[nameof(Person.Name)] = person.Name;
	row[nameof(Person.BirthDate)] = person.BirthDate ?? (object)DBNull.Value;
	row[nameof(Person.FavoriteMovie)] = person.FavoriteMovie;
	row[nameof(Person.FavoriteNumber)] = person.FavoriteNumber ?? (object)DBNull.Value;
	tvpPeople.Rows.Add(row);
}
Code language: C# (cs)

注:DataTable の列の順序は、TVP 定義の列の順序と一致する必要があります。

3 – クエリで TVP を使用する

ストアド プロシージャまたは生の SQL クエリで TVP を使用できます。以下に両方の例を示します。

生の SQL クエリで TVP を使用する

テーブル値パラメーターを渡すための鍵は、SqlDbType.Structured で SqlParameter を使用し、TypeName プロパティを手順 1 でデータベースに作成した TVP 型 (dbo.TVP_People) に設定することです。

using(var connection = new SqlConnection(GetConnectionString()))
{
	connection.Open();
	using (var command = new SqlCommand("INSERT INTO People (Id, Name, BirthDate, FavoriteMovie, FavoriteNumber) SELECT Id, Name, BirthDate, FavoriteMovie, FavoriteNumber FROM @TVP_People", connection))
	{
		var param = command.Parameters.AddWithValue("@TVP_People", tvpPeople);
		param.SqlDbType = SqlDbType.Structured;
		param.TypeName = "dbo.TVP_People";

		command.ExecuteNonQuery();
	}
}
Code language: C# (cs)

ストアド プロシージャで TVP を使用する

ストアド プロシージャ定義に TVP を読み取り専用パラメーターとして追加します。次に、次のように選択できます:

CREATE PROCEDURE InsertPeople
        @TVP_People dbo.TVP_People READONLY
AS
BEGIN
    INSERT INTO People (Id, Name, BirthDate, FavoriteMovie, FavoriteNumber) 
	SELECT Id, Name, BirthDate, FavoriteMovie, FavoriteNumber FROM @TVP_People
END
Code language: SQL (Structured Query Language) (sql)

コードで、SqlDbType.Structured を使用して TVP を SqlParameter として追加し、TypeName を手順 1 で作成した TVP タイプ (dbo.TVP_People) に設定します。

using(var connection = new SqlConnection(GetConnectionString()))
{
	connection.Open();
	using (var command = new SqlCommand("dbo.InsertPeople", connection))
	{
		command.CommandType = CommandType.StoredProcedure;

		var param = command.Parameters.AddWithValue("@TVP_People", tvpPeople);
		param.SqlDbType = SqlDbType.Structured;
		param.TypeName = "dbo.TVP_People";

		command.ExecuteNonQuery();
	}
}
Code language: C# (cs)

Dapper と EF Core で TVP を使用する例

上記の手順 1 ～ 3 では、ADO.NET で TVP を使用する方法を示しました。 Dapper および EF Core で TVP を使用することもできます。以下にその例を示します。この記事で前述した TVP、DataTable、およびストアド プロシージャを使用します。

Dapper を使用した TVP

Dapper nuget パッケージをまだインストールしていない場合は、次のコマンドで取得します ([表示]> [その他の Windows]> [パッケージ マネージャー コンソール]) :

Install-Package Dapper
Code language: PowerShell (powershell)

Dapper で TVP を使用するには、DataTable で AsTableValuedParameter(“TVP type name”) を呼び出し、次のように渡す必要があります。

using Dapper;

using (var connection = new SqlConnection(GetConnectionString()))
{
	connection.Execute("dbo.InsertPeople", new { TVP_People = tvpPeople.AsTableValuedParameter("dbo.TVP_People") }, commandType: CommandType.StoredProcedure);
	
}
Code language: C# (cs)

これは、ADO.NET を使用するよりも少し単純で、同様のパフォーマンスがあります。

EF Core を使用した TVP

EF Core SQL Server パッケージをまだ持っていない場合は、次のコマンドで取得します ([表示]> [その他の Windows]> [パッケージ マネージャー コンソール]) :

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Code language: PowerShell (powershell)

EF Core の使用時に TVP を渡す方法は次のとおりです。

using Microsoft.EntityFrameworkCore;

var optionsBuilder = new DbContextOptionsBuilder<DbContext>();
optionsBuilder.UseSqlServer(GetConnectionString());

using (var dbContext = new DbContext(optionsBuilder.Options))
{
	var tvpParameter = new Microsoft.Data.SqlClient.SqlParameter("@TVP_People", SqlDbType.Structured)
	{
		Value = tvpPeople,
		TypeName = "dbo.TVP_People"
	};
	dbContext.Database.ExecuteSqlInterpolated($"dbo.InsertPeople {tvpParameter}");
}
Code language: C# (cs)

注:これは、DbContext オブジェクトを手動で作成しています。

これは、ADO.NET を使用する場合に行う必要があることと似ていることに注意してください。 SqlDbType.Structured を使用して SqlParameter を渡す必要があり、TVP タイプ名を指定する必要があります。

Microsoft.Data.SqlClient.SqlParameter を使用

System.Data.SqlClient.SqlParameter を ExecuteSqlInterpolated() に渡そうとすると、次の紛らわしいエラーが発生します:

完全な型名が表示されていないため、これは紛らわしいだけです。この例外は、System.Data.SqlClient.SqlParameter の代わりに Microsoft.Data.SqlClient.SqlParameter を使用することを期待していることを意味します。

DataTable 使用時の TVP 一括挿入と SqlBulkCopy のパフォーマンス比較

TVP は、一括挿入に SqlBulkCopy を使用する代わりの方法です。 SqlBulkCopy は BULK INSERT を実行し、複数の挿入を行うよりもはるかに高速です。しかし、TVP 一括挿入と SqlBulkCopy のどちらが優れているでしょうか?

Microsoft は、挿入するレコード数が 1000 未満の場合、TVP 一括挿入の方が高速であると主張しています。

2 つのアプローチのパフォーマンスを比較するために、10、500、1000、および 10,000 のレコードを空のテーブルに挿入しました。各操作を 10 回実行し、ストップウォッチを使用して平均実行時間をミリ秒単位で取得しました。アプローチを比較できるようにするために、挿入時に DataTable を使用しました。

TVP 一括挿入アプローチでは、Dapper や EF Core を使用するよりも高速であるため、ストアド プロシージャで ADO.NET を使用しました。

結果

パフォーマンスの比較結果は次のとおりです:

注:参照用に複数の個々の挿入の結果を追加しました.

500 以下のレコードを挿入する場合、TVP 一括挿入と SqlBulkCopy はほぼ同じパフォーマンスを発揮します。その後、SqlBulkCopy は TVP アプローチよりも優れています。 1000 レコードでは、2 倍高速です。 10,000 レコードでは、最大 8 倍高速です。

SqlBulkCopy は一括挿入に特化していますが、TVP は汎用オプションです。シンプルでパフォーマンスの高い一括挿入を行う必要がある場合は、SqlBulkCopy を使用してください。