Do i need to dispose of MySqlCommand?

Question

I find it incredibly annoying to write a using statement on every one of my queries (which require its own command or write parameters.clear()) which sometimes require declaring variables outside of the using block. Its so incredibly annoying and looks much dirtier compared to the version without disposing the object.

Do i need to dispose of it? what happens if i dont? I do know its good practice to dispose of an object when it has that interface.

Answer 1

If you're targeting ASP.NET, you could setup your Connect() method (if you have one) to also setup a finalizer to run when the page unloads. This trick is also great if you're having problems with connection pools that run out.

I've also included a Exec method to simplify writing SQL commands with typesafe parameters - as in the Test method.

using System;
using System.Web;
using System.Data.SqlClient;
using Conf = System.Configuration.ConfigurationManager;
using System.Data;

public static class Sql {
    public static SqlConnection Connect() {
        // create & open connection
        SqlConnection result = new SqlConnection(Conf.ConnectionStrings["connectionString"].ConnectionString);
        result.Open();

        // add delegate to trigger when page has finished, to close the connection if it still exists
        System.Web.UI.Page page = HttpContext.Current.Handler as System.Web.UI.Page;
        if (page != null) {
            page.Unload += (EventHandler)delegate(object s, EventArgs e) {
                try {
                    result.Close();
                } catch (Exception) {
                } finally {
                    result = null;
                }
            };
        }

        // return connection
        return result;
    }

    public static SqlDataReader Exec(string name, params object[] parameters) {
        using (SqlCommand cmd = Connect().CreateCommand()) {
            cmd.CommandTimeout = int.Parse(Conf.AppSettings["commandTimeoutSec"]);
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = name;
            for (int x = 0; x + 1 < parameters.Length; x += 2) {
                SqlParameter p = cmd.Parameters.AddWithValue((string)parameters[x], parameters[x + 1]);
                if (parameters[x + 1] is string) {
                    p.DbType = DbType.AnsiString;
                }
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }

    public static void Test() {
        using (SqlDataReader reader = Exec(
            "SELECT * FROM member WHERE name=@firstname AND age=YEAR(GETDATE())-@yearborn",
            "@firstname", "tom",
            "@yearborn", 1978)) {
            while (reader.Read()) {
                // read
            }
        }
    }
}

Answer 2

From a quick glance in Reflector, it appears to call CloseStatement() on its PreparableStatement, and then call Dispose() on its superclasses, including System.ComponentModel.Component. Somewhere up the chain it ends up calling NativeDriver.CloseStatement32(id), which writes a command to the connection stream.

It's not the sort of thing I'd want to skip. Why risk it?

This is one of those times when you need to adjust your own thinking. You might think using looks "dirty", but -- like fresh oil glistening on a gear system -- it's actually a sign of cleanliness. You might be able to write an abstraction to hide it, but you shouldn't get rid of it.