How I can set Isolation level for DbCommand

Topics: Data Access Application Block
Nov 9, 2011 at 2:55 PM

How I can set isolation level for DbCommand using enterpire libratay


My code now looks like 


private Database baza = EnterpriseLibraryContainer.Current.GetInstance<Database>("ApplicationServices");

public DataSet pds ()


DataSet ds = new DataSet();

Data.Common.DbCommand konada = null;

konada = baza.GetStoredProcCommand("art_bat_art");


/*-- IF I Try to set isolation level --*/

konada.Transaction.IsolationLevel = System.Data.IsolationLevel.ReadUncommitted;

/*-- I get error:'System.Data.Common.DbTransaction.IsolationLevel' cannot be assigned to --*/
ds = baza.ExecuteDataSet(konada); 
return ds;

I have same problem if i try to set ConnectionTimeout.
What is best practice to set ConnectionTimeout and Isolation lever in Enterprise Library

Thank you in advanced


Nov 10, 2011 at 1:21 AM

IsolationLevel is read only so you cannot assign to it (as the compiler told you!).  

If you are dealing with transactions then the best practices are to use the System.Transactions namespace.  It will allow you to specify the IsolationLevel:

var transOptions = new System.Transactions.TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted };
using (var scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.RequiresNew, transOptions))
    var db = EnterpriseLibraryContainer.Current.GetInstance();
    using (var cmd = db.GetStoredProcCommand("art_bat_art"))
        return db.ExecuteDataSet(cmd);
Another way to do the same thing would be to manually ExecuteNonQuery with
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" (or whatever isolation level you want) and then perform your operations.
If you set the isolation level and are using pooling (which you should) with this approach you should set the isolation level on all calls according to this KB article
because the isolation level is kept is for the duration of the connection.

In terms of timeouts, ConnectionTimeout is the timeout when connecting to the database.  You actually want to set the CommandTimeout.
Based on your code you can just set the value on the command directly:
konada = baza.GetStoredProcCommand("art_bat_art");
konada.CommandTimeout = 600; // 10 minutes
ds = baza.ExecuteDataSet(konada);

Randy Levy
Enterprise Library support engineer