Cleaning up ODBC DSN in C# .NET

Question

I am using C# and an OBDC DSN to connect to a Paradox database. I seem to be leaking memory if I open and close each connection.

My code is basically:

            csb.Dsn = "DNSName";
            OdbcConnection con = new OdbcConnection(csb.ConnectionString);
            con.Open();

            OdbcCommand comm= new OdbcCommand("SELECT * FROM Tabl", con);
            OdbcDataReader reader= null;
            try
            {
                reader= comm.ExecuteReader();
                for (int count = 0; (count < 5 && reader.Read()); ++count)
                {
                    //Read
                }
            }
            finally
            {
                if (reader!= null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (comm!= null)
                {
                    con.Close();
                    con.Dispose();
                    OdbcConnection.ReleaseObjectPool();
                    GC.Collect();
                    comm.Dispose();
                }
            }

Any ideas or suggestions?

Update 1

I changed it to use using statments, still leaks.

Answer 1

Try putting the connection, command, and reader inside of using statements.

Then call OdbcConnection.ReleaseObjectPool(); at the end.

Note: it may take a few minutes for garbage collection to run. To prove that there is no leak you could call GC.Collect() three times in a row [three times to clear out objects through all three generations].

Do not leave the GC.Collect() in production code though because it can become a big performance hit.

Answer 2

How did you detect the leak? Sometime mem usage goes up in task manager and not release immediately may due to GC not kicking in straightaway or you have connection pool or handle that havent yet been released in the managed environment. I suggest that you use memory profiler like ANTS Mem Profiler suggested by Travis. You can get a trial version, otherwise use basic version by Microsoft CLRProfiler.

Another good measure if to load the process so that it runs longer during the profiling process so that if there is problem, it will clearly shown. The easiest is to put a loop around it so that say run the above for 1000 times or more. You can also use performance monitor to monitor some of the counter of interest and see how they trace during the execution.

Answer 3

Try to avoid calling GC.Collect

Never touch the garbage collector unless you are 100% sure you know what you're doing. Always remember - the garbage collector is smarter than you, and it knows the best time to run.