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();
        }
    }
 }}

you know or/m? search for. i believe you can solve your doubt with EntityFramework Example search for DTO too. — Daniel Melo
– Daniel Melo, Commented Apr 10, 2014 at 14:30
would be easier to guide you if you provided at least what you have tried so far. — Thewads
– Thewads, Commented Apr 10, 2014 at 14:30
This will almost certainly get closed. You will want to include some code samples and ask a more concrete question. — Jace Rhea
– Jace Rhea, Commented Apr 10, 2014 at 14:31
Why are the existing objects that you're already using SqlConnection, SqlCommand, etc, insufficient? What does this new class gain for code that's going to use it? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 10, 2014 at 14:47

Mike Perrenoud · Accepted Answer · 2014-04-10 15:03:04Z

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.

Thank you @Michael Perrenoud . I will do some reading on Dapper and IDBConnection interface, as its unfamiliar grounds for me and will have to spend some time before implementation.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related