3

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.

Improve this question

3 Answers 3

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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 } } } }
Improve this answer

Comments

0

If you check MySqlHelper.cs in the Connector/NET code, you'll find that the MySqlCommands are not disposed there.

So I guess it's not strictly necessary in the current version (8.0.33), but this may change in the future.

Improve this answer

Comments