Concurrency question

[Bill]

Here's the basic problem pertaining to the question to
come.

Using Access Database with the following tables

Customers
-CustomerID (primary key)
-Customer name,address,phone

Orders
-OrderID (primary key)
-CustomerID (referential key)
-Order Data

OrderDetails
-OrderDetailID (Primary key)
-OrderID (referential key)
-Order Details

Ok, on my data windows form for order entry the first
thing that is required is the phone number. Upon getting
this info the Customer Table is searched for an existing
matching record. If found the form is updated with
Customer Info.

My question is in an multiuser enviorment with ADO.NET
how do I handle the concurrency problems within this
business logic.

If using ADO.NET disconnected architecture when User "A"
starts to enter orders a snapshot is created of the
existing data. User "A" enters a new (or several new)
order which creates a new Customer Record. At the same
time User "B" is doing the same thing.

My question is since a new CustomerID is created with
each new record added to Customer Table which is tied to
the Order Table when the database is updated how is this
problem handled?

Any help would be appreciated.

Thanks

 

[Miha Markic]

Hi Bill,

To make it short:
In dataset set primary keys to autoincrement with negative numbers.
So, when you add new user to dataset it will have negative id.
The same negative pk will use child tables.
When saving data to database, the pk will be autogenerated and will replace
the negative number.

So, make sure that pk is autogenerated on both program and database sides.
Update the pk in dataset after insert.

Read more in
Creating and Using DataSets
..net help chapter.

 

[Rob]

Bill,

Here is an alterative we used because our application must support
Oracle and MSS. Hopefully it will work for you.

We created a CAKE (consecutive autoincrementing key table) to handle
this.

This table lives on the DMBS with the following structure:

TableName Varchar(10)
LastKeyValue Numeric(10)

Now when we need a pk value for the new row(s) we query the database
CAKE table for the lastkeyvalue that was used and increment.

Regards,
Rob Panosh

 

[Kathleen Dollard]

Rob,

How quickly are you able to save after retrieving the primary key? Are you
doing this in a stored procedure or do you lock the CAKE record?

Kathleen

 

[Rob]

Kathleen,

How quickly are you able to save after retrieving the primary key?

Extremely fast ... We essentially wrote a Function in our Business
Object layer to get a single value (header row) or a range of values
(child rows). The only downfall to this is once you retrieve a key
value from the CAKE they are used if you save fails.

doing this in a stored procedure or do you lock the CAKE record?

CAKE table record is locked via transaction.

We have been using this logic for 8 years now with 50+ customers that
have user base of from 5 to 500 concurrent users.

Regards,
Rob Panosh
Advanced Software Designs

 

[Kathleen Dollard]

Rob,

Cool. I just thought it worth adding to your message whether/how you were
handling concurrency and whether you're allowing holes in your sequence.

 

[Rob Panosh]

Kathleen,

I noticed your title "Author Code Generation in Microsoft .NET" which brings
me to ask you the following question.
If I create a library (assembly) using VB.Net and want to publish it. Is
there a way in VS.NET to encrypt my assembly to keep users from decompiling
my code? What are my options to keep my code safe?

Thanks,
Rob Panosh

 

[Scott Allen]

Rob:

About the best you can do is "obfuscate" your assembly. VS.NET 2003
ships with Dotfuscator Community Edition, and you can get more
information here:

Thwart Reverse Engineering of Your Visual Basic .NET or C# Code
http://msdn.microsoft.com/msdnmag/issues/03/11/netcodeobfuscation/default.aspx

 

[Kathleen Dollard]

Rob,

You can make it harder as Scott pointed out. But you can reverse engineer
most anything, including assembly language. If you're trying to protect
secrets like passwords, you can play some encryption games, although that
isn't my specialty. I've only seen one company that protected its algorithms
fully. It was years ago in the commodities trading industry and they were
disconnected and their programmers couldn't even eat lunch with the rest of
IT. I don't know how they learned anything and wonder if they were just
covering for Magic 8-ball use <g>.

Anyway. If your algorithms are truly secret, don't distribute them, but keep
them on your own server via webservice. But assess your real risk. If you're
just worried about someone grabbing general ideas, your database will
probably tell them as much faster.

It's a real problem. A high level langauge produces predictable code, so a
decompiler can be written. .NET does make this easier, unfortunately.