C# - Make a project MySQL and SQL Server compatible

Question

I am working on a project that I want both MySQL and MSQL compatible. All is working good, but I have a small issue about code repeating :

I have a separate class for all database queries, and for selects, I do the following (example for an object "Profil"):

I have a parameter "isMySQL" that I set to true if I want to use MySQL and false for using Microsoft SQL Server.

public Profil Select_profil(string query)
        {
            Profil profil = new Profil();
            if (this.OpenConnection() == true)
            {
                if (this.isMySQL)
                {
                    MySqlCommand cmd = new MySqlCommand(query.Replace("[myDataBase].", ""), connection);
                    using (MySqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            profil.ID = Int32.Parse(dataReader["ID"].ToString().Trim());
                            profil.Nom = dataReader["PROFIL"].ToString().Trim();
                            profil.Famille = dataReader["FAMILLE"].ToString().Trim();
                            profil.Largeur = double.Parse(dataReader["LARGEUR"].ToString().Trim());
                            profil.Hauteur = double.Parse(dataReader["LARGEUR_AILE"].ToString().Trim());
                            profil.EpAile = double.Parse(dataReader["EP_AILE"].ToString().Trim());
                            profil.EpAme = double.Parse(dataReader["EP_AME"].ToString().Trim());
                            profil.Radius1 = double.Parse(dataReader["R"].ToString().Trim());
                            profil.Radius2 = double.Parse(dataReader["R2"].ToString().Trim());
                            profil.PdsLin = double.Parse(dataReader["PDS_LINEAIRE"].ToString().Trim());
                            profil.PaintSurf = double.Parse(dataReader["PAINT_SURF"].ToString().Trim());
                            profil.P08 = double.Parse(dataReader["P08"].ToString().Trim());
                            profil.P09 = double.Parse(dataReader["P09"].ToString().Trim());
                            profil.P10 = double.Parse(dataReader["P10"].ToString().Trim());
                            profil.P11 = double.Parse(dataReader["P11"].ToString().Trim());
                        }
                    }
                }
                else
                {
                    SqlCommand cmd = new SqlCommand(query, MSconnection);
                    using (SqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            profil.ID = Int32.Parse(dataReader["ID"].ToString().Trim());
                            profil.Nom = dataReader["PROFIL"].ToString().Trim();
                            profil.Famille = dataReader["FAMILLE"].ToString().Trim();
                            profil.Largeur = double.Parse(dataReader["LARGEUR"].ToString().Trim());
                            profil.Hauteur = double.Parse(dataReader["LARGEUR_AILE"].ToString().Trim());
                            profil.EpAile = double.Parse(dataReader["EP_AILE"].ToString().Trim());
                            profil.EpAme = double.Parse(dataReader["EP_AME"].ToString().Trim());
                            profil.Radius1 = double.Parse(dataReader["R"].ToString().Trim());
                            profil.Radius2 = double.Parse(dataReader["R2"].ToString().Trim());
                            profil.PdsLin = double.Parse(dataReader["PDS_LINEAIRE"].ToString().Trim());
                            profil.PaintSurf = double.Parse(dataReader["PAINT_SURF"].ToString().Trim());
                            profil.P08 = double.Parse(dataReader["P08"].ToString().Trim());
                            profil.P09 = double.Parse(dataReader["P09"].ToString().Trim());
                            profil.P10 = double.Parse(dataReader["P10"].ToString().Trim());
                            profil.P11 = double.Parse(dataReader["P11"].ToString().Trim());
                        }
                    }
                }
                this.CloseConnection();
            }
            return profil;
        }

For MySQL and SQL Server, the code is the same(except the [MyDatabase]., that I replace in case of MySQL), but I need to repeat it twice because in one case I use objects MySqlCommand and MySqlDataReader, in the second case I need to use SqlCommand and SqlDataReader. Inconvenient is when I make some modify in my database, I need to change all twice (source of errors). Is there a way to group the code and put it once, without using functions?

Answer 1

Instead of applying the switching logic in if..else.. statement here, you should write a separate layer for data access, as suggested in previous comments. That layer would deal with the database selection and other operation.

for example, Add a contract say, ICommand -- this would be used at your current code in place of MySQLCommand or SQLCommand. A data access layer would have a factory/class and function that would return you the appropriate command based on the parameter.

Similarly, all other operation related to database, that you might be doing in your code, at business layer, would be moved to this new layer that you would create, DataAccessLayer.

Edited: Thanks @Richardissimo and @Siegfried.V for accepting it as an answer. As suggested, I put here the code so that you can accept it as an answer.

public IDataReader ExecuteReader(string query) 
    {
         IDataReader dataReader = null; 
       if (this.isMySQL) 
      {
         MySqlCommand cmd = new MySqlCommand(query.Replace("[myDataBase].", ""), 
         connection); 
         dataReader = cmd.ExecuteReader(); 
      } 
      else 
      { 
        SqlCommand cmd = new SqlCommand(query, connection); 
        dataReader = cmd.ExecuteReader(); 
      }
        return dataReader; 
  }

Answer 2

Thanks zacs for his solution, I put here the code :

public List<Profil> Select_profil(string query)
        {
            List<Profil> list = new List<Profil>();
            if (this.OpenConnection() == true)
            {
                IDataReader dataReader = ExecuteReader(query);
                while (dataReader.Read())
                {
                    ...
                    ...
                }
                this.CloseConnection();
            }
            return list;
        }

Then ExecuteReader function :

public IDataReader ExecuteReader(string query)
        {
            IDataReader dataReader = null;
            if (this.isMySQL)
            {
                MySqlCommand cmd = new MySqlCommand(query.Replace("[v-steel].", ""), connection);
                dataReader = cmd.ExecuteReader();
            }
            else
            {
                SqlCommand cmd = new SqlCommand(query, MSconnection); 
                dataReader = cmd.ExecuteReader();
            }
            return dataReader;
        }

Regarding connection ant MSconnection, these are two private objects :

private MySqlConnection connection;
private SqlConnection MSconnection;

Answer 3

If you wish to use different database engines and select either one in a particular installation from settings, you should have a data access layer that implements an interface for the optional database engines. For this, one option is to have an abstract class and inherit from it to implement peculiarities for each database engine. Have a factory that instantiates the required implementation and use it.