What is the best practice to execute multiple "nonQuery" SQL operations in a transaction ?

[TheSteph]

Hi,

I need some Advice..

What is the best practice to execute multiple "nonQuery" SQL operations in
a transaction ?

1) Execute all in one "sqlCommand.ExecuteNonQuery();" :

sqlCommand.CommandText =
"DELETE CUSTOMER WHERE CUST_ID = 'ID1' \n" +
"DELETE ORDER WHERE CUST_ID = 'ID1' ";
sqlCommand.ExecuteNonQuery();
transaction.Commit();

2) Or Execute each operation independently ? :

sqlCommand.CommandText =
"DELETE CUSTOMER WHERE CUST_ID = 'ID1'";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText =
"DELETE ORDER WHERE CUST_ID = 'ID1' "
sqlCommand.ExecuteNonQuery();
transaction.Commit();

Thanks for advices and comments !


Steph.

 

[Balasubramanian Ramanathan]

1)Its better do use the first method since you are saving the trip to server

2)The alternating best way is to use stored procedures for excuting series
of sql statements