Paramètres table

  • Article

Télécharger ADO.NET

Les paramètres table offrent un moyen simple de marshaler plusieurs lignes de données d’une application cliente sur 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 table est limitée uniquement par la mémoire du serveur.

Notes

Il n’est pas possible de retourner des données dans un paramètre 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 table, consultez les ressources suivantes.

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

Transmettre 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éer des types de paramètres table

Les paramètres table sont basés sur des structures de table fortement typées qui sont 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 table, consultez Utiliser des paramètres 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 nécessaire pour déclarer un paramètre table.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

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

Les paramètres table peuvent être utilisés dans des modifications de données par jeux qui affectent plusieurs lignes avec 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 d’un paramètre table pour effectuer une insertion (INSERT) dans une opération basée sur un jeu unique.

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

Limitations des paramètres table

Les paramètres table présentent plusieurs limitations :

  • Vous ne pouvez pas passer de paramètres table aux fonctions CLR définies par l’utilisateur.

  • Les paramètres table ne peuvent être indexés que pour prendre en charge les contraintes UNIQUE et PRIMARY KEY. SQL Server ne gère pas les statistiques de paramètres table.

  • Les paramètres 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 conception des paramètres 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 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 définissant la propriété CommandType sur la valeur StoredProcedure. SqlParameter est rempli à l’aide de la méthode AddWithValue et le SqlDbType est défini sur Structured. La SqlCommand est ensuite exécutée à l’aide de 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 la table dbo.Categories à l’aide d’une instruction INSERT 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.

Notes

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();
}

Diffusion en continu de lignes avec un DataReader

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. 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();

Étapes suivantes