Intranet Applications Issues

I'm not sure if this is ther right forum for this but I think its a problem as much related to database programming as to ASP.net.

In fact i'm working on the development of a ASP.net LAN-based application over SQL server back-end that is being used in a multi user environment. When a user is editing a row in a table in the SQL database, I lock that record using rowlock so that no one else can have access to it. now this mechanism works perfectly. Only that, if ever a user has initiated a rowlock and then closes the aspx page through by IE, then the row is not updated and rowlock is not released (correct me if i'm wrong). Thus I believe that another user won't have access to the row unless the rowlock is released.

One good thing would have been to detect the closing event of an aspx page when IE is closed but this is not possible I think. Any suggestions how this problem can be solved or worked around?

all help appreciated,

thanx

How about this. Store your session variable in SQL aswell. Also store the last updated time. Every time the user visits any page or does anything update the session time.

Have a VB.Net application scheduled (either via scheduled tasks on the server, or have it running constantly with a timer built-in) that searches through all the sessions.

When you find a session that's too old, then unlock the row that that user was editing. Also, on your ASPX pages that are used to edit the records, use a META refresh with half the time used by your vb.net application.
That way you'll always ensure that if the user is taking a long time to edit a record, then their lock won't be removed, and if they do close down IE, the lock will be removed 1 or 2 minutes later...

Yeah I think this could be a start.

I was thinking of either including a field in the tables with SessionId and LastUpdatedDate or a lock table that would contain SessionId, TableName, TablePrimaryKey, LastUpdatedDate. Well since I have many tables the first solution would not be very neat to implement. I'll also try to have a look at the META refresh tags. I was also thinking of using a Windows Service to do the backgroud clean-up and I think this is the closest solution I got till now.

I'll try to implement this and give feedback on it.

thanx.

I wonder if maybe you can make things a little easier for yourself.

Imagine you grab a set of data and store it in an ado.net DataTable.
Even if its only one row of data (the one the user is working on).

The DataRow class inherently maintains the original values of a row before the data was changed by the current user (modified).You can use this to check for conflicts.

When the row goes to update, if the original row values from the disconnected datarow do not match what is CURRENTLY in the database, then you know some other user has changed the data during the time the current user retrieved and tried to update the data.

You would be able to give the current user the option to replace that data, or reload his data with the data NOW in the database, or even further , perform a merge of sorts.

You eliminate locking (not sure if that is possible by your design goals), and give the user more of an oppurtunity to intelligently decide what action to take.

Well I must say that your proposed solution seems very interesting on a programmer point of view, it relieves the overhead of using rowlocks and simplifies a lot of work. but as you know, users are not very fond of such things. they tend to get confused easily and the first solution for me seems more appropriate im my case for the time being.

Any other solutions welcomed.

thanx.