ADO.net Auto-Commit On

// For conciseness, this code omits error checking
 
// Allocate a Command object
cmd = conn.CreateCommand();
 
// Bind parameters
cmd.Parameters.Add("id", DB2DbType.Integer);
cmd.Parameters.Add("name", DB2DbType.VarChar);
cmd.Parameters.Add("name", DB2DbType.Integer);
 
// Prepare an INSERT statement for multiple executions
sql = "INSERT INTO employees VALUES(?, ?, ?)";
cmd.CommandText = sql;
cmd.Prepare();
 
// Set parameter values before execution
cmd.Parameters[0].Value=20;
cmd.Parameters[1].Value="Employee20";
cmd.Parameters[2].Value=100000;
 
cmd.ExecuteNonQuery();  
 
// A commit occurs because auto-commit is on
 
// Change parameter values for the next execution
cmd.Parameters[0].Value = 21;
cmd.Parameters[1].Value = "Employee21";
cmd.Parameters[2].Value = 150000;
 
cmd.ExecuteNonQuery();  
 
// A commit occurs because auto-commit is on
 
// Execute a SELECT statement. A prepare is unnecessary
// because it’s executed only once.
sql = "SELECT id, name, salary FROM employees";
cmd.CommandText = sql;
 
// Fetch the data         
dataReader = cmd.ExecuteReader();
while (dataReader.Read()) {
   System.Console.WriteLine("Id: " + dataReader.GetInt32(0) +
                            " Name: " + dataReader.GetString(1) +
                            " Salary: " + dataReader.GetInt32(2));
}
 
// Close the DataReader
System.Console.WriteLine();
dataReader.Close();  
 
// Whether a commit occurs after a SELECT statement
// because auto-commit is on depends on the provider.
// It’s safest to assume a commit occurs here.
 
// Prepare the UPDATE statement for multiple executions
sql="UPDATE employees SET salary = salary * 1.05 WHERE id=?";
cmd.CommandText = sql;
cmd.Prepare();
 
// Execute the UPDATE statement for each
// value of index between 0 and 9.
for (int index = 0; index < 10; index++) {
   cmd.Parameters[0].Value = index;
   cmd.ExecuteNonQuery();  
 
// Because auto-commit is on, a commit occurs each time
// through loop for total of 10 commits.
 
}
 
// Execute a SELECT statement. A prepare is unnecessary
// because it’s only executed once.
sql = "SELECT id, name, salary FROM employees";
cmd.CommandText = sql;
 
// Fetch the data
dataReader = cmd.ExecuteReader();
while (dataReader.Read()) {
   System.Console.WriteLine("Id: " + dataReader.GetInt32(0) +
                            " Name: " + dataReader.GetString(1) +
                            " Salary: " + dataReader.GetInt32(2));
}
 
System.Console.WriteLine();
 
// Whether a commit occurs after a SELECT statement
// because auto-commit is on depends on the provider.
// It’s safest to assume a commit occurs here.
 
// Close the DataReader
dataReader.Close(); 
}
finally {
   dataReader.Close();
 
   cmd.Dispose();
}