multitier app

[helpful sql]

Hi,
I am new to multitier app development. I am working on a windows app
that will have business layer and data access layer components. When users
log into the client app, it connects to the business layer to get a list of
records that it then uses to populate a datagrid. The business layer
component in turn gets these records from Sql Server database using the data
access layer.
When a user highlight any record displayed in the datagrid, I need to
lock that record for editing and stop other users from editing that record.
My question is how do I flag that record to indicate other users that this
record is being edited by another user. I was first thinking of having a
Flag field in the underlying table and update the Flag field in the table
everytime the user highlights a row. But then I thought this would involve
too many unnecessary read/write operations on the table. So I am thinking of
maintaining the list of rows in the business layer and flag them only on the
business layer without having to flag them in the Sql Server table.
Everytime the user highlights a row it will check the Flag value of that
record on the business layer and if it is not being edited by any other user
it will lock that record and allow the user to edit it.

Is this possible? Do I need to use .Net Remoting for this? Are there any
better options?

Thanks in advance.

 

[Kevin Spencer]

I think you'll find the following article helpful:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp

This discusses all of the issues involved, including record locking.
Basically, it's not practical to keep each client up-to-date about what
records are being edited at any given time, and the recommended strategy is
to use either optimistic or pessimistic concurrency in your app. With
pessimistic concurrency, the record is locked in the database when a client
begins to edit that record. No other client will be able to lock that record
for editing until it is unlocked. The downside is that the connection to the
database must remain opened while the user is working with the record. With
optimistic concurrency, the record is not locked, but when the update
attempt is made, it fails if the record was changed since fetched by that
client.

In both cases, the general strategy is to use exception handling to deal
with the issue. In the case of pessimistic concurrency, an exception will be
thrown if a user attempts to get a lock on the record. With optimistic
concurrency, an exception will be thrown when the user attempts to update
the record.

The article provides a great deal more information, but that's a summary for
you.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

 

[helpful sql]

Thanks for your reply.

The records are scheduled call activity records and we want to show the same
list of calls to many users and make sure that no two users are calling the
same person.

 

[Kevin Spencer]

The records are scheduled call activity records and we want to show the

same list of calls to many users and make sure that no two users are
calling the same person.

In that case, you should find the article extremely helpful. Good luck!

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.