Class to manage MySQL and SQL Server CE databases

Question

I have been asked to create a piece of software for internal use that will allow us to keep up to date with support requests, etc.

one of the main features is the software works from a remote MySQL database, but if there is no internet connection it should then run from a local SQL Server CE database.

Ideally the software should only pass a single request to the database manager class, this class will know if it is connected to mysql and if not use the local SQL Server CE database instead.

I have attempted this but came across many roadblocks. Does anyone have a solution? My latest idea is to pass a variable to the method that dictates the type (see below) but I am uncertain on how to do this.

public MySQLCommand run (string query, "MYSQL", <List>(MySQLParameter) mysqlparams){}
public SQLCECommand run (string query, "SQLCE", <List>(SQLCEParameter) dbparams){}

the above code would be used as follows:

using (var sql = dbmanager.run("SELECT * FROM table", "MYSQL", mysqlparams)) {

I have also attempted the below code, which worked but when used in a using statement I could not access the method functions of the command, plus it gave me an error!

public Object run(string query, List<Object> dbparams = null){
if (isMySQLConnected){
    MySqlCommand sql = _MySQLConnection.CreateCommand();
    sql.CommandText = query;
    if (dbparams.Count > 0){
        sql.Parameters.AddRange(dbparams.ToArray());
    }
    return sql;
} else {
    SqlCeCommand sql = _OfflineConnection.CreateCommand();
    sql.CommandText = query;
    if (dbparams.Count > 0){
        sql.Parameters.AddRange(dbparams.ToArray());
    }
    return sql;
}
}

Answer 1

Using inheritance

public abstract class MyDBManager
{
    public abstract bool OpenConnetion();
    public abstract DataTable OpenDataTable(); // For select queroes
    public abstract int ExecuteNonQuery(string qry) // for insert/delete queries
    public abstract bool CloseConnection();
}

public class MySQLCEManger : MyDBManager
{
    public bool OpenConnection() 
    { 
        // your sqlce connection
    }

    public DataTable OpenDataTable(string query)
    {
         //Open connection
         //execute query and return datatable
         //Close connection
    }
}

 public class MySQLManager : MySQLCEManager
 {
    public bool OpenConnection() 
    { 
        // your sql cen connection
    }

    public DataTable OpenDataTable(string query)
    {
         if(!OpenConnection())  //failed to open connection
             return base.OpenDataTable();
         //execute query and return datatable
         //Close connection
    }

    public int ExecuteNonQuery(string query)
    {
         if(!OpenConnection())  //failed to open connection
             return base.ExecuteNonQuery();
         //execute query and return rows affected
         //Close connection
    }
}

Hope it helps. Please ignore any syntax errors it has. Just for implementation details.

You can also look into Microsoft's patterns and practices for abstract database manager