locked
Using .net SQLClient Class: Best way for Open and Closing Connection RRS feed

  • Question

  • Hi,

    I'm using the .net SQLClient Class System.Data.SQLClient to connect to the SQL-Server.
    Everything works well, but I've a question for security and performance reasons.

    What is the best way to handle the connection?
    Make only one System.Data.SQLClient.OPEN() at Program Start and a close() at the end (so the connection stays Open after a SQL-Command and the next process can use it) or make everytime a new Open(and Close) for every SQL-Command/ DataAdapterFilling etc.

    First solution would be (pseudo-code without ConnectionStateChecks etc):
    Sub frmMainLoad()
      System.Data.SQLClient.OPEN() 
    EndSub
    [...]
    Sub frmReadTable1
      SqlCommand.CommandText = "SELECT * FROM Table1"
      SqlCommand.Execute
    EndSub
    [...]
    Sub frmReadWriteTable2
       SqlCommand.CommandText = "UPDATE FROM Table2 WHERE...."
       SqlCommand.Execute
    EndSub
    [...]
    Sub frmMainUnload()
      System.Data.SQLClient.CLOSE() 
    EndSub

    Second solution would be (pseudo-code without ConnectionStateChecks etc):
    Sub frmReadTable1
      OPEN()
      SqlCommand.CommandText = "SELECT * FROM Table1"
      SqlCommand.Execute
     CLOSE()
    EndSub
    [...]
    Sub frmReadWriteTable2
      OPEN() 
      SqlCommand.CommandText = "UPDATE FROM Table2 WHERE...."
       SqlCommand.Execute
      CLOSE()
    EndSub


    I think Option 1 makes less DataTraffic on Network (because ConnectionOpen is only one time) but could be a security problem (because the connection stays open) .
    Option 2 will make more DataTraffic on Network but in my opinion it's the better way, because the connections are only open when needed.
    But I don't know.

    Regards,
    Daniel



    Friday, January 29, 2010 3:40 PM

Answers

  •  

    What is the best way to handle the connection?
    Make only one System.Data.SQLClient.OPEN() at Program Start and a close() at the end (so the connection stays Open after a SQL-Command and the next process can use it) or make everytime a new Open(and Close) for every SQL-Command/ DataAdapterFilling etc.


    Go with the second solution. Use Connection Pooling. You create connection object , open it right before you call SQLCommand's method and close it so it will return to the pool.
     

    You should open a connection immediately before you need to access the database and then close it as soon as you are done accessing the database. Connections hold open valuable resources to the database, consume memory, and can lock data that could cause other queries to slow down. So it's best to open connections late and close them as early as possible.

     
    Here is the documenation that backs my advice

    http://msdn.microsoft.com/en-us/magazine/cc163799.aspx#S2

    http://www.wintellect.com/ARTICLES/ADO%20NET%20CONNECTION.PDF

    • Edited by Chirag Shah Friday, January 29, 2010 5:49 PM
    • Marked as answer by Daniel2508 Friday, January 29, 2010 6:15 PM
    Friday, January 29, 2010 4:37 PM

All replies

  •  

    What is the best way to handle the connection?
    Make only one System.Data.SQLClient.OPEN() at Program Start and a close() at the end (so the connection stays Open after a SQL-Command and the next process can use it) or make everytime a new Open(and Close) for every SQL-Command/ DataAdapterFilling etc.


    Go with the second solution. Use Connection Pooling. You create connection object , open it right before you call SQLCommand's method and close it so it will return to the pool.
     

    You should open a connection immediately before you need to access the database and then close it as soon as you are done accessing the database. Connections hold open valuable resources to the database, consume memory, and can lock data that could cause other queries to slow down. So it's best to open connections late and close them as early as possible.

     
    Here is the documenation that backs my advice

    http://msdn.microsoft.com/en-us/magazine/cc163799.aspx#S2

    http://www.wintellect.com/ARTICLES/ADO%20NET%20CONNECTION.PDF

    • Edited by Chirag Shah Friday, January 29, 2010 5:49 PM
    • Marked as answer by Daniel2508 Friday, January 29, 2010 6:15 PM
    Friday, January 29, 2010 4:37 PM
  • Yes, you need to secure the connection in *both* options (although it seems that option 2 is less affected by the problem).
    If you finally decided to use Option 2 and you encountered a perf issue, you may try to use "Pooling = true" so that the connection will be cached inside the ADO.Net provider, after its Close() method is called.

    Thanks,
    Ming.
    WDAC Team, Microsoft.



    Pak-Ming Cheung - MSFT
    Friday, January 29, 2010 5:00 PM
    Answerer
  • Many Thanks.
    I allready thought that this is the best way, but I wasn't really sure.
    Also Thanks for the Links.

    Regards,

    Friday, January 29, 2010 6:17 PM