CODEWITHSUNDEEP
c#winformstransactionsfirebird
Mikhail Tregubov
Mikhail Tregubov

Reputation: 68

Multiple transactions using FirebirdSQL (C#)

I have a WinForms application that connects to Firebird 2.5 DB using FirebirdSQL. In this application I have a FBConnection that I'm using for interaction with DB and a control that provides UI for editing DB - buttons, textboxes, etc. with various event handlers. When this control loads I'm creating a new transaction by calling BeginTransaction() method of FBConnection instance. So when user interacts with controls(buttons) I'm calling methods for changing values in DB (by calling stored procedures or executing SQL statements) like so:

FbCommand command = new FbCommand(cmd_text, connection);
command.Transaction = transaction;
FbDataReader reader = command.ExecuteReader()

When user decides to apply all changes that he made to the DB, he clicks "Apply" button on my control, that calls transaction.Commit() or "Cancel" button that calls transaction.Rollback().

This worked fine but suddenly I faced a problem regarding using multiple transactions in my application. In a case of multiple editing controls used at the same time in one application it appears that you can't create and use multiple transactions within one connection.

My problem is - I don't know how to create multiple parallel transactions using one connection. I'm starting to believe that it's impossible with FirebirdSQL. It seems like the only solution is to create new connection to DB for each active transaction but I don't really like this way.

If I'm wrong about using of transactions in FirebirdSQL or anyone knows how resolve my issue rather than creating multiple connections to DB I'll appreciate the advice.

Upvotes: 1

Views: 2488

Answers (2)

Dmitry Kovalenko
Dmitry Kovalenko

Reputation: 1

Firebird (and Interbase, of course, also) supports the multiple transactions within one connection.

And some providers are allowed to use this feature.

Look here: http://www.ibprovider.com/eng/examples/lcpi_oledb_net__c001__example_0017.html

Upvotes: 0

Dennis
Dennis

Reputation: 37770

  • Don't share connections. Create a connection per control. Let the connection pool optimize and manage your physical connections.
  • Don't keep connection opened for a long time. If the physical connection will be broken, you'll lost all of your changes. ADO .NET is designed for disconnected scenarios.
  • Don't use long-running transactions. Especially, don't use them with Firebird, ever.

Consider these scenarios:

  • Open connection, load data from data source, close connection. When you need to change something, open connection, change something, then close connection.
  • Open connection, load data from data source, close connection. When you need to change something, change it locally. Track all of your changes and submit them once (e.g., when user presses "Save" button).

Upvotes: 1

Related Questions