Multiple Data Transactions in same page

[Srini]

Would this be an okay practice in an aspx page (I am using oledb)

oledbconnection cn = new .......
oledbcommand cmd = new .....
cmd.connection = cn

oledbtransaction trans = cn.begin.........
cmd.transaction = trans
cmd.commandtext = "UPDATE..xx..."
cmd.executenonquery()
cmd.commandtext = "UPDATE..yy..."
cmd.executenonquery()
trans.commit() //or rollabck
cmd.transaction = null

cmd.commandtext = "SELECT ...." //Should I create a new cmd object ?
oledbdatareader dr = cmd.executereader(........

Is it ok to use the same command object(its local to the page) for multiple
database transactions (They are sequential operations - not multithreaded) ?
Do I have to set the cmd.transaction = null after a Commit or Rollback
before using it for another another select ?

TIA

Srini

 

[W.G. Ryan - MVP]

Are you using the 2.0 Framework, if so, you may just want to use a
transactionscope object, much easier.

 

[Srini]

No I am not onto 2.0 yet.
I am just trying to understand the proper approaches for a web app. It is
more efficient to use the same object than creating a new one for each
statement and leaving bunch of objects for garbage collection instead of
one. But is this the proper approach or are there any side effects to this
approach ?

Thanks

 

[Sahil Malik [MVP C#]]

Srini,

I wouldn't write code as below. Even if it works, you are relying on the
"undocumented feature" that command objects are completely stateless - a big
risk IMO, for a very small upside. Just instantiate new objects.

 

[Sahil Malik [MVP C#]]

Yes that would be a rather cool way to do it. I just want to add a little
bit to anyone who googles or msnsearch's to this page, TransactionScope with
the same DB, multiple commands, may be more or less than same as
SqlTransaction only in case of SQL2k5. For SQL2k you would pay a serious
performance penalty.

Also, the TxScope integration is specific to the underlying provider - I see
you are using OleDb, so depending upon your exact data source, you may or
may not see results you were hoping for