How to retrieve what was inserted by one out of two insert queries

I’m building an API method that takes in, among other things, a dictionary and attempts to insert the data into the database. The data essentially is split out in a parent/child type relationship that is enforced via foreign keys. The database structure is designed this way for specific reasons and cannot change.

I originally wrote this using Entity Framework, but performance testing revealed it was far too slow with large requests due to all of the querying and processing required. Sending all of the data to the database and allowing it to figure out which records should be inserted was much, much faster (we’re talking 20-30 minutes down to 20-30 seconds).

  • Questions about variables in stored procedures
  • Using SSIS variable (@) as part of UPDATE in `Execute Sql Server Task'?
  • SQL Server Iterate through CTE
  • How to edit a SQL Server XML data field with asp.net Dynamic Data
  • Error: Input string was not in a correct format - ASP.Net
  • SSIS Fuzzy Lookup for dates
  • Here’s my problem: Originally, I was simply returning the number of records that were inserted with ExecuteNonQuery. Easy, right? Now, I need to be able to find out which parent records had a child record successfully inserted. So, I’ve been trying to refactor this to facilitate it.

    For clarity, I’m not interested in which parent records were inserted – I’m only interested in which parent records had a new child record inserted referencing said parent. This way, I can inform the API caller which records were not inserted successfully by comparing with what the caller passed to the API. The best way I can see so far is to use the OUTPUT clause in the child INSERT query to get the ParentIDs that were inserted and store them in a table variable. Then I can just look up the IDs against the parent table and get the names for my comparison. But that necessitates using a reader, and since multiple SQL statements are involved, bad things are happening.

    The code as currently presented results in the following exceptions:

    The transaction operation cannot be performed because there are pending requests working on this transaction. This SqlTransaction has completed; it is no longer usable. Test method My.Long.Project.Name.UnitTest.UnitTestMethod threw exception: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

    While fixing these exceptions is valuable, I’m not as interested in solving them as I am in solving the actual problem. If there’s a different path I can take that is extremely fast and provides the output I need, then I’ll investigate it. Here’s my code. I’m hoping that what I intend to do is clear and any help/guidance/suggestions would be appreciated.

    using (Context dbContext = createDbInstance())
    {
        //Not happy about setting MultipleActiveResultSets
        string conn = dbContext.Database.Connection.ConnectionString + ";MultipleActiveResultSets=True";
    
        SqlCommand newInsertCmd = new SqlCommand {Connection = new SqlConnection(conn)};
    
        //Set up input variables here, including a TPV
    
        SqlDataReader reader;
        List<string> results = new List<string>();
    
        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 string 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"].ToString());
            }
    
            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();
        }
    }
    

  • Execute multiple SQL commands in one round trip
  • Troubleshooting Timeout SqlExceptions
  • SqlCommand.ExecuteReader transfers data on Close even without a single Read
  • ASP.NET MVC4 - ADO.NET - Saving a large number of files from ZipArchives to sql server
  • find number of open connection on database
  • Extract machine name from connection string
  • 2 Solutions collect form web for “How to retrieve what was inserted by one out of two insert queries”

    Wait, I just looked over it again and it all makes perfect sense! I don’t know if these few days have left me delirious or wise (is there even a difference?) but I’m virtually positive that adding usings will do the trick.

    Here’s the code, granted I of course haven’t tested or even compiled this so I might be off on a few details:

    using (Context dbContext = createDbInstance())
    {
        //Not happy about setting MultipleActiveResultSets
        string conn = dbContext.Database.Connection.ConnectionString + ";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<string> results = new List<string>();
    
        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 string 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"].ToString());
                    }
                }
                sqlTran.Commit();
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
    
                try
                {
                    sqlTran.Rollback();
                }
                catch (Exception exRollback)
                {
                    Debug.WriteLine(exRollback.Message);
                    throw;
                }
    
                throw;
            }
        }
    }
    

    Or if you’re looking for something that’s a little more, in my opinion at least, readable:

    using (Context dbContext = createDbInstance())
    {
    
        List<string> results = new List<string>();
    
        //Not happy about setting MultipleActiveResultSets
        string conn = dbContext.Database.Connection.ConnectionString + ";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"].ToString());
                           }
                       }
                    }
                    sqlTran.Commit();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.Message);
    
                    try
                    {
                        sqlTran.Rollback();
                    }
                    catch (Exception exRollback)
                    {
                        Debug.WriteLine(exRollback.Message);
                        throw;
                    }
    
                    throw;
                }
            }
        }
    }
    

    And for good measure, the embedded option:

    using (Context dbContext = createDbInstance())
    {
        //Not happy about setting MultipleActiveResultSets
        string conn = dbContext.Database.Connection.ConnectionString + ";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<string> results = new List<string>();
    
            //The two insert statements work just fine. The other junk here (including the OUTPUT clause) is brand new
            const string 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"].ToString());
                }
            }
        }
    }
    

    The

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

    error is quite possibly due to the ExecuteReaderAsync and the await. Try a regular ExecuteReader and no await. This should allow the .Net-initiated transaction to work.

    If that doesn’t fix it, it could be that you are not calling reader.Close(); in your finally block (and this really should be done to not have that orphaned resource). And actually, looking at the error message again, it could be that the reader.Close(); is needed just after the while but before the sqlTran.Commit();.

    However, since you have a single SqlCommand call there really is no need for a .Net-initiated transaction, right? This could all be handled cleanly in the SQL by structuring it as follows:

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

    If the first INSERT (into Parent) fails, then the second INSERT (into Child) will be skipped as control will immediately pass to the CATCH block.

    EDIT:
    I just came across the following in the MSDN documentation for the IDataReader interface that supports my contention that the open SqlDataReader is the culprit and that it needs to be closed before the Commit can be issued. In the “Remarks” section of the Read() method, it says:

    While the data reader is in use, the associated connection is busy
    serving the IDataReader. This is the case until Close is called.

    This should explain why “a prior attempt that did close the DataReader did not solve the problem” (paraphrase of statement made in a comment on the question) because most likely you were closing it in the finally clause which is too late given that the transaction is committed at the end of the try block.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.