none
linq to sql connection striung RRS feed

  • Question

  • In a C# 2008 windows application that use call a web service, there is a large volume of statements that look like the following:
    In a C# 2008 application, I am using linq to sql statements that look like the following:

    TDataContext TData = new TDataContext();
    var TNumber = (from dw in cms_TData.people
    where dw.Organization_Name.ToUpper().Trim() == strOrgnizationName.Trim().

    Right before every call that is made to the database, a new data context object is created.

    Would this cause some kind of connection pooling problem to the database? If so, can you tell me how to resolve the connection pooling problem?
    Monday, February 11, 2013 4:03 AM

Answers

  • Hi Wendy;

    To your statement, "The application is not using the entity framework, it is using only linq to sql.", I ment to say Linq to SQL in my last post.

    To your question, "Could this be some kind of a permissions issue? If so where do you think it is?, Yes it could be. As too where it could be I do not know. I would start looking at object that make calls to the Registry for example read this documentation on CA Certificates Tools and Settings, are you accessing Certificates in the program?

     To your question, "On most of the objects in the application, it am not setting calling dispose method. I am assuming the .net framework would cleanup the memory, correct? If not, what do you recommend?", yes .Net will clean up but only when it needs memory that the system needs. It is recommended that you call Dispose on all object that implement one.

    To your question, "Also on some of the linq to sql statements, the 'using' clause is not being called. Should I change all the statements to have a using statement?", the good thing about using statements in code is that it calls the Dispose method on the object automatically when it leaves the using block.

    To your question, "Sometimes the data context objects have the same name from the previous creation of the datacontext object. Would this cause a problem? If so, how would you change the code?", If the compiler allows you to do this I can only assume that the other variable by the same name is out of scope and therefore should cause no problems.

    To your last question, ".net service coordination", I am not sure what the person means by this.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Tuesday, February 12, 2013 7:44 PM

All replies

  • In a C# 2008 windows application that use call a web service, there is a large volume of statements that look like the following:
    In a C# 2008 application, I am using linq to sql statements that look like the following:

    TDataContext TData = new TDataContext();
    var TNumber = (from dw in cms_TData.people
    where dw.Organization_Name.ToUpper().Trim() == strOrgnizationName.Trim().

    Right before every call that is made to the database, a new data context object is created.

    Would this cause some kind of connection pooling problem to the database? If so, can you tell me how to resolve the connection pooling problem?
    Monday, February 11, 2013 4:04 AM
  • Wendy,

    You can wrap these statements in a using block so they will be automatically disposed of.

    See this answer for details:

    Do I need to explicitly call Dispose() on DataContext object?


    Dan Randolph - My Code Samples List

    • Proposed as answer by RohitArora Monday, February 11, 2013 5:15 AM
    Monday, February 11, 2013 4:19 AM
  • Hi Wendy;

    The code snippet you showed in itself should not cause a connection pooling in itself, the only time the connection pool is being use is when a query is executed. What is leading you to think this to be the cause? Do you get an exception or something else?

    I also see something that does not look right. you have these two lines of code.

    TDataContext TData = new TDataContext();
    var TNumber = (from dw in cms_TData.people

    In the first line of code you create a data context. In the second line of code, in the query, you use the data context cms_TData, why are you not using TData or if you are going to use cms_TData why create TData?

      

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 11, 2013 4:23 AM
  • Wendy this question is a double post to the same question in Linq to SQL forum.

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 11, 2013 4:27 AM
  • Basically DataContext is a smart object, there is some logic in it, that somehow detects when you finish your query in database and disposes this object. However I've somewhere seen a guy, who did tests with DataContext object and he was saying that it can handle up to 200 opened connections.

    But my recommendation is always use using statement to make sure that unnecessary connection are not left open.

    using(TDataContext TData = new TDataContext())
    {
    
    ...
    
    }


    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Monday, February 11, 2013 8:18 AM
  • In this C# application, there no reference to use registry keys. However, registry keys error messages are showing up in the log files. This program  purpose is to call a web service at different times during the day. The only thing I can think of that would be causing the problem would be:

    1. I am updating the database too often and leaving connection sgtring open. and/or

    2. The web service is leaving the connection calls we make to their service open all the time.

    Monday, February 11, 2013 3:06 PM
  • Hi Wendy;

    So the only indication that something is not working correctly is the, "registry keys error messages are showing up in the log files", is that correct?

    Please post here the error messages that are showing up in the log.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 11, 2013 3:36 PM
  • You are correct. I am trying to determine what could be causing the problem like the *.dbml file is not being closed.
    Monday, February 11, 2013 5:52 PM
  •   

    Please post here the error messages that are showing up in the log.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 11, 2013 6:15 PM
  • Here are the error messages I am getting:

    Process 9528 (\Device\HarddiskVolume2\Program Files (x86)\red\sampleclient.exe) has opened key \REGISTRY\USER\S-1-5-21-11-222-333\Software\Microsoft\SystemCertificates\TrustedPeople
    Process 8880 (\Device\HarddiskVolume2\Program Files (x86)\red\sampleclient.exe) has opened key \REGISTRY\USER\S-1-5-21-11-222-333\Software\Microsoft\SystemCertificates\TrustedPeople
    Process 9528 (\Device\HarddiskVolume2\Program Files (x86)\red\sampleclient.exe) has opened key \REGISTRY\USER\S-1-5-21-11-222-333\Software\Microsoft\SystemCertificates\My
    Process 8880 (\Device\HarddiskVolume2\Program Files (x86)\red\sampleclient.exe) has opened key \REGISTRY\USER\S-1-5-21-11-222-333\Software\Microsoft\SystemCertificates\My
    Process 9528 (\Device\HarddiskVolume2\Program Files (x86)\red\sampleclient.exe) has opened key \REGISTRY\USER\S-1-5-21-11-222-333\Software\Microsoft\SystemCertificates\Disallowed
    Process 8880 (\Device\HarddiskVolume2\Program Files (x86)\red\sampleclient.exe) has opened key \REGISTRY\USER\S-1-5-21-11-222-333\Software\Microsoft\SystemCertificates\Disallowed
    Tuesday, February 12, 2013 3:46 AM
  • Is the program sampleclient.exe the program that you are working on that you suspect that Entity Framework is causing the problem? The listing is not pointing to any specific problem. I do not believe it is being cause by Entity Framework and do not know where to tell you to look.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 12, 2013 4:41 AM
  • Thanks for your answers!

    I want to mention the following:

    1. The application is not using the entity framework, it is using only linq to sql.

    2. Could this be some kind of a permissions issue? If so where do you think it is?

    3. On most of the objects in the application, it am not setting calling dispose method. I am assuming the .net framework would cleanup the memory, correct? If not, what do you recommend?

    4. Also on some of the linq to sql statements, the 'using' clause is not being called. Should I change all the statements to have a using statement?

    5. Everytime a linq query is ready to be executed, a new data context object is being created. Sometimes the data context objects have the same name from the previous creation of the datacontext object. Would this cause a problem? If so, how would you change the code?

    6. Also my network administrator said I should look at the " .net service coordination"? Would this make a difference? If so, what difference would it make?

    Tuesday, February 12, 2013 3:56 PM
  • Hi Wendy;

    To your statement, "The application is not using the entity framework, it is using only linq to sql.", I ment to say Linq to SQL in my last post.

    To your question, "Could this be some kind of a permissions issue? If so where do you think it is?, Yes it could be. As too where it could be I do not know. I would start looking at object that make calls to the Registry for example read this documentation on CA Certificates Tools and Settings, are you accessing Certificates in the program?

     To your question, "On most of the objects in the application, it am not setting calling dispose method. I am assuming the .net framework would cleanup the memory, correct? If not, what do you recommend?", yes .Net will clean up but only when it needs memory that the system needs. It is recommended that you call Dispose on all object that implement one.

    To your question, "Also on some of the linq to sql statements, the 'using' clause is not being called. Should I change all the statements to have a using statement?", the good thing about using statements in code is that it calls the Dispose method on the object automatically when it leaves the using block.

    To your question, "Sometimes the data context objects have the same name from the previous creation of the datacontext object. Would this cause a problem? If so, how would you change the code?", If the compiler allows you to do this I can only assume that the other variable by the same name is out of scope and therefore should cause no problems.

    To your last question, ".net service coordination", I am not sure what the person means by this.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Tuesday, February 12, 2013 7:44 PM
  • Thank yout for all your helpful answer!

    This application has no code for registry keys and the application is not accessing Certificates. From what I have seen on the internet, it looks like Windows 7 could be generating these errors. But I have no idea yet how winodws 7 could be generating these error messages.

    Wednesday, February 13, 2013 3:31 AM