SQL Server 2008 - 如何在 SQL 命令中使用作用域标识

Question

下面给出了我使用作用域标识的概念。使用支持我的概念的范围标识的正确方法是什么？

对于单个数据：

Imports System.Data.SqlClient
Public Class Class1
    Sub Something()
        Using con As SqlConnection = New SqlConnection("ConnnectionString")
            Dim tran As SqlTransaction = con.BeginTransaction("ATransaction")
            Using cmd As SqlCommand = New SqlCommand("DECLARE @ScopeId bigint;INSERT INTO AuditEvents(UserId) VALUES(@UserId);SELECT @ScopeId=SCOPE_IDENTITY();")
                cmd.Parameters.AddWithValue("@UserId", 1)
                cmd.Transaction = tran
                For rowNumber As Integer = 0 To 5 'DataGridView.Rows.Count - 1
                Next
                Using childCommand As SqlCommand = New SqlCommand("INSERT INTO AuditEventDetails(EventId, ResourceName, OldValue, NewValue) SELECT @EventId, @ResourceName, @OldValue, @NewValue")
                    childCommand.Parameters.AddWithValue("@EventId", "@ScopeId") '???????
                    childCommand.Parameters.AddWithValue("@ResourceName", "Something")
                    childCommand.Parameters.AddWithValue("@OldValue", "OldValue")
                    childCommand.Parameters.AddWithValue("@NewValue", "NewValue")
                    '............................................................................
                    '............................................................................
                    '............................................................................
                    '............................................................................
                End Using
            End Using
        End Using
    End Sub
End Class

对于多个数据：

Imports System.Data.SqlClient
Public Class Class1
    Sub Something()
        Using con As SqlConnection = New SqlConnection("ConnnectionString")
            Dim tran As SqlTransaction = con.BeginTransaction("ATransaction")
            Using cmd As SqlCommand = New SqlCommand("DECLARE @ScopeId bigint;INSERT INTO AuditEvents(UserId) VALUES(@UserId);SELECT @ScopeId=SCOPE_IDENTITY();")
                cmd.Parameters.AddWithValue("@UserId", 1)
                cmd.Transaction = tran
                For rowNumber As Integer = 0 To 5 'DataGridView.Rows.Count - 1
                Using childCommand As SqlCommand = New SqlCommand("INSERT INTO AuditEventDetails(EventId, ResourceName, OldValue, NewValue) SELECT @EventId, @ResourceName, @OldValue, @NewValue")
                    childCommand.Parameters.AddWithValue("@EventId", "@ScopeId") '???????
                    childCommand.Parameters.AddWithValue("@ResourceName", "Something")
                    childCommand.Parameters.AddWithValue("@OldValue", "OldValue")
                    childCommand.Parameters.AddWithValue("@NewValue", "NewValue")
                    '............................................................................
                    '............................................................................
                    '............................................................................
                    '............................................................................
               Next
                End Using
            End Using
        End Using
    End Sub
End Class

Answer 1

如果您使用存储的过程，那么我认为最好使用输出参数。

但是，对于基于文本的SQL命令，我倾向于使用类似的东西;

 public int InsertRecord(string ANumber, string Note)
    {
        string SQL_INSERT_RECORD = "INSERT INTO AnumberNotes ( ANumber, Note ) VALUES ( @ANumber, @Note ) ; SELECT CAST(scope_identity() AS int)";
        int NewId = -1;
        try
        {
            using (IDbConnection conn = DBFactory.GetConnection())
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = SQL_INSERT_RECORD;
                    cmd.Parameters.Clear();
                    IDbDataParameter param = cmd.CreateParameter();
                    param.ParameterName = "@ANumber";
                    param.Value = ANumber;
                    cmd.Parameters.Add(param);
                    param = cmd.CreateParameter();
                    param.ParameterName = "@Note";
                    param.Value = Note;
                    cmd.Parameters.Add(param);
                    NewId = (Int32) cmd.ExecuteScalar();
                }
            }
        } catch( Exception ex ) {
            throw new DAOException("AnumberNotesDAOBase.insertRecord: ", ex );
        }
        return NewId;
    }