Distributed transactions, EF 4 & the Large Objects table
Posted: Tue 01 Nov 2011 19:28
Working with a PostgreSql 9 database that has 2 tables that are related:
My program uses C# code to write the image bytes to the Large Objects table before it creates the row in the Images table. According to the documentation, the large object table code needs to be inside of a local transaction. Everything has been working fine up until today.
Today, I discovered that I needed to create a TransactionScope object to encompass a number of operations that my code has to do. Here's some code to illustrate what's going on:
The "process object here" involves determining what type the object is and then calling the proper method in a DataAccessor class specific to that type that has code like the below in it:
Here's the method that writes the image's bytes to the Large objects table:
When I didn't have the TransactionScope, all of the code worked fine. But now that I added the TranactionScope, I get an error message that says, "Local transaction can not be started while in a distributed transaction."
How do I correct this code to work in this configuraton?
Tony
- The Reads table is the parent table; its key column is a UUID called ReadId
- The Images table is the child table & relates to the Reads table with a UUID column called ReadId
- The Images table has a column called ImageOid of type int. This column contains the OID of a row in the Large Objects table that contains the bytes for a JPEG image.
My program uses C# code to write the image bytes to the Large Objects table before it creates the row in the Images table. According to the documentation, the large object table code needs to be inside of a local transaction. Everything has been working fine up until today.
Today, I discovered that I needed to create a TransactionScope object to encompass a number of operations that my code has to do. Here's some code to illustrate what's going on:
Code: Select all
using ( CarSystemEntities context = new CarSystemEntities() ) {
try {
TransactionScope transaction = new TransactionScope();
foreach ( IDableObject idObjet in message ) {
context.SaveChanges();
}
transaction.Complete();
} catch ( Exception ex ) {
}
}
Code: Select all
public static void SaveDomain( CarSystemEntities context, Properties.Domain data ) {
if ( context == null )
// We do not. Throw an error
throw new ArgumentNullException( "context", "You must pass a non-null CarSystemEntities instance." );
if ( data == null )
throw new ArgumentNullException( "data", "You must pass a non-null Domain instance." );
try {
CarSystem.Domain domain = null;
if ( data.ID == Guid.Empty ) {
// We do not. Create one now
data.ID = Guid.NewGuid();
}
if ( !context.Domains.Any( d => d.DomainGuid == data.ID && d.IActive == 1 ) ) {
domain = new CarSystem.Domain {
CreatedDate = DateTime.Now,
DomainGuid = data.ID,
DomainId = data.DomainId,
DomainName = data.Name,
IActive = 1
};
context.Domains.AddObject( domain );
} else {
domain = QueryDomains( context).Where( d => d.DomainGuid == data.ID ).Single();
bool changed = false;
if ( domain.DomainGuid != data.ID ) { changed = true; domain.DomainGuid = data.ID; }
if ( domain.DomainId != data.DomainId ) { changed = true; domain.DomainId = data.DomainId; }
if ( domain.DomainName != data.Name ) { changed = true; domain.DomainName = data.Name; }
if ( changed )
domain.ModifyDate = DateTime.Now;
}
} catch ( Exception ex ) {
throw new DataAccessException( DataAccessOperation.SaveDomain, FailureReason.DatabaseError, ex );
}
}
Code: Select all
public static int PutImageBytes( byte[] image ) {
try {
using ( PgSqlConnection conn = new PgSqlConnection( ConfigurationManager.ConnectionStrings[ "PgSqlServices" ].ConnectionString ) ) {
conn.Open();
PgSqlLargeObject lo = new PgSqlLargeObject( conn );
PgSqlTransaction trans = conn.BeginTransaction();
lo.Create();
lo.Open();
lo.Write( image, 0, image.Length );
lo.Close();
trans.Commit();
return lo.Oid;
}
} catch ( Exception ex ) {
throw new DataAccessException( DataAccessOperation.PutImageBytes, FailureReason.DatabaseError, ex );
}
}
How do I correct this code to work in this configuraton?
Tony