|
I'm not sure if anyone will be able to help me with this problem without actually debugging the code, but I thought I'd give it a shot.
Here's the situation. I have a fairly mature and stable C#/ASP.NET application that I inherited a few years back. I'm familiar with nearly all of the code, but there are few classes here and there that I've never had to deal with; I know they work and that's it.
Late last night we came across a bug in which connections were getting leaked until the pool was saturated with the default 100 connections. It turned out that the connections were getting orphaned in a class that hasn't been touched in about five years. As strange as that is, the part that I really can't explain is that the method is called an several places in the application and it works correctly. However, when it is called in a page that was recently added to the application it leaks connections like a siv.
If it can help, here's the offending method (underscores added for legibility).
<br />
public int GetUserID(String UserName) <br />
{<br />
________
________SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringUSR"]);<br />
________SqlCommand myCommand = new SqlCommand("GetSingleUserID", myConnection);<br />
<br />
________
________myCommand.CommandType = CommandType.StoredProcedure;<br />
<br />
________
________SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 100);<br />
________parameterEmail.Value = UserName;<br />
________myCommand.Parameters.Add(parameterEmail);<br />
<br />
<br />
________if ((parameterEmail.Value != null) && (parameterEmail.Value != System.DBNull.Value)) <br />
________{<br />
________________
________________myConnection.Open();<br />
<br />
________________SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);<br />
________________if(dr.Read()==true)<br />
________________{<br />
________________________if(dr.IsDBNull(0))<br />
________________________________return -1;<br />
________________________else<br />
________________________________return dr.GetInt32(0);<br />
________________}<br />
________________else<br />
________________{<br />
________________________return -1;<br />
________________}<br />
________}<br />
________else<br />
________{<br />
________________return -1;<br />
________}<br />
}<br />
<br />
The myConnection.Close() is missing, but the CommandBehavior is set to CloseConnection so it should be getting closed correctly.
I am at a complete loss why this code would behave different based on what on what's below it on the stack, what in the session, or what's in some static variable, but that seems to be the case.
If anyone can help me figure this out I'll owe you dinner.
Thanks, Jason
|
|
|
|
|
I don't know about you but I don't really trust something to close my connections for me. I'd have the open in a try block and the close in a finally block.
|
|
|
|
|
I couldn't agree more, in fact that's precisely what I did to correct the problem. But I also need to explain why the previous code behaved as it did, both for formal justifications as well as my own curiosity.
|
|
|
|
|
I'd say that a connection that is opened must always be closed, no exceptions. It's plain bad coding to rely on something else to auto close a connection.
As for why the code behaved as it did - I'd just put it down to badly written code.
|
|
|
|
|
Of course the behaviour depends on what else is happening in the code. You are never closing the data reader, so it will be depending on garbage collections to finalize it. If there doesn't happen to be a garbage collection soon, it will be leaking the connection.
Jason Pease wrote: it leaks connections like a siv
Perhaps even like a sieve? 
Jason Pease wrote: nderscores added for legibility
So much work for something that didn't end up very good anyway... Use the pre tag instead.
---
It's amazing to see how much work some people will go through just to avoid a little bit of work.
|
|
|
|
|
Guffa wrote: Of course the behaviour depends on what else is happening in the code. You are never closing the data reader, so it will be depending on garbage collections to finalize it. If there doesn't happen to be a garbage collection soon, it will be leaking the connection.
The garbage collection doesn't seems to be the issue based on the counters in perfmon, but I'm not sure how reliable they are.
Guffa wrote: Perhaps even like a sieve?
No, more like a siv. Very very leaky.
Guffa wrote: So much work for something that didn't end up very good anyway... Use the pre tag instead.
Nice tip. Can you tell I don't post that often?
Thanks, Jason
|
|
|
|
|
Jason Pease wrote: The garbage collection doesn't seems to be the issue based on the counters in perfmon, but I'm not sure how reliable they are.
You are missing the point. Read my reply again, and see if you can spot where I say that you are never closing the data reader.
---
It's amazing to see how much work some people will go through just to avoid a little bit of work.
|
|
|
|
|
Perhaps, you are missing the point.
Try to read my initial post again and see you if you can pick out the phrase where I say the code works, and has worked for years, correctly in many parts of the application. If it were simply a question of never closing the data reader it would have leaked connections from day one.
Or am I missing something here?
---
It's amazing how rude some people are to people that they don't know
|
|
|
|
|
It could depend on a lot of things as to why connections haven't been leaking up until recently. But it still stands that the connection and the data reader both need closing.
I wouldn't bother looking into this too far as you never wrote the code, just fix it and move on.
|
|
|
|
|
Jason Pease wrote: Perhaps, you are missing the point.
Nope. 
Jason Pease wrote: Try to read my initial post again and see you if you can pick out the phrase where I say the code works, and has worked for years, correctly in many parts of the application.
I saw that when I read it the first time. The fact the code does not produce errors is in no way any guarantee that it is correct.
Jason Pease wrote: If it were simply a question of never closing the data reader it would have leaked connections from day one.
It's not simply a question of not closing the data reader. As I explained, it depends on whether there are any garbage collections occuring that will finalize the data reader or not. If the code would have closed the data reader, it would not have to depend on the garbage collector.
The code has been incorrect from day one. You have just been lucky that the garbage collector has been picking up the slack.
---
It's amazing to see how much work some people will go through just to avoid a little bit of work.
|
|
|
|
|
Jason
This is actually quite a subtle one, but the problem is because the connection is closed with CommandBehavior.CloseConnection only when the datareader is closed (which isn't happening in this case). So, you've either got to call dr.Close, or remove the call to CommandBehavior and close the connection explicitly.
Hint, try this:
using (SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringUSR"]))
{
... Do the other database work here...
}
or
SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
try
{
if(dr.Read()==true)
{
if(dr.IsDBNull(0))
return -1;
else
return dr.GetInt32(0);
}
else
{
return -1;
}
}
finally
{
dr.Close();
}
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
please tell me about the level of C#
sorry because of my english
|
|
|
|
|
I'm not sure what you mean by 'the level of C#'
|
|
|
|
|
hi all,
i have a question that i have been thinking of for a while.
when i need to have an application that look like, for example, out look with a side panel and a cente panel. When clicking on a button on the side panel the center panel changes it components.
I usually use "UserControls" for this kind of application. And i assign the userCOntrols to the middle panels. It works fine with no problems.
But i have been thinking if i can assign form like that. Not a form that opens as a new window separate from the mother window. but a form that opens inside the mother form.
if it is possible please brief me.
|
|
|
|
|
can't you use Mdi parent and Mdi child controls.
|
|
|
|
|
i found useful links for learning 
|
|
|
|
|
No problem, have a play around with the basics then if there's anything specific you want help with - feel free to ask.
|
|
|
|
|
also tell us what are those link.
regards
imran khan
|
|
|
|
|
Don't use the message board for private messages. Either use the thread where you started your question of use e-mails.
regards
modified 12-Sep-18 21:01pm.
|
|
|
|
|
I have a simple question but I tried for a long time and I cannot find the answer.
I have a DataGridView object on a form binded to a DataTable and I want the first cell of the first row to be in edit mode when the form shows up. The idea is for the user to start inputting data as soon asthe form shows up without having to use the mouse. I capture CellEnter and put the grid in edit mode, this works well if you tab through the grid but it doesn't work for when the form shows.
|
|
|
|
|
Have you tried using the .Select and .SetFocus on the Load event?
- Eitsop
What we do not understand we do not possess. - Goethe.
|
|
|
|
|
please help me about begining of c#
|
|
|
|
|
|
Hey, I'm a novice C# programmer, and I'm trying to create an application that accesses a very large database.
The thing I'm trying to do is, instead of opening a new mysql connection each time I want to query the DB, how can I make it so that the connection is left open until manually closed, so that transactions are quicker? Is this even possible? Is it better to do it this way or to login and execute the query each time I need to?
Thanks in advance for your help!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)