Question about memory leak in .NET

Question

Hi, I have some memory leak in my C# code I have a class with some SqlCommand fields inside it. I also have an Initialize() public method that allocates these SqlCommands fields.

Before the allocation I check (in the Initialize's code) whether it not the first time I do allocation (i.e not the first time Initialize is called) and if it's not the first time I call Dispose on the SqlCommands, and it seems to cause a memory leak....

Now it's important to mention that all the SqlCommands use the same SqlConnection and this connection is alive during all the program's life. The connection is accessed via a static property of some static class... (let's call it ConnectionManager)

Any idea what might be the problem? Thanks!

Answer 1

Krembo, the recommended way to use SqlConnection and SqlCommand objects is inside a using statement, as in this sample code from MSDN:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here; connection closed on following line.
}

You should probably be disposing of your SqlConnection object as soon as you've finished using it, while it might seem that this would be slow, behind the scenes, connection pooling will eliminate most of the overhead.

Answer 2

You should probably post some of the relevant code. Without it, I'm just guessing. However, here's a guess:

You are following the wrong pattern here. Since your class maintains objects which implement IDisposable, your class should implement IDisposable itself. Your callers should then be changed to call your class's Dispose method when they're done with it:

using System;
using System.Data.SqlClient;

public static class ConnectionManager
{
    private static readonly SqlConnection _connection =
        new SqlConnection("connectionString");
    public static SqlConnection Connection { get { return _connection; } }
}

public class HoldsCommands : IDisposable
{
    private readonly SqlCommand _commandOne = new SqlCommand("Command1");
    private readonly SqlCommand _commandTwo = new SqlCommand("Command2");

    public void DoSomethingWithAConnection()
    {
    }

    public void Dispose()
    {
        if (_commandOne != null)
        {
            try
            {
                _commandOne.Dispose();
            }
            catch (Exception)
            {
            }
        }

        if (_commandTwo != null)
        {
            try
            {
                _commandTwo.Dispose();
            }
            catch (Exception)
            {
            }
        }
    }
}

Your caller would then call you like this:

using (var commands = new HoldsCommands()) {
    commands.DoSomethingWithAConnection();
}

As Luke Girvin says, you probably don't need the static ConnectionManager class if you're just trying to save on database connections. .NET handles this for you.