Friday, May 11, 2018

Part 1: Integrating MVC 5 with Dapper and Unity

Hi there, while thinking of ORM (Object Relational Mapping) I got eager to check what kind of integrations we can have using multiple ORM’s with deferent Dependency Injection mechanism. This is the first article in the series “Integrating MVC 5 with Dapper as ORM and Unity as the Dependency Injection technique”

Tools used in this article: Visual Studio 2017 and SQL Local DB (2014). Source code for this tutorial can be find at : https://github.com/buddike1112/DIIntegration
Let’s start:

1.   Open Visual Studio 2017 Community Edition and select .NET framework as “4.7” and select blank solution template. Then give the name as “VehiclePortal”. Click “OK”.



2.       Let’s create MVC 5 empty project. In Visual Studio, Click File -> New -> Project, then type asp.net in search box. Select highlighted item in below screenshot. Enter “Name” as “VehicleManager”, select “Solution” as “Add to Solution” (so new project will be added to the solution)



3.       Click “Empty” and “MVC” check boxes. Don’t worry about Unit tests at the moment, I’m planning to do separate article for TDD (Test driven development) as well as BDD (Behavior driven development)



MVC 5 project will get created.

4.       Let’s install required Nuget Packages for our lab. Right click on the new project we created and select “Manage Nuget Packages…”



5.       Let’s install our IoC container to address Dependency Injection. Type “Unity” in the search box and install package “Unity.Mvc”. This action will install 3 packages, “Unity.Mvc”, “Unity.Container” and “Unity.Abstractions”



