transaction support and DateTime support for entity properties using MySql

Hi

In the manual you go through how transaction is being done. We are using a MySql database and from trying the TransactionScope, it does not work which is fine by us since Oracle might be the database later on. However, what is worse, I can't get the standard .NET transaction to work either with MySql. Here is some sample code I used:

CREATE TABLE `contribution` (
  `Id` int(11) NOT NULL auto_increment,
  `Description` varchar(10) default NULL,
  `ThisDay` datetime NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

The entity class being obvious and which works fine for saving data. However, the following code will still add the column "aaaa" into the code although the exception occurs since ThisDay is not set for the "bbbb" contribution object. Have I missed something?

using (IDbTransaction transaction = Repository.BeginTransaction())
{
    try
    {
        Contribution contribution = new Contribution();
        contribution.Description = "aaaa";
        contribution.ThisDay = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

        Repository.Add(contribution);

        contribution = new Contribution();
        contribution.Description = "bbbb";

        Repository.Add(contribution);

        Repository.SaveChanges();
        transaction.Commit();
    }
    catch(Exception ex)
    {
        transaction.Rollback();
    }
}

Repository.CompleteUnitOfWork();

Seems like the entity field has to be a string using MySql due to the necessary formating that needs to be done converting the value properly for the insert into the DB. Is there a way around this being able to actually use DateTime in an entity class for a MySql DateTime table column?

Hi Tobias,

MySQL supports TransactionScope. Your code should look like:

using (TransactionScope transactionScope = new TransactionScope())
{
  // do stuff
  Repository.CompleteUnitOfWork()
  transactionScope.Complete();
}

Cheers,

Andrew.
 

Oh and MySQL supports DateTime fine too. 

 Well, we might very well be swaping to an Oracle database later on so I would prefer to stay with the standard .NET transaction and not have to change this code.

But you know what, as I left the office I realised I forgot to alter from the default  MyISAM table type to InnoDB instead (see here for instance).  And by doing this I discovered I don't even need to specify a transaction in the code for MySql since my code above roles back automatically. I reckon that this depends on  that  the @@autocommit is set to 1 as default. Since you are doing the inserts in a single batch to the database I reckon it ends up in the same transaction without me having to specify it directly. I'll see if I can confirm this by logging on the sql query that MySQL is actually is using.

 However, If I would keep the transaction in the code just for the future database switch sake, I'd have to write something like this to make it work:

Contribution contribution = new Contribution();
using (IDbTransaction transaction = Repository.BeginTransaction())
{
    try
    {
        contribution = new Contribution();
        contribution.Description = "aaaa";
        contribution.ThisDay = DateTime.Now;

        Repository.Add(contribution);

        contribution = new Contribution();
        contribution.Description = "bbbbb";
      
        Repository.Add(contribution);

        Repository.SaveChanges();

        transaction.Commit();
    }
    catch (Exception ex)
    {
        Repository.Remove(contribution);
        Repository.SaveChanges();
        transaction.Rollback();
    }
}

Repository.CompleteUnitOfWork();

----------------- 

Another way of doing it is to move the CompleteUnitOfWork call into the end of the try block, after the SaveChanges method since this is where I get the MindScape ValidationException.

Hmm. I just said it right there, didn't I? ValidationException would probably mean that the SQL queries is not even sent to the database since the domain model found the error first. So the Transaction is not even needed in this case since actual domain model is catching the error first. Hmm, I see if I can find a query that the domain model does not catch so that I can actually try the transaction support... 

 Must obliged for great support!

Yes, I know. But I reckoned a convertion to the correct date time format had to be made for the chosen culture. (e.g. DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")) which would require a string data type in the domain model. But nope. The DateTime type in the domain model seems to work transparently. Case closed for me.

One thing to note is that LS will always wrap a flush in a transaction if no user-level transaction is detected.

Cheers,

Andrew.
 

Damn you are a quick responder.

But höö? What is the point of having to specify a user specific transaction like you give an example of in the manual for the Standard .NET transaction then? To me that code seem like a non practical and quite useless example then if you have a transaction for each flush. No?

I reckon the user transaction use is if you would like to do several flushes (i.e. SaveChanges) within the transaction, correct? 

Might wanna update the manual with this info, no?