Como le enviamos una lista de valores al Sql server. Es algo que a menudo ocrre en desarrollo, que necesitamos enviar unos cuantos elementos a un procedure y tener que hacer muchas peticiones sobre el mismo procedure de 1 en 1, da un rendimiento pobre. Tvp mejora ese rendimiento.
Para empezar el laboratorio, hoy vamos a usar como no puede ser de otra manera, sql server, pero vamos a crear un proyecto de visual studio para consumir los datos. Yo voy a utilizar VS2012 y aunque desde 2008 me parece que son válidas todas las versiones.
Imagen de Gerd Altmann en Pixabay
Capa SQL Server
Creamos tres tablas. La de articulos, que es sobre la que trabajamos, y luego estadoArt y familia, por tener los datos de la tabla artículos bien normalizados.
-- Code 1.0
Create table dbo.Articulos
(
id int identity(1, 1)
, descripcion varchar(100)
, estado int
, familia int
);
Create table dbo.estadoArt
(
id int
primary key
, descripcion varchar(100)
);
Create table dbo.familia
(
id int
primary key
, descripcion varchar(100)
);
Insertamos un par de filas en estadoArt y familia.
-- Code 1.1
Insert into dbo.estadoArt
(id, descripcion)
values
(0, 'Baja'),
(1, 'Activo'),
(2, 'Baja Temporal');
Insert into dbo.familia
(id, descripcion)
values
(1,'Camisas'),
(2,'Camisetas'),
(3,'Pantalón');
Ahora vamos a crear un tipo de tabla definida por el usuario (Create Type), pero antes de hacerlo vamos a mirar las particularidades que tiene.
Create type dice: Crea un tipo de datos de alias o un tipo definido por el usuario en la base de datos actual de SQL Server o Azure SQL Database…..Luego menciona código CLR..
Por tanto si en la creación de los tipos te da un «zasca», por algo de clr enabled, puedes mirar este clr enabled (opción de configuración del servidor).
-- Code 1.2
Create Type dbo.articulosType As Table
(
descripcion varchar(100)
, esatdo int
, familia int
);
La simpleza para crear un tipo de dato tabla, hace casi que no se tenga, ni que comentar.
Create type ……. As Table
Simplemente genial
Ahora vamos a crear un procedimiento almacenado que Inserte en artículos, utilizando este tipo.
-- Code 1.3
Create Procedure dbo.ArticulosInsertar
(
@paramTable dbo.articulosType Readonly
)
As
Begin
Insert into dbo.Articulos
( descripcion
, estado
, familia)
Select p.descripcion
, p.esatdo
, p.familia
from @PARAMTABLE AS p;
End
Aquí los primeros matices. Pasamos un parámetro al procedure llamado @paramTable y es de tipo dbo.ArticulosType. Nuestro tipo de dato, pero le ponemos como restricción ReadOnly. Esto es un requisito que nos impone el paso de parámetros con valores de tabla.
C#
Creo un nuevo proyecto.
Y programamos todo el código por simplicidad en la clase Program.
using System.Data;
using System.Data.SqlClient;
namespace ConsoleTvp
{
class Program
{
static void Main(string[] args)
{
// Creamos un dataTable con la misma estructura
// del tipo de dato definido en la capa SQL
DataTable dtArts = new DataTable();
dtArts.Columns.Add(new DataColumn("descripcion",
typeof(string)));
dtArts.Columns.Add(new DataColumn("estado",
typeof(int)));
dtArts.Columns.Add(new DataColumn("familia",
typeof(int)));
// añadimos al dataTable varias filas
dtArts.Rows.Add("Camisa manga corta" , 0, 1);
dtArts.Rows.Add("Camiseta con logo" , 0, 2);
dtArts.Rows.Add("Pantalón corto" , 0, 3);
dtArts.Rows.Add("Camisa manga larga" , 2, 1);
dtArts.Rows.Add("Camisa sin mangas" , 0, 1);
dtArts.Rows.Add("Camiseta con dibujos", 0, 2);
dtArts.Rows.Add("Camisa simple" , 1, 2);
dtArts.Rows.Add("Pantalón largo" , 0, 3);
dtArts.Rows.Add("Pantalón de pana" , 0, 3);
//Ahora con la estructura de datos preparada.
// Declaramos la cadena de conexión al servidor SQL
string connectionString =
@"data source=aquí tu cadena de conexión";
using (var connection = new SqlConnection(connectionString))
{
// abrimos un command contra el proc creado de inserción
using (var cmd =
new SqlCommand("dbo.ArticulosInsertar", connection))
{
connection.Open();
// command de tipo stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// Creamos un parámetro. Lo agregamos al command
//con el nombre del parámetro del procedure
// y le adjuntamos el dataTable con el contenido
SqlParameter tvpParam =
cmd.Parameters.AddWithValue("@paramTable", dtArts);
// Indicamos que el parámetro es de tipo structured
tvpParam.SqlDbType = SqlDbType.Structured;
// Ejecutamos.
cmd.ExecuteNonQuery();
}
}
}
}
}
Una vez finalizada la ejecución:
-- Code 2.0
select a.id
, a.descripcion
, e.descripcion as estado
, f.descripcion as familia
from dbo.articulos a
inner join dbo.estadoArt e
on a.estado = e.id
inner join dbo.familia f
on a.familia = f.id
Tenemos nuestros artículos insertados correctamente.
La pregunta que hay que tener en cuenta para esto, es el porqué utilizarlos, y la respuesta nos la facilita la documentación oficial.
Ventajas
Un parámetro con valores de tabla está incluido en el ámbito de procedimiento almacenado, función o texto Transact-SQL dinámico, exactamente igual que los demás parámetros. Del mismo modo, una variable de tipo de tabla tiene el mismo ámbito que cualquier otra variable local creada mediante una instrucción DECLARE. Puede declarar variables con valores de tabla en instrucciones Transact-SQL dinámicas y pasar estas variables como parámetros con valores de tabla a procedimientos almacenados y funciones.
Los parámetros con valores de tabla proporcionan más flexibilidad y, en algunos casos, un rendimiento mayor que las tablas temporales u otros medios para pasar una lista de parámetros. Los parámetros con valores de tabla proporcionan las ventajas siguientes:
- No adquieren bloqueos para el rellenado inicial de datos de un cliente.
- Proporcionan un modelo de programación simple.
- Permiten la inclusión de lógica comercial compleja en una rutina única.
- Reducen los viajes de ida y vuelta al servidor.
- Pueden tener una estructura de tabla de cardinalidad diferente.
- etc
Parámetros con valores de tabla frente a operaciones BULK INSERT
El uso de parámetros con valores de tabla es comparable a otras formas de uso de variables basadas en conjuntos; sin embargo, el uso de parámetros con valores de tabla puede ser con frecuencia más rápido para grandes conjuntos de datos. Si se comparan con operaciones masivas que tienen un costo de inicio mayor que los parámetros con valores de tabla, el comportamiento de los parámetros con valores de tabla es excelente cuando se insertan menos de 1.000 filas.
Los parámetros con valores de tabla que se vuelven a utilizar se benefician del almacenamiento en caché de tablas temporales. Este almacenamiento en memoria caché de tablas proporciona una escalabilidad mejor que en el caso de operaciones BULK INSERT equivalentes. Si se usan pequeñas operaciones de inserción de filas, se puede conseguir una ligera mejora del rendimiento utilizando listas de parámetros o instrucciones por lotes en lugar de operaciones BULK INSERT o parámetros con valores de tabla. Sin embargo, estos métodos son menos apropiados para programar, y el rendimiento disminuye rápidamente cuando aumentan las filas.
Los parámetros con valores de tabla se comportan tan bien o mejor que una implementación de matriz de parámetros equivalente.
🙂
Datos y demás 