6.       Let’s install Dapper, to work as our ORM (Note: Dapper is a Micro-ORM (Object Relational Mapping) and is good for database queries other than Full ORM. Read more from here : http://gunnarpeipman.com/2017/05/micro-orm/)



Note: UnityConfig.cs and UnityMvcActivator.cs files. These will be used for our future implementation

7.       Now it’s time to create our simple database. Let’s name it as “VehicleDB”. Create the DB project from File -> New -> Project -> Search SQL project template.



8.    Create db schema folder called “dbo” under our “VehicleDB” project, so all our stored procedures, table definitions can stay there.

9.     Create “Makes”, “VehicleTypes”, “Models” and “Vehicles” tables using below sql scripts

Creating “Makes” table
Notes: Our primary key is auto populating Identity field

CREATE TABLE [dbo].[Makes]
(
    [MakeId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] VARCHAR(200) NULL
)

Creating “VehicleTypes” table

CREATE TABLE [dbo].[VehicleTypes]
(
            [VehicleTypeId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
            [Name] VARCHAR(100) NULL
)

Creating “Models” table
CREATE TABLE [dbo].[Models]
(
          [ModelId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
          [ModelName] VARCHAR(50) NULL,
          [EngineSize] VARCHAR(50) NULL,
          [NumberOfDoors] INT NULL,
          [Colour] VARCHAR(50) NULL,
          [VehicleTypeId] INT NOT NULL,
      CONSTRAINT FK_VehicleTypeId FOREIGN KEY (VehicleTypeId) REFERENCES VehicleTypes      (VehicleTypeId)
)

Creating “Vehicles” table
CREATE TABLE [dbo].[Vehicles]
(
          [VehicleId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
          [MakeId] INT NULL,
          [ModelId] INT NULL,
          [Year] INT NULL,
          [Price] DECIMAL NULL,
          [SoldDate] DATE NULL,
          CONSTRAINT FK_MakeId FOREIGN KEY (MakeId) REFERENCES Makes (MakeId),
          CONSTRAINT FK_ModelId FOREIGN KEY (ModelId) REFERENCES Models (ModelId)
)

Notes: You can add xml comments to each table definition, so table’s purpose will be more visible. Hope you noted, Models table have foreign key reference from VehicleTypes table. Also Vehicles table is consist of few foreign key references from Models and Makes tables

10.   Now let’s create seed test data scripts, so we don’t need to worry about CRUD actions, since our primary target is to study dependency injection and ORM integration. We are going to add PreDeploymentSeedData and PostDeploymentSeedData. Select DB project -> Right click -> Add  -> Select “Script” -> Select PostDeployment script and name as “Script.PostDeployment”. Then paste below script code over there, so we have some dummy content.

USE VehicleDB

GO

INSERT INTO [dbo].[Makes]
VALUES('TOYOTA')

INSERT INTO [dbo].[Makes]
VALUES('NISSAN')

GO

INSERT INTO [dbo].[VehicleTypes]
VALUES('SEDAN')

INSERT INTO [dbo].[VehicleTypes]
VALUES('SUV')

INSERT INTO [dbo].[VehicleTypes]
VALUES('HATCHBACK')

GO

INSERT INTO [dbo].[Models]
VALUES('COROLLA', '1800', 5, 'WHITE', 1)

INSERT INTO [dbo].[Models]
VALUES('XTRAIL', '2400', 5, 'BLACK', 2)

INSERT INTO [dbo].[Models]
VALUES('VITZ', '1500', 5, 'RED', 3)

GO

INSERT INTO [dbo].[Vehicles]
VALUES(1, 1, 2013, 5000.00, NULL)

INSERT INTO [dbo].[Vehicles]
VALUES(1, 3, 2014, 8000.00, '2018-01-01')

INSERT INTO [dbo].[Vehicles]
VALUES(1, 3, 2011, 4000.00, NULL)

GO

11.   Now we can create some stored procedures to retrieve data. My main target is to List all vehicles. So we may need to resolve multiple dependencies from VehicleTypes, Models, Makes which were directly affecting list view.

12. Create new stored procedure called “usp_GetVehicles.sql” inside a folder called “StoredProcedures”, as for the best practice include verb and the type of the data set returning from the stored procedure. For the user defined stored procedures, we use “usp_”. Paste below sql code block into the newly created stored procedure.

/*
   Author : XXXXXXX
   Description : Listing all the unsold vehicles
   Parameters :
   Example execution :
      exec usp_GetVehicles

*/

CREATE PROCEDURE [dbo].[usp_GetVehicles]
AS
BEGIN
       SET NOCOUNT ON;
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

   SELECT MAKE.[Name] AS Make, MODEL.[ModelName] AS Model, V.[Year], V.[Price]
   FROM [dbo].[Vehicles] V
   INNER JOIN [dbo].[Makes] MAKE
      ON MAKE.[MakeId] = V.[MakeId]
   INNER JOIN [dbo].[Models] MODEL
      ON MODEL.[ModelId] = V.[ModelId]
   WHERE V.[SoldDate] IS NULL
   ORDER BY V.[Price] DESC
END

Notes: As you can see, I’m using few SQL terms as well as documentation in stored procedure. You are free to follow the documentation practice above, I bet it will appreciate by your code reviewer J.
NOCOUNT ON – We don’t want to return the count of records, so stored proc performing well
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED – We are performing dirty reads. Please research about this, refer this link

After you have completed above tasks in SQL project, your project structure should be like below:



13. Now we have database completed and we need to build connectivity with our MVC project. I’m planning to create Database Manager who is responsible to manage SQL DB connections and maintain single collection, so we will apply singleton pattern to the Database Manager. Whenever repository member need a connection, Database Manager will provide on the go.

14. Let’s create a folder called “Data” in the MVC project. Then add folder called “Interfaces”, after that add “public” interface called “IDatabaseManager”. Finally create inherited class call “DatabaseManager” inside “Data” folder. After completing the tasks, it may look like below:




In “IDatabaseManager” interface add below method definition, so we can implement the method in derived class “DatabaseManager”

IDbConnection GetSqlConnection();

In “DatabaseManager” add below implementation

public IDbConnection GetSqlConnection()
              {
                     IDbConnection connection = new SqlConnection(this.connectionString);
                     connection.Open();
                     return connection;
       }

As you can see, I’m getting “connectionString parameter” from a property. So let’s store our database connection string in Web.config

Add below section just after <appsettings> section

<connectionStrings>
    <add name="VehiclePortalDBConnection" providerName="System.Data.SqlClient" connectionString="Server=(localdb)\MSSQLLocalDB;Database=VehicleDB;Trusted_Connection=True;MultipleActiveResultSets=True" />
  </connectionStrings>

Let’s create a read-only property to store our database connection string. Final implementation for our “DatabaseManager” is like below:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace VehicleManager.Data
{
    public class DatabaseManager : IDatabaseManager
    {
        private readonly string connectionString;

        public DatabaseManager()
        {
            this.connectionString = ConfigurationManager.ConnectionStrings["VehiclePortalDBConnection"].ConnectionString;
        }

        public IDbConnection GetSqlConnection()
        {
            IDbConnection connection = new SqlConnection(this.connectionString);
            connection.Open();
            return connection;
        }
    }
}

Notes: You can use GhostDoc to create documentation for the members and it’s so easy.

15. Now we got our own “DatabaseManager” to handle connections. It’s time to create our repository calls. Let’s create another “public” interface inside “Interfaces” folder, called “IVehicleRepository” so Repository can inherit. Then create repository class called “VehicleRepository”. After successful creation, they stays like below:



Let’s create our Database calls in Repository methods. Add below method definition to the “IVehicleRepository” interface.

public interface IDatabaseRepository
    {
        IEnumerable<VehicleDto> GetVehicles();
    }

Then add method implementation in repository class. Copy and paste below code

public class VehicleRepository : IVehicleRepository
    {
        public IDatabaseManager Manager { get; set; }

        public VehicleRepository(IDatabaseManager manager)
        {
            if (Manager == null)
            {
                Manager = manager;
            }
        }

        public IEnumerable<VehicleDto> GetVehicles()
        {
            using (IDbConnection conn = Manager.GetSqlConnection())
            {
                try
                {
                    return conn.Query<VehicleDto>("usp_GetVehicles", commandType: CommandType.StoredProcedure);
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("Exception occurred", ex.Message);
                    return null;
                }
            }
        }
    }

Notes: Above “GetVehicles” method we are applying “using” block with connection we acquired from the “DatabaseManager” instance, so instance will automatically under garbage collection. Once it’s done with its purpose, GC will perform it’s part. Notice we used “Dapper” extension method to query and convert it to our DTO (Data Transfer Object). Let’s create a folder under “Data” called “Dto” and create a class called “VehicleDto” and copy / paste below code:

public class VehicleDto
    {
        public string Make { get; set; }

        public string Model { get; set; }

        public int Year { get; set; }

        public double Price { get; set; }

    }

Notes: Make sure your Dto reflect your view requirements, otherwise we may end up with double work in scaffolding step. Also please resolve VehicleDto object in IVehicleRepository and VehicleRepository

16. Let’s add the “VehiclesController” under “Controllers” folder in MVC project. Copy and paste below code inside “VehiclesController”

    public IVehicleRepository vehiclesRepo { get; set; }

        public VehiclesController(IVehicleRepository repository)
        {
            vehiclesRepo = repository ?? throw new ArgumentNullException("Repository is null");
        }

        public ActionResult Index()
        {
            try
            {
                IEnumerable<VehicleDto> list = vehiclesRepo.GetVehicles();
                return View(list);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception occurred : {ex.Message}");
                return null;
            }
        }


17. Let’s create Vehicles Index view from the controller. Double click the index Action, right click and select “Add View”. Then fill the form like below




Click “Add” (scaffolding)

18. Go to App_Start folder and open “RouteConfig” file and update default settings with below:

public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            //enable attribute routing
            routes.MapMvcAttributeRoutes();

            //convention-based routes
            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Vehicles", action = "Index", id = UrlParameter.Optional }
            );
        }
    }

Since our project is not large, we just update default controller to “Vehicles”.

19. Open “UnityConfig.cs” file and copy and paste below code in “RegisterTypes” method:

       container.RegisterSingleton<IDatabaseManager, DatabaseManager>();
container.RegisterType<IVehicleRepository, VehicleRepository>();

Notes: Here we are registering our Database manager and repository. You need to register each type in here to resolve all the dependency injection issues. Better to follow design patterns as much as possible.

20. Let’s deploy our database to the sql instance. Open command prompt. Create sql instance called “MSSQLLocalDB” if it’s not available. Then publish our database project. If you completed all the tasks, database will get published without any issue and post deployment script will run and populate data to the table. Notes: Check SQL project properties “Target Platform”, this should be SQL Server 2014.

21. If you completed all the tasks MVC view should list down all the “Unsold” vehicles in Vehicle table like below:




Cool! That’s it.

No comments:

Post a Comment