locked
When I Open Sql Connection how log it will stay open RRS feed

  • Question

  • Hi,

    I can not afford to close and open connection due to performance. as my application retrives only one row per call what i have desided is to keep Sql connection open. every time i need data ,then i just query the database with same open connection. This functionality is developed in my server which is running continiously. if i open the connection and kept open up till how much time it will stay open ? do i need to put any special check for the connection.

    i am using C# .net Fremwork 4.0, Sql Server 2008 r2


    Regards, Preetam Ramdhave

    Friday, April 5, 2013 10:38 AM

Answers

  • if i open the connection and kept open up till how much time it will stay open ? do i need to put any special check for the connection.

    Hello Preetam,

    SQL Server don't close connections on it's own, so your connections stays open, until you close it ... or a network interruption occurs; so you better check the connection if it's still valid, otherwise re-open it.

    BTW, open/closing is because of connection pooling not really a performance issue.


    Olaf Helper

    Blog Xing

    Friday, April 5, 2013 10:43 AM

All replies

  • if i open the connection and kept open up till how much time it will stay open ? do i need to put any special check for the connection.

    Hello Preetam,

    SQL Server don't close connections on it's own, so your connections stays open, until you close it ... or a network interruption occurs; so you better check the connection if it's still valid, otherwise re-open it.

    BTW, open/closing is because of connection pooling not really a performance issue.


    Olaf Helper

    Blog Xing

    Friday, April 5, 2013 10:43 AM
  • Hello ,

    Please , could you tell us whether your thread is the logical consequence of your previous thread

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/9b20e938-cec7-40eb-8d6b-11c8068d64ce ?

    If yes , you have to open a connection towards your SQL Server instance when the application is starting with the pooling property set to false. see the following link :http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.pooling(v=vs.100).aspx

    You have to avoid the using block ( in C# or Using... End Using in VB ) to open a connection as your connection object is local to the block and consequently unreachable outside the block scope.

    Another possibility is to declare your connection as a static ( in VC# ) or Shared ( in VB ) property. In this case , it is known everywhere in your application. But I recognize that too many people are not pleased by this kind of declaration with many good reasons , but I am considering that your case could be a good exception. Of course , when the application is closing , you must not to forget to close compulsory your connection ( and you must be prepared to a good way to treat exceptions which could be thrown by your application ) nearly as the last statement executed by your application.

    If you want more code ( and to make easier the work of potential repliers ) , I would suggest you :

    - to provide the language used by your application to avoid to give you code samples in a language not corresponding to the choice done for your application ( every developer has his favorite language. I prefer VC# but I am also using VB as it is necessary on this forum, but I am nearly beginner in VC++ or F# )

    - to provide  the name of the provider that you wish to use to connect your SQL Server. I prefer to use System.Data.SqlClient because it is the most efficient provider for SQL Server http://msdn.microsoft.com/en-us/library/8t72t3k4(v=vs.100).aspx but you have  several other providers ( ODBC, OLEDB to avoid as depreciated , ... )

    http://msdn.microsoft.com/en-us/library/gg145028(v=vs.100).aspx

    You have also generic classes found in http://msdn.microsoft.com/en-us/library/gg145028(v=vs.100).aspx but they have less available methods/ properties

    We are waiting for your feedback to try to help you more efficiently.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    • Edited by Papy Normand Friday, April 5, 2013 11:17 AM Added links
    Friday, April 5, 2013 10:43 AM
  • Hello ,

    Please , could you tell us whether your thread is the logical consequence of your previous thread

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/9b20e938-cec7-40eb-8d6b-11c8068d64ce ?

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Hi,

    actually the previous thread is i was finilizing my architecture, is it good to just use one connection and keep it open. now i have developed that functionality. but when i thought if i open connection at the time of application start, then when ever i am sending that  connection i just want to make sure that connection object shoud not outomatically time out.

     


    Regards, Preetam Ramdhave

    Friday, April 5, 2013 11:19 AM
  • hi Olaf Helper,

    Thanks a lot for you reply


    Regards, Preetam Ramdhave

    Friday, April 5, 2013 11:23 AM