CODEWITHSUNDEEP
.netsqlselecttransactions
SARAVAN
SARAVAN

Reputation: 15111

working of SQL server transactions using ASP .net

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.

Upvotes: 0

Views: 699

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300817

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

Upvotes: 2

Related Questions