Comment récupérer ce qui a été inséré par une requête d'insertion sur deux

Je construis une méthode API qui inclut, entre autres, un dictionary et tente d'insert datatables dans la database. Les données sont essentiellement divisées dans une relation de type parent / enfant qui est appliquée via des foreign keys. La structure de la database est conçue de cette manière pour des raisons spécifiques et ne peut pas changer.

À l'origine, j'ai écrit ceci en utilisant Entity Framework, mais les tests de performance ont révélé qu'il était beaucoup trop lent pour les grosses requêtes en raison de l'set de l'interrogation et du traitement requirejs. Envoyer toutes datatables à la database et lui permettre de déterminer quels loggings devraient être insérés était beaucoup, beaucoup plus rapide (nous parlons 20-30 minutes jusqu'à 20-30 secondes).

Voici mon problème: À l'origine, je returnnais simplement le nombre d'loggings qui ont été insérés avec ExecuteNonQuery. Facile, non? Maintenant, je dois être en mesure de savoir quels loggings parents ont inséré un logging enfant. Donc, j'ai essayé de refactoriser cela pour le faciliter.

Pour plus de clarté, je ne suis pas intéressé par les loggings parents qui ont été insérés – je ne m'intéresse qu'aux dossiers parent ayant un nouvel logging enfant inséré référençant ledit parent. De cette façon, je peux informer l'appelant API quels loggings n'ont pas été insérés avec succès en comparant avec ce que l'appelant a transmis à l'API. La meilleure façon de voir jusqu'à présent est d'utiliser la clause OUTPUT dans la requête INSERT enfant pour get les ParentID qui ont été insérés et les stocker dans une variable de table. Ensuite, je peux juste chercher les ID par rapport à la table parent et get les noms pour ma comparaison. Mais cela nécessite l'utilisation d'un lecteur, et comme plusieurs instructions SQL sont impliquées, de mauvaises choses se produisent.

Le code tel que présenté actuellement entraîne les exceptions suivantes:

L'opération de transaction ne peut pas être effectuée car des requests en attente sont en cours d'exécution sur cette transaction. Cette SqlTransaction est terminée; il n'est plus utilisable. Méthode de test My.Long.Project.Name.UnitTest.UnitTestMethod a lancé l'exception: System.InvalidOperationException: cette opération SqlTransaction est terminée; il n'est plus utilisable.

