C#:cómo usar parámetros con valores de tabla (TVP) con ADO.NET, Dapper y EF Core

Los parámetros con valores de tabla (TVP) le permiten enviar múltiples filas de datos como un parámetro en consultas SQL.

Los TVP son flexibles. Se pueden usar tanto en consultas SQL sin formato como con procesos almacenados, y se pueden usar en todo tipo de consultas, desde inserciones hasta selecciones.

En este artículo, mostraré cómo usar los TVP mediante inserciones masivas. Se pueden usar para mucho más que esto, pero este es uno de los escenarios más comunes. Mostraré ejemplos usando ADO.NET, Dapper y EF Core. Al final, mostraré una comparación de rendimiento entre hacer una inserción masiva con TVP y SqlBulkCopy.

Nota:En todos los ejemplos, estoy usando el enfoque de DataTable en lugar del enfoque de transmisión de DataReader.

1 – Crear el tipo de TVP en la base de datos

Para poder pasar parámetros con valores de tabla, debe crear un tipo de tabla en la base de datos, como este:

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)

Esto puede tener cualquier número de columnas.

2 – Rellenar una tabla de datos

Para insertar un TVP desde el código, primero debe crear y completar un DataTable, como este:

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:El orden de las columnas en DataTable debe coincidir con el orden de las columnas en la definición de TVP.

3 – Usar el TVP en una consulta

Puede usar TVP con un proceso almacenado o una consulta SQL sin formato. Mostraré ejemplos de ambos a continuación.

Usar el TVP con una consulta SQL sin formato

La clave para pasar un parámetro con valores de tabla es usar SqlParameter con SqlDbType.Structured y establecer la propiedad TypeName en el tipo de TVP que creó en la base de datos en el paso 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)

Usar el TVP con un proceso almacenado

Agregue el TVP como un parámetro de solo lectura en la definición del proceso almacenado. Luego puede seleccionar de él, así:

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)

En el código, agregue el TVP como SqlParameter con SqlDbType.Structured y establezca TypeName en el tipo de TVP que creó en el paso 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)

Ejemplos de uso de un TVP con Dapper y EF Core

En los pasos 1 a 3 anteriores, mostré cómo usar TVP con ADO.NET. También puede usar TVP con Dapper y EF Core. Mostraré ejemplos de esto a continuación. Usaré TVP, DataTable y el proceso almacenado que se muestran anteriormente en el artículo.

TVP con Dapper

Si aún no tiene instalado el paquete nuget de Dapper, consígalo con el siguiente comando (Ver> Otras ventanas> Consola del administrador de paquetes) :

Install-Package Dapper
Code language: PowerShell (powershell)

Para usar un TVP con Dapper, debe llamar a AsTableValuedParameter ("nombre de tipo de TVP") en su DataTable y pasarlo así:

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)

Esto es un poco más simple que usar ADO.NET y tiene un rendimiento similar.

TVP con EF Core

Si aún no tiene el paquete EF Core SQL Server, consígalo con el siguiente comando (Ver> Otras ventanas> Consola del administrador de paquetes) :

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

Aquí se explica cómo pasar un TVP cuando se usa 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:Esto es crear manualmente un objeto DbContext.

Note que esto es similar a lo que tiene que hacer cuando usa ADO.NET. Debe pasar un SqlParameter usando SqlDbType.Structured y debe especificar el nombre del tipo de TVP.

Usar Microsoft.Data.SqlClient.SqlParameter

Si intenta pasar un System.Data.SqlClient.SqlParameter a ExecuteSqlInterpolated(), obtendrá el siguiente error confuso:

Esto solo es confuso porque no muestra los nombres completos de los tipos. Esta excepción realmente significa que espera que use Microsoft.Data.SqlClient.SqlParameter en lugar de System.Data.SqlClient.SqlParameter.

Comparación de rendimiento entre la inserción masiva de TVP y SqlBulkCopy cuando se usa un DataTable

TVP es una alternativa al uso de SqlBulkCopy para la inserción masiva. SqlBulkCopy realiza una INSERCIÓN BULK y es mucho más rápido que hacer varias inserciones. Pero, ¿qué funciona mejor:la inserción masiva de TVP o SqlBulkCopy?

Microsoft afirma que la inserción masiva de TVP es más rápida cuando se insertan menos de 1000 registros.

Para comparar el rendimiento de los dos enfoques, inserté 10, 500, 1000 y 10 000 registros en una tabla vacía. Ejecuté cada operación 10 veces y tomé el tiempo promedio de ejecución en milisegundos usando un cronómetro. Para hacer que los enfoques sean comparables, utilicé un DataTable al insertar.

Para el enfoque de inserción masiva de TVP, usé ADO.NET con un proceso almacenado, ya que es más rápido que usar Dapper y EF Core.

Resultados

Estos son los resultados de la comparación de rendimiento:

Nota:agregué resultados para varias inserciones individuales como referencia.

La inserción masiva de TVP y SqlBulkCopy funcionan casi igual cuando se insertan 500 registros o menos. Después de eso, SqlBulkCopy supera el enfoque de TVP. Con 1000 registros, es 2 veces más rápido. Con 10 000 registros, es aproximadamente 8 veces más rápido.

SqlBulkCopy está especializado para inserciones masivas, mientras que TVP es una opción de propósito general. Si necesita realizar inserciones masivas sencillas y de buen rendimiento, utilice SqlBulkCopy.