How to Create a Data Access Layer
As we mentioned in the introductory blog in this series as well as the post on creating custom web applications, Entity Framework (EF) is an object-relational framework (ORM) that is Microsoft's recommended data access technology usually on new .NET applications development. Basically, it allows the developer to work easily with relational data using domain-specific objects and without hard code database access. With my experience in building .NET applications, I'll be showing you how to create a data access layer using Entity Framework.
To create the entity data model (EDM) or the conceptual model for the entities of your domain, EF provides three models including model first, database first and code first. With the code first and model first approaches, it's assumed that a database schema is created based on the model you also need not have an existing database as you create your application. This is because database administrators (DBAs) are responsible for designing and maintaining database within enterprise environments and not the developers. In this post, we take a look at the database first option where the entity data model becomes a virtual reflection of the database or any of its subsets.
CREATE TABLE [dbo].[Department] (
[DepartmentId] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([DepartmentId] ASC)
CREATE TABLE [dbo].[Employee] (
[EmployeeId] INT IDENTITY (1, 1) NOT NULL,
[DepartmentId] INT NOT NULL,
[FirstName] VARCHAR (20) NOT NULL,
[LastName] VARCHAR (20) NOT NULL,
[Email] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([EmployeeId] ASC),
CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId])
REFERENCES [dbo].[Department] ([DepartmentId])
The N-tier architecture
Normally, large enterprise applications will require more than one database to store their data and a data access layer (DAL) to read the databases. Repositories may also exist to communicate with DAL, business layer for logic, classes for business domain, service layer for displaying the business layer to the clients and finally a user interface such as ASP.NET application which I normally use in my case or a WPF desktop application.
Creating Data access layer (DAL)
1. Start by creating a new C# class library example (Mm.DataAccessLayer) and add an ADO.NET Entity Data Model. This is how I like to start off. In the Entity Data Model wizard, choose "Generate from database". The wizard allows you to connect the database selecting the Department and the Employees to be included in the model.
2. In order to separate entity classes from EMDX which is vital separation is of importance, add a new class library (Mm.DomainModel) using visual studio, my favorite to the solution.
3. Open the File Explorer and move the Model.tt file to your new project. folder.
4. Include Model.tt file back in Visual Studio in the created project by clicking
"Include In Project" option on the "Show All Files" icon.
5. Delete Model.tt file from DAL project.
6. In the Model.tt template file, set the inputFile variable to point to an explicit path
where to find the model.
As Follows: const string inputFile = @"../Mm.DataAccessLayer/Model.edmx";
Right-click on your Model.tt template and choose the "Run Custom Tool" to regenerate and reflect the latest changes to that model.
7. As you well know, the context by default expects the entity classes included in the same namespace. For their new namespace you need to add a using statement to your Model.Context.tt template file of your DAL project.
using Mm.DomainModel; <!-- Added -->
8. All you need now is to add a reference from your DAL project to the domain model project and compile it.
Hopefully this post has been helpful to you in creating a data access layer. The next installment in the Microsoft Entity Framework series will concentrate on troubleshooting common problems.