none
n-tier solution + Dal Layer using LINQ to SQL + TransactionScope RRS feed

  • Question

  • Hi,

    I have a solution with those tiers:

    - Interface
    - Business
    - Data (Using LINQ to SQL to access SQL Server 2005)

    When I do not use TransactionScope, everything works fine. But, when I use TransactionScope, my DataContext Connection dont close and dont dispose, and the connection pool reachs the max open connections. So, I get an exception.

    I have a process in my project which executes a great number of database access, cause it is a looping inserting and updating a lot of rows in a lot of tables.

    I need to execute this process in the code behind (interface tier), cause some business decitions are called in a great number of business classes.

    I made all kind of combinations to try to not increase my connection pool but unfortunatelly nothing happens. Tried to pass the sql connection as parameter to initiate datacontext, modified connectionstring including pooling directives; used Enlist directive in connectionstring with true and false (with MSDTC service running); close and dispose the datacontext connection manually etc.

    I'm here to ask you if there is a solution to this situation.

    I need to respect project layers, so, its necessary dont access database from interface and business layers. My Dal layer need to be reached only from business layer.

    Thanks,
    Marcelo.


    Olivertech
    Friday, February 12, 2010 6:42 AM

All replies

  • Hi Oliverteh,

    1. Are you using Transaction as below, if not try using it. It ensures the transaction object is disposed properly
        using (TransactionScope scope = new TransactionScope())

     

         {

              /* Perform transactional work here */

                  

               scope.Complete();

         }

     

    2. Do not try to reuse the existing DataContext by changing  sql connection, create a new instance of DataContext by using new keyword.

     

    Hope this will solve your problem.

    Regards

    Chitrsen Aujikar



    Friday, February 12, 2010 8:37 AM
  • Hi Chitrse,

    Yes, I am using this structure of "using (TransactionScope....".

    I really dont know what is the problem, cause I used all the things needed to close my connection, but nothing works. As I said before, I have a solution with layers, and I try to respect each layer objectives, to isolate code as many as I can.

    I tried almost everything I can, to reduce the number of connections that is opened during my process. Let me show you:

    I am using this code in my mais method (its a command application):


     ....
    using (TransactionScope scope = new TransactionScope())
    {
      using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString))
      {
        connection.Open();
        using (UploadODiaDataContext dc = new UploadODiaDataContext(connection))
        {
          ProcessarArquivo(xmlNodes, publicacaoDTO, xmlDoc, nomeArquivo);
        }
        connection.Close();
      }
      scope.Complete();
    }

    The method "ProcessarArquivo" is responsable for calling a lot of Business classes to resolve rules of the application. Those Business classes, calls Data Layer classes, which are responsable to access database. There, I am using LINQ to SQL to execute my database queries.

    Below is an example of simple Business class:

            public void AtualizarLocalidadeAnuncio(int idAnuncio, int idLocalidade)
            {
                ANUNCIO repAnuncio = objDO.Find(idAnuncio);
                repAnuncio.ID_LOCALIDADE = idLocalidade;
                objDO.AtualizarLocalidadeAnuncio(repAnuncio);
            }

    And here are some Data Access classes, using LINQ:

            public void UpdateLocalidadeAnuncio(ANUNCIO anuncio)
            {
                UploadODiaDataContext dbLinq = new UploadODiaDataContext();

                ANUNCIO repAnuncio = dbLinq.ANUNCIOs.Where(a => a.ID_ANUNCIO == anuncio.ID_ANUNCIO).FirstOrDefault<ANUNCIO>();
                repAnuncio.ID_LOCALIDADE = anuncio.ID_LOCALIDADE;
                dbLinq.SubmitChanges();
            }

            public void Remove(ANUNCIO entidade)
            {
                var item = base.DBLinq.ANUNCIOs.Single(d => d.ID_ANUNCIO == entidade.ID_ANUNCIO);
                base.DBLinq.ANUNCIOs.DeleteOnSubmit(item);
                base.DBLinq.SubmitChanges();
            }

            public void Insert(ANUNCIO entidade)
            {
                base.DBLinq.ANUNCIOs.InsertOnSubmit(entidade);
                base.DBLinq.SubmitChanges();
            }

            public int InsertNewId(ANUNCIO entidade)
            {
                base.DBLinq.ANUNCIOs.InsertOnSubmit(entidade);
                base.DBLinq.SubmitChanges();
                return entidade.ID_ANUNCIO;
            }

            public IList<ANUNCIO> FindAll()
            {
                return base.DBLinq.ANUNCIOs.ToList();
            }

    Those Dal classes
    inherits from a Base class, listed below, to get the DataContext object. Only on update methods I use another DataContext, to get the entity object to update. Besides that, other methods, like select, inser, delete I use the inherited DataContext (base.DBLinq). I did that because of a problem in LINQ, to use the same Datacontext to get entity and to update it.

        public class BaseDO
        {
            private UploadODiaDataContext dbLinq = new UploadODiaDataContext();
            private UploadODiaDataContext dbLinqWithoutTracking = new UploadODiaDataContext();

            public UploadODiaDataContext DBLinq
            {
                get
                {
                    return dbLinq;
                }
            }
    ...
    ...
          }

    There are other methods here, but I simplify just showing only the DataContext class that is created to be used by all Dal classes.

    I tried to change this Base DataContext, but without success.

    I tried to use after command "dbLinq.SubmitChanges()" in Dal classes, those commands:

      dbLinq.Connection.Close()
      dbLinq.Connection.Dispose()

    But nothing happens.

    I changed my connection string as follow:

    1) Here, my pool reaches 10 and take an exception

    <add name="UploadODiaDO.Properties.Settings.UploadODiaConnectionString"
                connectionString="Data Source=Server;Initial Catalog=UploadODiaDB;Integrated Security=True;Pooling=True;Max Pool Size=10"
                providerName="System.Data.SqlClient" />

    2) Here, forces the use of MSDTC service to control my connection pool, but without success

    <add name="UploadODiaDO.Properties.Settings.UploadODiaConnectionString"
                connectionString="Data Source=WORK\SQLEXPRESS;Initial Catalog=UploadODia;Integrated Security=True;Enlist=True;"
                providerName="System.Data.SqlClient" />

    3) Finnaly, I used this one, letting my pool grows without control, to permit application to execute up to the end

    <add name="UploadODiaDO.Properties.Settings.UploadODiaConnectionString"
                connectionString="Data Source=WORK\SQLEXPRESS;Initial Catalog=UploadODia;Integrated Security=True;Pooling=False;"
                providerName="System.Data.SqlClient" />

    My process create up to 980 connections every time it executes. Its amazing but I really spent all my knowledge to resolve that. No way up to now :(

    So, I am executing my application, but not as I wonder. I really want to resolve this problem. Control my connection pool, using only 10 of them, to do all my database operations.

    I expects that above code was clear enough to you. I will appreciate any kind of help.

    Regard,
    Marcelo (Olivertech).




    Olivertech
    Saturday, February 13, 2010 3:45 AM
  • Hi Ovlivertech

    Try with not opening and closing the connection. Let DataContext do it for you.

     ....
    using (TransactionScope scope = new TransactionScope())
    {
      using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString))
      {
        /// xxxxx connection.Open();
        using (UploadODiaDataContext dc = new UploadODiaDataContext(connection))
        {
          ProcessarArquivo(xmlNodes, publicacaoDTO, xmlDoc, nomeArquivo);
        }
        //// xxxxx connection.Close();
      }
      scope.Complete();
    }


    http://msdn.microsoft.com/en-us/library/k6ksw5zc.aspx

    Regards
    Chitrsen Aujikar
    Saturday, February 13, 2010 7:00 AM
  • Hi Chirrsen,

    First of all, I would like to thank you for your help.

    Well, unfortunatelly, your suggestion do not work. When I am running the process using Database,  pool goes up to 1000 connection openned (it appears is SQL status "sleeping"... I am using this query to get all my connections during processing.

    SELECT spid, uid=rtrim(loginame), pname=rtrim(Program_name),
    dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses
    where db_name(dbid) = 'Databasename'

    It returns lines like this:

    ...
    69    WORK\Celo&Paty    .Net SqlClient Data Provider    UploadODia    sleeping
    70    WORK\Celo&Paty    .Net SqlClient Data Provider    UploadODia    sleeping
    71    WORK\Celo&Paty    .Net SqlClient Data Provider    UploadODia    sleeping
    72    WORK\Celo&Paty    .Net SqlClient Data Provider    UploadODia    sleeping
    ...

    Regards,
    Olivertech
    Sunday, February 14, 2010 3:45 AM