Use Dependency Injector Ninject to dynamically choose between ORM's (Entity Framework or Dapper) and Databases (SQL Server or Oracle Database)

• Download DI_DB.zip - 1.5 KB
• Download DI.zip - 8 MB

Idea

The idea is to use Dependency Injector (DI) framework Ninject to dynamically target our application to SQL Server or Oracle database either by using Entity framework (EF) or Dapper ORM.

Summary

In this article, we are going to create a simple Asp.Net MVC application which will interact with Data Access layer to perform basic CRUD operations. The main focus will be on designing Data Access Layer to target our application to SQL Server or Oracle dynamically. The other interesting thing here is to choose Entity Framework (EF) or Dapper as our underlying Object-relational mapping (ORM). Best thing is all dynamic with simple flag switches.

Before getting into details lets take a quick look at SOLID principles and Dependency Inversion Principle (DIP)

Quick note about SOLID principle and DIP

SOLID Principles:

1. Single Responsibility Principle (SRP): A class should have only a single responsibility (i.e. only one potential change in the software's specification should be able to affect the specification of the class
2. Open/Closed Principle (OCP): “software entities … should be open for extension, but closed for modification.”
3. Liskov Substitution Principle (LSP): “objects in a program should be replaceable with instances of their subtypes without altering the correctness of that program.”
4. Interface Segregation Principle (ISP): “many client-specific interfaces are better than one general-purpose interface.”
5. Dependency Inversion Principle (DIP): one should “Depend upon Abstractions. Do not depend upon concretions.”

Dependency Inversion Principle:

1. High level modules should not depend upon low level modules. Both should depend upon abstractions.
2. Abstractions should not depend upon details. Details should depend upon abstractions.

Dependency Inversion states that the conventional dependency relationships within the project from high-level modules to low-level dependency modules are inverted thus rendering high-level modules independent of the low-level module implementation details.

Process:

1. To create Entity Data Model Layer to hold Context and Entities using Entity Data Model Code-First approach.
2. Create a Data Access Layer following Unity and Dependency Inversion design patterns which facilitate us to configure Ninject at #3.
3. Create a UI Layer to import & configure global settings for Ninject to inject abstract implementation and perform basic CRUD operations

To create Entity Data Model Layer to hold Context and Entities using Entity Data Model Code-First approach

Start by creating a new project “EntityModel” of type “Class Library” with the solution name “DI”

Delete the default class that is being offered by Visual Studio from the project. Now go and add a new folder “DomainModel” in our project. Then add new item “ADO.Net Entity Data Model” with the name “DiContext” which acts as a medium between UI and Database to pass data back and forth.

From the "Entity Data Model Wizard" window, select “Empty Code First Model” and click Finish

Now, comment the default constructor and add another one which accept connection string as a parameter.

public DiContext(string conString) 
: base(conString)
{
    Database.SetInitializer<DiContext>(null);
}

Although the following method is not required, we will override a method “OnModelCreating” in DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}

Now that our "DiContext" is ready, we can add POCO/model classes as separate files within “DomainModel” folder and add their references (DbSet) in context class. In this sample we are dealing with single table called "Company". Add new class “Company.cs” to our DomainModel folder. Here is the database table structure:

GO

CREATE TABLE [dbo].[Company](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Description] [nvarchar](500) NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NULL,
    [Status] [int] NOT NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now add Company as DbSet to our DiContext using the following statement to access database table from our context.

public virtual DbSet<Company> Company { get; set; }

With this we are ready with our “Company” entity within our “EntityModel” project. Now we will create Data Access Layer with Database and ORM specific generic abstracts and repository classes.

Create a Data Access Layer by following Unity and Dependency Inversion design pattern which facilitate us to configure Ninject in #3

Add another class library project called “DataAccess” to our solution and delete the default class file that is being generated.

Create three new folders “Interface”, “Abstract” and “Repository” to hold corresponding files and classes. Now our solution will look like this

Our Interface, Generic Abstract and Repository class file relationship should be like this...

