|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Multiple Data Transactions in same pageoledbconnection 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 Are you using the 2.0 Framework, if so, you may just want to use a
transactionscope object, much easier. Show quote "Srini" <ls_re***@hotmail.com> wrote in message news:uDBR0nRHGHA.3448@TK2MSFTNGP10.phx.gbl... > 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 > > > 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 Show quote "W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message news:uto4IFTHGHA.3200@tk2msftngp13.phx.gbl... > Are you using the 2.0 Framework, if so, you may just want to use a > transactionscope object, much easier. > "Srini" <ls_re***@hotmail.com> wrote in message > news:uDBR0nRHGHA.3448@TK2MSFTNGP10.phx.gbl... >> 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 >> >> >> > > 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 :) -- Show quote- Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- "W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message news:uto4IFTHGHA.3200@tk2msftngp13.phx.gbl... > Are you using the 2.0 Framework, if so, you may just want to use a > transactionscope object, much easier. > "Srini" <ls_re***@hotmail.com> wrote in message > news:uDBR0nRHGHA.3448@TK2MSFTNGP10.phx.gbl... >> 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 >> >> >> > > 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. -- Show quote- Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- "Srini" <ls_re***@hotmail.com> wrote in message news:uDBR0nRHGHA.3448@TK2MSFTNGP10.phx.gbl... > 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 > > >  |
|||||||||||||||||||||||
Other interesting topics