TransactionScope not working with EnlistTransaction [DNET439]

[firebird-automations]

Submitted by: Hélio Tibagí de Oliveira (chogoki)

Votes: 1

Assuming the table "TEST" created in Firebird and SQLServer using the statement below: ------------------------------------------------------------------------------------------------------------------------- CREATE TABLE TEST (INTEGER_COLUMN INT NOT NULL)

Then, the following code in a console application connecting to the SQLServer: ----------------------------------------------------------------------------------------------------------- SqlConnection connSQLServer = new SqlConnection("Data Source=localhost;Initial Catalog=DATABASE;User ID=sa;Password=mypassword"); connSQLServer.Open();

using (TransactionScope scope = new TransactionScope()) { connSQLServer.EnlistTransaction(Transaction.Current); Int32 affectedLines = new SqlCommand("INSERT INTO TEST (INTEGER_COLUMN) VALUES (1)", connSQLServer).ExecuteNonQuery(); Console.WriteLine(affectedLines); // ---> #⁠1 line affected }

Int32 persistedLines = (Int32)new SqlCommand("SELECT COUNT(*) FROM TEST", connSQLServer).ExecuteScalar();

Console.WriteLine(persistedLines); // ---> #⁠0 line persisted

Running the same code in Firebird: ------------------------------------------------ FbConnection connFirebird = new FbConnection("User=SYSDBA;Password=masterkey;Database=myserver:backup02.fdb;Dialect=3;Charset=WIN1252;"); connFirebird.Open();

using (TransactionScope scope = new TransactionScope()) { connFirebird.EnlistTransaction(Transaction.Current); Int32 affectedLines = new FbCommand("INSERT INTO TEST (INTEGER_COLUMN) VALUES (1)", connFirebird).ExecuteNonQuery(); Console.WriteLine(affectedLines);// ---> #⁠1 line affected }

Int32 persistedLines = (Int32)new FbCommand("SELECT COUNT(*) FROM TEST", connFirebird).ExecuteScalar();

Console.WriteLine(persistedLines);// ---> #⁠1 line persisted

The rollback is done successfully in SQLServer, but not in Firebird. Can you fix this problem?

Thanks in advance.

[firebird-automations]

Commented by: Hélio Tibagí de Oliveira (chogoki)

I also tested this same code in Firebird 2.5.1 and had the same result.

[firebird-automations]

Commented by: Thiago Araújo (thiagoasilveira)

Entity Framework Core don't have "Level transaction" and we can't implement ReadUncommited transactions. Reading about it in Microsoft documentation I saw we need to use TransactionScope.