A very simple wrapper over MySqlConnection to simplify querying

Database.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace CircuitGallery
{
	public class IndexedAttribute : Attribute
	{
	}
	
	public class Database : IDisposable
	{
		IDbConnection _conn = null;

		class TypeMap
		{
			string _tableName;
			List<TypeMapCol> _cols;
			TypeMapCol _pkCol;
			string _insertText = null;
			string _getText = null;

			public TypeMap (Type type)
			{
				_tableName = type.Name;
				_cols = new List<TypeMapCol> ();
				foreach (var p in type.GetProperties ()) {
					if (p.CanWrite) {
						var col = new Database.TypeMapCol (p, p.Name);
						if (col.IsPK)
							_pkCol = col;
						_cols.Add (col);
					}
				}
				if (_pkCol != null)
					GetGetText ();
				GetInsertText ();
			}

			public void Migrate (IDbConnection conn)
			{
				var existingCols = new Dictionary<string, TypeMapCol> ();
				using (var cmd = conn.CreateCommand ()) {
					var schema = conn.Database;
					cmd.CommandText = "select * from information_schema.columns where table_name = '" + _tableName + "' and table_schema = '" + schema + "'";
					using (var reader = cmd.ExecuteReader ()) {
						while (reader.Read ()) {
							var colName = (string)reader ["column_name"];
							existingCols [colName] = null;
						}
					}
				}
				if (existingCols.Count == 0) {
					//
					// Create the table
					//
					var sb = new StringBuilder ();
					var head = "  ";
					sb.AppendFormat ("create table `{0}` (", _tableName);
					sb.AppendLine ();
					foreach (var col in _cols) {
						sb.Append (head);
						sb.Append ("`");
						sb.Append (col.ColumnName);
						sb.Append ("` ");
						sb.Append (col.ColumnType);
						if (col.IsPK) {
							sb.Append (" primary key auto_increment");
						}
						sb.AppendLine ();
						head = " ,";
					}
					foreach (var col in _cols) {
						sb.Append (head);
						if (col.IsIndexed) {
							sb.AppendLine ("INDEX(`" + col.ColumnName + "`)");
						}
						head = " ,";
					}

					sb.AppendLine (");");
					using (var cmd = conn.CreateCommand ()) {
						cmd.CommandText = sb.ToString ();
						cmd.ExecuteNonQuery ();
					}
				} else {
					foreach (var col in _cols) {
						if (!existingCols.ContainsKey (col.ColumnName)) {
							var sb = new StringBuilder ();
							sb.AppendFormat ("alter table `{0}` add column `{1}` {2}", _tableName, col.ColumnName, col.ColumnType);
							using (var cmd = conn.CreateCommand ()) {
								cmd.CommandText = sb.ToString ();
								cmd.ExecuteNonQuery ();
							}
							if (col.IsIndexed && !col.IsPK) {
								sb = new StringBuilder ();
								sb.AppendFormat ("create index `{0}` on `{1}`(`{2}`)",
									_tableName + "_" + col.ColumnName,
									_tableName,
									col.ColumnName);
								using (var cmd = conn.CreateCommand ()) {
									cmd.CommandText = sb.ToString ();
									cmd.ExecuteNonQuery ();
								}
							}
						}
					}
				}
			}

			void GetGetText ()
			{
				_getText = string.Format ("select * from `{0}` where `{1}` = ?p0 limit 1", _tableName, _pkCol.ColumnName);
			}

			public T Get<T> (IDbConnection conn, object pk) where T : new()
			{
				if (_pkCol == null) {
					throw new ArgumentException (_tableName + " has no PK");
				}
				using (var cmd = conn.CreateCommand ()) {
					cmd.CommandText = _getText;
										
					var p = cmd.CreateParameter ();
					p.ParameterName = "p0";
					p.Value = pk;
					cmd.Parameters.Add (p);
					
					using (var reader = cmd.ExecuteReader ()) {
						var all = ReadAll<T> (reader);
						return all.FirstOrDefault ();
					}
				}
			}

			void GetInsertText ()
			{
				var sb = new StringBuilder ();
				sb.Append ("insert into `");
				sb.Append (_tableName);
				sb.Append ("`(");
				var head = "";
				foreach (var col in _cols) {
					if (!col.IsPK) {
						sb.Append (head);
						sb.Append ("`");
						sb.Append (col.ColumnName);
						sb.Append ("`");
						head = ",";
					}
				}
				sb.Append (") values (");
				head = "";
				var pi = 0;
				foreach (var col in _cols) {
					if (!col.IsPK) {
						sb.Append (head);
						sb.Append ("?p" + pi);
						pi++;
						head = ",";							
					}
				}
				sb.Append (")");
				_insertText = sb.ToString ();
			}

			public void Insert (IDbConnection conn, object obj)
			{
				var pi = 0;
				
				using (var cmd = conn.CreateCommand ()) {
					cmd.CommandText = _insertText;
					
					pi = 0;
					foreach (var col in _cols) {
						if (!col.IsPK) {
							var p = cmd.CreateParameter ();
							p.ParameterName = "p" + pi;
							p.Value = col.Prop.GetValue (obj, null);
							if (p.Value == null) {
								p.Value = DBNull.Value;
							}
							cmd.Parameters.Add (p);
							pi++;
						}
					}
					
					cmd.ExecuteNonQuery();
				}
			
				if (_pkCol != null) {
					using (var cmd = conn.CreateCommand ()) {
						cmd.CommandText = "select last_insert_id()";
						var id = cmd.ExecuteScalar ();
						_pkCol.Prop.SetValue (obj, Convert.ToInt32(id), null);
					}
				}
			}

			public void Update (IDbConnection conn, object obj, string[] propsToUpdate)
			{
				if (_pkCol == null) {
					throw new NotSupportedException (obj + " doesn't have a primary key and cannot be updated");
				}
				
				var i = 0;
				
				//
				// Find the props
				//
				var colsToUpdate = default(TypeMapCol[]);
				if (propsToUpdate.Length > 0) {
					colsToUpdate = new Database.TypeMapCol[propsToUpdate.Length];
					for (i = 0; i < propsToUpdate.Length; i++) {
						var gotit = false;
						for (var j = 0; !gotit && j < _cols.Count; j++) {
							if (propsToUpdate [i] == _cols [j].PropertyName) {
								colsToUpdate [i] = _cols [j];
								gotit = true;
							}
						}
						if (!gotit) {
							throw new ArgumentException (propsToUpdate [i] + " not found in " + _tableName);
						}
					}
				} else {
					throw new NotSupportedException ("No props");
				}

				//
				// Build the query
				//
				var sb = new StringBuilder ();
				sb.Append ("update `");
				sb.Append (_tableName);
				sb.Append ("` set ");
				var head = "";
				for (i = 0; i < colsToUpdate.Length; i++) {
					sb.Append (head);
					sb.Append ("`");
					sb.Append (colsToUpdate [i].ColumnName);
					sb.Append ("`=");
					sb.Append ("?p");
					sb.Append (i);						
					head = ",";
				}
				sb.Append (" where `");
				sb.Append (_pkCol.ColumnName);
				sb.Append ("` = ?p");
				sb.Append (i);
				
				//
				// Execute it
				//
				using (var cmd = conn.CreateCommand ()) {
					cmd.CommandText = sb.ToString ();
					
					for (i = 0; i < colsToUpdate.Length; i++) {
						var p = cmd.CreateParameter ();
						p.ParameterName = "?p" + i;
						p.Value = colsToUpdate [i].Prop.GetValue (obj, null);
						if (p.Value == null) {
							p.Value = DBNull.Value;
						}
						cmd.Parameters.Add (p);
					}
					var idp = cmd.CreateParameter ();
					idp.ParameterName = "?p" + i;
					idp.Value = _pkCol.Prop.GetValue (obj, null);
					cmd.Parameters.Add (idp);
					
					cmd.ExecuteNonQuery ();
				}
			}

			public List<T> ReadAll<T> (IDataReader reader) where T : new ()
			{
				var list = new List<T> ();
				
				var fields = new TypeMapCol [reader.FieldCount];
				
				for (var i = 0; i < fields.Length; i++) {
					var name = reader.GetName (i);
					fields [i] = _cols.FirstOrDefault (c => c.PropertyName == name);
				}
				
				var values = new object [fields.Length];
				
				while (reader.Read ()) {
					var obj = new T ();
					
					reader.GetValues (values);
					
					for (var i = 0; i < fields.Length; i++) {
						var col = fields [i];						
						if (col == null) continue;
						
						var val = values [i];
						
						if (val is DBNull) {
							col.Prop.SetValue (obj, null, null);
						} else {
							col.Prop.SetValue (obj, val, null);
						}
					}
					
					list.Add (obj);
				}
				
				return list;
			}
		}

		class TypeMapCol
		{
			public readonly bool IsPK;
			public readonly bool IsIndexed;
			public readonly string PropertyName;
			public readonly string ColumnName;
			public readonly string ColumnType;
			public readonly PropertyInfo Prop;

			public TypeMapCol (PropertyInfo prop, string colName)
			{
				PropertyName = prop.Name;
				ColumnName = colName;
				Prop = prop;
				IsPK = (PropertyName == "Id");
				IsIndexed = prop.GetCustomAttributes (typeof(StringLengthAttribute), true).Length > 0;
				
				if (prop.PropertyType == typeof(string)) {
					var sls = prop.GetCustomAttributes (typeof(StringLengthAttribute), true);
					if (sls.Length > 0) {
						var len = ((StringLengthAttribute)sls [0]).MaximumLength;
						ColumnType = "varchar(" + len + ")";
					} else {
						ColumnType = "text";
					}
				} else if (prop.PropertyType == typeof(int)) {
					ColumnType = "int";
				} else if (prop.PropertyType == typeof(DateTime)) {
					ColumnType = "datetime";
				} else {
					throw new NotSupportedException ("Unsupported column type: " + prop.PropertyType);
				}
			}
		}
		
		static Dictionary<Type, TypeMap> _typeMaps = new Dictionary<Type, TypeMap> ();

		TypeMap GetTypeMap (object obj)
		{
			return GetTypeMap (obj.GetType ());
		}

		TypeMap GetTypeMap (Type type)
		{
			lock (_typeMaps) {
				var typeMap = default(TypeMap);
				if (_typeMaps.TryGetValue (type, out typeMap)) {
					return typeMap;
				} else {
					typeMap = new TypeMap (type);
					_typeMaps [type] = typeMap;
					if (_conn != null)
						typeMap.Migrate (_conn);				
					return typeMap;
				}
			}
		}

		public Database ()
		{
		}

		public void Dispose ()
		{
			if (_conn != null) {
				_conn.Dispose ();
				_conn = null;
			}
		}

		void EnsureConnected ()
		{
			if (_conn == null) {
				var cs = System.Configuration.ConfigurationManager.ConnectionStrings ["MySql"].ConnectionString;
				var mysql = new MySql.Data.MySqlClient.MySqlConnection (cs);
				mysql.Open ();
				_conn = mysql;
			}
		}

		public void CreateTable<T> () where T : new()
		{
			EnsureConnected ();
			
			GetTypeMap (typeof (T));
		}
		
		public T Get<T> (object pk) where T : new()
		{
			if (pk == null)
				throw new ArgumentNullException ("pk");

			EnsureConnected ();
			
			var map = GetTypeMap (typeof(T));
			
			return map.Get<T> (_conn, pk);
		}

		public void Insert<T> (T obj)
		{
			if (obj == null)
				throw new ArgumentNullException ("obj");
			
			EnsureConnected ();
			
			var map = GetTypeMap (obj);
			
			map.Insert (_conn, obj);			
		}

		public void Update<T> (T obj, params string[] propsToUpdate)
		{
			if (obj == null)
				throw new ArgumentNullException ("obj");
			if (propsToUpdate == null)
				throw new ArgumentNullException ("propsToUpdate");
			
			EnsureConnected ();

			var map = GetTypeMap (obj);
			
			map.Update (_conn, obj, propsToUpdate);
		}
		
		IDbCommand CreateCommand (string query, params object[] args)
		{
			EnsureConnected ();
			
			var cmd = _conn.CreateCommand ();
				
			var t = new StringBuilder ();
			var pi = 0;
			foreach (var ch in query) {
				if (ch == '?') {
					t.Append ("?p");
					t.Append (pi++);
				} else {
					t.Append (ch);
				}
			}
			cmd.CommandText = t.ToString ();
			
			pi = 0;
			foreach (var a in args) {
				var p = cmd.CreateParameter ();
				p.Value = a;
				p.ParameterName = "p" + pi;
				cmd.Parameters.Add (p);
				pi++;
			}				
			
			return cmd;
		}

		public List<T> Query<T> (string query, params object[] args) where T : new()
		{
			using (var cmd = CreateCommand (query, args)) {
				
				var map = GetTypeMap (typeof(T));
				
				using (var reader = cmd.ExecuteReader ()) {
					return map.ReadAll<T> (reader);
				}
			}
		}
		
		public int Execute (string query, params object[] args)
		{
			using (var cmd = CreateCommand (query, args)) {				
				return cmd.ExecuteNonQuery ();
			}
		}
	}
}