|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
All or nothing for DataAdapterI am using DataAdapter.Update to update database tables based on changes in my DataTable. How can I make sure that the whole Update operation (which can include several update, delete and insert operations) is within a transaction. If anything goes wrong, the Update should rollback all changes. Is that DataAdapter's default behavior? Thank you, Max If this is not in a transaction, then some updates could succeed and others
could fail. To put it in a transaction, begin a transaction, and assign it to the transaction property of the update/insert/delete command objects of the data adapter. Or, if you are using the command builder, to the select command before your create the command builder. Then you are responsible for committing/rolling back the transaction. Show quote "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message news:uZeE7k4QGHA.5248@TK2MSFTNGP09.phx.gbl... > Hi, > > > > I am using DataAdapter.Update to update database tables based on changes > in my DataTable. > > > > How can I make sure that the whole Update operation (which can include > several update, delete and insert operations) is within a transaction. If > anything goes wrong, the Update should rollback all changes. > > > > Is that DataAdapter's default behavior? > > > > > > Thank you, > > Max > > This is something I am always confuse about.
My assumption was that the Update method automatically uses a transaction, unless we explicitly deny that by using . Isn't it the case? If the answer is NO, then Can I use System.Transactions in ADO.NET 2.0 to do the transactions? Thank you for help, Max Show quote "Marina Levit [MVP]" <someone@nospam.com> wrote in message news:%23gSDNr4QGHA.2300@TK2MSFTNGP11.phx.gbl... > If this is not in a transaction, then some updates could succeed and > others could fail. > > To put it in a transaction, begin a transaction, and assign it to the > transaction property of the update/insert/delete command objects of the > data adapter. Or, if you are using the command builder, to the select > command before your create the command builder. Then you are responsible > for committing/rolling back the transaction. > > "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message > news:uZeE7k4QGHA.5248@TK2MSFTNGP09.phx.gbl... >> Hi, >> >> >> >> I am using DataAdapter.Update to update database tables based on changes >> in my DataTable. >> >> >> >> How can I make sure that the whole Update operation (which can include >> several update, delete and insert operations) is within a transaction. If >> anything goes wrong, the Update should rollback all changes. >> >> >> >> Is that DataAdapter's default behavior? >> >> >> >> >> >> Thank you, >> >> Max >> >> > > I don't believe it works that way. You can certainly test this, to confirm.
Yes, you can use the 2.0 transactions. I haven't had a chance to use them yet myself, just did some reading. But I think in your case it will end up just being an ADO transaction created for you behind the scenes anyway. Show quote "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message news:O64c9y4QGHA.5552@TK2MSFTNGP14.phx.gbl... > > > This is something I am always confuse about. > > > > My assumption was that the Update method automatically uses a transaction, > unless we explicitly deny that by using . Isn't it the case? > > > > If the answer is NO, then Can I use System.Transactions in ADO.NET 2.0 to > do the transactions? > > > > Thank you for help, > > Max > > > > > > "Marina Levit [MVP]" <someone@nospam.com> wrote in message > news:%23gSDNr4QGHA.2300@TK2MSFTNGP11.phx.gbl... >> If this is not in a transaction, then some updates could succeed and >> others could fail. >> >> To put it in a transaction, begin a transaction, and assign it to the >> transaction property of the update/insert/delete command objects of the >> data adapter. Or, if you are using the command builder, to the select >> command before your create the command builder. Then you are responsible >> for committing/rolling back the transaction. >> >> "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message >> news:uZeE7k4QGHA.5248@TK2MSFTNGP09.phx.gbl... >>> Hi, >>> >>> >>> >>> I am using DataAdapter.Update to update database tables based on changes >>> in my DataTable. >>> >>> >>> >>> How can I make sure that the whole Update operation (which can include >>> several update, delete and insert operations) is within a transaction. >>> If anything goes wrong, the Update should rollback all changes. >>> >>> >>> >>> Is that DataAdapter's default behavior? >>> >>> >>> >>> >>> >>> Thank you, >>> >>> Max >>> >>> >> >> > > Update does not automatically use Transactions.
In order for you to use Transaction on a DataAdapter, you can do so by creating a SqlTransaction object and assign it to the Update/Delete/InsertCommand.Transaction properties before calling "Update". Yes you could technically use System.Transactions but that would be a bad solution **unless** you a) Manage connection lifetime yourself (don't let DataAdapter do that for you). b) Work on SQL Server 2005 (not lower). - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- Show quote "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message news:O64c9y4QGHA.5552@TK2MSFTNGP14.phx.gbl... > > > This is something I am always confuse about. > > > > My assumption was that the Update method automatically uses a transaction, > unless we explicitly deny that by using . Isn't it the case? > > > > If the answer is NO, then Can I use System.Transactions in ADO.NET 2.0 to > do the transactions? > > > > Thank you for help, > > Max > > > > > > "Marina Levit [MVP]" <someone@nospam.com> wrote in message > news:%23gSDNr4QGHA.2300@TK2MSFTNGP11.phx.gbl... >> If this is not in a transaction, then some updates could succeed and >> others could fail. >> >> To put it in a transaction, begin a transaction, and assign it to the >> transaction property of the update/insert/delete command objects of the >> data adapter. Or, if you are using the command builder, to the select >> command before your create the command builder. Then you are responsible >> for committing/rolling back the transaction. >> >> "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message >> news:uZeE7k4QGHA.5248@TK2MSFTNGP09.phx.gbl... >>> Hi, >>> >>> >>> >>> I am using DataAdapter.Update to update database tables based on changes >>> in my DataTable. >>> >>> >>> >>> How can I make sure that the whole Update operation (which can include >>> several update, delete and insert operations) is within a transaction. >>> If anything goes wrong, the Update should rollback all changes. >>> >>> >>> >>> Is that DataAdapter's default behavior? >>> >>> >>> >>> >>> >>> Thank you, >>> >>> Max >>> >>> >> >> > > Thank you for help.
Could you refer me to a link that explains why System.Transaction is a bas solution? We are using Oracle 9i. Thanks again, Max Show quote "Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message news:OBLkf24QGHA.4696@tk2msftngp13.phx.gbl... > Update does not automatically use Transactions. > > In order for you to use Transaction on a DataAdapter, you can do so by > creating a SqlTransaction object and assign it to the > Update/Delete/InsertCommand.Transaction properties before calling > "Update". > > Yes you could technically use System.Transactions but that would be a bad > solution **unless** you > > a) Manage connection lifetime yourself (don't let DataAdapter do that for > you). > b) Work on SQL Server 2005 (not lower). > > - Sahil Malik [MVP] > ADO.NET 2.0 book - > http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx > ---------------------------------------------------------------------------- > > > > > "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message > news:O64c9y4QGHA.5552@TK2MSFTNGP14.phx.gbl... >> >> >> This is something I am always confuse about. >> >> >> >> My assumption was that the Update method automatically uses a >> transaction, unless we explicitly deny that by using . Isn't it the case? >> >> >> >> If the answer is NO, then Can I use System.Transactions in ADO.NET 2.0 to >> do the transactions? >> >> >> >> Thank you for help, >> >> Max >> >> >> >> >> >> "Marina Levit [MVP]" <someone@nospam.com> wrote in message >> news:%23gSDNr4QGHA.2300@TK2MSFTNGP11.phx.gbl... >>> If this is not in a transaction, then some updates could succeed and >>> others could fail. >>> >>> To put it in a transaction, begin a transaction, and assign it to the >>> transaction property of the update/insert/delete command objects of the >>> data adapter. Or, if you are using the command builder, to the select >>> command before your create the command builder. Then you are >>> responsible for committing/rolling back the transaction. >>> >>> "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message >>> news:uZeE7k4QGHA.5248@TK2MSFTNGP09.phx.gbl... >>>> Hi, >>>> >>>> >>>> >>>> I am using DataAdapter.Update to update database tables based on >>>> changes in my DataTable. >>>> >>>> >>>> >>>> How can I make sure that the whole Update operation (which can include >>>> several update, delete and insert operations) is within a transaction. >>>> If anything goes wrong, the Update should rollback all changes. >>>> >>>> >>>> >>>> Is that DataAdapter's default behavior? >>>> >>>> >>>> >>>> >>>> >>>> Thank you, >>>> >>>> Max >>>> >>>> >>> >>> >> >> > > Max,
Well I had a conversation with Jim Johnson from the System.Transactions team on his blog and then further offline with the ADO.NET team and the System.Transactions team. So the only link with half cooked information is on Jim Johnsons' blog in the comments over here - http://pluralsight.com/blogs/jimjohn/archive/2005/09/15/14838.aspx#FeedBack .. You may also find similar information on my blog as well. Unfortunately a better cooked version that is more understandable is coming up in my code-magazine article which was supposed to be in print in February but got bumped up to next month :). Anyway, so just trust me on this - you don't want to mix DataAdapter/TableAdapter with Sys.Tx - they tend to screw up on transactions. You can get around this behavior by using SQL Server 2005 and maintaining the connection lifetime yourself. - Sahil Malik [MVP] ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx ---------------------------------------------------------------------------- Show quote "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message news:uvYvy05QGHA.5092@TK2MSFTNGP11.phx.gbl... > Thank you for help. > > Could you refer me to a link that explains why System.Transaction is a bas > solution? > > We are using Oracle 9i. > > Thanks again, > Max > > > "Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message > news:OBLkf24QGHA.4696@tk2msftngp13.phx.gbl... >> Update does not automatically use Transactions. >> >> In order for you to use Transaction on a DataAdapter, you can do so by >> creating a SqlTransaction object and assign it to the >> Update/Delete/InsertCommand.Transaction properties before calling >> "Update". >> >> Yes you could technically use System.Transactions but that would be a bad >> solution **unless** you >> >> a) Manage connection lifetime yourself (don't let DataAdapter do that for >> you). >> b) Work on SQL Server 2005 (not lower). >> >> - Sahil Malik [MVP] >> ADO.NET 2.0 book - >> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx >> ---------------------------------------------------------------------------- >> >> >> >> >> "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message >> news:O64c9y4QGHA.5552@TK2MSFTNGP14.phx.gbl... >>> >>> >>> This is something I am always confuse about. >>> >>> >>> >>> My assumption was that the Update method automatically uses a >>> transaction, unless we explicitly deny that by using . Isn't it the >>> case? >>> >>> >>> >>> If the answer is NO, then Can I use System.Transactions in ADO.NET 2.0 >>> to do the transactions? >>> >>> >>> >>> Thank you for help, >>> >>> Max >>> >>> >>> >>> >>> >>> "Marina Levit [MVP]" <someone@nospam.com> wrote in message >>> news:%23gSDNr4QGHA.2300@TK2MSFTNGP11.phx.gbl... >>>> If this is not in a transaction, then some updates could succeed and >>>> others could fail. >>>> >>>> To put it in a transaction, begin a transaction, and assign it to the >>>> transaction property of the update/insert/delete command objects of the >>>> data adapter. Or, if you are using the command builder, to the select >>>> command before your create the command builder. Then you are >>>> responsible for committing/rolling back the transaction. >>>> >>>> "Maxwell2006" <alanalan@newsgroup.nospam> wrote in message >>>> news:uZeE7k4QGHA.5248@TK2MSFTNGP09.phx.gbl... >>>>> Hi, >>>>> >>>>> >>>>> >>>>> I am using DataAdapter.Update to update database tables based on >>>>> changes in my DataTable. >>>>> >>>>> >>>>> >>>>> How can I make sure that the whole Update operation (which can include >>>>> several update, delete and insert operations) is within a transaction. >>>>> If anything goes wrong, the Update should rollback all changes. >>>>> >>>>> >>>>> >>>>> Is that DataAdapter's default behavior? >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Thank you, >>>>> >>>>> Max >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics