Refactoring to use a generic function with different input objects

Question

I'd like to refactor some code that runs in a console app. The App updates an external database, and it was recently updated to support either MySQL or SQL Server. So now there are two nearly identical methods with a lot of duplicate code in them, because one has a method signature that uses MySqlConnection and MySqlCommand (etc) and the other uses SqlConnection and SqlCommand (etc).

The code is essentially identical, other than the obvious differences in the ADO objects.

What I'd like to do is something like the following. I've seen several posts here on SO (for e.g. How do I use reflection to call a generic method? ) as well as other sites that show how to set this up with a dynamic type, which is great, except that none of the examples do anything more than write foo.GetType() in the generic method to prove that the dynamic type is correct.

So, how do you call a method on that dynamic type? Of course, when I tried to set this up, trying to call the Open() method on the sqlConnection parameter doesn't compile.

Here's sort of what I'm trying to accomplish:

private static void TransferXmlData(ExportManifest m_settings, XmlNodeList xmlNodeList)
{
    if (m_Settings.ServerType.ToLower() == "mysql")
    {
        using (MySqlConnection mySqlConnection = new MySqlConnection(m_Settings.TargetData.ConnectionString))
        {
            MySqlCommand mySqlCommand = 
                new MySqlCommand(Program.GetCommandTextTemplate(m_settings), mySqlConnection);
            PrepareSqlCommand(mySqlConnection, mySqlCommand, m_settings)
        }
    }
    else
    {
        using (SqlConnection sqlConnection = 
            new SqlConnection(m_Settings.TargetData.ConnectionString))
        {
            SqlCommand sqlCommand = 
                new SqlCommand(Program.GetCommandTextTemplate(m_settings), sqlConnection);
            PrepareSqlCommand(sqlConnection, sqlCommand, m_settings)
        }
    }
}

private static void PrepareSqlCommand<T>(T sqlConnection, T sqlCommand, ExportManifest m_settings)
{
    // Potentially a lot of code here that looks just like the 
    // code in the else block, Except that it uses the 
    // MySqlConnection objects instead of SqlConnection
    // Do some stuff
    sqlConnection.Open();  // obviously doesn't work
}

Thanks in advance!

Can't you make your method expecting an `IDbConnection` and an `IDbCommand` ? — Sehnsucht, Oct 22 '16 at 00:25

Rajput · Answer 1 · 2016-10-23T19:03:17.487

May be you can Implement factory design pattern(If you do not want to go with generics, this is my opinion you can think about it.) This will help you to prevent code duplication.

Implement your Factory class.

`

Public class Factory
    {
        public static IDbConnection createDbInstance(ExportManifest m_settings)
        {
            if (m_Settings.ServerType.ToLower() == "mysql")
            {
                 return new MySqlConnection();

            }

            else
               return new SqlConnection();
        }
    } `

and in your actual method you can use IDbConnection and IDbCommand

private static void TransferXmlData(ExportManifest m_settings, XmlNodeList xmlNodeList)
{
    IDbConnection db = Factory.createDbInstance(m_settings);
                db.ConnectionString = m_Settings.TargetData.ConnectionString;
                IDbCommand comnd = db.CreateCommand();

                comnd.CommandText = Program.GetCommandTextTemplate(m_settings);
                comnd.CommandType = CommandType.Text;
               // db.Open(); if you want to open connection here
                PrepareSqlCommand(db, comnd, m_settings);

}

private static void PrepareSqlCommand(IDbConnection sqlConnection, IDbCommand sqlCommand, ExportManifest m_settings)
{
    // Potentially a lot of code here that looks just like the 
    // code in the else block, Except that it uses the 
    // MySqlConnection objects instead of SqlConnection
    // Do some stuff
    sqlConnection.Open(); 
}

score 0 · Answer 2 · answered Oct 22 '16 at 01:37

In order to write your data access code once, but be able to switch out your implementation based on some logic, you should be coding against IDbConnection.

Something to the effect of:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString))

        {

            //execute database actions against IDBConnection               

        }

In my opinion, this is very well established using the repository pattern, and it shields you from database implementation details, but proper implementation of the pattern might over-complicate your use case. As far as logic to decide which connections get new(), a factory approach as stated above is sufficient, but you could just as easily pass an enum flag if this is a trivial app. In large scale software, you'd typically want to use an Inversion of Control container to control a specifc instance of IDbConnection to get injected in. In any case, reflection, generics, and dynamics would be the wrong tools here (barring any object mapping).

romain-aga · Answer 3 · 2016-10-24T09:14:38.793

As @Sehnsucht said, you could do something like that:

private static void TransferXmlData(ExportManifest m_settings, XmlNodeList xmlNodeList)
{
    if (m_Settings.ServerType.ToLower() == "mysql")
        Connect(connectionString => new MySqlConnection(connectionString),
            (text, connection) => new MySqlCommand(text, connection));
    else
        Connect(connectionString => new SqlConnection(connectionString),
            (text, connection) => new SqlCommand(text, connection));
}

private static void Connect(ExportManifest m_settings,
    Func<string, IDbConnection> createConnection,
        Func<string, IDbConnection, IDbCommand> createCommand)
{
    using (IDbConnection mySqlConnection =
        createConnection(m_Settings.TargetData.ConnectionString))
    {
        IDbCommand mySqlCommand =
            createCommand(Program.GetCommandTextTemplate(m_settings), mySqlConnection);
        PrepareSqlCommand(mySqlConnection, mySqlCommand, m_settings);
    }
}

private static void PrepareSqlCommand(IDbConnection sqlConnection,
    IDbCommand sqlCommand, ExportManifest m_settings)
{
    sqlConnection.Open();
}

Both SqlConnection and MySqlConnection inherit from DbConnection which implements IDbConnection. Same go for SqlCommand and MySqlCommand, they implement IDbCommand.
Then, you can use the interfaces to merge your code.

But if, for some reasons, you will need to work with the real types (as return values). You can change your methods for something like this:

private static void Connect<TConnection, TCommand>(ExportManifest m_settings,
        Func<string, TConnection> createConnection,
        Func<string, TConnection, TCommand> createCommand)
    where TConnection : IDbConnection
    where TCommand : IDbCommand
{
    using (TConnection mySqlConnection =
        createConnection(m_Settings.TargetData.ConnectionString))
    {
        TCommand mySqlCommand =
            createCommand(Program.GetCommandTextTemplate(m_settings), mySqlConnection);
        PrepareSqlCommand(mySqlConnection, mySqlCommand, m_settings);
    }
}

private static void PrepareSqlCommand<TConnection, TCommand>(TConnection sqlConnection,
        TCommand sqlCommand, ExportManifest m_settings)
    where TConnection : IDbConnection
    where TCommand : IDbCommand
{
    sqlConnection.Open();
}

Refactoring to use a generic function with different input objects

3 Answers3