EF-Dapper-DAL

A demonstration .NET Core Data Access Layer (DAL) using a mixture of Entity Framework (EF) and Dapper ORMs

Contents

• Technologies
• Installation
• Usage
• Recreationsteps

Technologies

This solution contains a Data Access, a test project and was created with the following technologies, although earlier IDE versions may be compatible:

• Visual Studio 2019
• .NET Core 3.1
• Entity Framework Core 5.0
• Dapper 2.0
• SQL Server 2019
• NUnit 3.12

Installation

1. Clone the repo: git clone https://github.com/offstone/EF-Dapper-DAL
2. Install NuGet packages and build.
3. Update the NorthwindDBConnectionString connection string inside the appsettings.json files. There are 2 found in the root of each project. Point to your local SQL server instance, use an account with permission to create databases. For this demo I've used a cut down copy of the old Northwind database and called it [NorthwindCopy].
4. To initialise the database either run the EF migrations from the DAL project: PM> Update-Database
   Or Run the full SQL initialisation script: SQL/Install_NorthwindCopy_Full.sql If using the full SQL script prevent migrations from running subsequently.

Usage

This DAL demo can be run against the initialised [NorthwindCopy] database from the integration test project. Simply run the tests and a number of DAL operations will be performed against the DB using the connection string within the test project's appsettings.json

RecreationSteps

1. Create a new .NET Core library project.
2. Add the following packages via NuGet or Package Manager -> Install-Package:

    PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer  
    PM> Install-Package Microsoft.EntityFrameworkCore.Tools  
    PM> Install-Package Microsoft.EntityFrameworkCore.Design  
    PM> Install-Package System.Configuration.ConfigurationManager  
    PM> Install-Package Microsoft.Extensions.Configuration  
    PM> Install-Package Microsoft.Extensions.Configuration.Json  
    PM> Install-Package Dapper

1. Add an appsettings.json config file, connection string for the use by migrations.
   Set to copy to output directory.
2. Create the [NorthwindCopy] database with either Northwind.sql or EF Migrations: PM> Update-Database
3. Scaffold the entities from DB tables into /Entities folder:
   PM> Scaffold-DbContext -Connection "Server=[ADD_SERVERNAME_HERE];Database=NorthwindCopy;Trusted_Connection=True;" -Provider "Microsoft.EntityFrameworkCore.SqlServer" -Tables Categories,Customers,Employees,EmployeeTerritories,OrderDetails,Orders,Products,Region,Shippers,Suppliers,Territories -OutputDir "Entities"
4. Add a generic repository pattern into /Repository folder.
5. Create the initial migration:
   PM> Add-Migration InitialMigration -Context NorthwindContext
6. Create a unit test project to set up integration tests over the DAL and DB.
7. Set up ConfigureServices() in the test project to configure dependency injection and connection string from appsettings.json
8. Create a bunch of integration tests to call Data Access Layer functionality.