Databases Reference
In-Depth Information
actually involve? The database must write to disk every modification made by a
transaction to the database. This is usually a sequential write to a journal file (or
log); nevertheless, it involves expensive disk I/O.
In ADO.NET, the default transaction commit mode is auto-commit. In
auto-commit mode, a commit is performed for every SQL statement that
requires a request to the database (
Insert
,
Update
,
Delete
, and
Select
state-
ments). When auto-commit mode is used, your application doesn't control when
database work is committed. In fact, commits commonly occur when there's
actually no real work to commit.
Some databases, such as DB2, don't support auto-commit mode. For these
databases, the data provider sends a commit request after every successful opera-
tion (SQL statement). The commit request equates to a network round trip
between the provider and the database. The round trip to the database occurs
even though the application didn't request the commit and even if the operation
made no changes to the database. For example, the data provider makes a net-
work round trip even when a
Select
statement is executed.
Let's look at the following code, which doesn't turn off auto-commit mode.
Comments in the code show when commits occur if the data provider or the
database performs commits automatically.
// 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;
