w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to design an object oriented C# class for all database operations like execute query, execute SP, execute scalar query, etc?

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.





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.