Multithreading when working with the database. Using the DBExecutor
Glossary Item Box
Introduction
Using a number of streams in working with the database via UserConnection may cause to issues in starting synchronization or committing transactions.
ATTENTION
The issue occurs during the work with the database even without direct using of the DBExecutor. For example, it can be used indirectly, through the EntitySchemaQuery.
ATTENTION
As unmanaged resources are used to work with the database, you need to wrap the DBExecutor in the using operator. Another way is to call the Dispose() method explicitly to release resources. More information about the using operator can be found in the "C# Guide” documentation.
An example of misuse
A source code fragment with incorrect usage of the the DBExecutor is given below. You cannot call the DBExecutor instance methods in the parallel threads.
// Create a parallel thread. var task = new Task(() => { // Using a DBExecutor instance in a parallel thread. using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { dbExecutor.StartTransaction(); //... dbExecutor.CommitTransaction(); } }); // Running an asynchronous task in a parallel thread. // The execution of the program in the main thread continues on. task.Start(); //... var select = (Select)new Select(UserConnection) .Column("Id") .From("Contact") .Where("Name") .IsEqual(Column.Parameter("Supervisor")); // Using an instance of DBExecutor in the main thread will cause an error, // because The instance DBExecutor is already used in a parallel thread. using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) { while (dataReader.Read()) { //... } } }
An example of correct use
A source code fragment with correct usage of the the DBExecutor is given below. The call of the DBExecutor instance methods is preformed consistently in one thread.
// The first use of the instance DBExecutor in the main thread. using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { dbExecutor.StartTransaction(); //... dbExecutor.CommitTransaction(); } //... var select = (Select)new Select(UserConnection) .Column("Id") .From("Contact") .Where("Name") .IsEqual(Column.Parameter("Supervisor")); // Reusing the DBExecutor instance in the main thread. using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) { while (dataReader.Read()) { //... } } }
