Starting DB connections using LINQ to SQL
When developing a relatively simple web service that takes data provided by mail and writes it to a database table, we get this error:
Excluded: The remote server returned an error: (500) Internal server Er or. Stack trace: at System.Net.HttpWebRequest.GetResponse ()
on some servers but not others. The ones that get this are physical machines, others are virtual machines, and obviously the physical servers are much more powerful.
As far as we can tell, the problem is that DB connections are not being returned back to the pools after every request. I am using the usage pattern below:
using (VoteDaoDataContext dao = new VoteDaoDataContext())
{
dao.insert_response_and_update_count(answerVal, swid, agent, geo, DateTime.Now, ip);
dao.SubmitChanges();
msg += "Thank you for your vote.";
dao.Dispose();
}
I added a call to dao.Dispose () to ensure that the connections are released when the method ends, but I don't know if this should be done.
Am I using this pattern correctly? Is there something else I need to do to make sure connections are being returned to pools correctly?
Thank!
source to share
Your diagnostic information is not good enough. There is not enough detail for HTTP / 500 to really tell if your theory is correct. You will need to capture the full stack trace at registration if you want to fix the problem. I think you came to a conclusion immediately. And no, you don't need to Dispose () until the end of your use of the {} block. What uses {} does.
source to share
I thought the call to dispose () was redundant, but I wanted to be sure.
We see connection pools saturating in SQL logs (I can't look directly, I'm just a developer and this stuff works in prod environment) and my ops guy said he sees connections timeout ... and as soon as they will time out, the server will restart again until the next time it saturates the connection pool.
We are currently going through the process of tweaking the connection pool settings ... I wanted to make sure I was not doing anything wrong as this is my first time using Linq.
Thank!
source to share