SQLDatabase.NET

SQL Transactions BEGIN/COMMIT

Each Transaction is an execution of single SQL statement against the database and Transactions are SQL Statements as sequences of work accomplished in some logical order. A transaction usually apply of one or more changes to the database. For example, if you are inserting, updating and deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors. SQL database library supports running multiple transactions against single connection in ACID compliance manner.

Library supports transactions against a single connection either through SQL Statement or through SqlDatabaseTransaction class. Only one transaction can be active against the connection but multiple command objects can use the same transaction even when command has been completed or disposed.

ACID (Atomicity, Consistency, Isolation, Durability) are properties of database system to protect the integrity of data. The presence of four properties: atomicity, consistency, isolation and durability are developed to ensure that a database transaction is completed in a timely manner with a guarantee of validity even in the case of an error. Even if an error occurred after one command has been executed it can be reversed by the connection if ROLLBACK is requested.

Following is a short list of Transaction related commands when running in Explicit mode.

  • BEGIN − starts a new transaction.
  • COMMIT − is used to save all the changes.
  • ROLLBACK − to roll back entire changes since BEGIN.
  • SAVEPOINT − creates points within the transaction in which to ROLLBACK or COMMIT.
  • RELEASE − only used with SAVEPOINT and is equivalent of COMMIT for a SAVEPOINT.

Transactions can be started manually (Explicit mode) using the BEGIN command. Such transactions persist until the COMMIT or ROLLBACK command. Once the transaction starts, NO changes can be made to the database except within a transaction. If there are any pending write operation through threading or another transaction then transaction may not start and COMMIT will also fail. The statement END TRANSACTION is an alias for COMMIT and such will also generate an error by the library. When using multiple threads it is a good practice is to use only BEGIN/COMMIT and not use save points as nested commands.

Transaction can also speed up INSERT statement since all the inserts are committed at once. For code example see Example code below and .Net examples on github.

C# Transaction Example

SqlDatabaseTransaction class has IsOpen (Boolean) property which can indicate if transaction is active or open

static void SimpleTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{

cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();

SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;

try
{
for (int i = 0; i < 1000; i++)
{
	cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
	cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Console.WriteLine(sqlex.Message);
}

cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Table Record Count using COUNT(*) : {0}", cmd.ExecuteScalar());

//starting and committing transaction through SQL
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
// Your code and commands can reside here to run in a transaction.
cmd.CommandText = "COMMIT"; 
// OR cmd.CommandText = "ROLLBACK"; 
cmd.ExecuteNonQuery();
}
}
}

Named transactions similar to other database systems are not supported only save points with name is supported and SAVEPOINT can behave like nested transaction. When a SAVEPOINT is the outer most savepoint and also it is not within a BEGIN/COMMIT block then the behavior is the same as BEGIN DEFERRED TRANSACTION. Transactions provides ability to return back to a safe database state if an error occurs in the middle of your code without compromising data integrity.