SqlTransaction Record Not Found

[Joe Rigley]

Hi All,

I am using a SqlTransaction object to process a group of database insert /
update statements on Sql Server 200 SP4 to complete a business process.

Directly after the Commit method is issued, I perform a select on one of the
tables that was inserted into during the SqlTransaction process.
Unfortunately, the select statement does not return any data that was just
inserted. I have checked the SQL statement syntax and it is valid. (I run
it in the Query Analyzer afterwards and data is returned.) No error is
being thrown either. It seems to be a timing issue because I will use the
Query Analyzer with the same Sql Select statement and the data is returned.

In the ASP / ADO .NET code, I have tried using the same db connection object
that the SqlTransaction object used as well as a new one and that made no
difference. I am using a new command object and attempt to pull the row of
data back with a SqlDataReader.

Does any one know why I this is happening? Any and all advice would
appreciated.

Thanks,
-Joe

 

[Mary Chipman [MSFT]]

I'd recommend creating a stored procedure that implements the
transaction (see the BEGIN TRANSACTION topic in SQL BOL). Once you've
committed the transaction, select the relevant rows and return them to
the client as a result set. That way you confine the transaction
boundary to the server. Implementing explicit transactions on the
client is always tricky and can lead to other problems besides the one
you are seeing, such as deadlocks and blocking issues when locks are
held too long.

--Mary

 

[Joe Rigley]

Mary,

Great idea... I can't believe I missed going that route initially. Much
easier and places all of the code on to the DB, where it should be.

Much appreciated...
-Joe