working of SQL server transactions using ASP .net

Question

I have an ASP .net application that calls a webservice. The webservice calls the data access classes to update the database.

I have a code snippet(actually a pseudocode) in my webservice similar to the following:

Public Function SubmitCheque(//params are passed here)
     //Call sql stored procedure(SP1) to generate a reference Number

     //Begin loop. loop count would be number of records that are in a collection
       //Insert records to database using another stored procedure(SP2) using the
       //reference number generated in the previous step
     //End Loop
End Function

SP1 performs a select query on a table, get the last row's column value and then increments its sequence by 1. Say if the last row's value is ABC02282012*56*, then my new reference number would be ABC02282012*57*

SP2 performs a single insert statement, and the row that it inserts will have the reference number that was generated.

Here is the problem that is expected to occur and I am not sure about the right way to do it.

In my code SP1 and SP2 executes sequentially, and in a millisecond of time interval between SP1 and SP2 there is a possiblity that another instance of the application would have executed SP1 and can get the same transaction number. So there is a possiblity that multiple users accessing the application simultaneously might end up in getting a same transaction number which is a big problem.

I wanted to know if this is something that SQL server will take care or do I need to re-write the stored procedure or code in a different way to implement a transaction.

Please help and let me know your comments.

Mitch Wheat · Accepted Answer · 2012-02-28 08:50:43Z

2

You would be better implementing all the logic inside a single stored procedure, with the statements wrapped inside a suitable transaction.

Failing that use a TransactionScope to enlist your separate actions in a transaction. e.g.:

try
{
    // Create the TransactionScope to execute the commands, guaranteeing
    // that both commands can commit or roll back as a single unit of work.
    using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection connection1 = new SqlConnection(connectString1))
        {
            // Opening the connection automatically enlists it in the 
            // TransactionScope as a lightweight transaction.
            connection1.Open();


           // Call sql stored procedure(SP1) to generate a reference Number                 

           SqlCommand command1 = new SqlCommand(commandText1, connection1);
           returnValue = command1.ExecuteScalar();


          //Begin loop. loop count would be number of records that are in a collection 
              //Insert records to database using another stored procedure(SP2) using the 
              //reference number generated in the previous step 
          //End Loop 

        }
        // The Complete method commits the transaction. If an exception has been thrown,
        // Complete is not  called and the transaction is rolled back.
        scope.Complete();
    }
}
catch (TransactionAbortedException ex)
{
    writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
}

edited Feb 28, 2012 at 8:50

answered Feb 28, 2012 at 8:44

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SARAVAN Over a year ago

I understand but there is a .net collection, that contains several payment instructions(say for instance 4 payments and the count can even be 10). If I pass these as parameters to my Stored procedure then my stored procedures input parameters might look way too lengthy.

SARAVAN Over a year ago

I am looking in to the TransactionScope. If I wrap the SP calls in a TransactionScope, will it be considered as a single transaction?

SARAVAN Over a year ago

Thanks for the sample code snippet. One difference is that the function that I have does not have any SQL connections opened. Its only the other class library functions(which are called from my service) that have the SQL connections opened. \n Will this case still be considered as a transaction when using transaction scope.

Mitch Wheat Over a year ago

only if the connections are opened after the transaction scope is created, and if they do not explicitly specify that they run outside of a transaction (unlikely).

Collectives™ on Stack Overflow

working of SQL server transactions using ASP .net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related