Create a new interface “IRepositoryBase.cs” under Interface folder that implements IDisposable interface

public interface IRepositoryBase : IDisposable
{
    bool CanDispose { get; set; }
    void Dispose(bool force);
}

Now, we will create generic abstract repository base “EfRepositoryBase.cs” specific to Entity Framework that implements IRepositoryBase interface

Add required using statement for IRepository base interface.

Note: In order to use context class we need to add reference to Entity Framework in “DataAccess” project. Right click “References” in DataAccess project and select “Manage Nuget Packages…” and install Entity Framework package. Also add reference to “EntityModel” library to DataAccess project to use DiContext.

Also, notice that there is no default constructor. We will be injecting context as a parameter to the constructor. If Ninject is not properly configured and we make use of this class without a default constructor will throw an error message.

Now create another generic abstract base repository “DapperRepositoryBase.cs” in Abstract folder specific to Dapper which also implements IRepositoryBase interface. Replace the new class with the following code.

After adding valid namespace for Interface, go and add “Dapper dot net” ORM package from NuGet.

Use the following namespace for Dapper:

using Dapper;

Now it’s time to create our module specific interface in “Interface” folder and then its implementation in “Repository” folder.

Add new interface “ICompanyRepository.cs” in“Interface” folder which implements IRepositoryBase interface.

public interface ICompanyRepository : IRepositoryBase
{
    List<Company> GetCompanies();
    Company GetCompanyById(int id);
    int InsertCompany(Company company);
    int UpdateCompany(Company company);
    bool DeleteCompany(int id);
}

Add using statement to refer “EntityModel.DomainModel”

using EntityModel.DomainModel;

Let’s start implementing concrete behavior for interface ICompanyRepository by creating ORM and Database specific repositories. First we will create folder structure and then add repository classes. Create two folders “Dapper” and “EF” in “Repository” folder for ORM specific. Then create two new folders “MSSQL” and “ORA” in “Dapper” folder specific to corresponding database.

Notice that we have only one repository for Entity Framework. Why because, we can use the same repository with Linq expressions to target any database.

EF Repository:

Let us first implement EF repository “CompanyEfRepository”. Create a new class that implements abstract class EfRepositoryBase<Company> and ICompanyRepository interface. Since we are implementing ICompanyRepository we need to implement all methods that are declared in the base interface.

Note: Check to see that we have single constructor with context as parameter and there is no default constructor. For obvious reason context will be injected dynamically.

ORM Specific Repository:

Now we will create repositories under Dapper ORM when using SQL Server as database. Create a new class “CompanySqlRepository.cs” in “MSSQL” folder which should implement DapperRepositoryBase and ICompanyRepository.

Note: We need to create a two parameter constructor which accepts open connection interface IDbConnection and a connection string. Here is the constructor:

public CompanySqlRepository(IDbConnection conn, string conString)
{
    _connection = conn;
    _connection.ConnectionString = conString;
}

Apply the following code to implement CRUD operations using Dapper for SQL Server.

Use the following SQL script to create required table and stored procedure.

Download DI_DB.zip

Now we will create another repository “CompanyOraRepository.cs” in Dapper/ORA folder for Oracle client same as SQL repository with two parameter constructor. Add the following code to implement CRUD operations.

Note: Since I don’t have access to Oracle client to test the implementation, I left it to throw NotImplementedException()

Now this is how our folder structure looks like after adding all required interfaces, abstracts and repository files.

Build the entire solution once. There should be now build errors. Perfect

Now that everything is set with interfaces, unity abstracts and repositories, we will go ahead and create MVC project “UI” to configure and utilize injection mechanism.

Create a UI Layer to import & configure global settings for Ninject to inject abstract implementation to perform basic CRUD operations

Create a new Asp.Net Web Application within the same solution.

Select “MVC” from the template:

Change authentication to “No Authentication” as our focus is not on authentication in this article.

Click “Ok” to create a new UI project.

Delete the default "HomeController" from the Controller folder as well as its corresponding views from the Views folder. Create a new "MVC 5 Controller - Empty" controller with the name "CompanyController" and delete default action method.

