SCOPE_IDENTITY in C# - range

Question

I've checked the documentation for SCOPE_IDENTITY(), and it says "A scope is a module: a stored procedure, trigger, function, or batch." That is simple when I'm running a query in SSMSE, but in C# I'm using SqlCommand for executing my statements.

The question is: what is the scope there? Is executing subsequent commands under one connection an equivalent of batch? Or maybe every command is in a different scope and I need a transaction for this to work?

Answer 1

I believe scope is only applicable for the single command, not for the entire connection.

strSQL = "INSERT INTO tablename (name) VALUES (@name);SELECT SCOPE_IDENTITY()"
SQLCommand.CommandText = strSQL
Id = SQLCommand.ExecuteScalar()

In The above code strSQL is a complete scope,and it always return the @@identity value of the associated insert statement.

so the subsequent commands will have its own scope.

Answer 2

I suggest thinking of your C# commands and T-SQL "Batches" as completely separate to one another.

Think of SQLCommand as your execution wrapper only, within which the actual definition of what constitutes a batch is defined and controlled by the T-SQL language.

Your session scope is maintained at the Connection object level.

You will likely find the following MSDN forum post interesting reading. Notice how the initial example executes two separate SQL Commands but the SCOPE_IDENITY() of the second call can see the result of the previous call. This is because the current scope is visible at the connection level.

SQLCommand With Parameters and Scope_Indentity

For completeness of explanation, the reason why this does not work using parameters, as later demonstrated in the linked example, is because sp_executesql is executed within it's own scope and so therefore cannot see the scope of the connection.

[EDIT]

Further reading for the more inquisitive reader, please find VB.NET code below that provides an example of executing two separate commands on a single Connection, with the second command sucessfully issuing the SCOPE_IDENTITY() function.

The source code can be executed from within the SCRIPT component of an SSIS package Task. You will also need to edit the connection details for your environment and also create the table object referenced.

Create Table Script:

create table TestTable
(
    ID int identity(1,1) primary key not null,
    SomeNumericData int not null
);

VB.NET Source Listing:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient.SqlConnection
Imports Windows.Forms.MessageBox

Public Class ScriptMain



    Public Sub Main()
        '
        ' Add your code here

        Dim oCnn As New Data.SqlClient.SqlConnection
        Dim sSQL As String
        Dim sSQL2 As String
        Dim resultOne As Integer
        Dim resultTwo As Integer
        Dim messageBox As Windows.Forms.MessageBox

        resultOne = 0
        resultTwo = 0

        oCnn.ConnectionString = "Server=ServerName;Database=DatabaseName;Trusted_Connection=true"
        sSQL = "INSERT INTO TestTable(SomeNumericData) VALUES(666) "
        sSQL2 = "SELECT SCOPE_IDENTITY()"
        Dim oCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sSQL, oCnn)
        Dim oCmd2 As SqlClient.SqlCommand = New SqlClient.SqlCommand(sSQL2, oCnn)

        oCmd.CommandType = CommandType.Text
        oCmd.Connection = oCnn
        oCnn.Open()

        resultOne = oCmd.ExecuteNonQuery()
        resultTwo = Convert.ToInt32(oCmd2.ExecuteScalar())

        oCnn.Close()

        messageBox.Show("result1:" + resultOne.ToString + Environment.NewLine + "result2: " + resultTwo.ToString)

        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class