asked on

VB.NET / ADO.NET Suggestions

I have an VB.NET 2003/2005 program that is a report server. It's currently using classic ADO and has no problems... when I stress test it runs select statements every 3 seconds to check a queue on a connection that's global and never closed (if it happens to lose connection, it reconnects).

Now, I've updated my code to use ADO.NET's SqlConnection, SqlCommand and SqlDataReader (used to use ADODB.Connection and ADODB.Recordset). Same theory, I create a global connection and set MultipleActiveResultSets=True so the connection can handle multiple data readers (using SQL Server 2005). The problem is, now the program crashes under heavy load with a not enough system resources error (when there is plenty of RAM left and plenty of disk space left)... I can only surmise this is a leak of some sort.

I believe I'm disposing of all of my commands and then setting them equal to nothing... the connection however is constant so I'm not disposing of it at all unless it should lose connection for some reason, then it re-establishes. I've tried with and without connection pools... My brain is fried, I need suggestions to get me back on track.

I could try opening and closing a connection every three seconds when it runs the query but I thought it would be more effiecient if I didn't, if that assumption is incorrect, also let me know. I'm putting 500 on this because my brain hurts, it's Friday and I'm going to obsess over this until I figure it out.

IUFITS

ASKER

1.) Does the order I dispose in things make a difference?
2.) Is it better to close and re-establish with each query, every 3 seconds?
3.) Is the global connection causing this leak somehow?
4.)

Additional Code Info:

' Conn is then opened in a timer event if it's not established... that event also re-establishes the connection if it's lost
Public ConnectionString As String = "server=<server>;database=<db>;MultipleActiveResultSets=True;uid=<user>;pwd=<password>"
Public Conn As New SqlConnection(ConnectionString)

' These are my two SQL Command objects
Dim SqlCommand1 As New SqlCommand("", Conn)
Dim SqlCommand2 As New SqlCommand("", Conn)

' This is how I dispose and remove them
SqlCommand1.Dispose()
SqlCommand2.Dispose()
SqlCommand1 = Nothing
SqlCommand2 = Nothing
Dr = Nothing

ASKER CERTIFIED SOLUTION

iHadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jayadev Nair

Hi
DataReaders SHOULD be Closed & Disposed. You are doing so right?

Thanks.

IUFITS

ASKER

I'll triple check and make sure that my data readers are being closed and disposed of. It always crashes on the same line where it loads a crystal report but the reason I don't think that is the problem is because that code hasn't changed and the old engine had no memory issues... the only thing that has changed are the database connections from ADO to ADO.NET.

iHadi - You brought up something interesting... I was calling gc.collect for reasons I can't remember and it's never been an issue in the past (I would call it during non-report genereating time)... I wonder if now that I've changed to ADO.NET if it's behaving differently with the new objects... I'll try and comment it out and see how it performs.

The odd thing is that when it says the system it out of resources, the executables aren't really overly large and there's typically plenty of system memory left... I'll post the statistics that I log at crash time (logged the systems virtual and physical memory used and total).

peterdevadoss

you can also try to close the connection when you finished loading the record.

Conn.close