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.