How to use scope identity in SQL Command?

Question

My concept for using the scope identity is given below. What is the proper way to use the scope identity that support my concept?

For single data:

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

For multiple data:

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

If you use stored procs, then I think it is preferable to use output parameters.

For text based SQL commands though, I tend to use something like;

 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;
    }