C# – Come usare i parametri con valori di tabella (TVP) con ADO.NET, Dapper ed EF Core

I parametri con valori di tabella (TVP) consentono di inviare più righe di dati come parametro in query SQL.

I TVP sono flessibili. Possono essere utilizzati sia in query SQL grezze che con processi archiviati e possono essere utilizzati in tutti i tipi di query, dagli inserti alle selezioni.

In questo articolo, mostrerò come utilizzare i TVP eseguendo inserimenti in blocco. Possono essere utilizzati per molto di più, ma questo è uno degli scenari più comuni. Mostrerò esempi usando ADO.NET, Dapper ed EF Core. Alla fine, mostrerò un confronto delle prestazioni tra l'esecuzione di un inserimento collettivo con TVP e SqlBulkCopy.

Nota:in tutti gli esempi, utilizzo l'approccio DataTable invece dell'approccio di streaming DataReader.

1 – Crea il tipo TVP nel database

Per poter passare parametri con valori di tabella, devi creare un tipo di tabella nel database, come questo:

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)

Può avere un numero qualsiasi di colonne.

2 – Compila una DataTable

Per inserire un TVP dal codice, devi prima creare e popolare una DataTable, come questa:

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)

Nota:l'ordine delle colonne in DataTable deve corrispondere all'ordine delle colonne nella definizione TVP.

3 – Usa il TVP in una query

È possibile utilizzare TVP con un processo archiviato o una query SQL grezza. Mostrerò esempi di entrambi di seguito.

Usa TVP con una query SQL grezza

La chiave per passare un parametro con valori di tabella consiste nell'usare un SqlParameter con SqlDbType.Structured e nell'impostare la proprietà TypeName sul tipo TVP creato nel database nel passaggio 1 (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)

Usa il TVP con un processo memorizzato

Aggiungi il TVP come parametro di sola lettura nella definizione proc memorizzata. Quindi puoi selezionarlo, in questo modo:

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)

Nel codice, aggiungi TVP come SqlParameter con SqlDbType.Structured e imposta TypeName sul tipo TVP che hai creato nel passaggio 1 (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)

Esempi di utilizzo di un TVP con Dapper ed EF Core

Nei passaggi 1-3 precedenti, ho mostrato come utilizzare i TVP con ADO.NET. Puoi anche usare TVP con Dapper ed EF Core. Mostrerò esempi di questo di seguito. Userò TVP, DataTable e stored procedure mostrati in precedenza nell'articolo.

TVP con Dapper

Se non hai già installato il pacchetto Dapper nuget, scaricalo con il comando seguente (Visualizza> Altre finestre> Console di gestione pacchetti) :

Install-Package Dapper
Code language: PowerShell (powershell)

Per utilizzare un TVP con Dapper, devi chiamare AsTableValuedParameter ("TVP type name") sul tuo DataTable e passarlo in questo modo:

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)

Questo è un po' più semplice rispetto all'utilizzo di ADO.NET e ha prestazioni simili.

TVP con EF Core

Se non disponi già del pacchetto EF Core SQL Server, scaricalo con il comando seguente (Visualizza> Altre finestre> Console di gestione pacchetti) :

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

Ecco come trasmettere un TVP quando si utilizza 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)

Nota:questo sta creando manualmente un oggetto DbContext.

Nota che è simile a quello che devi fare quando usi ADO.NET. Devi passare un SqlParameter usando SqlDbType.Structured e devi specificare il nome del tipo TVP.

Utilizza Microsoft.Data.SqlClient.SqlParameter

Se provi a passare un System.Data.SqlClient.SqlParameter a ExecuteSqlInterpolated(), otterrai il seguente errore di confusione:

Questo è solo fonte di confusione perché non mostra i nomi dei tipi completi. Questa eccezione significa davvero che si aspetta che tu usi Microsoft.Data.SqlClient.SqlParameter invece di System.Data.SqlClient.SqlParameter.

Confronto delle prestazioni tra l'inserimento in blocco TVP e SqlBulkCopy quando si utilizza una DataTable

TVP è un'alternativa all'utilizzo di SqlBulkCopy per l'inserimento in blocco. SqlBulkCopy esegue un BULK INSERT ed è molto più veloce rispetto all'esecuzione di più inserimenti. Ma cosa funziona meglio:inserimento di massa TVP o SqlBulkCopy?

Microsoft afferma che l'inserimento in blocco TVP è più veloce quando si inseriscono meno di 1000 record.

Per confrontare le prestazioni dei due approcci, ho inserito 10, 500, 1000 e 10.000 record in una tabella vuota. Ho eseguito ogni operazione 10 volte e ho preso il tempo medio di esecuzione in millisecondi usando un cronometro. Per rendere gli approcci comparabili, ho usato una DataTable durante l'inserimento.

Per l'approccio di inserimento in blocco TVP, ho utilizzato ADO.NET con un processo archiviato, poiché è più veloce rispetto all'utilizzo di Dapper ed EF Core.

Risultati

Ecco i risultati del confronto delle prestazioni:

Nota:ho aggiunto risultati per più inserti singoli come riferimento.

L'inserimento in blocco TVP e SqlBulkCopy funzionano più o meno allo stesso modo quando si inseriscono 500 record o meno. Successivamente, SqlBulkCopy supera l'approccio TVP. Con 1000 record, è 2 volte più veloce. Con 10.000 record, è circa 8 volte più veloce.

SqlBulkCopy è specializzato per gli inserimenti in blocco, mentre TVP è un'opzione generica. Se devi eseguire inserimenti in blocco semplici ed efficaci, usa SqlBulkCopy.