Why Am I Getting Error Using TransactionScope?

Everybody!

I am having trouble with TransactionScope. I want my web method to be transactional. If any exception occurs, I want all database changes rolled back. Otherwise, commit. Please see error message below.

System.ApplicationException: Error in someClass :: Method public returnType methodName(parameterType parameter) :

System.ApplicationException: Error in someClass :: Method private string someMethod() :

System.Data.Odbc.OdbcException (0x80131937):

ERROR [08003] [Sybase][ODBC Driver]Connection not open

at System.Data.Odbc.OdbcConnection.Open_EnlistTransaction(Transaction transaction)

at System.Data.Odbc.OdbcConnectionOpen.EnlistTransaction(Transaction transaction)

at System.Data.Odbc.OdbcConnection.EnlistTransaction(Transaction transaction)

at System.Data.Odbc.OdbcConnection.Open()

at someNameSpace.someMethod() in c:\somePath\someClass.cs:line 35

The following is an excerpt of my code. What am I doing wrong?

 [WebMethod]
 public returnType methodName(parameterType parameter)
 {
     using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
     {
         try
         {
             var someValue = someMethod();
                 :
                 :
                 :

             transactionScope.Complete();

             return response;
         }
         catch (Exception ex)
         {
             return ErrorMessages(ex);
         }
     }
 }

 private string someMethod()
 {
     var commandText = "...some valid SQL expression...";
     var commandType = CommandType.Text;

     try
     {
         using (var odbcConnection = new OdbcConnection(DefaultDbConnection.ToString()))
         {
             using (var odbcCommand = new OdbcCommand(commandText, odbcConnection))
             {
                 odbcConnection.Open();  // Exception is thrown here!
                 odbcCommand.CommandType = commandType;

                 using (var reader = odbcCommand.ExecuteReader())
                 {
                     reader.Read();
                     return reader.GetString(0);
                 }
             }
         }
     }
     catch (Exception ex)
     {
         throw new ApplicationException("Error in someMethod.", ex);
     }
 }

Thank-you in advance for your help!

Ultimately, not all connection types - and not all platforms - support transaction scope based transactions, and even when they do: they can be unpredictable because of scoping problems when talking to multiple systems, requiring things like DTC.

Instead, I would strongly suggest simply: don't use transaction scope (unless you have a very good reason). Instead, prefer the much simpler and more widely supported ADO.NET transaction model:

• use BeginTransaction (or the async equivalent) on the connection to (unsurprisingly) begin a transaction
• carry that transaction instance around in the code
• set the transaction property on each command (I've never understood why this would be necessary given that it is implied through the connection, but: it is)
• make sure to commit or abort the transaction at the end, ideally using try/catch/finally so that it happens correctly in both success and failure