Code duplication, same functionality different types

Question

I have this method that works with Npgsql:

private DataTable GetTableN(string sql, string[] pars)
    {
        NpgsqlCommand zapytanie = new NpgsqlCommand(sql, connn, trann);
        NpgsqlDataAdapter da = new NpgsqlDataAdapter();
        DataSet ds = new DataSet();
        try
        {
            if (pars != null)
            {
                for (int i = 0; i < pars.Length; i++)
                {
                    zapytanie.Parameters.AddWithValue("@param" + i, pars[i]);
                }
            }
            connn.Open();
            da.SelectCommand = zapytanie;
            da.Fill(ds);
            return ds.Tables[0];
        }
        catch (NpgsqlException e)
        {
            throw (new SqlException(e.Message.ToString()));
        }
        finally
        {
            connn.Close();
            zapytanie.Dispose();
            da.Dispose();
            ds.Dispose();
        }
    }

now I need to have exactly the same method but using Odbc instead. I'd only need to make these changes

1. NpgsqlCommand to ObdcCommand
2. NpgsqlDataAdapter to OdbcDataAdapter
3. NpgsqlException to OdbcException

How do I merge this in order to avoid code duplication and have only one method ?

Answer 1

you can try to change signature of your method

private DataTable GetTableN(string sql, string[] pars, DbCommand zapytanie, DbDataAdapter da)
{   
    DataSet ds = new DataSet();
    try
    {
        if (pars != null)
        {
            for (int i = 0; i < pars.Length; i++)
            {
                zapytanie.Parameters.AddWithValue("@param" + i, pars[i]);
            }
        }
        connn.Open();
        da.SelectCommand = zapytanie;
        da.Fill(ds);
        return ds.Tables[0];
    }
    catch (DbException e)
    {
        throw (new SqlException(e.Message.ToString()));
    }
    finally
    {
        connn.Close();
        zapytanie.Dispose();
        da.Dispose();
        ds.Dispose();
    }
}

or use some kind of factory

private DataTable GetTableN(string sql, string[] pars, MyFactory factory)
{
    DbCommand zapytanie = factory.CreateCommand(...);
    DbDataAdapter da = new factory.CreateAdapter(...);
    ...
}

or you able to use inheritance for that

public abstract class MyClass
{
    private DataTable GetTableN(string sql, string[] pars)
    {
        DbCommand zapytanie = CreateCommand();
        DbDataAdapter da = CreateAdapter();
        ...
    }

    protected abstract DbCommand CreateCommand();
    protected abstract DbDataAdapter CreateAdapter();
}

public class OdbcClass : MyClass
{
    protected override DbCommand CreateCommand()
    {
        // create for odbc
    }

    protected override DbDataAdapter CreateAdapter()
    {
        // create for odbc
    }
}

public class PostgrClass : MyClass
{
    protected override DbCommand CreateCommand()
    {
        // create for postgr
    }

    protected override DbDataAdapter CreateAdapter()
    {
        // create for postgr
    }
}