Print Page | Close Window

setting isolation level

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=971
Printed Date: 10-Jun-2026 at 8:33pm


Topic: setting isolation level
Posted By: Dominique
Subject: setting isolation level
Date Posted: 21-Oct-2008 at 3:40am
Hi,
I have a small transaction that should run with a high isolation level. I don't manage to set the transaction level, could you give me an example or a pointer to the documentation.
I want to do something similar to

CREATE PROCEDURE [dbo].[MyProc]
        
             (
             @serieType char(2) = null,
             @antall int = 1,
             @firstIdx int = null OUTPUT
             ) 

 SET NOCOUNT ON
 declare @current int, @maks int, @new_current int
 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   begin transaction
    SELECT @current = [currentNr] , @maks = [MaxNr]
    FROM [dbo].[SERIE]
     WHERE serieType = @serieType
     if @current is not null
          [some logic]
   commit


I tried something like:

 TransactionSettings transactionSettings = // just want to set isolation level
                new TransactionSettings(UseDTCOption.False);
           
            RdbQuery q = new RdbQuery(typeof(NrSerie));
            q.QueryStrategy = new QueryStrategy(QueryStrategy.DataSourceOnly, transactionSettings); 
            q.AddClause(NrSerieDataRow.SerieTypeEntityColumn, EntityQueryOp.EQ, typecode);
            //  --- begin transac. 
            NrSerie serie = pm.GetEntity<NrSerie>(q);
            result = serie.Counter;
            serie.Counter += 1;
           List<IdeaBlade.Persistence.Entity> l = new List<IdeaBlade.Persistence.Entity>(1);
            l.Add(serie);
            SaveResult saveOperation = pm.SaveChanges(l);
            if (!saveOperation.Ok)
            { //roll back
                pm.RejectChanges();//???
                throw saveOperation.Exception;
            }
            //commit

--
Dominique


-------------
Dominique


Replies:
Posted By: davidklitzke
Date Posted: 22-Oct-2008 at 1:06pm

If you want to do both the fetch and the save within a single transaction, this is something which we don’t directly support

If you truly need a single transaction containing both the query and save, then you will need to create your own TransactionScope and do the calls within that.  If you are 2-tier you can do this fairly easily, but if n-tier this would have to be done on the server, so you would need to call some sort of RPC method which created the TransactionScope and then did the logic.

Posted By: Dominique
Date Posted: 22-Oct-2008 at 11:41pm
Hi David,
Thank you for your answer.
It is important to do both the read and the write in the same transaction as it is used by a unique number generator (and I am on the server).
This is the only thing I ever do with this class so it's ok to fall back to ado.net.
The motivation for handling this with devforce was to avoid managing a connection string. I guess that I can quite easily get the connection string from the rdbkey but I would greatly appreciate any tips on how to build a sqlConnection string from a pm. :)



-------------
Dominique

Posted By: davidklitzke
Date Posted: 23-Oct-2008 at 9:17am
Every PM has as associated RdbKey, and every RdbKey has a connection string.

Posted By: Dominique
Date Posted: 24-Oct-2008 at 5:47am
Hi,
I post how to extract the connection string as it might be of interest for others.
  PersistenceManager manager = new PersistenceManager();
  string keyName = manager.GetDataSourceKey(typeof(MyEntity)).Name; // or "Default"
  string oleDbConnString = RdbKey.LocateRdbKey(keyName).ConnectionString;

You can then use it directly.
using (OleDbConnection sqlConn = new OleDbConnection(oleDbConnString))
{
  OleDbCommand  cmd = new OleDbCommand("Select count(*) from MyTable");
  cmd.Connection = sqlConn;
  sqlConn.Open();
    count = (Int32)(cmd.ExecuteScalar());
  sqlConn.Close();
}

NB: if you plan to use a SqlClient.SqlConnection then you must remove the "provider" part of the oledb connection string.

Hope this helps


-------------
Dominique


Print Page | Close Window