SQL Anywhere, Entity Framework 4 and Transactions

Question

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?

score 2 · Accepted Answer · answered Mar 27 '13 at 16:32

2

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.

answered Mar 27 '13 at 16:32

Tony Vitabile

8,298
15
67
123

I know this is old, but could you tell me how did you find out that the SaBulkCopy creates and SACommand and executes Insert statment ... ? By the way, what was your solution when you decided to give up on SaBulkCopy? Thanks – EagerToLearn Sep 07 '17 at 06:20
1

Yes, this was a long time ago. I may have used a decompiler like .Net Reflector to look at the decompiled code to figure out what was happening during the SaBulkCopy. I think the code is an open source project, so you can look it up. Sorry, I'm not in that position any more, so I can't remember. I seem to remember we had to write code that inserted the data a row at a time (no getting around that) into a temp table without indexes and then updated the main table from that table, then dropped the temp table afterwards. We also did it all in one transaction. – Tony Vitabile Sep 08 '17 at 14:28
Thanks alot for you anwser @Tony Vitabile – EagerToLearn Sep 11 '17 at 00:22
1

I think that the SaBulkCopy would create an `SaCommand`, insert a row, discard the `SaCommand`, and then repeat for each row. We wrote the code so it created one `SaCommand` object that it would reuse for all of the rows. For one or two rows, not much of an improvement, but for millions of rows, the improvement would be significant. – Tony Vitabile Sep 13 '17 at 01:50

SQL Anywhere, Entity Framework 4 and Transactions

1 Answers1