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;
}
}

score 2 · Answer 1 · answered Jun 12 '12 at 11:55

What I would suggest is create a base class which always connects to SqlCe ( and add database specific methods like

public virtual DataTable OpenDataTable(string query)
public virtual int ExecuteNonQuery(string query)

Ovrride same methods in mysql manager class. If mysql manager class failed to execute it will call the base class which will connect to sqlce. For both of these classes create an abstract or base class for future extendibility(Name it is MyDBManager).

score 1 · Answer 2 · answered Jun 12 '12 at 11:50

1

Ideally you should only pass the query and it should return the results.

It should be the responsibility of the method to decide which database to connect.

It should only return the results and the caller should be unaware of this complexity.

public IEnumerable run(string query){}

answered Jun 12 '12 at 11:50

Asif Mushtaq

13,010
3
33
42

I did attempt this orriginally but the return type had to be set to an object for it to return either sqlcecommand or mysqlcommand, this then meant that I could not use the using command or any features of the command. – Neo Jun 12 '12 at 11:52
Why you want command to be returned rather then the actual results? – Asif Mushtaq Jun 12 '12 at 11:55
the results are retrieved later by different methods, by getting the row data with ExecuteReader or by nonScaller – Neo Jun 12 '12 at 12:00

score 1 · Accepted Answer · answered Jun 12 '12 at 12:13

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

So in this example it would try the SQLCE connection first then mysql? What if I wanted it the other way round so it tries mysql first, and if there is no connection then do the sqlce can I just reverse the class order or would I need to do a specific return type? — Neo, Jun 12 '12 at 12:34
No, Create an object of MySQLManager and always use mySqlManager object. mySqlManager object first it tries to connect, if its failed it calls the base class method. base class (SqlCEManager) executes query and returns results — Sudhakar B, Jun 13 '12 at 03:54
Thanks I eventually got what you meant I'll accept yours as he accepted answer. — Neo, Jun 13 '12 at 09:10

Class to manage MySQL and SQL Server CE databases

3 Answers3