SQL Anywhere, Entity Framework 4 and Transactions
I have a process in my program that uses an Entity Framework 4 EDM. The entity context object contains function imports for calling stored procedures.
The process receives a batch of data from a remote server. The batch can consist of data for any of our tables / data types (each data type is stored in its own table). The batch can also contain data for the same row multiple times. It has to handle this as a single insert (for the first occurance) and one or more updates (for each subsequent occurance). The stored procedures therefore implement an upsert operation using the INSERT ... ON EXISTING UPDATE command.
Our code basically determines which stored procedure to call and then calls it using the entity context object's method for that stored procedure. Then entire batch has to be done in a single transaction, so we call context.Connection.BeginTransaction() at the beginning of the batch.
There is one data type that has millions of rows. We need to load that data as quickly as possible. I'm implementing logic to import that data type using the SABulkCopy class. This also needs to be a part of the single transaction already started. The issue is that I need to pass an SATransaction to the SABulkCopy class's constructor (there is no way to set it it using properties) and I don't have an SATransaction. context.Connection.BeginTransaction() returns a DBTransaction. I tried to cast this into an SATransaction without success.
What's the right way to get the SABulkCopy object to join the transaction?
We gave up on the SABulkCopy class. It turns out that it doesn't do a bulk load. It creates an SACommand object that executes an INSERT statement and inserts the rows one at a time. And it does it inefficiently, to boot.
I still needed to get at the SATransaction associated with the DBTransaction returned by context.Connection.BeginTransaction(). I was given some reflection code that does this in response to another question I posted about this:
SATransaction saTransaction = (SATransaction) dbTransaction.GetType() .InvokeMember( "StoreTransaction", BindingFlags.FlattenHierarchy | BindingFlags.NonPublic | BindingFlags.InvokeMethod | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.NonPublic, null, dbTransaction, new object[ 0 ] );
The program does what it needs to do. It's unfortunate, though, that Microsoft didn't make the StoreTransaction property of the EntityTransaction class public.