Transactions and Scope_Identity

[Joe Fallon]

I use code like this to Insert a row into a table which has an Identity.
Notice that a running transaction is passed in to the method as the variable
tr. The insert command runs and then next command is SELECT
Scope_Identity().

Protected Overridable Sub InsertData(ByVal tr As IDbTransaction)
DAL.ExecuteNonQuery(tr, CommandType.Text, SQL.Insert(mName, ...))

'retrieve the newly generated Identity and update the PK in the BO so
the child records will save correctly.
mIdentitykey = CDec(DAL.ExecuteScalar(tr, CommandType.Text, "SELECT
Scope_Identity()"))
End Sub

Someone suggested there might be a problem with this code because the 2
commands are not in the same batch.

Is there an issue? The code has worked reliably for a long time - but I am
slightlyt concerned there could be an iusse that I am not aware of.

Would it also work if I just passed a cmd object instead of a transaction?

If I created a 2nd command object and used 2 different cmds to run the 2
statements I would not expect them to work.
But the term "scope" never seems to include the same command object or
transaction.
"SQL Statements that are contained in the same batch, stored procedure, or
trigger are considered to be in the same scope."
OK - but is the same connection considered the same batch? Or is the same
transaction the same scope?

 

[Cowboy \(Gregory A. Beamer\)]

A batch is anything that ends in a GO. When sent to SQL Server, your code
looks like this:

Command1
GO

SELECT
Scope_Identity()"))
GO

The transaction is probably shielding you here, so you are unlikely to have
an issue with wrong numbers. I would still refactor into a stored procedure
and return scope identity there. Either that or send multiple commands on
the same object and run as a batch. Of the two, the first is more
maintainable.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

 

[Joe Fallon]

Thanks Greg.
I understand your recommendations.

Any feedback on the more specific questions I asked?