[nrdo-list] Proposal: Maintaining a connection across multiple queries

[Stuart Ballard]

Here's a tentative proposal for the API I'm considering to allow a
single database connection to be maintained across multiple nrdo
queries. This API also is intended to optionally provide basic
transaction support but if you want to ignore those parts you can.

The following is the rough notes I've made describing the proposed
API. I hope it's clear enough to understand what the proposal entails.
I'm looking for feedback on the open questions listed at the bottom,
whether the general approach is sensible and will fit your needs, and
anything else you might have to add.

A class NrdoScope creates and maintains a shared database connection.
NrdoTransactedScope inherits from NrdoScope and also maintains a transaction.

But NrdoScope can be used perfectly well even if you're handling the
transaction by yourself using TransactionContext, or by hand. If
you're happy with using TransactionContext or prefer its model to
mine, then just keep using it, and ignore the NrdoTransactedScope
parts of this discussion.

Note that as proposed the NrdoTransactedScope model does NOT support
nested transactions or handing transactions off to other threads. If
you want either of these things you must handle the transactions
yourself.

There are two ways to use both these classes:

Nrdo(Transacted)Scope.Begin() / Nrdo(Transacted)Scope.End() - these
can be used in separate methods and are intended for use in ASP.NET
pages, perhaps by adding Begin() to the BeginRequest event and End()
to the EndRequest event.

Begin() and End() are only intended for use at the top level, so if a
scope is already active on the current thread when Begin() is called,
an exception will be thrown; similarly if End is called when more than
one scope is active, an exception will be thrown and any current
TransactedScope will be rolled back. The Begin and End calls must
match - you can't use NrdoTransactedScope.Begin() and then
NrdoScope.End().

The alternative way of using it is more in tune with the way
TransactionContext works in .NET:

using (NrdoScope scope = new NrdoScope()) {
}
or
using (NrdoTransactedScope scope = new NrdoTransactedScope()) {
}

Or, if you don't need the scope values themselves,

using (new NrdoScope()) {
}
or
using (new NrdoTransactedScope()) {
}

With this syntax the scopes will nest inside each other and the
connection will be obtained from the top-level scope.

The plan is for NrdoTransactedScope to be "lazy" about creating the
transaction - a transaction will only be created, and associated with
the connection, the first time Update() or Delete() are called.
Similarly, both kinds of NrdoScope will not actually open a connection
until something is called that needs it.

Under the hood NrdoScope will track two things for each thread - the
lowest-level most nested scope, and the topmost scope. Each scope will
track its parent. NrdoTransactedScope will track the topmost scope
that is Transacted, if any.

NrdoScope.Current will return the *topmost* scope (or null if there
are none), not the most nested one. Similarly
NrdoTransactedScope.Current will return the topmost Transacted scope,
or null if there are none.

NrdoScope will have a public GetConnection() method that obtains the
connection. If the connection does not yet exist, it will be created
at this point. Regardless of which scope you call this on, the
connection is associated with the topmost current scope
(NrdoScope.Current).

NrdoTransactedScope will have Commit() and RollBack() methods. Just
like GetConnection(), regardless of which scope you call these on,
they will defer to the topmost transacted scope
(NrdoTransactedScope.Current) - because as noted this model does not
support nested transactions in any way. It will also have a
BeginTransaction() method that actually creates the transaction and
makes it active by assigning it to Transaction.Current (the .NET
framework's "ambient transaction"). Calling BeginTransaction() when it
has already been called is a no-op. Calling Commit() or Rollback() on
scopes that have not had BeginTransaction() called will do nothing. If
BeginTransaction() *has* been called, these methods will commit or
rollback the ambient transaction accordingly, and restore the scope to
the same state as if BeginTransaction() had not yet been called.

For convenience, all these methods (which are by default defined on
the individual scope objects) will have static versions which operate
on the Nrdo(Transacted)Scope.Current object, but have sensible
fallback behavior if that is null:

public class NrdoScope {
 public static IDbConnection GetConnection() {
   if (NrdoScope.Current == null) throw new
ApplicationException("There is no scope to get the connection from!");
   return NrdoScope.Current.GetConnection();
 }
}
public class NrdoTransactedScope : NrdoScope {
 public static void BeginTransaction() {
   if (NrdoTransactedScope.Current == null) return;
   NrdoTransactedScope.Current.BeginTransaction();
 }
 public static void Commit() {
   if (NrdoTransactedScope.Current == null) return;
   NrdoTransactedScope.Current.Commit();
 }
 public static void RollBack() {
   if (NrdoTransactedScope.Current == null) throw new
ApplicationException("Can't roll back a non-transacted scope!");
   NrdoTransactedScope.Current.RollBack();
 }
}

When the topmost NrdoScope is Disposed (ie at the end of it's using
block) it will close the associated connection, if any.
When the topmost NrdoTransactedScope is Disposed, it will commit the
associated transaction, if any. If you want it to rollback you must do
it explicitly. This is different from the framework's
TransactionContext where you must explicitly call Complete() on every
context to make it commit.

If you want a "rollback on errors" sort of setup, you need to write
code that makes it explicit, like this:

using (NrdoTransactedScope scope = new NrdoTransactedScope()) {
 try {
   // do some stuff
 } catch {
   scope.RollBack();
   throw;
 }
}
Or alternatively:
using (new NrdoTransactedScope()) {
 try {
   // do some stuff
 } catch {
   NrdoTransactedScope.RollBack();
   throw;
 }
}

The upshot of all this is that you can write code like the above to
work with transactions in isolation but in ASP.NET you can also put
NrdoTransactedScope.Begin() in your BeginRequest,
NrdoTransactedScope.End() in your EndRequest, and
NrdoTransactedScope.RollBack() in your error handler, and get
transactions for free in all code in all your event handlers. PLUS for
any pages that only do Gets and never write anything to the database,
no transaction gets created - still without having to do anything
codewise.

Alternatively you can do NrdoScope.Begin() in your BeginRequest and
NrdoScope.End() in your EndRequest, all your code will be run on a
shared connection, but you will be responsible for managing
transactions yourself (whether you do that with NrdoTransactedScope or
with TransactionContext or by hand is up to you).

The other effect is that if you do:
using (new NrdoScope()) {
 ...
}
and you're already running inside a NrdoScope, your code inherits the
connection automatically, but if that same code gets executed without
an active NrdoScope, it will create a connection and close it when
finished. Similarly, if it gets run with a TransactedScope present, it
will automatically run inside the transaction.

Similarly, if you do:
using (new NrdoTransactedScope()) {
 ...
}
and you're already running inside a transacted scope, it inherits the
transaction automatically (and doesn't commit until the outer
transaction scope ends). If the same code gets executed with only a
regular NrdoScope active, then it will create a transaction *on* that
connection and commit it at the end of the using block. If it's run
with no scope present at all, it will create both a connection *and* a
transaction and commit/close when finished.

Open questions:
- Do we actually need the non-static methods at all? They seem kind of
redundant since they always end up deferring to the top level objects.

- Should there be a method which you can use in case of errors to not
only roll back the transaction, but force the entire scope all the way
to the top level to be read-only thereafter? If not, it's possible for
someone to catch an exception, not rethrow it, and then continue
updating stuff as if nothing was wrong (and create a new transaction
in the process). Providing this seems open to abuse because it leaves
a situation where even if code deliberately chooses to handle the
error and wants to continue, it can't. Not providing it seems
dangerous because people might continue making changes in situations
where they shouldn't. Perhaps we should provide it but *also* provide
a way to reset that flag for people who know they're in an error
situation and want to continue anyway.


--
http://sab39.dev.netreach.com/