Code Changes Second Opinion.

[rxelizondo]

Hello,

I am currently in charge of supporting a very mission critical application developed using the CSLA. This application is so critical that to be honest, I am becoming more and more paranoid about any updates I make to the application that I consider to be "core" updates. No matter how much testing I do, I still loose sleep over those types of changes. That said, I was hoping to get a second option on some changes we are introducing to get some peace of mind.

Looking at the sample code below:

private void Child_Insert(SomeObject obj)
{
    using (var ctx = GetTheContext())
    {
        const string commandText = @"
              INSERT INTO SomeTable (Column1, Column2) VALUES (@Val1, @Val2);
              SELECT AutoGeneratedIdentityValue FROM SomeTable WHERE AutoGeneratedIdentityValue = SCOPE_IDENTITY();
              INSERT INTO CRUDTracking(TrackedTableUniqueIdentifier, CRUD) VALUES (SCOPE_IDENTITY(), 'C')
         ";

        using (var command = new SqlCommand(commandText, ctx.Connection))
        {
            command.Parameters.AddWithValue("@Val1", obj.Val1);
            command.Parameters.AddWithValue("@Val2", obj.Val2);

            using (var reader = new SafeDataReader(command.ExecuteReader()))
            {
                reader.Read();
                LoadProperty(TheObjectID, reader.GetInt32("AutoGeneratedIdentityValue"));
                reader.NextResult(); // Make sure we throw any errors that may have been produced by the last insert query.                    
            }
        }
    }
}

Do you see any potential issues with the reliability of the code above? Specifically, do you see a problem (some corner case issue) that may be caused by the last insert?

Thanks.

[hurcane]

Is this all running as part of a transaction? Then you're probably OK. This is more of a Sql question than a Csla question, but I would bet most of us using Csla are also working with Csla databases.

I have a different technique to suggest. Declare a table variable in your statement and use the OUTPUT clause to insert AutoGeneratedIdentityValue into the table variable. You can then use the table variable in your select and in your second insert statement. With this technique, you can handle scenarios where multiple rows are being generated and you might want to record the identity of all the records that were inserted.

Here's a sample syntax I tested out on our audit log table, which has column LogId as an identity column.

declare @identities table (id int)
insert into tblsaAuditLog (OldValue, NewValue) 
output inserted.LogId into @identities
values (1, 0)
select id from @identities
/* 
-- Code I could not test...
INSERT INTO CRUDTracking(TrackedTableUniqueIdentifier, CRUD)
select id, 'C'
from @identities
*/

All of that can go into your CommandText string.