CODEWITHSUNDEEP
.netvb.nett-sql
afuzzyllama
afuzzyllama

Reputation: 6548

How to view pending changes on an SQL transaction?

While working on some functions that update my database, I was writing some quick tests just to make sure my functions were executing properly. When it comes time to actually update the database I can pass a transaction to a function and then commit it:

user.Assignment("assignment value for some column in table", connection, transaction)
transaction.Commit()

I am curious if there is a way to write a quick test that can verify that the transaction will actually update certain rows before committing?

(pseudoish-code)

user.Assignment("assignment value for some column in table", connection, transaction)
If Not transaction["SomeColumn"] = "Expected value for SomeColumn"
    assert("ColumnName was not going to be updated to the expected value!")
transaction.Rollback()

Upvotes: 2

Views: 3420

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062780

As long as you are inside the connection, you can just query the data like you would normally; other callers will be blocked, but you will have full access to the modified data.

If you are using TransactionScope, this means you can just use any command/connection (to the same database) as long as that connection is created inside the TransactionScope - it will automatically enlist to the transaction (although using the same connection instance is more efficient - avoids any risk of jumping to DTC).

If you are using DbTransaction you will have to use the same connection instance, and remember to set the .Transaction appropriately on any commands you execute against the connection.

Upvotes: 3

SQLMason
SQLMason

Reputation: 3275

You can check the values by selecting them using with (nolock) from SSMS. If you'd like to view this from .NET, I believe you can change the isolation level and re-query.

Upvotes: 1

Related Questions