Access different SQL Server Types such as TSql or MySql in the same method in C#

Question

Currently I'm writing a program to access multiple 'types' of SQL servers, such as TSQL or MySQL in C#. I created a base class DBConnector that has an abstract method:

public abstract class DBConnector
{
    //some other code...

    protected abstract int ExecuteNonQueryOrScalar(DbConnection con, string statement, bool executeAsScalar, int timeout = 20);

    //some other code...
}

Now I have two derived classes TSqlConnector and MySqlConnector which implement this abstract method:

For MySql it looks like this:

protected override int ExecuteNonQueryOrScalar(DbConnection con, string statement, bool executeAsScalar, int timeout = 20)
{
    int result = -1;
    using (MySqlCommand cmd = new MySqlCommand(statement, (MySqlConnection)con))
    {
        cmd.CommandTimeout = timeout;
        cmd.Connection.Open();

        if (executeAsScalar)
        {
            object resultObject = cmd.ExecuteScalar();
            if (resultObject is int tmpResult)
                result = tmpResult;
        }
        else
        {
            result = cmd.ExecuteNonQuery();
        }
    }
    return result;
}

For TSql it looks like this:

protected override int ExecuteNonQueryOrScalar(DbConnection con, string statement, bool executeAsScalar, int timeout = 20)
{
    int result = -1;
    using (SqlCommand cmd = new SqlCommand(statement, (SqlConnection)con))
    {
        cmd.CommandTimeout = timeout;
        cmd.Connection.Open();

        if (executeAsScalar)
        {
            object resultObject = cmd.ExecuteScalar();
            if (resultObject is int tmpResult)
                result = tmpResult;
        }
        else
        {
            result = cmd.ExecuteNonQuery();
        }
    }
    return result;
}

By the way, these methods also contain some error handling and some other stuff, but I simplified my methods for this post.

This method gets called in my other custom methods like that:

Code in my calling Insert-Method (obj is an instance of a model class which has the same properties as the database table, including ID):

obj.ID = ExecuteNonQueryOrScalar(GetConnection(), sql, true); //true for scalar-execution

Code in my calling Update-Method:

affectedLines = ExecuteNonQueryOrScalar(GetConnection(), sql, false); //false for nonQuery-execution

Code in my calling Delete-Method:

affectedLines = ExecuteNonQueryOrScalar(GetConnection(), sql, false); //false for nonQuery-execution

GetConnection() returns a DbConnection-Object which is either a SqlConnection or a MySqlConnection at runtime. sql is my sql-string. The boolean parameter decides whether to call ExecuteNonQuery or ExecuteScalar in ExecuteNonQueryOrScalar() as you can see in my code above.

Now to my questions:

1. As you can see, the code for the two implementations is almost the same. The only differences are the type of the connection and of the command. I have heard that you should follow the pattern "Don't repeat yourself". But I'm repeating myself here. Do you guys have an idea what I could do here? Or should I just stick what I currently have? I've had the idea to move the two methods to one single method in my base class DBConnector and work with generic parameters, which I limit with where T: IDBEntity and where K: DBConnection, but I get compile time errors when I do that. I couldn't really find a solution to prevent this.

2. Do you have any suggestions how to implement this differently? What would you change?

Let me know if I need to share more of my code.

Thank you for taking the time to read my question.

Edit: Solution to my question:
After I read the responses I realized how to improve my code. I moved my method ExecuteNonQueryOrScalar into the my base class DBConnector and added a IDbCommand-Parameter that can be any IDbCommand-Object at runtime. This method looks like this (simplified version):

protected int ExecuteNonQueryOrScalar(IDbCommand cmd, bool executeAsScalar, int timeout = 20)
{
    int result = -1;
    try
    {
        cmd.CommandTimeout = timeout;

        if (cmd.Connection.State != ConnectionState.Open)
            cmd.Connection.Open();

        if (executeAsScalar)
        {
            string resultObject = cmd.ExecuteScalar().ToString();

            if (Int32.TryParse(resultObject, out int tmpResult)) //if (resultObject is int tmpResult)
                result = tmpResult;
        }
        else
        {
            result = cmd.ExecuteNonQuery();
        }
    }
    //Some error handling...
    finally
    {
        if (cmd.Connection.State != ConnectionState.Closed)
            cmd.Connection.Close();
    }

    return result;
}

Here's an example how I call that ExecuteNonQueryOrScalar-Method in my Update-Method (simplified) in the same DBConnector-Class:

protected int Update<T, K>(T obj, List<DBCondition> filterValues = null) where T : IDBEntity, new() where K : IDbCommand, new()
{
    int affectedLines = -1;
    if (obj != null)
    {
        using (IDbCommand cmd = new K())
        {
            cmd.Connection = GetConnection();
            cmd.CommandText = obj.GetSqlUpdateStatement(DBMapping.GetDBMapping<T>(), _sqlSpecificSymbols, filterValues);
            affectedLines = ExecuteNonQueryOrScalar(cmd, false);
        }
    }
    return affectedLines;
}

And finally here are two examples how I call this update-method in my MySqlConnector or TSqlConnector:

MySqlConnector:

public override int Update<T>(T obj, List<DBCondition> filterValues = null)
{
    return base.Update<T, MySqlCommand>(obj, filterValues); //Call DbConnector.Update<T, K>() from the example above
}

TSqlConnector:

public override int Update<T>(T obj, List<DBCondition> filterValues = null)
{
    return base.Update<T, SqlCommand>(obj, filterValues); //Call DbConnector.Update<T, K>() from the example above
}

Please don't hesitate to ask me if you want to see more of my code! I could also upload my not-simplified original code to sites such as github if some of you want to go through everything I did.

Answer 1

Your idea is a good one. But someone on the .net team has saved you some time by creating interfaces that work this way, like IDBConnection and IDBCommand.

Then there are different concrete implementations of this interface that you can use when needed. For example IDBConnection con = new MySqlConnection for MySQL, or IDBConnection con = new SqlConnection for SQL Server.

These interfaces expose common methods, like ExecuteNonQuery. So what you're working with in your code is just a bunch of interfaces. Your code always passes around IDBConnections and IDBCommands, you just choose whichever implementation you need at construction time. This is dependency inversion.

Of course, when populating the actual text of the command, you will have to use the right SQL dialect.

Does this do what you were trying to do?