C# – Verwendung von Tabellenwertparametern (TVP) mit ADO.NET, Dapper und EF Core

Mit Tabellenwertparametern (TVP) können Sie mehrere Datenzeilen als Parameter in SQL-Abfragen senden.

TVPs sind flexibel. Sie können sowohl in reinen SQL-Abfragen als auch mit gespeicherten Prozeduren verwendet werden und können in allen Arten von Abfragen verwendet werden, von Einfügungen bis zu Auswahlen.

In diesem Artikel zeige ich, wie man TVPs durch Masseneinfügungen verwendet. Sie können für viel mehr als das verwendet werden, aber dies ist eines der häufigsten Szenarien. Ich werde Beispiele mit ADO.NET, Dapper und EF Core zeigen. Am Ende zeige ich einen Leistungsvergleich zwischen einer Masseneinfügung mit TVP und SqlBulkCopy.

Hinweis:In allen Beispielen verwende ich den DataTable-Ansatz anstelle des DataReader-Streaming-Ansatzes.

1 – Erstellen Sie den TVP-Typ in der Datenbank

Um Tabellenwertparameter übergeben zu können, müssen Sie in der Datenbank einen Tabellentyp wie diesen erstellen:

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)

Dieser kann beliebig viele Spalten haben.

2 – Füllen Sie eine Datentabelle auf

Um einen TVP aus dem Code einzufügen, müssen Sie zuerst eine DataTable erstellen und füllen, wie hier:

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)

Hinweis:Die Spaltenreihenfolge in der DataTable muss mit der Spaltenreihenfolge in der TVP-Definition übereinstimmen.

3 – Verwenden Sie den TVP in einer Abfrage

Sie können TVPs mit einer gespeicherten Prozedur oder einer unformatierten SQL-Abfrage verwenden. Ich werde unten Beispiele für beides zeigen.

Verwenden Sie das TVP mit einer rohen SQL-Abfrage

Der Schlüssel zum Übergeben eines Tabellenwertparameters besteht darin, einen SqlParameter mit SqlDbType.Structured zu verwenden und die TypeName-Eigenschaft auf den TVP-Typ festzulegen, den Sie in Schritt 1 in der Datenbank erstellt haben (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)

Verwenden Sie das TVP mit einer gespeicherten Prozedur

Fügen Sie den TVP als schreibgeschützten Parameter in der gespeicherten Prozessdefinition hinzu. Dann können Sie wie folgt daraus auswählen:

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)

Fügen Sie im Code den TVP als SqlParameter mit SqlDbType.Structured hinzu und legen Sie TypeName auf den TVP-Typ fest, den Sie in Schritt 1 erstellt haben (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)

Beispiele für die Verwendung eines TVP mit Dapper und EF Core

In den Schritten 1-3 oben habe ich gezeigt, wie man TVPs mit ADO.NET verwendet. Sie können TVPs auch mit Dapper und EF Core verwenden. Beispiele dafür zeige ich weiter unten. Ich werde das weiter oben in diesem Artikel gezeigte TVP, DataTable und Stored Proc verwenden.

TVP mit Dapper

Wenn Sie das Dapper-Nuget-Paket noch nicht installiert haben, holen Sie es sich mit dem folgenden Befehl (View> Other Windows> Package Manager Console) :

Install-Package Dapper
Code language: PowerShell (powershell)

Um einen TVP mit Dapper zu verwenden, müssen Sie AsTableValuedParameter („TVP-Typname“) für Ihre DataTable aufrufen und wie folgt übergeben:

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)

Dies ist etwas einfacher als die Verwendung von ADO.NET und hat eine ähnliche Leistung.

TVP mit EF Core

Wenn Sie das EF Core SQL Server-Paket noch nicht haben, rufen Sie es mit dem folgenden Befehl ab (Ansicht> Andere Windows> Paket-Manager-Konsole) :

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

So übergeben Sie einen TVP bei Verwendung von EF Core:

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)

Hinweis:Dies ist das manuelle Erstellen eines DbContext-Objekts.

Beachten Sie, dass dies dem ähnelt, was Sie tun müssen, wenn Sie ADO.NET verwenden. Sie müssen einen SqlParameter mit SqlDbType.Structured übergeben und den Namen des TVP-Typs angeben.

Microsoft.Data.SqlClient.SqlParameter verwenden

Wenn Sie versuchen, einen System.Data.SqlClient.SqlParameter an ExecuteSqlInterpolated() zu übergeben, erhalten Sie den folgenden verwirrenden Fehler:

Dies ist nur verwirrend, da nicht die vollständigen Typnamen angezeigt werden. Diese Ausnahme bedeutet wirklich, dass erwartet wird, dass Sie Microsoft.Data.SqlClient.SqlParameter anstelle von System.Data.SqlClient.SqlParameter verwenden.

Leistungsvergleich zwischen TVP-Masseneinfügung und SqlBulkCopy bei Verwendung einer DataTable

TVP ist eine Alternative zur Verwendung von SqlBulkCopy für die Masseneinfügung. SqlBulkCopy führt eine BULK INSERT durch und ist viel schneller als mehrere Einfügungen. Aber was ist besser – TVP-Masseneinfügung oder SqlBulkCopy?

Microsoft behauptet, dass die TVP-Masseneinfügung schneller ist, wenn weniger als 1000 Datensätze eingefügt werden.

Um die Leistung der beiden Ansätze zu vergleichen, habe ich 10, 500, 1000 und 10.000 Datensätze in eine leere Tabelle eingefügt. Ich habe jede Operation 10 Mal ausgeführt und die durchschnittliche Ausführungszeit in Millisekunden mit einer Stoppuhr gemessen. Um die Ansätze vergleichbar zu machen, habe ich beim Einfügen eine DataTable verwendet.

Für den TVP-Masseneinfügungsansatz habe ich ADO.NET mit einer gespeicherten Prozedur verwendet, da dies schneller ist als die Verwendung von Dapper und EF Core.

Ergebnisse

Hier sind die Ergebnisse des Leistungsvergleichs:

Hinweis:Ich habe Ergebnisse für mehrere einzelne Einsätze als Referenz hinzugefügt.

TVP-Masseneinfügung und SqlBulkCopy verhalten sich ungefähr gleich, wenn 500 oder weniger Datensätze eingefügt werden. Danach übertrifft SqlBulkCopy den TVP-Ansatz. Bei 1000 Datensätzen ist es 2x schneller. Bei 10.000 Datensätzen ist es ~8x schneller.

SqlBulkCopy ist auf Masseneinfügungen spezialisiert, während TVP eine Allzweckoption ist. Wenn Sie einfache, leistungsstarke Masseneinfügungen durchführen müssen, verwenden Sie SqlBulkCopy.