SqlTransaction and Scope_Identity or another way?

[MrMancunian]

Ok, I'm stuck on a design problem and I need some feedback how to go around it.

CASE:

Pathologists can request stains on certain tissues. It's possible to request more than one stain a time. The program where they make these requests (which is third-party software) creates an XML-file for one stain. So, if two stains are requested on one piece of tissue, it will create two XML-files. The program I'm writing needs to parse those XML-files, merge the stains that are requested on one piece of tissue by one person at the same moment and then insert that as one Request in the database. The database is MS SQL. It consists of 30 tables and isn't very exciting. When the request has been inserted, i use the identifier of that request in another table to create a StainID, which I use to identify the stains in their own table (each stain has it's own table, as they have different properties). After the XML-files have been parsed, merged and inserted into the database, I want to rename the files to .xml.bak. However, before this is done, I need to make sure that the request has been entered into the database correctly.

So, basically, the process should be something like this:

1. Pathologist requests stains on tissue
2. Third-party-software creates XML-files for each stain
3. XML-files are parsed and then merged, based on the request
4. Data is inserted in database
5. XML-files are renamed to *.xml.bak

PROBLEM:

I need to make sure that a request has been inserted in the database and all stains are present. If not, the XML-file has to be parsed again. I was thinking of using a SqlTransaction to make sure that all database events are handled, before the XML-files get renamed. However, there is one big issue. When I insert a new request in the database, I want the identity to be returned, so I can use that in the insert queries for the stains.

strCommando0 = "INSERT INTO tblAangevraagdMoleculair (AanvraagID, MoleculairID, FixatieID) VALUES (" & intAanvraagID & ", " & intMoleculairID & ", " & intFixatieID & "); SELECT SCOPE_IDENTITY();"
Dim command0 As New SqlClient.SqlCommand(strCommando0, cnnKaSuS)
daMIPAanvraag.InsertCommand = command0
 intNieuwBepalingID = daMIPAanvraag.InsertCommand.ExecuteScalar()
 
strCommando = "INSERT INTO tblERBB2PerBepaling (BepalingID, B1_P3, B1_P9, B1_P21, B2_P3, B2_P9, B2_P21, GW_P3, GW_P9, GW_P21) VALUES (" & intNieuwBepalingID & ", '" & strP3B1 & "', '" & strP9B1 & "', '" & strP21B1 & "', '" & strP3B2 & "', '" & strP9B2 & "', '" & strP21B2 & "', '" & strP3GW & "', '" & strP9GW & "', '" & strP21GW & "');"
Dim command6 As New SqlClient.SqlCommand(strCommando, cnnKaSuS)
daMIPERBB2Updaten.InsertCommand = command6
daMIPERBB2Updaten.InsertCommand.ExecuteNonQuery()

As you can see, I need the Scope_Identity for further referencing in other tables. The main problem now is that when I use the SqlTransaction, it won't return any value until the transaction is committed.

Does anyone have an idea how to solve this? I'm not looking for code or a complete solution, but I think it's a design issue.

Thanks,

Steven

[JosAH]

Would it be an option to create (yet) another table where you store the names of the xml files that have successfully completed processing? A trigger could rename the xml file(s) and remove the particular row(s) from that table again.

kind regards,

Jos

[MrMancunian]

Hi Jos,

Thanks for your solution. Storing the names in a separate table will work fine. I also found out that the SqlTransaction does return a Identity, even if the transaction is cancelled later :-)

Steven

[capttaz]

I would recommend using stored procs to put your transaction in. Use the SQL 2005 try catch block and return the indentity in the try along with the commit. Obviously rollback in the catch. This way you can even make a second attempt to insert a record right in the sp.