Click here to Skip to main content
15,867,308 members
Articles / Web Development / HTML

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

Rate me:
Please Sign up or sign in to vote.
4.96/5 (23 votes)
30 Nov 2015CPOL10 min read 47K   1.5K   35   16
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 using global settings

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”

Image 1

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.

Image 2

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

Image 3

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

C#
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

C#
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:

SQL
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.

C#
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.

Image 4

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

Image 5

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

Image 6

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

C#
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

Image 7

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.

Image 8

Image 9

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.

Image 10

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.

Image 11

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

Image 12

Use the following namespace for Dapper:

C#
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.

C#
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”

C#
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.

Image 13

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:

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

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

Image 14

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.

Image 15

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.

Image 16

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.

Image 17

Select “MVC” from the template:

Image 18

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

Image 19

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.

Image 20

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

Note: There is no default parameter less constructor.

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

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

Image 21

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

Image 22

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.

XML
<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.

XML
<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”.

Image 23

Accept the license terms to complete installation.

Image 24

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.

Image 25

Add necessary using statements to access configuration settings and repositories.

C#
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.

C#
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.

Image 26

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

C#
using System.Data.OracleClient;

Image 27

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:

Image 28

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:

Image 29

When clicking on "Create New":

Image 30

After insertion it will show list of companies:

Image 31

When clicked on "Edit" link for a record:

Image 32

After update it will show updated Companies list:

Image 33

When clicking on "Details" link for a record:

Image 34

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Mou_kol30-Jan-18 21:41
Mou_kol30-Jan-18 21:41 
QuestionHow to do the same when using Unity DI Pin
Mou_kol30-Jan-18 21:41
Mou_kol30-Jan-18 21:41 
QuestionWhy we need to use both ORM Pin
Mou_kol26-Jun-17 22:45
Mou_kol26-Jun-17 22:45 
QuestionHow to use Unity to choose ORM dynamically instead of Ninject Pin
Tridip Bhattacharjee29-Nov-16 20:56
professionalTridip Bhattacharjee29-Nov-16 20:56 
AnswerRe: How to use Unity to choose ORM dynamically instead of Ninject Pin
Sreekanth Mothukuru2-Dec-16 0:08
Sreekanth Mothukuru2-Dec-16 0:08 
GeneralRe: How to use Unity to choose ORM dynamically instead of Ninject Pin
Tridip Bhattacharjee5-Dec-16 2:14
professionalTridip Bhattacharjee5-Dec-16 2:14 
QuestionTwo questions to ask Pin
wmjordan12-Dec-15 4:17
professionalwmjordan12-Dec-15 4:17 
AnswerRe: Two questions to ask Pin
Sreekanth Mothukuru13-Dec-15 21:31
Sreekanth Mothukuru13-Dec-15 21:31 
GeneralRe: Two questions to ask Pin
wmjordan13-Dec-15 23:19
professionalwmjordan13-Dec-15 23:19 
Thank you very much for sharing your thoughts.

Actually I am running into a project which has to connect to two database products at the same time, MySQL and Oracle, which have similar table and view structures but not all the same. My work fellows are working with EF, and partially Dapper, and I am sticking with Dapper only, for its superior runtime performance, and observable SQL execution--compared to those SQL generated by EF LINQ which might not be so clean.

I think creating POCO classes would not be a big problem, compared to EF, which uses designers to create classes and entity relations. Dapper also supports Stored Procedures, so the coupling would not be that 'tight'.
GeneralRe: Two questions to ask Pin
Sreekanth Mothukuru14-Dec-15 6:52
Sreekanth Mothukuru14-Dec-15 6:52 
GeneralRe: Two questions to ask Pin
wmjordan14-Dec-15 13:46
professionalwmjordan14-Dec-15 13:46 
PraiseMy vote of 5 Pin
WagMelo1-Dec-15 16:01
professionalWagMelo1-Dec-15 16:01 
GeneralRe: My vote of 5 Pin
Sreekanth Mothukuru1-Dec-15 19:23
Sreekanth Mothukuru1-Dec-15 19:23 
GeneralMy vote of 5 Pin
Santhakumar M30-Nov-15 3:52
professionalSanthakumar M30-Nov-15 3:52 
GeneralRe: My vote of 5 Pin
Sreekanth Mothukuru30-Nov-15 5:55
Sreekanth Mothukuru30-Nov-15 5:55 
GeneralRe: My vote of 5 Pin
Santhakumar M30-Nov-15 17:10
professionalSanthakumar M30-Nov-15 17:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.