Paramètres de type table

Télécharger ADO.NET

Les paramètres de type table offrent un moyen simple de transférer plusieurs lignes de données d’une application cliente vers SQL Server. Ils n’ont pas besoin de plusieurs allers-retours ou d’une logique spéciale côté serveur pour traiter les données. Vous pouvez utiliser des paramètres table pour encapsuler des lignes de données dans une application cliente et envoyer les données au serveur dans une commande paramétrable unique. Les lignes de données entrantes sont stockées dans une variable de table que vous pouvez ensuite utiliser à l’aide de Transact-SQL.

Les valeurs de colonne dans les paramètres table sont accessibles à l’aide d’instructions Transact-SQL SELECT standard. Les paramètres table sont fortement typés et leur structure est validée automatiquement. La taille des paramètres de type table est limitée uniquement par la mémoire du serveur.

Remarque

Vous ne pouvez pas retourner des données dans un paramètre de table. car il prennent uniquement des valeurs d’entrée ; le mot clé OUTPUT n’est pas pris en charge.

Pour plus d’informations sur les paramètres de type table, consultez les ressources suivantes.

Ressource	Description
Utiliser les paramètres de table (Moteur de base de données)	Décrit comment créer et utiliser des paramètres de type table.
Création d'un type de table défini par l'utilisateur	Décrit les types de table définis par l’utilisateur utilisés pour déclarer des paramètres de type table.
Types de tables définis par l'utilisateur	Décrit les types de table définis par l’utilisateur utilisés pour déclarer des paramètres de type table.

Passage de plusieurs lignes dans les versions précédentes de SQL Server

Avant l’introduction des paramètres table, les options permettant de passer plusieurs lignes de données à une procédure stockée ou à une commande SQL paramétrisée étaient limitées. Un développeur peut choisir parmi les options suivantes pour transmettre plusieurs lignes au serveur :

Utilisez une série de paramètres individuels pour représenter les valeurs dans plusieurs colonnes et lignes de données. La quantité de données transmissibles selon cette méthode est limitée par le nombre de paramètres autorisés. Les procédures SQL Server peuvent contenir jusqu’à 2 100 paramètres. Une logique côté serveur est requise pour assembler ces différentes valeurs dans une variable de table ou une table temporaire à des fins de traitement.
Regroupez plusieurs valeurs de données dans des chaînes délimitées ou des documents XML, puis transmettez ces valeurs de texte à une procédure ou une instruction. Cette méthode implique que la procédure ou l’instruction comprenne la logique de validation des structures de données et de dissociation des valeurs.
Créez une série d’instructions SQL pour les modifications de données qui affectent plusieurs lignes, telles que celles créées en appelant la méthode Update d’un SqlDataAdapter. Ces modifications peuvent être envoyées individuellement au serveur ou regroupées par lots. Toutefois, même lorsqu’elles sont soumises dans des lots contenant plusieurs instructions, chacune des instructions est exécutée séparément sur le serveur.
Utilisez le programme utilitaire bcp ou l’objet SqlBulkCopy pour charger de nombreuses lignes de données dans une table. Bien que cette technique soit très efficace, elle ne prend pas en charge le traitement côté serveur, sauf si les données sont chargées dans une table temporaire ou une variable de table.

Création de types de paramètres de table

Les paramètres de table sont basés sur des structures de table fortement typées, définies à l’aide d’instructions Transact-SQL CREATE TYPE. Vous devez créer un type de table et définir la structure dans SQL Server avant de pouvoir utiliser des paramètres table dans vos applications clientes. Pour plus d’informations sur la création de types de tables, consultez Utiliser les paramètres de table (moteur de base de données).

L’instruction suivante crée un type table nommé CategoryTableType qui se compose de colonnes CategoryID et CategoryName :

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

Après avoir créé un type table, vous pouvez déclarer des paramètres table basés sur ce type. Le fragment Transact-SQL suivant montre comment déclarer un paramètre table dans une définition de procédure stockée. Le mot-clé READONLY est requis pour déclarer un paramètre de type table.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modification des données avec des paramètres de table (Transact-SQL)

Les paramètres de type table peuvent être utilisés dans des modifications de données ensemblistes qui affectent plusieurs lignes en exécutant une seule instruction. Par exemple, vous pouvez sélectionner toutes les lignes d’un paramètre table et les insérer dans une table de base de données, ou créer une instruction de mise à jour en joignant un paramètre table à la table à mettre à jour.

L’instruction Transact-SQL UPDATE suivante montre comment utiliser un paramètre table en le joignant à la table Categories. Si vous utilisez un paramètre table avec un JOIN dans une clause FROM, vous devez également créer un alias pour celui-ci (ici, le paramètre table a pour alias « ec ») :

UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;

Cet exemple Transact-SQL montre comment sélectionner des lignes depuis un paramètre de table afin d’effectuer une opération INSERT en une seule opération ensembliste.

INSERT INTO dbo.Categories (CategoryID, CategoryName)
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Limitations des paramètres de type table