Bien que la résolution de ces exceptions soit utile, je ne suis pas aussi intéressé à les résoudre que je le suis pour résoudre le problème réel. S'il y a un path différent que je peux prendre qui est extrêmement rapide et qui fournit la sortie dont j'ai besoin, alors je l'étudierai. Voici mon code. J'espère que ce que j'ai l'intention de faire est clair et que toute aide / orientation / suggestion serait appréciée.

 using (Context dbContext = createDbInstance()) { //Not happy about setting MultipleActiveResultSets ssortingng conn = dbContext.Database.Connection.ConnectionSsortingng + ";MultipleActiveResultSets=True"; SqlCommand newInsertCmd = new SqlCommand {Connection = new SqlConnection(conn)}; //Set up input variables here, including a TPV SqlDataReader reader; List<ssortingng> results = new List<ssortingng>(); newInsertCmd.Connection.Open(); SqlTransaction sqlTran = newInsertCmd.Connection.BeginTransaction(); newInsertCmd.Transaction = sqlTran; try { //The two insert statements work just fine. The other junk here (including the OUTPUT clause) is brand new const ssortingng qryInsertTrans = @"INSERT INTO Parent ([Name], [CreateDate]) SELECT n.Name, GETUTCDATE() [CreateDate] FROM @NewRecords n LEFT JOIN Parent p ON n.Name = p.Name WHERE p.ParentID IS NULL; DECLARE @OutputVar table( ParentID bigint NOT NULL ); INSERT INTO Child ([ParentID], [SomeText], [CreateDate]) OUTPUT INSERTED.ParentID INTO @OutputVar SELECT p.ParentID, n.Text, GETUTCDATE() [CreateDate] FROM @NewRecords n INNER JOIN Parent p ON n.Name = p.Name LEFT JOIN Child c ON p.ParentID = c.ParentID AND c.SomeCol = @SomeVal WHERE c.ChildID IS NULL; SELECT p.Name FROM Parent p INNER JOIN @OutputVar o ON p.ParentID = o.ParentID"; newInsertCmd.CommandText = qryInsertTrans; reader = await newInsertCmd.ExecuteReaderAsync(); while (reader.Read()) { results.Add(reader["Name"].ToSsortingng()); } sqlTran.Commit(); } catch (Exception ex) { Debug.WriteLine(ex.Message); try { sqlTran.Rollback(); } catch (Exception exRollback) { Debug.WriteLine(exRollback.Message); throw; } throw; } finally { newInsertCmd.Connection.Close(); } }

Attends, je l'ai regardé à nouveau et tout est parfaitement logique! Je ne sais pas si ces quelques jours m'ont laissé délirant ou sage (y a-t-il même une différence?) Mais je suis quasiment certain que l'ajout d'utilisations fera l'affaire.

Voici le code, bien sûr que je n'ai pas testé ou même compilé cela, donc je pourrais être sur quelques détails:

 using (Context dbContext = createDbInstance()) { //Not happy about setting MultipleActiveResultSets ssortingng conn = dbContext.Database.Connection.ConnectionSsortingng + ";MultipleActiveResultSets=True"; using (var connection = new SqlConnection(conn)) using (var newInsertCmd = new SqlCommand(connection)) { newInsertCmd.Connection.Open(); //Set up input variables here, including a TPV List<ssortingng> results = new List<ssortingng>(); using(SqlTransaction sqlTran = newInsertCmd.Connection.BeginTransaction()) { newInsertCmd.Transaction = sqlTran; try { //The two insert statements work just fine. The other junk here (including the OUTPUT clause) is brand new const ssortingng qryInsertTrans = @"INSERT INTO Parent ([Name], [CreateDate]) SELECT n.Name, GETUTCDATE() [CreateDate] FROM @NewRecords n LEFT JOIN Parent p ON n.Name = p.Name WHERE p.ParentID IS NULL; DECLARE @OutputVar table( ParentID bigint NOT NULL ); INSERT INTO Child ([ParentID], [SomeText], [CreateDate]) OUTPUT INSERTED.ParentID INTO @OutputVar SELECT p.ParentID, n.Text, GETUTCDATE() [CreateDate] FROM @NewRecords n INNER JOIN Parent p ON n.Name = p.Name LEFT JOIN Child c ON p.ParentID = c.ParentID AND c.SomeCol = @SomeVal WHERE c.ChildID IS NULL; SELECT p.Name FROM Parent p INNER JOIN @OutputVar o ON p.ParentID = o.ParentID"; newInsertCmd.CommandText = qryInsertTrans; using(var reader = await newInsertCmd.ExecuteReaderAsync()) { while (reader.Read()) { results.Add(reader["Name"].ToSsortingng()); } } sqlTran.Commit(); } catch (Exception ex) { Debug.WriteLine(ex.Message); try { sqlTran.Rollback(); } catch (Exception exRollback) { Debug.WriteLine(exRollback.Message); throw; } throw; } } }

Ou si vous cherchez quelque chose qui est un peu plus, à mon avis au less, lisible:

 using (Context dbContext = createDbInstance()) { List<ssortingng> results = new List<ssortingng>(); //Not happy about setting MultipleActiveResultSets ssortingng conn = dbContext.Database.Connection.ConnectionSsortingng + ";MultipleActiveResultSets=True"; using (var connection = new SqlConnection(conn)) { newInsertCmd.Connection.Open(); using(SqlTransaction sqlTran = newInsertCmd.Connection.BeginTransaction()) { try { using (var parentInsert = new SqlCommand(connection)) { parentInsert .Transaction = sqlTran; //Set up input variables here, including a TPV newInsertCmd.CommandText = @"INSERT INTO Parent ([Name], [CreateDate]) SELECT n.Name, GETUTCDATE() [CreateDate] FROM @NewRecords n LEFT JOIN Parent p ON n.Name = p.Name WHERE p.ParentID IS NULL;"; await newInsertCmd.ExecuteNonQueryAsync(); } using (var childInsert = new SqlCommand(connection)) { childInsert.Transaction = sqlTran; //Set up input variables here, including a TPV newInsertCmd.CommandText = @"DECLARE @OutputVar table( ParentID bigint NOT NULL ); INSERT INTO Child ([ParentID], [SomeText], [CreateDate]) OUTPUT INSERTED.ParentID INTO @OutputVar SELECT p.ParentID, n.Text, GETUTCDATE() [CreateDate] FROM NewRecords n INNER JOIN Parent p ON n.Name = p.Name LEFT JOIN Child c ON p.ParentID = c.ParentID AND c.SomeCol = @SomeVal WHERE c.ChildID IS NULL; SELECT p.Name FROM Parent p INNER JOIN @OutputVar o ON p.ParentID = o.ParentID"; using(var reader = await childInsert.ExecuteReaderAsync()) { while (reader.Read()) { results.Add(reader["Name"].ToSsortingng()); } } } sqlTran.Commit(); } catch (Exception ex) { Debug.WriteLine(ex.Message); try { sqlTran.Rollback(); } catch (Exception exRollback) { Debug.WriteLine(exRollback.Message); throw; } throw; } } } }

Et pour faire bonne mesure, l'option embeddede:

 using (Context dbContext = createDbInstance()) { //Not happy about setting MultipleActiveResultSets ssortingng conn = dbContext.Database.Connection.ConnectionSsortingng + ";MultipleActiveResultSets=True"; using (var connection = new SqlConnection(conn)) using (var newInsertCmd = new SqlCommand(connection)) { newInsertCmd.Connection.Open(); //Set up input variables here, including a TPV List<ssortingng> results = new List<ssortingng>(); //The two insert statements work just fine. The other junk here (including the OUTPUT clause) is brand new const ssortingng qryInsertTrans = @"BEGIN TRY BEGIN TRANSACTION; INSERT INTO Parent ([Name], [CreateDate]) SELECT n.Name, GETUTCDATE() [CreateDate] FROM @NewRecords n LEFT JOIN Parent p ON n.Name = p.Name WHERE p.ParentID IS NULL; DECLARE @OutputVar table( ParentID bigint NOT NULL ); INSERT INTO Child ([ParentID], [SomeText], [CreateDate]) OUTPUT INSERTED.ParentID INTO @OutputVar SELECT p.ParentID, n.Text, GETUTCDATE() [CreateDate] FROM @NewRecords n INNER JOIN Parent p ON n.Name = p.Name LEFT JOIN Child c ON p.ParentID = c.ParentID AND c.SomeCol = @SomeVal WHERE c.ChildID IS NULL; SELECT p.Name FROM Parent p INNER JOIN @OutputVar o ON p.ParentID = o.ParentID COMMIT TRAN; END TRY BEGIN CATCH ROLLBACK TRAN; THROW; END CATCH;"; newInsertCmd.CommandText = qryInsertTrans; using(var reader = await newInsertCmd.ExecuteReaderAsync()) { while (reader.Read()) { results.Add(reader["Name"].ToSsortingng()); } } } }

 The transaction operation cannot be performed because there are pending requests working on this transaction. This SqlTransaction has completed; it is no longer usable.

erreur est très probablement due à l' ExecuteReaderAsync et l' await . Essayez un ExecuteReader régulier et pas d' await . Cela devrait permettre à la transaction initiée par .Net de fonctionner.

Si cela ne le résout pas, il se peut que vous reader.Close(); pas reader.Close(); dans votre bloc finally (et cela devrait être fait pour ne pas avoir cette ressource orpheline). Et en fait, en regardant à nouveau le message d'erreur, il se pourrait que le reader.Close(); est nécessaire juste après, mais avant le sqlTran.Commit(); .

Cependant, puisque vous avez un seul appel SqlCommand, il n'est pas nécessaire d'effectuer une transaction initiée par .Net, n'est-ce pas? Tout ceci pourrait être géré correctement dans le SQL en le structurant comme suit:

 BEGIN TRY BEGIN TRANSACTION; <your code> COMMIT TRAN; END TRY BEGIN CATCH ROLLBACK TRAN; THROW; END CATCH;

Si le premier INSERT (dans Parent) échoue, alors le second INSERT (dans Child) sera ignoré car le contrôle passera immédiatement au bloc CATCH.

MODIFIER:
Je viens de find ce qui suit dans la documentation MSDN pour l'interface IDataReader qui supporte mon affirmation que le SqlDataReader ouvert est le coupable et qu'il doit être fermé avant que la validation puisse être émise. Dans la section "Remarques" de la méthode Read () , il est dit:

Pendant l'utilisation du lecteur de données, la connection associée est occupée par IDataReader. C'est le cas jusqu'à ce que Close soit appelé.

Cela devrait expliquer pourquoi "une tentative antérieure qui a fermé le DataReader n'a pas résolu le problème" (paraphrase de déclaration faite dans un commentaire sur la question) parce que très probablement vous l'avez fermé dans la clause finally qui est trop tardive est engagé à la fin du bloc try .