"Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
news:uMS7iSJ$GHA.1172@TK2MSFTNGP03.phx.gbl...
>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
>
>
>
>

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in
message news:ePSdwOP$GHA.4740@TK2MSFTNGP03.phx.gbl...
>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" <jfallon1@nospamtwcny.rr.com> wrote in message
> news:uMS7iSJ$GHA.1172@TK2MSFTNGP03.phx.gbl...
>>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
>>
>>
>>
>>
>
>