database connection, to close or not to do

Discussion in 'ASP .Net' started by Han, May 8, 2009.

  1. Han

    Han Guest

    Hello

    I have been using database in server script for years without problem.

    connection.open();
    do something...
    connection.close();

    All is fine. Sometimes,

    connection.open();
    xmlreader xrd = command.executeXmlreader();
    connection.close();
    return xrd;

    All is fine. But not always!
    Sometimes I see packet size 4096 error in above scenario. I hope you know
    the error. The error comes when result stream size is bigger than default
    4096 packet size. You can set the size in connection string. e.g.,

    string strcon = "Server=.\\sqlExpress; Database=db1; Integrated
    Security=SSPI; Packet Size=32767"

    But setting packet size is not ideal way as I heard. Another workaround
    suppress the packet size error is let database connection open. e.g.,

    xmlreader func() {
    connection.open();
    xmlreader xrd = command.executeXmlreader();
    //connection.close();
    return xrd;
    }

    It works when I do without connection.close(). Now my question comes.

    1. Why does above procedure work without close(), and not with close()?

    Anyway all is fine if it works. But not that.

    When I call the procedure several times I see now connection pool error. I
    thought Dotnet database connection is closed automatically when container
    procedure ends. My second question is,

    2. Is an opened connection not closed even after the container procedure
    ends? Should you always close explicitly?

    Thanks in advance
     
    Han, May 8, 2009
    #1
    1. Advertising

  2. Han

    Han Guest

    Resolved.

    Don't bother with the question. All the problem comes from returning
    xmlreader as is. Loading onto Xelement solved all the issues.

    xelement func() {
    connection.open();
    xmlreader xrd = command.executeXmlreader();
    xelement1 = xelement.load(xrd);
    connection.close();
    return xelement1;
    }

    Thanks for reading anyway

    "Han" <> ´ÔÀÌ ´ÙÀ½ ¸Þ½ÃÁö¸¦ ÀÛ¼ºÇß½À´Ï´Ù.
    news:...
    > Hello
    >
    > I have been using database in server script for years without problem.
    >
    > connection.open();
    > do something...
    > connection.close();
    >
    > All is fine. Sometimes,
    >
    > connection.open();
    > xmlreader xrd = command.executeXmlreader();
    > connection.close();
    > return xrd;
    >
    > All is fine. But not always!
    > Sometimes I see packet size 4096 error in above scenario. I hope you know
    > the error. The error comes when result stream size is bigger than default
    > 4096 packet size. You can set the size in connection string. e.g.,
    >
    > string strcon = "Server=.\\sqlExpress; Database=db1; Integrated
    > Security=SSPI; Packet Size=32767"
    >
    > But setting packet size is not ideal way as I heard. Another workaround
    > suppress the packet size error is let database connection open. e.g.,
    >
    > xmlreader func() {
    > connection.open();
    > xmlreader xrd = command.executeXmlreader();
    > //connection.close();
    > return xrd;
    > }
    >
    > It works when I do without connection.close(). Now my question comes.
    >
    > 1. Why does above procedure work without close(), and not with close()?
    >
    > Anyway all is fine if it works. But not that.
    >
    > When I call the procedure several times I see now connection pool error. I
    > thought Dotnet database connection is closed automatically when container
    > procedure ends. My second question is,
    >
    > 2. Is an opened connection not closed even after the container procedure
    > ends? Should you always close explicitly?
    >
    > Thanks in advance
     
    Han, May 8, 2009
    #2
    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. Paul van Rossem
    Replies:
    0
    Views:
    607
    Paul van Rossem
    Apr 7, 2005
  2. tshad
    Replies:
    1
    Views:
    467
    Scott M.
    Sep 6, 2006
  3. S_K
    Replies:
    6
    Views:
    679
    Russell
    Dec 7, 2007
  4. Iñaki Baz Castillo
    Replies:
    7
    Views:
    873
    Iñaki Baz Castillo
    Jan 12, 2010
  5. hyejin
    Replies:
    8
    Views:
    346
    Martin Honnen
    Aug 29, 2006
Loading...

Share This Page