How to design an object oriented C# class for all database operations like execute query, execute SP, execute scalar query, etc?

Question

A class with methods to execute a query, execute stored procedures, execute scalar query, basically all database operations for an Oracle and Microsoft databases. Currently I have one class with different methods for each operation, but there is a lot of repeat code. How to design this in a proper object oriented way?

Here is what I have currently: I have similar method like execute for execute scalar that returns a string, execute scalar that returns an int, etc.

public class DBoperations
{
    private string querystringval;
    private string logfileloc;
    private string connectionstringval;
    LogToFile logobj = new LogToFile();
    SqlConnection SqlConn = new SqlConnection();
    public string logfilelocval
    {
        get { return logfileloc; }
        set { logfileloc = value; }
    }
    public string queryValue
    {
        get { return querystringval; }
        set { querystringval = value; }
    }
    public string connectionvalue
    {
        get { return connectionstringval; }
        set { connectionstringval = value; }
    }

    public Boolean connecttodb()
    {
        logobj.fileName = logfilelocval;
        //SqlConnection SqlConn = new SqlConnection(connectionvalue);
        SqlConn.ConnectionString = connectionvalue;
        try
        {
            SqlConn.Open();
            logobj.MyLogFile("**SUCCESS** ", "Database connection opened");
            return true;
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
            logobj.MyLogFile("**FAILURE**", "Database Connection Failed" + e.Message);
            return false;
            throw new IndexOutOfRangeException();
        }
    }
    public string executeaquery()
    {
        try
        {
            SqlCommand querystring = new SqlCommand(queryValue, SqlConn);
            querystring.CommandTimeout = 90;
            querystring.ExecuteNonQuery();
            logobj.MyLogFile("**SUCCESS** ", "Query Executed Successfully.");
            querystring.Dispose();
            return "True";
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
            logobj.MyLogFile("**FAILURE**", "Query did not execute." + e.Message);
            return e.Message;
            throw new IndexOutOfRangeException();
        }
    }
 }}

Answer 1

So it appears you're just trying to abstract away the implementation so that you can connect to different database engines. This can be done better using something like Dapper because it simply extends the IDbConnection interface. This means that any database engine that has a connection object that implements IDbConnection can be used; incidentally that's all of them. :D

Alright, so with Dapper you might do something like this:

using (SqlConnection c = new SqlConnection(connString))
{
    var customer = c.Query<Customer>(
        "SELECT FirstName, LastName, DateOfBirth FROM Customer WHERE CustomerID = @CustomerID",
        new { CustomerID = 1 });
}

and that would get you a Customer. Now, abstracting this is much easier. Now we simply build an interface that handles Query and Execute, the two methods you're going to use with Dapper:

public interface IMyConnection
{
    TModel Query<TModel>(string sql, object parms);

    int Execute(string sql, object parms);
}

Now that we have an interface, we can build different concrete versions of that interface like this:

public class MsSqlMyConnection : IMyConnection
{
    public TModel Query<TModel>(string sql, object parms)
    {
        using (SqlConnection c = new SqlConnection(connString))
        {
            return c.Query<TModel>(sql, parms);
        }
    }

    public int Execute(string sql, object parms)
    {
        using (SqlConnection c = new SqlConnection(connString))
        {
            return c.Execute(sql, parms);
        }
    }
}

Okay, so now we have one concrete, but that could use any type of IDbConnection, like the OracleConnection. Now that we have that, instead of building a connection, we build one of our connections:

var c = new MsSqlMyConnection();
var customer = c.Query<Customer>(
    "SELECT FirstName, LastName, DateOfBirth FROM Customer WHERE CustomerID = @CustomerID",
    new { CustomerID = 1 });

There are many other methods that Dapper builds that can return collections of types and even multiple types at the same time. You just need to extend your interface to support your needs, that's it. You could then use a dependency injection container to instantiate the right concrete class during execution. Research the use of containers like StructureMap or Unity.

The DI container would replace this line:

var c = new MsSqlMyConnection();

because it would go get the right concrete based on configuration.