ASP.NET 4.0 and the Entity Framework 4 - Part 4 - A 3 Layered Approach to the Entity Framework
page 3 of 10
by Vince Varallo
Average Rating: 
Views (Total / Last 10 Days): 59090/ 101

Step 2: Create the Data Access Layer

The next step is to create a new Class Library project using Visual Studio 2010.  This project will be the data access layer.

1.    Launch Visual Studio 2010.

2.    Select FileàNew Project from the menu.

3.    Select Visual C# Class Library project and name the project OrderSystemDAL. 

4.    Make sure the "Create directory for solution" checkbox is checked and change the Solution Name to OrderSystem.

5.    Click the OK button.

6.    Visual Studio will create the solution and project for you.  Now you can add an Entity Data Model to the project.  Right click on the OrderSystemDAL project and select AddàNew Item… from the pop-up menu.

7.    The Add New Item dialog should appear.  Select ADO.NET Entity Data Model from the list of templates and change the name of the file to OrderSystemDataModel.edmx.  Click the Add button.

8.    The Entity Data Model Wizard will appear.  Select "Generate from database" and click the Next button.

9.    The next screen asks you for the database.  Click the New Connection button.  Enter the name of your server and then select the OrderSystem database from the list of databases and click the OK button.

10. Click the Next button.

11. The next screen asks you to choose your database objects.  You want to select the UserAccounts table and the five stored procedures create in step 1.  You can see the tables or stored procedures by click on the plus(+) sign next to the Tables or Store Procedures nodes.  Simply check the box next to the object to indicate that you want to import the object. 

12. Click the Finish button.  Visual Studio will now create the OrderSystem data model for you.

You still need to create the functions to execute the stored procedures.  To do this you simply right click on the stored procedure under the Stored Procedures folder and select Add Function Import from the pop-up menu.

13. Click the plus sign next to the Stored Procedures folder in the Model Browser and right click on the UserAccounts_Delete stored procedure. 

14. Select Add Function Import… from the pop-up menu. 

15. The Add Function Import dialog box should appear.  Leave the Function Import Name and Stored Procedure Name the same.  Since this stored procedure does not return any value select None from the "Returns a Collection Of" list.  Now click the OK button.  The method will now appear under the Function Imports node in the Model Browser.

16. Right click on the UserAccounts_Insert stored procedure and select Add Function Import… from the pop-up menu.  Since this procedure will return the Id of the newly added record you need to change the "Returns a Collection Of" to Scalars and choose Int32 from the list.  Click the OK button.

17. Repeat the same steps for the other procedures.  For the SelectAll and SelectById procedures you should change the "Returns a Collection Of" to Entities and select UserAccounts from the list.  The Update procedure does not return a value either so you can select None for its return type.

You now have all you need in the Entity Data Model.  The next step is to create a class in the data layer to manage the calls to the procedures associated with the UserAccount table.

18. In the Solution Explorer, right click on the OrderSystemDAL project and select AddàClass… from the pop-up menu.

19. Name the class UserAccountsData.cs and click the Add button.

20. Change the class declaration to make it public and static.

public static class UserAccountsData

21. Add the follow methods to the class.

public static int Insert(string firstName, string lastName, DateTime insertDate)
    using (OrderSystemEntities db = new OrderSystemEntities())
        return Insert(db, firstName, lastName, insertDate);
public static int Insert(OrderSystemEntities db, string firstName, 
                         string lastName, DateTime insertDate)
    return db.UserAccounts_Insert(firstName, lastName, insertDate, insertDate).ElementAt(0).Value;
public static void Update(int id, string firstName, string lastName, 
                          DateTime updateDate)
    using (OrderSystemEntities db = new OrderSystemEntities())
        Update(db, id, firstName, lastName, updateDate);
public static void Update(OrderSystemEntities db, int id, string firstName, 
                          string lastName, DateTime updateDate)
    db.UserAccounts_Update(id, firstName, lastName, updateDate);
public static void Delete(int id)
    using (OrderSystemEntities db = new OrderSystemEntities())
        Delete(db, id);
public static void Delete(OrderSystemEntities db, int id)
public static UserAccount SelectById(int id)
    using (OrderSystemEntities db = new OrderSystemEntities())
        return SelectById(db, id);
public static UserAccount SelectById(OrderSystemEntities db, int id)
    return db.UserAccounts_SelectById(id).ElementAtOrDefault(0);
public static List<UserAccount> SelectAll()
    using (OrderSystemEntities db = new OrderSystemEntities())
        return SelectAll(db);
public static List<UserAccount> SelectAll(OrderSystemEntities db)
    return db.UserAccounts_SelectAll().ToList();

