如何在 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;
    }