StrataFrame Forum

How to disallow user to edit a record if another user is already editing that record?

http://forum.strataframe.net/Topic7570.aspx

By Vikram Saxena - 3/18/2007

Respected Sir/Madam,





We are using SF with our project.

we just have developed a windows application that will run on different machine on a Local are network with central Database present at server. Now, as all user can add, view, edit, update all the records so i don't want any user to edit a record if any other user is already been editing that record. i just wanna show him a message when he/she clicks edit that "Any other user is already editing this records so you can't edit this record." Please tell me how can i achieve this kind of functionality using SF.



Thanks,



Aman.
By Trent L. Taylor - 3/19/2007

Well, this goes beyond concurrency.  StrataFrame has teh ability to handle concurrency so if two users modify the same data, it will raise the ConcurrencyException event (and even manage it for you if you by default).  However, if you do not want to use concurrency and just have a "hard" lock on the record when editing, you will have to add another field to the table and update that field when the end-user edits the BO.

You would have to handle the EditingStateChanged event in the BO to determine when you are editing.  Within this event if you are editing you would want to update that single field back on the database.  You will need to do this outside of the BO otherwise the state will change back.  Wheh I say outside of the BO, I just mean you will need to use a connection and command object manually.  HOwever, you will already have the connection string the in DataSources collection which can be used within the connection object.

'-- Establish Locals
Dim loCommand As New SQLCommand()
Dim loConn As New SQLConnection(MicroFour.StrataFrame.Data.Databasics.DataSources(0).ConnectionString)

loCommand.CommandText = "UPDATE MyTable SET MyField = MyValue WHERE MyPrimaryKey = MyPKValue"

'-- Open the connection
loConn.Open()

'-- Set the connection on the command
loCommand.Connection = loConn

'-- Execute the query
loCommand.ExecuteNonQuery()

'-- Close the connection
loConn.Close()

When the state goes back to Idle, you will then just reset the flag back to a "allow edit" state.  I will caution you against this type of functionality as it goes against the purpose of concurrency and can potentially leave records "locked" if there is some type of error.  I recommend using the built in concurrency rather than this methodology.

By Ben Hayat - 3/19/2007

I will caution you against this type of functionality as it goes against the purpose of concurrency and can potentially leave records "locked" if there is some type of error. I recommend using the built in concurrency rather than this methodology.




As Trent has pointed out, "Hard Lock" can eventualy drive you crazy, due to some issues remaining there. I had my share of problems many years ago, and I won't do it again.



Just my 2 cents!
By Vikram Saxena - 3/20/2007

Thanks Ben!!



now i think i should go for default concurrency model of SF but,..but i don't know how can i achieve this model in my project. can you please explain this to me...



Thanks in Advance....
By Trent L. Taylor - 3/21/2007

By default StrataFrame BOs automatically implement AllRowsOptimistic concurrency.  However, this is not the most efficient way.  The best type of concurrency is using a row version.  To do this, just add a single integer field in your table.  Once added, rebuilt your partial class for the BO so the new field is recognized.   Double-click the BO you are working on and go to the RowVersionOrTimeStamp property and set it to the name of the integer field you have just added to your table.  Next, set the UpdateConcurrencyType to OptmizsticRowVersion....and that's it!  The BOs and data access layer will handle the rest.  If you have a concurrency collision the BOs ConcurrencyException event will be raised.  By default, the UI will automatically show the concurrency exception form to the end-user,  If you wish to manage this yourself then you just set the AutoShowConcurrencyExceptions property on the form to false and manage the ConcurrencyException event yourself.
By Greg McGuffey - 3/21/2007

One other thing: the row version column needs to be 0 initially. I.e.



RowVersion int Not Null CONSTRAINT def_rowversion DEFAULT (0)
By Trent L. Taylor - 3/21/2007

Correct...NULL values are not supported on the Row Version column.  Thanks, Greg BigGrin
By Pertti Karjalainen - 3/26/2007

Here's a technique I've used with VFP:

Use a VFP "lock table" which has entries for rows that are locked, who locked them and when, and any other information you care to put in there.  Since Strataframe can open VFP databases along with any other databases, this can work in many scenarious.

Here's the simplistic pseudo code:

1. Ready to edit a row, check VFP lock table to see if that row is in there AND if it is locked by someone else.

2. If it is there and locked, do not allow concurrent editing, notify user trying to lock it

3. If it is there and not locked, lock it, write pertinent information into the lock record and open row for editing

4. If it is not there, locate another lock table record that is not locked and recycle it and go to 3.  If there is no recycleable (non-locked) record available, create a new one and go to 3.

The beauty of this approach is that it is dynamic -- if your app crashes or "forgets" to UNLOCK the locktable entry when done with it, VFP releases the lock as soon as the application exits, one way or another.  This approach avoids the cleanup mess (!), but can still accidentally leave a record locked for the duration of the session if you are don't pay attention to the unlock action in the save/discard row procedures.

Pertti

By Ivan George Borges - 3/26/2007

Hi Pertti.

For some scenarios, this looks great! I can think of one place I'm going to use it.

Thanks for sharing.