Transactions and Scope_Identity
- From: "Joe Fallon" <jfallon1@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 30 Oct 2006 21:31:11 -0500
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?
--
Joe Fallon
.
- Prev by Date: Re: ASMX not showing webmethod that should return a dataset
- Next by Date: Re: Dot Net 2.0 or SQL Server 2005 problem?
- Previous by thread: Regenerate datasets
- Next by thread: Join Tables in 2 SQL Server Databases
- Index(es):
Relevant Pages
|
