C# and Data Concurrency

[Omatase]

I have a data in a table that I need to read once and only once. I
will have multiple instances of my application accessing this database
potentially at the same time (because my application is WCF service
hosted by IIS) but no two instances of this application should access
the same rows of data even on reads.

If I were in TSQL I could just do an XLOCK, ROWLOCK to lock the row on
a SELECT but I can't find any way to lock on a SELECT in linqtosql. I
know I can use TransactionScope to provide an isolation level but none
of the isolation levels will take an exclusive lock on a select
statement. While SERIALIZABLE seems to provide this functionality from
the description on Microsoft's site, in practice (I tested it in
Management Studio) it does not.

My questions are is there a way to take an exclusive lock on a SELECT
in pure LINQ or is there a way I can manage my concurrency directly in
the C# code? I haven't done anything with C# object transactioning and
I don't know if it can satisfy my requirement since I don't know how I
can share data across different instances of my WCF service and am not
really even sure a singleton or static class would accommodate me in
this situation.

Thanks for any help

 

[Mr. Arnold]

I have a data in a table that I need to read once and only once. I
will have multiple instances of my application accessing this database
potentially at the same time (because my application is WCF service
hosted by IIS) but no two instances of this application should access
the same rows of data even on reads.

If I were in TSQL I could just do an XLOCK, ROWLOCK to lock the row on
a SELECT but I can't find any way to lock on a SELECT in linqtosql. I
know I can use TransactionScope to provide an isolation level but none
of the isolation levels will take an exclusive lock on a select
statement. While SERIALIZABLE seems to provide this functionality from
the description on Microsoft's site, in practice (I tested it in
Management Studio) it does not.

My questions are is there a way to take an exclusive lock on a SELECT
in pure LINQ or is there a way I can manage my concurrency directly in
the C# code? I haven't done anything with C# object transactioning and
I don't know if it can satisfy my requirement since I don't know how I
can share data across different instances of my WCF service and am not
really even sure a singleton or static class would accommodate me in
this situation.

You know the whole world doesn't center around Linq-2-SQL or even ADO.NET
Entity Framework.

There is nothing wrong in using ADO.NET SQL Command Objects with a Stored
Procedure with (T-SQL) along with using Linq-2-SQL or ADO.NET Entity
Framework within the same solution or C# application.

I don't see why you couldn't also cache the data in memory on IIS so that
subsequent access of the data is from cached data by the WCF Web Service. If
the cached data is not there, then you go read it from the database and
cache it.

I don't know SQL Command Object using an sproc, a datareader is used to read
the resultset, List<t> used to hold DTO(s) Data Transfer Object with
accessor properties that represents the fields of the table row, and put
into cache.



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4471 (20090930) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Omatase]

You know the whole world doesn't center around Linq-2-SQL or even ADO.NET
Entity Framework.

What? Of course it does! It has been scientifically proven that if MS
had not created Linq 2 Sql when it did we would not now have a
replacement for the previous "sun". Whew, that was a close one!