CoordinateDAO.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using DDSGpsNav.Classes;
using DDSGpsNav.DB;

namespace DDSGpsNav.DB
{
    public class CoordinateDAO : DAO<Coordinate>, IMasterFileDAO
    {
        private const string UpdateSql =
            "UPDATE coordinates SET LAT = {1}, LON = {2}, MAP_REGION_ID = {3}, REMOVE = {4} WHERE ID = {0};";
        private const string InsertSql = 
            "INSERT INTO coordinates (ID, LAT, LON, MAP_REGION_ID, REMOVE) VALUES ({0}, {1}, {2}, {3}, {4});";
        private const string SelectSql =
            "SELECT ID, LAT, LON, MAP_REGION_ID, REMOVE FROM COORDINATES WHERE MAP_REGION_ID = {0};";

        public override Coordinate mapRow(DbDataReader reader)
        {
            return new Coordinate
                       {
                           Id = (int) reader["ID"],
                           Latitude = (double) reader["LAT"],
                           Longitude = (double) reader["LON"],
                           MapRegionId = (int) reader["MAP_REGION_ID"],
                           Remove = (bool) reader["REMOVE"]
                       };
        }

        private string FormatSQL(string sql, DataRow row)
        {
            return String.Format(sql, row["ID"],
                                 row["LAT"],
                                 row["LON"],
                                 row["MAP_REGION_ID"],
                                 row["REMOVE"]);
        }

      /**
       * Updates the master record table of important coordinates
       */
        public string UpdateMFTable(DataSet resultsSet)
        {
            var table = resultsSet.Tables["COORDINATES"];
            var lastCoordId = "";
            foreach (DataRow row in table.Rows)
            {

                try
                {
                    var sql = FormatSQL(UpdateSql, row);
                    if(ExecuteNonQuery(sql) == 0)
                    {
                      var sql = FormatSQL(InsertSql, row);
                      ExecuteNonQuery(sql);
                    }
                }
                catch(Exception ex)
                {
                  logger.error("Error updating MF Table: {0}", ex);
                  throw ex;
                }
                lastCoordId = row["ID"].ToString();
            }
            return lastCoordId;

        }

        public List<Coordinate> GetAllCoordinatesByMapRegion(int mapRegionID)
        {
            var sql = string.Format(SelectSql, mapRegionID);
            return SelectAllRows(sql);
        }

        public string GetTableName()
        {
            return "coordinates";
        }
    }
}

DAO.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using DDSGpsNav.Classes;
using MySql.Data.MySqlClient;

namespace DDSGpsNav.DB
{
    public abstract class DAO<T> where T : new()
    {
        //Data Access Layer
        protected readonly DDSDAL dal;
        protected static readonly FileLogManager logger = FileLogManager.Instance;

        protected DAO()
        {
            dal = new DDSDAL();
        }

        public void ClearMFTable(string tableName)
        {
            var deleteSQL = String.Format("DELETE FROM {0};", tableName);
            ExecuteNonQuery(deleteSQL);
        }

        /// <summary>
        /// Executes an SQL statement which has no return such as an Insert, Delete or Update statement
        /// </summary>
        /// <param name="sql">The sql string to be executed</param>
        /// <return>The number of records updated or inserted if any
        public int ExecuteNonQuery(string sql)
        {
            try
            {
                DbConnection conn = dal.Connection;
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                return command.ExecuteNonQuery();
            } 
            catch
            {
                logger.Error("Error executing sql: " + sql);
                throw;
            }
            finally
            {
                dal.CloseConnection();
            }
        }


        public DataSet SelectAllRowsDataSet(string sql)
        {
            try
            {
                var conn = dal.Connection;
                var dataAdapter = new MySqlDataAdapter(sql, conn);
                var dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                return dataSet;
            }
            finally
            {
                dal.CloseConnection();   
            }
        }

        public List<T> SelectAllRows(string sql)
        {
            DbConnection conn = dal.Connection;
            var select = conn.CreateCommand();
            select.CommandText = sql;
            DbDataReader reader = null;
            var results = new List<T>();
            try
            {
                reader = select.ExecuteReader();
                while(reader.Read())
                {
                    results.Add(mapRow(reader));
                }
                return results;
            } catch(Exception ex)
            {
                logger.Error("Error executing '{0}': {1}", sql, ex.Message);
                throw;
            }
            finally
            {
                if(reader != null)
                {
                    reader.Close();
                }
                dal.CloseConnection();
            }
        }
        /// <summary>
        /// Returns a single domain object from a select statement
        /// </summary>
        /// <exception cref="DataException">If to many rows are returned</exception>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T SelectOneRow(string sql)
        {
            DbConnection conn = dal.Connection;
            DbCommand select = conn.CreateCommand();
            select.CommandText = sql;
            DbDataReader reader = null;
            T t = default(T);

            // Added try..catch in case exception is thrown, need to close the reader
            try
            {
                reader = select.ExecuteReader();
                if (reader.Read())
                {
                    t = mapRow(reader);
                }
                if (reader.Read())
                {
                    throw new DataException("Too many rows returned");
                }
            } catch(Exception e)
            {
                logger.Error("Error executing '{0}': {1}", sql, e.Message);
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                dal.CloseConnection();
            }
            return t;
        }

        public Object SelectScalar(string sql)
        {
            var conn = dal.Connection;
            var select = conn.CreateCommand();
            select.CommandText = sql;


            try
            {
                return select.ExecuteScalar();
            } catch(Exception ex)
            {
                logger.Error("Error executing '{0}': {1}", sql, ex.Message);
                throw;
            } finally
            {
                dal.CloseConnection();
            }
        }

        /// <summary>
        /// Abstract method that contains logic for mapping
        /// from a DataTableReader into a domain object
        /// </summary>
        /// <param name="reader"></param>
        /// <returns>The Domain Object</returns>
        public abstract T mapRow(DbDataReader reader);

        protected bool MysqlToBool(string s)
        {
            return s.Equals("Y");
        }

        public string formatDateToMysql(DateTime pickup)
        {
            return pickup.ToString("s").Replace("T", " ");

        }

        protected string MysqlFromBool(bool input)
        {
            return input ? "Y" : "N";
        }
        

        /// <summary>
        /// just because ado.net xml removes null values from it's datatables.  yay
        /// </summary>
        /// <param name="row"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetNullableRowValue(DataRow row, string id)
        {
            try
            {
                var value = row[id].ToString();
                return value;
            }
            catch (ArgumentException)
            {
                return "null";

            }
        }
    }
}