Multiple SQL Insert statements in a single ADO.NET Command Object call 
Author Message
 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
 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
 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
 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
 
 [ 4 post ] 

 Relevant Pages 

1. Is their a way of inserting multiple rows via a single SQL statement

2. multiple inserts in a single sql statement

3. How can I insert multiple rows in a single SQL statement

4. How can I insert multiple rows in a single SQL statement

5. Open multiple recordsets using single parameterized command object

6. Help: multiple select statements in SQL stored proc called by ADO

7. Parameterized SQL statements with ADO.Command object

8. Calling a sproc with ADO command object?

9. ADO command parameter object and stored procedure calls from VC

10. Problem executing sp multiple times via ADO command object

11. Returning key field value from an ADO.Command Insert statement


 
Powered by phpBB® Forum Software