Secure Block of Ids from KeyTable for use in remote app.

We have a central database which uses a KeyTable for the allocation of entity Ids. And this is working very well.

We also have a number of handheld terminals (Android), that use WFC to talk to a service that performs its database CRUD on their behalf. To keep network traffic down we compile all the data changes in the android first, then send them to the service. As many of these changes entail creating new objects that must relate to other new objects, we need to assign keys and foreign keys within the android.

To manage this we have created a WCF method as follows. List GetIds(); This simple returns a list of Ids that the android can use. The Android app calls this whenever it is getting low on available Ids.

Within the Windows service, and in the Lightspeed model, we have overridden GenerateIds so we can insert entities that have the IDs already assigned, again this works well.

I know lightspeed does a similar thing in that it takes blocks of Ids. But I cannot find any method that gets the block of IDs. I was wondering if the method you use can be used by my code also? Obviously Lightspeed, does all the required locking, checking etc. to ensure no duplicates are possible. If possible I would rather use this, than crated an Stored Proc to get the block of IDs. Is this possible?

Thanks

The method isn't public, but really all it comes down to is, in SQL Server terms, doing a SELECT NextId FROM KeyTable WITH (UPDLOCK) followed by an UPDATE KeyTable SET NextId = .... The bulk of the method is mostly devoted to doing this in a database-independent way, handling naming strategies for the KeyTable, etc. About the only wrinkle is making sure we do it in a separate transaction from whatever is in progress on the UOW. For locking we just use the C# lock keyword so that only one thread can be accessing and updating the KeyTable at a time.

I'd be happy to provide you with the code if you think it would be useful, but to be honest so much of it is devoted to database portability that it may be more confusing than helpful...

Thanks for the prompt reply. To be honest I had fogotten about database portaibility, but that will be an issue for us also, as we provide our app with the option of using SQL Server and MySQL databases.

Therefore, if you do not mind, could you please send me a snippet of code like you use so I can implement the same, or at least something similar.

I pressuem to use this code, I would need to add KeyTable to the model, or do you do it completely outside of the model, and talk directly to the database in a seperate connection?

Many thanks

I've emailed you the LightSpeed KeyTable identity generation code. Please note that it does use LightSpeed's internal SQL provider abstraction layers so you won't be able to use it directly, but hopefully it will help make sense of the general structure of how we do it.

You would not add KeyTable to the model. KeyTable doesn't have an Id column and is not an entity. We do KeyTable selects and updates directly to the database; we do not represent them with entities. As you surmise, we use a separate connection and a separate transaction scope for the KeyTable, so for example rolling back a unit of work does not roll back any KeyTable allocations (which could have been applied to other, non-rolled back units of work).

Hi Ivan,

The code provided was extremely useful, and I have now successfully produced a static class that provides our required functionality. Whilst maintaining the safeguards you had implemented in you code.

Many thanks for you assistance.