This is the class you should use to manipulate the data in the UserAccounts table.

View Entire Article

User Comments

Title: Where is the UserAccounts table diagram?   
Name: Fernando Linhares
Date: 2012-07-11 6:12:05 PM
Thanks for the article series.
Where is the UserAccounts table diagram picture?
Title: without stored procedures   
Name: Junior
Date: 2012-06-06 2:21:42 PM
how would this approach without stored procedures ?
Title: problem   
Name: zia
Date: 2012-05-22 9:33:03 AM
Brother i cant see the pictures u given in the project
Title: Mr   
Name: Varun Maggo
Date: 2011-03-16 4:16:28 AM
You rockmaan!
Title: Nice work!   
Name: janmeis
Date: 2011-03-02 11:52:40 AM
Thanks Vince, I've created the project according the article and it works fine! There's one minor bug, the SCOPEIDENTITY returned by the UserAccounts_Insert procedure is mapped as decimal not int, so in the item 16 the procedure must be as follows " need to change the "Returns a Collection Of" to Scalars and choose decimal from the list." Then it can be casted to int in the "public static int Insert". Link with the uploaded project is attached.
Title: connectionstring problem   
Name: Mark
Date: 2010-12-25 7:59:09 AM
I am getting server error on data source keyword.

Keyword not supported: 'data source'.

Exception Details: System.ArgumentException: Keyword not supported: 'data source'.
Title: Lnking to a data Object   
Name: Richard
Date: 2010-10-01 4:12:41 PM
So far so good, but now I have this BLL how do I use it say in a datagrid?
Title: Where is the concurrency management???   
Name: Matt
Date: 2010-07-30 12:52:28 PM
Where is the concurrency management code? If you load a user account from the database and five minutes later the person clicks the submit button how do you handle the issue where someone else already updated that record and you need to check for that???
Title: Why add the DAL and BLL as separate projects?   
Name: Alan
Date: 2010-07-12 12:16:59 PM
Even assuming that this is a good way to structure your application (which seems to be a matter of debate judging by other people's comments), why did you do the DAL and BLL as separate projects? I don't see any benefit at all to this.

Given that this is a web site, why not just add the .cs files for the DAL and BLL to the App_Code folder? You would then have the objects you need to separate the layers, but you wouldn't have the extra mess of separate projects and DLLs.
Title: Not convincing   
Name: Kum
Date: 2010-05-09 11:27:07 AM
Duplicate data model, one generated by entity framework in DL and another hand written in BL...
There should be a better way.
Title: Is it realy the way?   
Name: Kristian
Date: 2010-05-07 6:54:23 AM
Though I loved article part 1 and 2, this seems like a bad approach, like Mant wrote. Why do another mapping?
Title: Good Work   
Name: Rajbharat
Date: 2010-05-07 6:40:38 AM
Its good and simple to follow..thanks for ur good work
Title: Appreciate that!   
Name: Waldek
Date: 2010-05-06 5:57:10 AM
Thanks a lot Vince! Based on your articles all kinds of web.CRUD applications can now be built with a little hassle!
Title: use IsNullOrWhiteSpace instead if .Trim() == ""   
Name: Binoj Antony
Date: 2010-05-06 5:14:00 AM
Suggest that you improvise FirstName.Trim() == "" with

Also the most of the else blocks that does just a return false , here you could eliminate the else and just write return false; (This last one is just an opinion)
Title: Bad Approach   
Name: Mant
Date: 2010-05-05 11:05:10 AM
It seems what you have done is take the approach used in 1.1/2.0 with datasets then just swapped them with the Entity Framework completely missing the point of an ORM. I would suggest reading up on the Repository pattern, Unit of Work pattern, specification pattern and persistence ignorance. There are some great examples of using the framework out there, but just about everything in this one is a poor way of doing things.
Title: Great article!   
Name: Rich D.
Date: 2010-05-05 9:13:07 AM
I loved the article. I was looking for some direction and ideas on how to create a BL. But, I thought the idea was to save much of the template hand coding by using t4 templates to generate POCO classes for the BL. The template for self-tracking entities looks promising for n-tier architectures.
Title: ASP.NET 4.0 and the Entity Framework 4 - Part 4 - A 3 Layered Approach to the Entity Framework   
Name: Ray
Date: 2010-05-05 3:16:16 AM
Thanks Vince for a very helpful article.......
Title: ASP.NET 4.0 and the Entity Framework 4 - Part 4 - A 3 Layered Approach to the Entity Framework   
Name: tvs
Date: 2010-05-04 9:35:39 PM
a very good article... well done vince...

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-07-25 12:39:11 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search