SCOPE_IDENTITY在C＃中 - 范围 [英] SCOPE_IDENTITY in C# - range

问题描述

我检查了 SCOPE_IDENTITY（）的文档，并它说：作用域是一个模块：一个存储过程，触发器，函数或批处理。 。这是当我运行在SSMSE查询简单，但在C＃中我使用的SqlCommand执行我的语句

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?

推荐答案

我建议你的C＃命令和T-SQL批处理的思想作为完全独立于彼此。

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

想象的SqlCommand的，因为只有你的执行的包装，其内是什么构成了一批实际定义的定义，并由T-SQL语言的控制。

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.

您会话范围保持在Connection对象的水平。

Your session scope is maintained at the Connection object level.

您可能会发现下面的MSDN论坛上发帖有趣的阅读。请注意如何最初的例子执行两个独立的SQL命令，但第二个呼叫的SCOPE_IDENITY（）可以看到前面的调用的结果。这是因为目前的范围在连接级别可见。

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时使用的参数和Scope_Indentity

有关解释的完整性，为什么这不使用参数的工作的原因，如后面在链接例子证明，是因为sp_executesql的是它自己的范围内执行，因此，因此不能看到连接的范围。

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.

进一步阅读了更多好奇的读者，请找低于VB.NET代码提供在一个单一的连接执行两个独立的命令，与第二命令成功地发出SCOPE_IDENTITY（）函数的一个例子。

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.

的源代码可以从SSIS包任务的脚本组件内执行。您还需要编辑连接的详细信息为您的环境，也可以创建引用的表对象

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 TestTable
(
    ID int identity(1,1) primary key not null,
    SomeNumericData int not null
);

VB.NET源码列表：

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

这篇关于SCOPE_IDENTITY在C＃中 - 范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！