Selecting / Update records - Concurrancy
I'm new to Visual Studio 2005 (Visual Basic). I'm an Oracle guy but I do allot of development in MS Access.
I don't want to use ADO.NET's generic behaviour for record locking, etc. What I'd really like to do is to have the same behaviour as Access has where if a user changes a field, the database locks the record. I was wondering if you had any sample code that would allow me to set up a form that has a combo box on the top of the form where the user selects a record. Then the database fetches the row and displays the data on the screen. If the user changes the data, the form locks the record.
What's the best way to accomplish this?
I've heard that this can be accomplished using transaction control but I haven't found any form / coding samples.
Thank you for your time and consideration.
Regards,
Dave
[882 byte] By [
Dave9999] at [2007-12-25]
I can think of no "built-in" way to accomplish this.
You could add a column to your datatable containing currentlyOpenedBy.
So when a user selects a record it asks the database if it is currently openened, if not then open and update it.
Dave,
What you are asking for is the default behavior anyway. ADO.NET respects the underlying database's default isolation level in all transactions - in case of Oracle it is ReadCommitted. Under that, any modified record will be locked for reads by other transactions running in isolation levels other than read uncommitted.
So basically - if you start a transaction, and keep the connex-open (and the tx-active), you have already acheived what you are trying to suggest above. That isn't great design - but hey, if you wanna shoot yourself, feel free. ;-)
Sahil Malik
http://blah.winsmarts.com
Hi Sahil,
I'm not looking to shot myself but what I would like if some could explain another way to extract the data. Perhaps do the optimistic locking but implement it in Code. I was speaking to someone at MS and they said that writing code was a better way to do it rather then relying on dataset objects (default).
Would you have a sample form you might be able to share with me? Something that retreives a single record, allows for the data to be modified and have the form check some to ensure the record is not already updated by someone else and write the data back?
Thoughts?
Thank you!
Dave
Dave,
The answer to that is a bit bigger than I can fit into a single reply. At the very basic you would need to read up on the disconnected architecture, transactions basics, and concurrency basics. Don't worry - it isn't overwhelming, just some theory you need to get over before coming up with a proper disconnected architecture.
If you want your app to scale - disconnected architecture is probably a cheaper way to get there. So if scalability is not a concern - don't bother about all this.
Regards specific examples, I have blogged about Oracle specific examples numerous times, but I'll repeat here in short, the best way to implement concurrency in SQL Server is TimeStamp, and the best way to implement concurrency in Oracle is ORA_ROWSCN. Essentially the concept is the same - some column (pseudo or not), that changes everytime a DML is executed on the row. Thus, your disconnected data cache could check for that column value, and see if it has changed since the last time you read it (thus - has the record changed?).
Usually this is done by modifying your Where clause, and checking the results of ExecuteNonQuery (exepcted # of rows modified = 1, but if the row got modified, the return will be 0 - and you can throw an optimistic concurrency exception).
Finally - you must also consider the various user experience scenarios. A user, who enters data for 15 minutes, to be told that the data is "out of date" will probably break his keyboard in agony. There are some tricks to get around these issues - that I have discussed in chapter 10 of my book. My book is somewhat SQL Server specific, but I have shown oracle specific differences as necessary.
Anyway, hope this was some help :)
Sahil Malik
http://blah.winsmarts.com
Dave,
Use a timestamp in your database table. It is specifically desgined to meet this type of scenario.
Hi,
The backend database is SQL Server 2005. I commented on Oracle cause that was my main programming language before giving VB / SQL a try.
Thanks
Hi,
Do you have a sample on how I can do this?
What's the file type I should use for this field?
What's the best way to populate / update the field?
Thank you
Hi,
Do you have a sample on how I can do this?
What's the field type I should use for this field?
What's the best way to populate / update the field?
Thank you
Hi,
I've heard about your book and I'm looking at getting it. The problem is I need to feel comfortable about using VB before I leave for England next week.
BTW, there may be some confusion, the database I'm using for this project is SQL Server 2005. Also this is a back end payroll application so there won't be a ton of users, perhaps 20 or less and 10 of them will be readers, not writers of data.
May I ask you, how do I handle data grids if I don't use datasets. Do I have to populate them manually? Would you have a sample? How do you (I) handle changes in data grids. I would prefer use code to read and write the data.
I will use the timestamp method as you suggest. Do I have to do the same for data grids? How would I know a user changed a perticular row?
Thank you.
Dave
Hi Again,
I will get your book but in the meantime, would you be so kind as to give me one example of how keep users from breaking their keyboards?
Many Thanks!
Dave
A timestamp is a field that fills itself. You can only have one timestamp per table.
This value will change if you update a record. You have to create a update query that takes the current timestamp as well as the primairy key into account. This way when users try to update a row with the wrong timestamp you will get 0 records affected.
Here is an example of an update query with a timestamp
"UPDATE Customer SET Name = @Name WHERE ID = @ID AND timestamp = @timestamp;"
Once you have sucsesfully updated a row, you have to read the new timestamp into your DataRow.
Hope this helps.
Hi,
Yes, this does help allot. What field type would I read the timestamp back in as in VB?
i notice that I can't see the data in the field timestamp in the table when using SQL Server Management Studio. Is this normal?
Thank you very much!
Hi Again,
Could you be so kind as to give me some advise on using datagrids? Can I populate them programmatically and do you have an example?
How do you handle updates with datagrids?
I saw some examples where they were using 'DataAccessLayer'. Seems kind of complicated, would you have a good example?
Thank you,
Dave
