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

Question

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

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

Answer 1

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.

Answer 2

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