none
Set Common SQL connection RRS feed

  • Question

  • Hi all,

    In my DAL i have more than 100 methods/Function, each and every method am opening the sqlconnection and closing the connection, this is taking too much of time to establish the connection at every time. So what i expect is one common class will create the SqlConnection that will check if the connection is Broken or Closed then create the connection again else return the connection, how to do this(Also i would like to apply ConnectionPooling).

    Please post any code.


    Thanks In Advance, Jeyaseelan
    Thursday, April 8, 2010 9:44 AM

Answers

  • To me it's dangerous, because if connection will be broken, connection state will still return you Open state and you will receive an exception. In addition keeping connection open all the time is not scalable solution and could lead to database connection leaks. First you need to identify if it's really the connection that causes performance issues or it's something else. I would not recommend to keep connection all the time


    Val Mazur (MVP) http://www.xporttools.net
    Friday, April 9, 2010 10:17 AM
    Moderator

All replies

  • You cannot really identify if connection is broken unless you try to execute some sort of SQL statement. Provider returns connection status as Open (after establishing it) even if communication to database server is lost. What you are using right now (opening connection on each operation) is fine and should not affect performance significantly as long as you use connection pooling. What makes you believe that opening and closing connection is slow?
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, April 8, 2010 9:55 AM
    Moderator
  • For Common Connetion, You Can define Connetion String In App.Config Then Can Use In Different Pages.

    Ex-

    In App.Config

    <

     

    appSettings>

    <

     

    add key="Connection" value="Data Source=.;Initial Catalog=MEXICAN_PHARMA; User ID=sa; Password=masters;"/>

    </

     

    appSettings>

    and Use in Form

    like as

    SqlConnection

     

    scon = new SqlConnection(ConfigurationManager.AppSettings["connection"]);

     


    Thanks & Regards, PSWASULE
    Thursday, April 8, 2010 9:58 AM
  • Hi Val Mazur thanks for your post. WIth the help of SqlHelper.cs file we were doing am i correct?

    The same thing style i would like to create the class. I think Now you understand.


    Thanks In Advance, Jeyaseelan
    Thursday, April 8, 2010 5:43 PM
  • Hi Pswasule thanks for your response.

    Common connection in the sense,

    1. I would like to create the Class that class will open the SqlConnection one tim e then if the next method will try to cretate the connection that time this class will check the connection state, if its closed then only connection will establish againg. this is what i expect.


    Thanks In Advance, Jeyaseelan
    Thursday, April 8, 2010 5:51 PM
  • To me it's dangerous, because if connection will be broken, connection state will still return you Open state and you will receive an exception. In addition keeping connection open all the time is not scalable solution and could lead to database connection leaks. First you need to identify if it's really the connection that causes performance issues or it's something else. I would not recommend to keep connection all the time


    Val Mazur (MVP) http://www.xporttools.net
    Friday, April 9, 2010 10:17 AM
    Moderator