Insert rows within transaction with FK to first row created

Question

I've got a batch of rows that need to be inserted within a single transaction, the first row is a header and all subsequent rows must reference the first.

This is how I'm attempting to insert the first row:

using ( var connection = new SqlConnection( _connectionString ) )
{
    connection.Open();
    var transaction = connection.BeginTransaction();

    try
    {
        // create the record header
        using ( var createRecordCommand = connection.CreateCommand() )
        {
            createRecordCommand.CommandText =
                "DECLARE @RecordId INT;" +
                $"INSERT INTO ..." +
                "SET @RecordId = scope_identity();";

            createRecordCommand.Transaction = transaction;
            createRecordCommand.ExecuteNonQuery();

I'm struggling to reference RecordId in subsequent statements, it seems to think the variable @RecordId is not declared in the snippet below. 'Must declare the scalar variable "@RecordId".'

using ( var createAttributeCommand = connection.CreateCommand() )
{
    createAttributeCommand.Transaction = transaction;

    createAttributeCommand.CommandText =
        $"INSERT INTO ... (RecordId,...) VALUES (@RecordId,...)";

Also extracting the @RecordId variable as a parameter is proving difficult, it doesn't like it when I use the below line in conjunction with DECLARE @RecordId INT;

createRecordCommand.Parameters.Add( new SqlParameter( "@RecordId", SqlDbType.Int ) { Direction = ParameterDirection.Output } );

Why don't you put your logic into a stored procedure and then just call the procedure from C#? — Rigerta, Oct 05 '17 at 14:38
I've shaved allot of the complexity out of the question, what we're trying to achieve is too complex for a stored proc unfortunately. — Dead.Rabit, Oct 05 '17 at 14:40
Ummm what? Too complex for a stored procedure? Do the insert as a stored procedure and use an OUTPUT parameter to return the identity. — Sean Lange, Oct 05 '17 at 14:47
Use bulkcopy. See following posting : https://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row/20108861#20108861 — jdweng, Oct 05 '17 at 14:54
You need to return the ReportID to your code or combe the second insert in with your first command. I think Rigerta is right. Use a sproc. Or, if you use the output operator on your insert you could return the scope_identity and then add the value to the parameter for your subsequent call. `createRecordCommand.Parameters.Add( new SqlParameter( "@RecordId", SqlDbType.Int ) { Direction = ParameterDirection.Output , value = ReportIDValue} );` — Jarealist, Oct 05 '17 at 18:00
@SeanLange You can't create a stored procedure with a variable number of parameters AFAIK, unless you parse parameters from XML or CSV, while not impossible to achieve with a stored procedure, it would involve rebuilding a bunch of stuff in SQL, this isn't appropriate at this stage, we're still prototyping. — Dead.Rabit, Oct 06 '17 at 08:47
I would at least consider the possibility that the process needs to be rethought. Since you are still in the prototyping phase maybe this is possible. A variable number of parameters being required is a pretty good indication that something is a bit left of center. — Sean Lange, Oct 06 '17 at 13:03

Dead.Rabit · Accepted Answer · 2017-10-06T09:04:19.643

I hadn't realised you can extract data before the transaction was committed, assuming that since ExecuteScalar() didn't work, I'd run into the same issue.

As pointed out in the comments, a stored procedure is probably the best solution, SQL experts sound like broken records after a while ;p. This will be too complex short term as there are variable # of parameters, validation and values will mutate based on business logic. As a team, we're more comfortable in C# so that's where the logic will remain until it's completely nailed down.

Resulting code is as follows, N.B. in real code, pass all variables as parameters to avoid SQL injection, we are, this is demonstration only:

using ( var connection = new SqlConnection( _connectionString ) )
{
    connection.Open();
    var transaction = connection.BeginTransaction();

    try
    {
        // create the record header
        using ( var createRecordCommand = connection.CreateCommand() )
        {
            createRecordCommand.CommandText =
                //"DECLARE @RecordId INT;" + 
                $"INSERT INTO ..." +
                "SET @RecordId = scope_identity();";

            createRecordCommand.Transaction = transaction;
            createRecordCommand.Parameters.Add( new SqlParameter( "@RecordId", SqlDbType.Int ) { Direction = ParameterDirection.Output } );
            createRecordCommand.ExecuteNonQuery();

            recordId = (int) createRecordCommand.Parameters["@RecordId"].Value;

// ...

using ( var createAttributeCommand = connection.CreateCommand() )
{
    createAttributeCommand.Transaction = transaction;
    createAttributeCommand.CommandText =
        $"INSERT INTO ... (RecordId,...) VALUES ({recordId},...)";

// ...

transaction.Commit();

Insert rows within transaction with FK to first row created

1 Answers1