Les paramètres de type table comportent plusieurs limites :

Vous ne pouvez pas passer de paramètres table aux fonctions CLR définies par l’utilisateur.
Les paramètres de type table ne peuvent être indexés que afin de prendre en charge les contraintes UNIQUE et PRIMARY KEY. SQL Server ne conserve pas de statistiques sur les paramètres de type table.
Les paramètres de type table sont en lecture seule dans le code Transact-SQL. Vous ne pouvez pas mettre à jour les valeurs de colonne dans les lignes d’un paramètre table, ni insérer ou supprimer des lignes. Pour modifier les données passées à une procédure stockée ou à une instruction paramétrisée dans un paramètre table, vous devez les insérer dans une table temporaire ou une variable de table.
Vous ne pouvez pas utiliser les instructions ALTER TABLE pour modifier la définition des paramètres de type table.

Exemple de configuration de SqlParameter

Microsoft.Data.SqlClient prend en charge le remplissage des paramètres table à partir d’objets DataTable, DbDataReader ou IEnumerable<T> \ SqlDataRecord. Spécifiez un nom de type pour le paramètre de table à l’aide de la propriété TypeName d’un SqlParameter. Le TypeName doit correspondre au nom d’un type compatible précédemment créé sur le serveur. Le fragment de code suivant montre comment configurer SqlParameter pour insérer des données.

Dans l’exemple ci-dessous, la variable addedCategories contient un DataTable. Pour voir comment la variable est remplie, consultez les exemples de la section suivante, Passage d'un paramètre table à une procédure stockée.

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

Vous pouvez également utiliser n’importe quel objet dérivé de l’objet DbDataReader pour transmettre en continu des lignes de données à un paramètre table, tel qu’indiqué dans ce fragment :

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);
tvpParam.SqlDbType = SqlDbType.Structured;

Passage d’un paramètre table à une procédure stockée

Cet exemple montre comment passer des données de paramètre table à une procédure stockée. Le code extrait des lignes ajoutées dans une nouvelle DataTable à l’aide de la méthode GetChanges. Le code définit ensuite une SqlCommand, en affectant la valeur CommandType à la propriété StoredProcedure. SqlParameter est rempli à l’aide de la méthode AddWithValue et le SqlDbType est défini sur Structured. Le SqlCommand est ensuite exécuté en utilisant la méthode ExecuteNonQuery.

// Assumes connection is an open SqlConnection object.
using (connection)
{
    // Create a DataTable with the modified rows.
    DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

    // Configure the SqlCommand and SqlParameter.
    SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
    tvpParam.SqlDbType = SqlDbType.Structured;

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

Passage d’un paramètre table à une instruction SQL paramétrable

L’exemple suivant montre comment insérer des données dans le dbo. Table Catégories à l’aide d’une INSERT instruction avec une sous-requête SELECT qui a un paramètre table comme source de données. Lors du passage d’un paramètre table à une instruction SQL paramétrable, vous devez spécifier un nom de type pour le paramètre table à l’aide de la nouvelle propriété TypeName d’un SqlParameter. Ce TypeName doit correspondre au nom d’un type compatible précédemment créé sur le serveur. Le code de cet exemple utilise la propriété TypeName pour référencer la structure de type définie dans dbo.CategoryTableType.

Remarque

Si vous fournissez une valeur pour une colonne d’identité dans un paramètre table, vous devez émettre l’instruction SET IDENTITY_INSERT pour la session.

// Assumes connection is an open SqlConnection.
using (connection)
{
    // Create a DataTable with the modified rows.
    DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);

    // Define the INSERT-SELECT statement.
    string sqlInsert =
        "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
        + " SELECT nc.CategoryID, nc.CategoryName"
        + " FROM @tvpNewCategories AS nc;"

    // Configure the command and parameter.
    SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);
    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.CategoryTableType";

    // Execute the command.
    insertCommand.ExecuteNonQuery();
}

Lecture en continu de lignes avec un DataReader

Vous pouvez également utiliser n’importe quel objet dérivé de DbDataReader pour diffuser en continu des lignes de données vers un paramètre de type table. Le fragment de code suivant montre comment récupérer des données d’une base de données Oracle à l’aide d’une OracleCommand et d’un OracleDataReader. Le code configure ensuite une SqlCommand pour appeler une procédure stockée avec un seul paramètre d’entrée. La propriété SqlDbType du SqlParameter a la valeur Structured. AddWithValue transmet le jeu de résultats OracleDataReader à la procédure stockée en tant que paramètre table.

// Assumes connection is an open SqlConnection.
// Retrieve data from Oracle.
OracleCommand selectCommand = new OracleCommand(
    "Select CategoryID, CategoryName FROM Categories;",
    oracleConnection);
OracleDataReader oracleReader = selectCommand.ExecuteReader(
    CommandBehavior.CloseConnection);

// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam =
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", oracleReader);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();

Opérations de données SQL Server dans ADO.NET

Commentaires

Cette page a-t-elle été utile ?

Dernière mise à jour : 2026-10-08