[Mono-list] Questions about coding style

[Jonathan Pryor]

On Aug 20, 2012, at 3:28 PM, Philippe Grohrock <philippe.grohrock at gmail.com> wrote:
> Thanks for the reply already and I'm sorry, I should've added the lines of code.
> 
> 	static class GlobalVariables
> 	{
> 		public static MySqlConnection connection = new connection();
> 	}
> 
> This way the whole program has access to it and can modify/query the DB when needed (this is what I meant with global).

I believe that this is a Bad Idea™.

Firstly, this is counter to ~every MSDN example on using connections, which always scopes the Connection instance:

	// http://msdn.microsoft.com/en-us/library/ff647768.aspx#scalenetchapt12_topic9
	using (SqlConnection conn = new SqlConnection(connString))
	{
		conn.Open();
		// ...
	}

This implies that you should instead do:

	static class Database {

		internal static MySqlConnection CreateConnection ()
		{
			return new connection ();
		}
	}

And narrowly scope your use:

	using (var c = Database.CreateConnection ()) {
		c.Open ();
		// ...
	}

Now, _why_ should you do this? Unfortunately I can't find anything to confirm or deny the following, but this is my recollection from using SQL many years ago...

The reason why is connection-related errors: if (when) you hit a network interruption, the DbConnection instance is unusable afterward, even if the network came back. (Though maybe I needed to .Close() and .Open() to repair the instance? I no longer remember.) I found that the easiest/sanest way to go was to just recreate the Connection instance when needed, and Dispose() of it as soon as possible (relying on lower-level connection pooling if possible).

 - Jon