SqlConnections, abstraction etc

Discussion in 'ASP .Net' started by Ben, Jun 23, 2004.

  1. Ben

    Ben Guest

    Hi I have a question concerning SqlConnections and when they should be
    opened and closed.

    Right now I have a database utilities class. I instantiate it when in
    page_load and dispose it in page_prerender, which opens and closes class
    level sqlconnection.

    Now, say I need to run a SqlCommand, all I do is:
    SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");

    And I get a SqlCommand, type storedprocedure, with the connection set to the
    classes database. I add parameters etc, and execute. Everything seems
    great. I've been told (#mscorlib and #asp.net on efnet) that this isn't a
    good approach. I should be using late open and early close on my database
    connection. Especially to take advantage of connection pooling. This means
    I need code like...

    using (SqlCommand oCmd = DBUtils.CreateSqlCommand("spName"))
    {
    oCmd.Connection.Open();
    oCmd.ExecuteScalar();
    oCmd.Connection.Close();
    }

    Assuming all things are equal except the connection in dbutils is closed.
    Is that a better approach? To me that doesn't guarantee the connection
    closes or anything.

    Maybe I should use...

    using (SqlConnection oConn = DBUtils.DatabaseReference)
    {
    oCmd.Connection = oConn
    oCmd.Connection.Open();
    oCmd.ExecuteScalar();
    oCmd.Connection.Close();
    }

    Or something like that??? Basically I'm trying to farm out as much of the
    database coding as possible. Is this possible? What is everyone else
    doing?

    What if I have a class in my page that needs to access the database? What
    do I do now? Pass reference to my database class?

    Please, any help / advise will be greatly appreciated!

    Thanks,
    Ben
    Ben, Jun 23, 2004
    #1
    1. Advertising

  2. Ben

    David Browne Guest

    "Ben" <> wrote in message
    news:aOlCc.5732$...
    > Hi I have a question concerning SqlConnections and when they should be
    > opened and closed.
    >
    > Right now I have a database utilities class. I instantiate it when in
    > page_load and dispose it in page_prerender, which opens and closes class
    > level sqlconnection.
    >
    > Now, say I need to run a SqlCommand, all I do is:
    > SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");
    >
    > And I get a SqlCommand, type storedprocedure, with the connection set to

    the
    > classes database. I add parameters etc, and execute. Everything seems
    > great. I've been told (#mscorlib and #asp.net on efnet) that this isn't a
    > good approach.


    Whoever told you was wrong. Except possibly that page_prerender is possibly
    not the best place to close the connection. Instead override Page.Dispose
    and close the connection there. If an exception occurs somewhere you still
    need to close the connection, and Page.Dispose will run even if another page
    method throws an exception.

    But the basic idea of tying the life of the connection to the life of the
    page instance is fine. If you open and close your connections around each
    command you may get slightly fewer connections in your connection pool, but
    I wouldn't worry about it. You will never have more connections than
    ASP.NET worker threads. No matter what you do you may have that many, so
    it's just fine.

    David
    David Browne, Jun 23, 2004
    #2
    1. Advertising

  3. Hi Ben,

    As for the connection management problem, I agree with David that it dosn't
    matter much which coding style you use, as long as we remember to close the
    connection after using it. Also, the
    ========================
    I should be using late open and early close on my database
    connection.
    ========================
    you mentioned is also right, that'll help the connection pool to better
    determine how to adjust the pooled connections. And since the actual
    connections are managed by the connectdion pool which is apparent to us(how
    many acutal instances of connection will it maintain), we don't need to
    care about it.

    #Connection Pooling for the .NET Framework Data Provider for SQL Server
    http://msdn.microsoft.com/library/en-us/cpguide/html/cpconconnectionpoolingf
    orsqlservernetdataprovider.asp?frame=true

    So, as for your situation, you'd like to use connection in a certain page,
    just open it and be sure to close it before the page is disposed, it's
    enough.

    Regards,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)

    Get Preview at ASP.NET whidbey
    http://msdn.microsoft.com/asp.net/whidbey/default.aspx
    Steven Cheng[MSFT], Jun 24, 2004
    #3
  4. Ben

    Ben Guest

    Thanks,

    I'll digest everything and see what fits my senerio.
    "Steven Cheng[MSFT]" <> wrote in message
    news:...
    > Hi Ben,
    >
    > As for the connection management problem, I agree with David that it

    dosn't
    > matter much which coding style you use, as long as we remember to close

    the
    > connection after using it. Also, the
    > ========================
    > I should be using late open and early close on my database
    > connection.
    > ========================
    > you mentioned is also right, that'll help the connection pool to better
    > determine how to adjust the pooled connections. And since the actual
    > connections are managed by the connectdion pool which is apparent to

    us(how
    > many acutal instances of connection will it maintain), we don't need to
    > care about it.
    >
    > #Connection Pooling for the .NET Framework Data Provider for SQL Server
    >

    http://msdn.microsoft.com/library/en-us/cpguide/html/cpconconnectionpoolingf
    > orsqlservernetdataprovider.asp?frame=true
    >
    > So, as for your situation, you'd like to use connection in a certain page,
    > just open it and be sure to close it before the page is disposed, it's
    > enough.
    >
    > Regards,
    >
    > Steven Cheng
    > Microsoft Online Support
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    > Get Preview at ASP.NET whidbey
    > http://msdn.microsoft.com/asp.net/whidbey/default.aspx
    >
    Ben, Jun 24, 2004
    #4
  5. Ben

    Ben Guest

    "David Browne" <davidbaxterbrowne no potted > wrote in
    message news:...
    >
    > "Ben" <> wrote in message
    > news:aOlCc.5732$...
    > > Hi I have a question concerning SqlConnections and when they should be
    > > opened and closed.
    > >
    > > Right now I have a database utilities class. I instantiate it when in
    > > page_load and dispose it in page_prerender, which opens and closes class
    > > level sqlconnection.
    > >
    > > Now, say I need to run a SqlCommand, all I do is:
    > > SqlCommand oCmd = DBUtils.CreateSqlCommand("spName");
    > >
    > > And I get a SqlCommand, type storedprocedure, with the connection set to

    > the
    > > classes database. I add parameters etc, and execute. Everything seems
    > > great. I've been told (#mscorlib and #asp.net on efnet) that this isn't

    a
    > > good approach.

    >
    > Whoever told you was wrong. Except possibly that page_prerender is

    possibly
    > not the best place to close the connection. Instead override Page.Dispose
    > and close the connection there. If an exception occurs somewhere you

    still
    > need to close the connection, and Page.Dispose will run even if another

    page
    > method throws an exception.
    >
    > But the basic idea of tying the life of the connection to the life of the
    > page instance is fine. If you open and close your connections around each
    > command you may get slightly fewer connections in your connection pool,

    but
    > I wouldn't worry about it. You will never have more connections than
    > ASP.NET worker threads. No matter what you do you may have that many, so
    > it's just fine.
    >
    > David
    >
    >

    Thanks for the pointers, I will move my class dispose to page.dispose.
    Ben, Jun 24, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alex
    Replies:
    2
    Views:
    542
  2. Bijoy Naick

    Managing SQLConnections

    Bijoy Naick, Nov 12, 2004, in forum: ASP .Net
    Replies:
    11
    Views:
    560
    Scott Allen
    Nov 13, 2004
  3. Stefano
    Replies:
    0
    Views:
    303
    Stefano
    Oct 13, 2003
  4. Martin Burger
    Replies:
    0
    Views:
    821
    Martin Burger
    Jul 18, 2005
  5. Kevin Walzer

    Re: PIL (etc etc etc) on OS X

    Kevin Walzer, Aug 1, 2008, in forum: Python
    Replies:
    4
    Views:
    388
    Fredrik Lundh
    Aug 13, 2008
Loading...

Share This Page