Comment récupérer des valeurs d'identité générées par le serveur lors de l'utilisation de SqlBulkCopy

c# identity sqlbulkcopy sql-server

Question

Je sais que je peux effectuer une insertion en bloc dans ma table avec une colonne d'identité en ne spécifiant pas SqlBulkCopyOptions.KeepIdentity comme indiqué ici .

Ce que j'aimerais pouvoir faire, c'est obtenir les valeurs d'identité générées par le serveur et les mettre dans mon datatable, voire une liste. J'ai vu ce post, mais je veux que mon code soit général et je ne peux pas avoir de colonne de version dans toutes mes tables. Les suggestions sont très appréciées. Voici mon code:

public void BulkInsert(DataTable dataTable, string DestinationTbl, int batchSize)
{
    // Get the DataTable 
    DataTable dtInsertRows = dataTable;

    using (SqlBulkCopy sbc = new SqlBulkCopy(sConnectStr))
    {
        sbc.DestinationTableName = DestinationTbl;

        // Number of records to be processed in one go
        sbc.BatchSize = batchSize;

        // Add your column mappings here
        foreach (DataColumn dCol in dtInsertRows.Columns)
        {
            sbc.ColumnMappings.Add(dCol.ColumnName, dCol.ColumnName);
        }

        // Finally write to server
        sbc.WriteToServer(dtInsertRows);
    }
}

Réponse acceptée

Autant que je sache, vous ne pouvez pas.

Le seul moyen (que je sache) d'obtenir les valeurs du champ d'identité consiste à utiliser soit SCOPE_IDENTITY() lorsque vous insérez ligne par ligne; ou en utilisant l'approche OUTPUT lors de l'insertion d'un jeu entier.

L'approche la plus «simple» serait probablement de copier les enregistrements de la table avec SqlBulkCopy, puis de les récupérer ultérieurement. Le problème pourrait être qu’il pourrait être difficile de récupérer correctement (et rapidement) ces lignes du serveur. (Par exemple, il serait plutôt moche (et lent) d’avoir une clause WHERE avec IN (guid1, guid2, .., guid999998, guid999999) =)

Je suppose que les performances sont un problème ici car vous utilisez déjà SqlBulkCopy, donc je vous conseillerais d’opter pour l’approche OUTPUT , auquel cas vous aurez d’abord besoin d’une table intermédiaire pour enregistrer vos enregistrements. une sorte d'identifiant de lot (GUID?) permettant à plusieurs bandes de roulement de fonctionner côte à côte. Vous aurez besoin d'une procédure stockée pour INSERT <table> OUTPUT inserted.* SELECT les données de la table de transfert dans la table de destination réelle et nettoyez à nouveau la table de transfert. Le jeu d'enregistrements renvoyé à partir de ladite procédure correspondrait alors 1: 1 au jeu de données d'origine chargé de remplir la table de transfert, mais vous ne devez évidemment PAS compter sur sa commande. En d'autres termes: votre prochain défi consistera à faire correspondre les champs d'identité renvoyés aux enregistrements d' origine de votre application.

En y réfléchissant bien, je dirais que dans tous les cas - sauf l'approche rangée par rangée & SCOPY_IDENTITY (), qui va être lente comme un chien - vous aurez besoin (ou d'ajouter) d'une 'clé 'à vos données pour relier l'identifiant généré aux données d'origine = /


Réponse populaire

Vous pouvez faire une approche similaire à deroby décrite ci-dessus, mais au lieu de les récupérer via un WHERE IN (guid1, etc... correspondre aux lignes insérées en mémoire en fonction de leur ordre.

Je suggérerais donc d'ajouter une colonne à la table pour faire correspondre la ligne à une transaction SqlBulkCopy, puis procédez comme suit pour faire correspondre les ID générés à la collection de lignes en mémoire que vous venez d'insérer.

  • Créez un nouveau Guid et définissez cette valeur sur toutes les lignes du mappage de copie en bloc sur la nouvelle colonne

  • Exécuter la méthode WritToServer de l'objet BulkCopy

  • Récupérer toutes les lignes qui ont cette même clé

  • Parcourez cette liste qui sera dans l'ordre dans lequel elles ont été ajoutées, elles seront dans le même ordre que la collection de lignes en mémoire afin que vous sachiez ensuite l'id généré pour chaque élément.

Cela vous donnera de meilleures performances que de donner à chaque rangée une clé unique. Donc, après avoir inséré en bloc la table de données, vous pouvez faire quelque chose comme ceci (dans mon exemple, je vais avoir une liste d'objets à partir desquels je vais créer la table de données, puis y mapper les identifiants générés)

List<myObject> myCollection = new List<myObject>

Guid identifierKey = Guid.NewGuid();

//Do your bulk insert where all the rows inserted have the identifierKey
//set on the new column. In this example you would create a data table based
//off the myCollection object.

//Identifier is a column specifically for matching a group of rows to a sql  
//bulk copy command
var myAddedRows = myDbContext.DatastoreRows.AsNoTracking()
            .Where(d => d.Identifier == identiferKey)
            .ToList();


 for (int i = 0; i < myAddedRows.Count ; i++)
 {
    var savedRow = myAddedRows[i];
    var inMemoryRow = myCollection[i];

    int generatedId = savedRow.Id;

   //Now you know the generatedId for the in memory object you could set a
   // a property on it to store the value

   inMemoryRow.GeneratedId = generatedId;
 }


Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi