database connection, to close or not to do

[Han]

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]

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