Implicit transactions

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
mlagasio
Posts: 43
Joined: Mon 14 Mar 2011 13:42

Implicit transactions

Post by mlagasio » Mon 04 Apr 2011 09:07

In our application we have use implicit transactions (we have not explicit SqlTransaction instances).
How could we migrate these cases to dotConnect Universal?

Many Thanks

Marco

Shalex
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 06 Apr 2011 12:22

I have tested the following code with dotConnect Universal v 3.20.44. The records are inserted to the database only after executing "COMMIT TRANSACTION;":

Code: Select all

    using (UniConnection conn = new UniConnection("Provider=SQL Server;Data Source=MSSQL2008RTM;user id=sa;Initial Catalog=Test;")) {
        conn.Open();
        UniCommand cmd = conn.CreateCommand();
        cmd.CommandText = "SET IMPLICIT_TRANSACTIONS ON;";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "insert into dept values (50, 'a', 'a');";
        int i = cmd.ExecuteNonQuery();
        cmd.CommandText = "insert into dept values (60, 'b', 'b');";
        int i2 = cmd.ExecuteNonQuery();
        if (i == 1 && i2 == 1) {
            cmd.CommandText = "COMMIT TRANSACTION;";
            cmd.ExecuteNonQuery();
        }
    }
Is that what you mean?

mlagasio
Posts: 43
Joined: Mon 14 Mar 2011 13:42

Post by mlagasio » Wed 06 Apr 2011 13:37

Hi,

perhaps the effect at database level is that you have tested, but
our code look like this

TransactionOptions txOptions = new TransactionOptions();
txOptions.Timeout = new TimeSpan(0, 1, 00);
txOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
using (TransactionScope transactionSc = new TransactionScope(TransactionScopeOption.Required, txOptions))
{
using (Connection = new SqlConnection("Data Source=MyDataSource;Initial Catalog=MyCatalog;User ID=MyUserID;Password=MyPassword"))
{
Connection.Open();
try
{
........(DoSomething).......

transactionSc.Complete();
}
finally
{
Connection.Close();
}
}
}

Thanks, Marco

Shalex
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 08 Apr 2011 15:36

It works. dotConnect Universal supports TransactionScope.

Post Reply