DevForce Community Forum : setting isolation level

setting isolation level : Hi,I post how to extract the connection...

Fri, 24 Oct 2008 05:47:13 -700

Author: Dominique
Subject: 971
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

setting isolation level : Every PM has as associated RdbKey,...

Thu, 23 Oct 2008 09:17:03 -700

Author: davidklitzke
Subject: 971
Posted: 23-Oct-2008 at 9:17am

Every PM has as associated RdbKey, and every RdbKey has a connection string.

setting isolation level : Hi David,Thank you for your answer....

Wed, 22 Oct 2008 23:41:27 -700

Author: Dominique
Subject: 971
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. :)

setting isolation level : If you want to do both the fetch...

Wed, 22 Oct 2008 13:06:39 -700

Author: davidklitzke
Subject: 971
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.

setting isolation level : Hi,I have a small transaction...

Tue, 21 Oct 2008 03:40:51 -700

Author: Dominique
Subject: 971
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(q);
            result = serie.Counter;
            serie.Counter += 1;
           List l = new List(1);
            l.Add(serie);
            SaveResult saveOperation = pm.SaveChanges(l);
            if (!saveOperation.Ok)
            { //roll back
                pm.RejectChanges();//???
                throw saveOperation.Exception;
            }
            //commit

--
Dominique