Как использовать идентификатор области видимости в 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;
    }