Rules for server sided database requests?

wolfganguhr

Hello

Yesterday we have chatted about a standard method to handle database requests. Here GetPartnersBestLocationData should have been a good example.

Let us have a look on to a small code snippet:

TDBTransaction ReadTransaction;
Boolean NewTransaction;

ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
    IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction);

try
  {
     // Some DB-code
	 // But no Commit ...	 
  }
finally
  {
  if (NewTransaction)
    {
       DBAccess.GDBAccessObj.CommitTransaction();
#if DEBUGMODE
       if (TSrvSetting.DL >= 7)
          {
              Console.WriteLine("TMailing.GetPartnersBestLocationData: committed own transaction.");
          }
#endif
     }
  }

I think that it is not a good idea to define IsolationLevel and TEnforceIsolationLevel on each database request separately and so I prefer two read database read levels, one for never changing tables like a_currency and one for tables which are changed frequently. So the first table (a_currency) shall be read by ReadUncommitted and the others in ReadCommitted.

So I would prefer to Methods at DB.Access.GDBAccessObj which may named like GetParameterTableTransaction, GetValueTableTransaction and of course a GetChangeDBTransaction. This last method shall handle the isolation level more specificly table depended. GLM and GMLp needs a higher protection level than other tables, but this shall be an internal problem.

Furthermore I think that it is not a good idea to force everyone who wants to read something from the date base to handle the Situation of a new transaction. I would prefer to place the new transaction handling code into DB.Access.GDBAccessObj.

At least I would propose not to use the "finally" construct. I suggest the following code ...

TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.GetParameterTableTransaction();

try
  {
     // Some DB-code
	 // But no Commit ...	 
     DBAccess.GDBAccessObj.CommitTransaction();
  } Catch(Exception exception) 
  {
     DBAccess.GDBAccessObj.RollbackTransaction();
     Console.TLog(exception.Message);
  }

Most database problems will occur in the CommitTransaction-Sequence and in case of an error we need a rollback.

Best regards
Wolfgang