Using a transaction doesn't mean you save roundtrips to the database.
string query = “insert into t1 (‘value’); insert into t1 (‘value2’);”;
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
string query = “BEGIN insert into t1 (‘value’); insert into t1 (‘value2’); END;”;
OracleCommand cmd = new OracleCommand(query, conn);
cmd.ExecuteNonQuery();
using (SqlConnection oCon = new SqlConnection(“ConnectionString”))
{
oCon.Open();
IDbTransaction transaction = oCon.BeginTransaction();
try
{
string Commands[] = new string[] {“insert…”, “insert…”};
foreach (string sqlCommand in Commands)
{
SqlCommand oCom = new SqlCommand(sqlCommand, oCon, transaction);
oCom.ExecuteNonQuery();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
// I’ve created Cities table (Sql Server) with the fields –
// 1. ID – int – PK & identity
// 2. Name – varchar(50) – Unique !
using (SqlConnection oCon = new SqlConnection(“connString”))
{
oCon.Open();
SqlTransaction transaction = oCon.BeginTransaction();
SqlCommand c1 = new SqlCommand(“Insert into cities(name)values(‘oren_test1’)”, oCon, transaction);
SqlCommand c2 = new SqlCommand(“Insert into cities(name)values(‘oren_test1’)”, oCon, transaction); // this will throw an exception for duplicated row (Cities.Name is unique)
SqlCommand[] commands = new SqlCommand[] {c1, c2};
try
{
foreach (SqlCommand command in commands)
{
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch(Exception err)
{
Console.WriteLine(“error: {0}, rollback.”, err.Message);
transaction.Rollback();
}
}//using will close the connection even in case of exception.
As you can see for yourself(try it), the second ExecuteNonQuery (for the second insert) throw me an exception about the duplicated row (unique exception) so now I’m sure that the roundtrips are NOT “saved” by wrapping the command with a transaction. It’s making a lot of sense, the DB opens a transaction while calling the BeginTransaction() method and keeping it open until we call the Commit() or Rollback() methods. In between, while calling the ExecuteNonQuery() method, the database saves the row in a temporary table just until the the transaction ends (again, via Commit() or Rollback()) and only then the rows are inserted to the “real” table (i.e Cities table).
Conclusion
One of your main concerns while developing a web application is to prevent redundant roundtrips from your application server to the database server.
While the transaction object is vital for data integrity while inserting\updating\deleting many(more than 1) rows and it can boost your request(Trying to insert 1000 rows to the table, each insert with it’s own private transaction, will be much slower than calling the same insert with one transaction for the all 1000 rows), it doesn’t mean that it’s wrapping your calls to the DB and saves those redundant roundtrips.
And if I wasn’t clear so far – You should use the first approach showed in this post.
p.s –
General tip: (need I to say?) Always check your theories before you running along and using them in your applications.