Declare an object of type interface “ICompanyRepository” to hold an injected object set via parameterized constructor.

Note: There is no default parameter less constructor.

private ICompanyRepository _repo;
public CompanyController(ICompanyRepository repo)
{
    _repo = repo;
}

Now add reference to both EntityModel and DataAccess project to UI from add references.

Add the following code to the “CompanyController.cs” to perform Get All, Get by Id, Insert, Update and Delete operations.

Also add the required views in the Views folder for Company Controller. (Refer source code attached for corresponding views)

Global Settings & Configurations:

This is the key to the entire application whether to go with Entity Framework or Dapper and to switch between SQL Server and Oracle as database. So lets begin adding some settings in our web.config file.

<add key="UseEF" value="true" />
<add key="TargetDataBase" value="mssql" /><!--oracle-->

These are the global settings whether to use Entity Framework or not and to which database we are targeting to.

Also add two connection string settings in web.config file. One for SQL Server and another for Oracle database.

<connectionStrings>
    <add name="mssql" connectionString="Password=sa!2015;Persist Security Info=True;User ID=sa;Initial Catalog=DI;Data Source=MYLAP2" />
    <add name="oracle" connectionString="!No Connection String Provided!" />
</connectionStrings>

Now we got Data Access project ready with repositories and UI with views, controllers and global settings, lets add Ninject package to our project.

Ninject installation and configuration:

Right click on UI project references and select “Manage NuGet Packages...” under online search for Ninject and install “Ninject.MVC5”.

Accept the license terms to complete installation.

Once the installation is complete it will add a new class file “NinjectWebCommon.cs” to “App_Start” folder. This is where we set switch logic based on web.config settings.

Go ahead and add the following code to “RegisterServices” method to register our methods to the Ninject kernel.

Add necessary using statements to access configuration settings and repositories.

kernel.Bind<IDbConnection>().To<SqlConnection>().InRequestScope();

The above statement injects SqlConnection whenever we access high level module or interface IDbConnection. Usually we pass this as a constructor parameter or property to a class. In our case constructor of the class.

kernel.Bind<ICompanyRepository>().To<DataAccess.Repository.Dapper.MSSQL.CompanySqlRepository>().InRequestScope().WithConstructorArgument("conString", conString);

The above statement injects "CompanySqlRepository" when declaring its base interface "ICompanyRepository" along with the conString as a parameter.

Note: In order to use the context class we need to add reference to “Entity Framework” via. NuGet.

Also add reference to OracleClient located under Assemblies and use the namespace

using System.Data.OracleClient;

That’s it. So far soo good.

Now wait! before building and running the application let’s change the default router to point to our “GetCompanies” action method in Company Controller.

This is how our UI project structure looks like:

Build and run the application with the global settings set to use Entity Framework ORM and target SQL Server database:

First screen to appear "List" of Companies:

When clicking on "Create New":

After insertion it will show list of companies:

When clicked on "Edit" link for a record:

After update it will show updated Companies list:

When clicking on "Details" link for a record:

Now run the application by setting web.config app settings with Dapper as ORM to target SQL Server database.

Perfect isn’t it. If you have Oracle Database available try to complete the sample DI application with Oracle Repository and Client.

In this article we covered

1. Generic repository also called as Unity for both Entity Framework and Dapper
2. Implemented Dependency Inversion principle using Ninject
3. Implemented Repository Pattern
4. Implemented CRUD operations with Entity Framework as well as Dapper (with stored procedures only) and
5. Finally used how to configure Ninject to dynamically choose between ORM and Databases

Hope you got something to learn from this. We need to make certain changes to use this as production standard code. Rate this article and give me your suggestions in the comments section below.

My other articles if you might missed:

Implementing CRUD operations using AngularJs, Asp.Net MVC & Entity Framwork

Create a Responsive HTML Table using FooTable and Apply Client Side Binding using Handlebars.Js

Baby Steps towards ASP.NET 5 Web Application and see What’s New