Database, Multiple SQL Insert statements in a single ADO.NET Command Object call

Multiple SQL Insert statements in a single ADO.NET Command Object call

Author	Message
jps #1 / 4	Multiple SQL Insert statements in a single ADO.NET Command Object call The Data Tier in our multi-tier application has a C# method which passes 3 SQL Insert statements, separated by semi-colons, prefixed by a SQL Begin Tran, interspersed with if-needed SQL Rollback statements after each Insert, and finally suffixed by a if-needed SQL Rollback or Commit statement, to an ADO.NET Command Object. In other words, the string passed to the Command object is of the form (ignore the line feeds): BEGIN TRAN; INSERT statement 1; INSERT statement 2; INSERT statement 3; However, we found that if we excluded the BEGIN TRAN and ROLLBACK / COMMIT statements, the ADO.NET Command object nevertheless appeared to treat the 3 Insert statements as part of a transaction: they all succeeded or failed as a unit. Is this expected behaviour? Are we guaranteed transaction semantics whenever an ADO.NET Command Object is instantiated with a string comprising multiple SQL statements separated by semi-colons? Thanks, jps
Sat, 06 Nov 2004 02:35:56 GMT

Val Mazu #2 / 4	Multiple SQL Insert statements in a single ADO.NET Command Object call Hi, Code looks fine, but I think in your case you need to move all SQL statements into stored procedure. -- Val Mazur Microsoft MVP Quote: > The Data Tier in our multi-tier application has a C# > method which passes 3 SQL Insert statements, separated by > semi-colons, prefixed by a SQL Begin Tran, interspersed > with if-needed SQL Rollback statements after each Insert, > and finally suffixed by a if-needed SQL Rollback or Commit > statement, to an ADO.NET Command Object. In other words, > the string passed to the Command object is of the form > (ignore the line feeds): > BEGIN TRAN; > INSERT statement 1; > INSERT statement 2; > INSERT statement 3; > However, we found that if we excluded the BEGIN TRAN and > ROLLBACK / COMMIT statements, the ADO.NET Command object > nevertheless appeared to treat the 3 Insert statements as > part of a transaction: they all succeeded or failed as a > unit. Is this expected behaviour? Are we guaranteed > transaction semantics whenever an ADO.NET Command Object > is instantiated with a string comprising multiple SQL > statements separated by semi-colons? > Thanks, > jps
Sat, 06 Nov 2004 14:43:09 GMT

jps #3 / 4	Multiple SQL Insert statements in a single ADO.NET Command Object call I don't understand why we should be forced to use a stored proc. We do not use one because the SQL Insert strings are determined at run-time, not a' priori. Of course, this problem can be addressed by passing parameters to the stored proc. at run-time; but, again, please convince me why we MUST use a stored proc. to be guaranteed transaction semantics. Thanks, jps Quote: >-----Original Message----- >Hi, >Code looks fine, but I think in your case you need to move all SQL >statements into stored procedure. >-- >Val Mazur >Microsoft MVP >> The Data Tier in our multi-tier application has a C# >> method which passes 3 SQL Insert statements, separated by >> semi-colons, prefixed by a SQL Begin Tran, interspersed >> with if-needed SQL Rollback statements after each Insert, >> and finally suffixed by a if-needed SQL Rollback or Commit >> statement, to an ADO.NET Command Object. In other words, >> the string passed to the Command object is of the form >> (ignore the line feeds): >> BEGIN TRAN; >> INSERT statement 1; >> INSERT statement 2; >> INSERT statement 3; >> However, we found that if we excluded the BEGIN TRAN and >> ROLLBACK / COMMIT statements, the ADO.NET Command object >> nevertheless appeared to treat the 3 Insert statements as >> part of a transaction: they all succeeded or failed as a >> unit. Is this expected behaviour? Are we guaranteed >> transaction semantics whenever an ADO.NET Command Object >> is instantiated with a string comprising multiple SQL >> statements separated by semi-colons? >> Thanks, >> jps >.
Sun, 07 Nov 2004 02:00:58 GMT

Val Mazu #4 / 4	Multiple SQL Insert statements in a single ADO.NET Command Object call Hi, In your case ADO executes SQL statements one-by-one. In case of error in first statement it will rollback transaction and will execute next INSERT statement, which will insert (or fail) data. This happens because you cannot specify RETURN inside your batch to cancel execution of the rest of your batch. If you will place same code into SP, then you can add RETURN as a statement in case of error. It will allow you to exit from SP and do not execute rest of staff -- Val Mazur Microsoft MVP Quote: > I don't understand why we should be forced to use a stored > proc. We do not use one because the SQL Insert strings are > determined at run-time, not a' priori. Of course, this > problem can be addressed by passing parameters to the > stored proc. at run-time; but, again, please convince me > why we MUST use a stored proc. to be guaranteed > transaction semantics. > Thanks, > jps > >-----Original Message----- > >Hi, > >Code looks fine, but I think in your case you need to > move all SQL > >statements into stored procedure. > >-- > >Val Mazur > >Microsoft MVP > >> The Data Tier in our multi-tier application has a C# > >> method which passes 3 SQL Insert statements, separated > by > >> semi-colons, prefixed by a SQL Begin Tran, interspersed > >> with if-needed SQL Rollback statements after each > Insert, > >> and finally suffixed by a if-needed SQL Rollback or > Commit > >> statement, to an ADO.NET Command Object. In other words, > >> the string passed to the Command object is of the form > >> (ignore the line feeds): > >> BEGIN TRAN; > >> INSERT statement 1; > >> INSERT statement 2; > >> INSERT statement 3; > >> However, we found that if we excluded the BEGIN TRAN and > >> ROLLBACK / COMMIT statements, the ADO.NET Command object > >> nevertheless appeared to treat the 3 Insert statements > as > >> part of a transaction: they all succeeded or failed as a > >> unit. Is this expected behaviour? Are we guaranteed > >> transaction semantics whenever an ADO.NET Command Object > >> is instantiated with a string comprising multiple SQL > >> statements separated by semi-colons? > >> Thanks, > >> jps > >.
Sun, 07 Nov 2004 02:10:19 GMT

Page 1 of 1

[ 4 post ]