multitier app

microsoft.public.dotnet.languages.vb

28 Mar 2006 3:53 PM

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.

28 Mar 2006 6:02 PM

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.

Show quoteHide quote

"helpful sql" <nospam@stopspam.com> wrote in message
news:%237SkJ$nUGHA.5108@tk2msftngp13.phx.gbl...
> 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.
>
>
>

29 Mar 2006 2:32 PM

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.

Show quoteHide quote

"Kevin Spencer" <kevin@DIESPAMMERSDIEtakempis.com> wrote in message
news:%2392cYHpUGHA.4608@tk2msftngp13.phx.gbl...
>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" <nospam@stopspam.com> wrote in message
> news:%237SkJ$nUGHA.5108@tk2msftngp13.phx.gbl...
>> 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.
>>
>>
>>
>
>

29 Mar 2006 5:27 PM

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.

Show quoteHide quote

"helpful sql" <nospam@stopspam.com> wrote in message
news:eH8LQ2zUGHA.4792@TK2MSFTNGP14.phx.gbl...
> 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" <kevin@DIESPAMMERSDIEtakempis.com> wrote in message
> news:%2392cYHpUGHA.4608@tk2msftngp13.phx.gbl...
>>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" <nospam@stopspam.com> wrote in message
>> news:%237SkJ$nUGHA.5108@tk2msftngp13.phx.gbl...
>>> 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.
>>>
>>>
>>>
>>
>>
>
>