locked
Connection Open Close issue........ RRS feed

  • Question

  • Hi,

    Need your inputs of the approach to open the connection at application level or open and close the connection for every database interaction.

    Here are the details of my application :-

    I have 1 web application, 20-25 windows service and 10-15 Web services which uses 4 databases.

    Some windows services also maintain the transaction.

    Question : What is the best approach to manage the Database connection?

    1. Is connection pooling a good idea ? 

         - but how connection pooling will work for the application running in different app domain

    2. Is opening and closing db connection for every database interaction a good idea?

      - my windows service hit the database very frequently, this may be a performance issue

    3. Is keep a connection alive at application level is good?

      - My Web services is hit by external application we can not determine how frequently they will hit the database. and  I also read something on internet "The difficulty with long-lived connections is that you might not be entirely sure that they're still there. A network failure, server restart or stateful firewall forgetting some of its state could all result in a "stale" connection which looks open, but then gives an error when you try to use it."

    Please suggest .


    Kapul

    Friday, June 7, 2013 6:35 AM

Answers

  • 1. Is connection pooling a good idea ? 

         - Connections cannot be shared among AppDomains, so Connection Pool will not help in AppDomains for each calls.

        - Refer SQL Server Connection Pooling

    2. Is opening and closing db connection for every database interaction a good idea?

      - If you are using Connection Pool within same App domain, it will surely help.

      -Open and close the connection per unit of Work

    3. Is keep a connection alive at application level is good?

      - Keeping a connection alive at application level is never a good idea.

      - It will keep the resource unnecessarily.


    Muthukrishnan Ramasamy
    net4.rmkrishnan.net
    Use only what you need, Reduce global warming

    Friday, June 7, 2013 8:28 AM

All replies

  • 1. Is connection pooling a good idea ? 

         - Connections cannot be shared among AppDomains, so Connection Pool will not help in AppDomains for each calls.

        - Refer SQL Server Connection Pooling

    2. Is opening and closing db connection for every database interaction a good idea?

      - If you are using Connection Pool within same App domain, it will surely help.

      -Open and close the connection per unit of Work

    3. Is keep a connection alive at application level is good?

      - Keeping a connection alive at application level is never a good idea.

      - It will keep the resource unnecessarily.


    Muthukrishnan Ramasamy
    net4.rmkrishnan.net
    Use only what you need, Reduce global warming

    Friday, June 7, 2013 8:28 AM
  • 1.  Yes, connection pooing is a good idea.

    2.  After enable connection pool, the connection will not closed when we call close method.

    3.  After enable connection pool, the life time of connection will be managed by the pool.  I application do as usuall.


    Friday, June 7, 2013 8:29 AM
  • So what i need to do 

    Shall i open and close the DB connection for every request or keep it open for per session?


    Kapul

    Friday, June 7, 2013 9:01 AM
  • So what i need to do 

    Shall i open and close the DB connection for every request or keep it open for per session?


    Kapul

    yes

    Muthukrishnan Ramasamy
    net4.rmkrishnan.net
    Use only what you need, Reduce global warming

    Friday, June 7, 2013 1:52 PM