Managing Transaction using ADO.net

[Guest]

Hello Everyone,
I got flexgrid with around 500 rows and 24 columns which gets populated
using data from various tables. User can change any cell values at anytime. I
want to save those values in DB at run time but I don't want to commit, till
User clicks on SAVE button and should Rollback if user clicks Cancel. In the
mean time other users should able to Query those tables.

In this case I thought, I had two options.
Option 1: was to save all new values in string variable in XML format n then
pass XML string to Store Proc in DB using DB data type Text. Which works
absoulately fine but if user changes many cell values SP sometime takes
around 4-5 seconds to update DB, which is not acceptable. Of course I m
trying to tune SP for better peformance but I dont' think it will improve
much due to amount of data I m updating or inserting.

Option 2: which is to use UPDLOCK - I had written code as follows:
Dim myConnection As SqlConnection = New SqlConnection("Data
Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;")
Dim myTrans As SqlTransaction
Dim objDR As SqlDataReader
Dim myCommand As SqlCommand
Dim sSQL As String

sSQL = "Insert into Region WITH (UPDLOCK) (RegionID,
RegionDescription) VALUES (100, 'TEST')"
myConnection.Open()
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted)
myCommand = New SqlCommand(sSQL, myConnection)
myCommand.Transaction = myTrans
objDR = myCommand.ExecuteReader()

The problem I am facing is till I commit or rollback this transaction other
users can't query this table, which of course is not acceptable.

But if I mimic above process in SQL Query Analyzer I can write Insert/Update
Queries with UPDLOCK and can still manage to Query table in other connections.

I don't want to use NOLOCK in SQL, in order to avoid Dirty pages.

I am using SQL 2000 and Visual Studio.net(2003)

Any suggestions......Many thanks in advance.

 

[Imran Koradia]

Since you're using ReadCommitted, I think you shouldn't need to use UPDLOCK
in your insert statements. I could be wrong though. Did you try without the
UPDLOCK in option 2?

Also, maybe this would be of some help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/transact.asp


hope that helps..
Imran.