Data Access Layers
The purpose of a Data Access Layer is to simplify access to data stored in a persistent storage separate from your application.
In the past, creating a functional and easy-to-use Data Access Layer (DAL) in your application could be a tedious and long-winded process. Following this tutorial, you will learn how to easily generate a DAL from a database with minimal effort. You will learn how to automatically generate classes that map your database entities, their relationships, and even the stored procedures to .NET objects and methods. You’ll also discover how to update your DAL when the database has been changed. Finally, you’ll learn how to extend your automatically created classes to really customise your DAL.
Pre-Requisites
The following are required for this tutorial:
• SQL Server
• SQL Server Management Studio
• Some flavour of Microsoft Visual Studio
• The Entity Framework DLL (I use http://www.microsoft...s.aspx?id=8363)
The Database
Creating the Instance
I’m going to start by creating a SQL Server database for our application. It’s possible to use many database types, but that is beyond the scope of this tutorial. Note that if you’re using Visual Studio Express you might need to create a class library using Visual Web Developer to connect to anything other than SQLCE databases (including SQL Server). We’re going to start really simple by creating a database that just tracks Users, Products and Orders.
Firstly, from SQL Server Management Studio, click the “Connect” drop-down and select “Database Engine”. You can also use “File” -> “Connect Object Explorer” and use the drop-down to select “Database Engine”. You’ll see the “Connect to Server” dialog as in the screenshot below.
Use this to connect to your SQL Server instance as usual. You should then be able to view your available databases in the Object Explorer as in the below screenshot. I haven’t got any at the moment, but that’s about to change.
Right-click on the “Databases” folder and click “New Database”, after which the “New Database” dialog should then appear as below.
Set your database name. I’m going to call mine “EntityTutorial”. Now click OK, and the database will be created. You should be able to see it sitting in your Object Explorer.
Creating the Tables
To start with, our application database is going to be very simple. We’re going to have 3 tables; “Users”, “Products”, and “Orders”. Expand your database, and right-click the “Tables” folder. Next, select “New Table” and you will then be able to design your table. Using the “Properties” window, name the table “Users”. We need to also add the columns “UserID” (an “int” type that does not allow nulls), “UserName” (nvarchar(5), not nullable), “FirstName” and “LastName” (nvarchar(5), nullable), and “EmailAddress”(nvarchar(MAX), not nullable). Before we finalise this table, we need to make the UserID field start from 1 and auto-increment itself. That means the IDs for this table will be assigned on creation, and we don’t need to worry about them. Click on the “UserID” column, and you will see the “Column Properties” section of the window has its details. In the “Identity Specification” section, set “(Is Identity)” to “Yes”, and ensure the fields “Identity Increment” and “Identity Seed” fill with a 1. Save and close this table design.
Do the same to create two more tables. “Products” will need columns for “ProductID” (int, not nullable, identity), “ProductName” (nvarchar(100), not null), “Description” (nvarchar(MAX), null), “Price”(float, not null), “AddedBy” (int, not null), and “AddedOn”(datetime, not null). The “Orders” table needs columns “OrderID” (int, not null, identity), “Product” (int, not null), “OrderedBy” (int, not null), “OrderedOn” (datetime, not null), and “Quantity” (int, not null).
All three tables should now be showing in object explorer, as in the screenshot above. However, if you examine them, you’ll notice there are no Primary Keys on any table. We want the ID field (first column) from each table to be the Primary Key, so we need to add them. Right-click a column on the “Users” table, select “Modify” and you’re taken back to the designer. Then, right-click the column selector to the left of the “UserID” column and select “Set Primary Key”; there should now be a yellow key next to it. Do the same for the “Products” and “Orders” tables, setting the “ProductID” and “OrderID” columns as Primary Keys.
You might have been sharp enough to pick up on the fact that we’ve created some columns that will connect to another column in another table. We’ll set these as Foreign Keys now. Right-click the “Keys” folder for the “Products” table, and select “New Foreign Key…” You’ll see a “Foreign Key Relationships” dialog like the screenshot below.
Click the ellipsis (“…”) button and you’ll see a “Tables and Columns” dialog. Change the “Primary key table” to “Users”, set the left column to “UserID” and the right to “AddedBy”. That means that when a Product gets added, it will need to reference the UserID of the user who added it. Click “OK”, then “Close”.
Do the same linking the “Product” field on the “Orders” table to the “ProductID” field on the “Products” table, and the “OrderedBy” field on the “Orders” table to the “UserID” field on the “Users” table. You will have to add a second relationship in the “Tables and Columns” dialog or you will overwrite your previous relationship. Our database is complete, for now.
The Data Access Layer
Connecting to the Database
So, we have a simple database for our application. Now, we need an application for our simple database. Using Visual Studio, let’s create a new Console project named “EntityTutorial”. The first thing I’m going to do is add another project to this solution: a Class Library called “DataAccessLayer”. I’m also going to delete the “Class1” file generated with that library.
First, we need an EDMX: this is a file that defines the mapping to the database. These are really easy to generate in Visual Studio. Right-click your “DataAccessLayer” project and select “Add”, then “New Item…” An “Add New Item” dialog will appear. Select “ADO.NET Entity Data Model” and name it “TutorialModel.edmx”.
When you click “Add”, you’ll be taken to the “Entity Data Model Wizard”. First, select “Generate From Database” and click “Next”. Then, you’ll need to create a new connection to your database: Select “New Connection”, ensure your Data Source is “Microsoft SQL Server (SqlClient)” and select the “Server name” and “Database name” fields. Note as per my previous warning, if using Visual Studio Express you may have to use Visual Web Developer Express to create this connection.
Click “Test Connection”, and hopefully it will succeed. Click “OK” and in the previous part of the Wizard, you’ll see a new connection string, as well as a checkbox asking if you want to save the connection string. Make sure you have, so you can substitute a different database location further down the line. Click “Next, and the Wizard will retrieve information from your database. Select all the Tables and click “Finish”.
Once the EDMX has been created, you’ll see it looks similar to the database we made, including the relationships (as indicated by the dotted lines). Great! We have a connection from our application to the database.
Generate the Entity Classes
Next, we want to generate the classes we will use in our application that will be modelled on our database. We will also generate a “DbContext” class that will give us read/write access to the database so we can get and manipulate these objects from storage, as well as save any state changes.
To begin with, you’ll need to get the “Entity Framework 4.x [or 5.x] DbContext Generator” component from the Online Gallery in Extension Manager. Then, right-click your EDMX and select “Add Code Generation item…” Select “EF [ver] DbContext Generator” and set the name as “TutorialModel.tt” and click “Add”.
Seconds later, this should have finished. You’ll have two new items in the Solution Explorer: “TutorialModel.Context.tt” and “TutorialModel.tt”. If you expand the “TutorialModel.Context.tt” you should also see “TutorialModel.Context.cs”.
Below the “TutorialModel.tt” you have “Order.cs”, “Product.cs”, “TutorialModel.cs” and “User.cs”.
Open the User.cs class; you’ll see it has properties to match each database field, and an ICollection to represent the relationships. Also check out the “TutorialModel.Context.cs” class; this inherits from DbContext, giving us access to the underlying database. However, this project will need to reference Entity Framework; add a reference to the project and navigate to where the DLL is saved on your machine.
A quick note: NEVER make changes to these classes. They’re generated automatically, and can be overwritten when regenerated. Later, we’ll learn how to extend these classes.
Using the Classes
Creating a New User
At some point we’re going to need to use what we’ve made here, so let’s add a reference in our EntityTutorial console application to our DataAccessLayer project. You’ll also need to add a reference to Entity Framework. Now, in the Program.cs class file, add a method with the following signature:
This helper method will allow us to easily create a user and save it in the database. It will also return true if the save was successful.
We need to create a context. The good news is, the DbContext class implements the IDisposable interface, so we can wrap it in a using statement, like so:
That forces the context to be disposed of implicitly when out of scope. Within curly braces, we then need to create an object of type User (I’m going to use an object initializer to save effort):
Now we need to add this User object to the database context’s Users collection.
The database context class has methods we can invoke as well as the collections of objects representing the tables. The SaveChanges() method will, unsurprisingly, save changes to the database, and its return value is the number of changes saved.
Finally, we will return true if the changesSaved variable has a value of greater than or equal to 1.
The whole method looks like this:
Let’s test that works. Add this line to the Main method in your console app’s Program.cs:
Hopefully, you understand what that line does: it creates a user with the user name “MrShoes”, first name “Mister”, last name “Shoes”, email address “mrshoes@shoefits.com”, and the variable addedUser holds a Boolean value indicating whether the changes were saved or not. We probably want to output something to the screen.
Run the console application and let’s see the result.
Oh no! An unhandled exception! The message for this exception is: "No connection string named 'EntityTutorialEntities' could be found in the application config file." Yes, we do have an app.config with that information in the class library; we just need to copy the file into the console project. You can easily do that in VS by right-clicking the App.config file and selecting “Copy”, then right-click the console project and select “Paste”. Run it again and you should see:
Seems like it’s been successful. Let’s check the Users table in the database.
You’ll see a new entry in this table matching what we added in code; even better, the row has a UserID property that was automatically generated.
Getting a User from the Database
Obviously, we need to be able to read from the database as well as writing to it. We use the same DbContext object to access the database entities, which will automatically be mapped to the classes we’ve created.
If you wanted to get all the Users, for example, you’d use:
The variable allUsers would then be of type DbSet and, in
our current state, will include one single User object. If you wanted to
get the users with an ID greater than 7, you’d use:
In this case, the “usersAfterSeven” variable would be of type IQueryable and in our example hold no entries (since our
only user has an ID of 1). The .Where() extension method is very useful
when using Entity Framework, but can be fairly intimidating if you’re
not familiar with using a lambda as a predicate. It’s beyond the scope
of this tutorial, but I’m sure you’ll quickly pick up how to use them.
The above line will return a single User object, the first in the database, or null if there are no Users stored. What’s useful is that you can also supply a lambda expression as an argument to the .FirstOrDefault extension method. For example, if you want to get a User by its UserName property, you’d use:
That will return our User from the database. Let’s create another method in our console app to help us get Users by UserName.
Now let’s try this out. Change your Main method to match:
Run that, and you should get this output:
Creating a New Product
We’re going to create a method that will help us easily add Products to the database. This method will also take an object of type User as a parameter. Create a method with the following signature:
We need to create the database context as before.
Now, within curly braces, we use an object initializer to create a Product object.
You notice that for the AddedBy property, we’re accessing the UserID of the User object we’ve passed as a parameter. This will ensure we are creating the correct relationship between entities. We’re also using DateTime.Now to store as the AddedOn date. As before, we need to add the Product to the context’s Products collection.
The method should look like this, after we’ve finished it off the same way we did AddUser:
Change your Main method like so:
Run this, then check your database.
We now have a new Product, it has been assigned its own ProductID, and the AddedBy matches the User’s UserID field.
Getting a Product from the Database
Returning the Products is done the same as with Users. Four our purposes, we’ll use a helper method:
You might also want a method that lets you easily get the Products that were created by a User. Because the relationship exists in the database, Entity Framework will allow us to access them simply as a property of a User object.
Creating a New Order
By now, you won’t be surprised to hear that we’re going to make a helper method just like our others to create Orders.
We’ll add an order from our Main method.
Run this and check your Orders table in the database.
You can see it has been added, and the ProductID and OrderedBy field match the User and Product we created earlier.
Dealing with Changes
Often, especially when developing a new product, the database might change. Whether it’s new tables, new columns, or stored procedures, if you wish to use them in your application you’ll need to update the mapping in your EDMX. Fortunately, that’s just as easy as it was to generate the EDMX in the first place.
Adding a New Column
In SQL Server Management Studio, right-click your Users table and select “Design”. Add a new column “LastActive” of type datetime, and allow nulls. Close and save this table.
The column now exists in the table, but not our Entities nor our mapping. Return to the EDMX in Visual Studio, right-click and select “Update Model from Database…” You’ll then be shown the Update Wizard.
Click “Finish” and it will refresh the current tables. After a while, you’ll see the “LastActive” field has been added to your EDMX. Check your User.cs file and… oh, it hasn’t changed. Not yet, at least. Save your EDMX and check again.
OK, we now have the LastActive property. It’s important to remember that the code generation tool won’t be re-run until the EDMX is saved.
Adding a New Table
Sometimes an entirely new table has been added to the database, and you’ll need to start using it in your application. This is just as easy as adding a column.
To start with, in SQL Server Management Studio, we’ll create a new table called “News”, with the columns ItemID(int, not null, identity), Date(datetime, not null), Heading (varchar(200), not null), Body (text, not null), and AddedBy(int, not null). Set ItemID as the Primary Key, and create a relationship linking AddedBy to the UserID field in the Users table.
We’ll need to map this in our EDMX. Go back to Visual Studio, right-click the EDMX and select “Update Model from Database…” This time, you’ll be able to select the “News” table to add, so do that and select “Finish”.
The “News” table has now been mapped to entities, and the relationship has been created. Save, and you should now have a News.cs class file.
You should also see a new collection named “News” in the User class.
Adding a Stored Procedure
Sometimes, the database will have stored procedures that you’ll need to call. This is also possible using the mapping of Entity Framework, and it makes calling a stored procedure as simple as invoking a method.
In SQL Server Management Studio, navigate to “Programmability” -> “Stored Procedures” in your database. Right-click and select “New Stored Procedure”. Replace the template text with the following:
Run this and the procedure will be created. You will be able to see it in the Stored Procedures folder. Now, let’s add it to our mapping. Again, go to Visual Studio, right-click your EDMX and select “Update Model from Database…” Now you will be able to select the “DeleteUser” stored procedure; do so, then click “Finish”.
This time, you won’t see anything visually added to your model. That makes sense, since we’ve only added a stored procedure and not an object. We need to use “Function Import” to add this to our context class. Go to the “Model Browser” window in VS (if you can’t view it, right-click on the EDMX and select “Model Browser”) and, there in the Stored Procedures folder you should see our “DeleteUser” procedure. Right-click it and select “Add Function Import”.
The “Add Function Import window will now appear. We don’t need to change anything here, we’re not returning anything and we don’t need to change the name. Click “OK”, then save your EDMX. The EntityTutorialEntities class should now look like this:
Ok, we can see that all the Entities we’ve created are collected here, and we now have a DeleteUser method. How do we call it?
Extending the Classes
I mentioned earlier that you should never change the automatically generated classes. That’s because any update will rerun the code generator, and will therefore overwrite any changes you make. However, the people who made the code generator knew you might need to extend the classes, and so they made them partial classes.
To extend the classes, then, you only need to create a new class with the same name in the same namespace, and also make it partial. That way, you can access all the properties and methods of the generated classes and create your own.
Create a new UserExtend.cs file in your DataAccessLayer project, so we can extend the User entity. Add the keyword to the class definition, and rename it “User” from “UserExtend”.
Let’s give it a property that returns the number of Orders placed by the User.
Now you see we can access the Orders collection from this extended class, even though it is defined in a separate file. They are the same class defined in multiple files.
Maybe you want to use this class as an IIdentity so your application can keep track of which user is logged in. Change the class signature:
Remembering to add the statement:
And implement the interface members:
Now our generated class implements a useful interface!
Summary
By now you’ll see just how easy it can be to automatically generate a DAL from a database with automatically generated code. Obviously, it’s possible to create much more complex data layers than the one shown, but this should server as a good reference for a starting point.
The purpose of a Data Access Layer is to simplify access to data stored in a persistent storage separate from your application.
In the past, creating a functional and easy-to-use Data Access Layer (DAL) in your application could be a tedious and long-winded process. Following this tutorial, you will learn how to easily generate a DAL from a database with minimal effort. You will learn how to automatically generate classes that map your database entities, their relationships, and even the stored procedures to .NET objects and methods. You’ll also discover how to update your DAL when the database has been changed. Finally, you’ll learn how to extend your automatically created classes to really customise your DAL.
Pre-Requisites
The following are required for this tutorial:
• SQL Server
• SQL Server Management Studio
• Some flavour of Microsoft Visual Studio
• The Entity Framework DLL (I use http://www.microsoft...s.aspx?id=8363)
The Database
Creating the Instance
I’m going to start by creating a SQL Server database for our application. It’s possible to use many database types, but that is beyond the scope of this tutorial. Note that if you’re using Visual Studio Express you might need to create a class library using Visual Web Developer to connect to anything other than SQLCE databases (including SQL Server). We’re going to start really simple by creating a database that just tracks Users, Products and Orders.
Firstly, from SQL Server Management Studio, click the “Connect” drop-down and select “Database Engine”. You can also use “File” -> “Connect Object Explorer” and use the drop-down to select “Database Engine”. You’ll see the “Connect to Server” dialog as in the screenshot below.
Spoiler
Use this to connect to your SQL Server instance as usual. You should then be able to view your available databases in the Object Explorer as in the below screenshot. I haven’t got any at the moment, but that’s about to change.
Spoiler
Right-click on the “Databases” folder and click “New Database”, after which the “New Database” dialog should then appear as below.
Spoiler
Set your database name. I’m going to call mine “EntityTutorial”. Now click OK, and the database will be created. You should be able to see it sitting in your Object Explorer.
Creating the Tables
To start with, our application database is going to be very simple. We’re going to have 3 tables; “Users”, “Products”, and “Orders”. Expand your database, and right-click the “Tables” folder. Next, select “New Table” and you will then be able to design your table. Using the “Properties” window, name the table “Users”. We need to also add the columns “UserID” (an “int” type that does not allow nulls), “UserName” (nvarchar(5), not nullable), “FirstName” and “LastName” (nvarchar(5), nullable), and “EmailAddress”(nvarchar(MAX), not nullable). Before we finalise this table, we need to make the UserID field start from 1 and auto-increment itself. That means the IDs for this table will be assigned on creation, and we don’t need to worry about them. Click on the “UserID” column, and you will see the “Column Properties” section of the window has its details. In the “Identity Specification” section, set “(Is Identity)” to “Yes”, and ensure the fields “Identity Increment” and “Identity Seed” fill with a 1. Save and close this table design.
Do the same to create two more tables. “Products” will need columns for “ProductID” (int, not nullable, identity), “ProductName” (nvarchar(100), not null), “Description” (nvarchar(MAX), null), “Price”(float, not null), “AddedBy” (int, not null), and “AddedOn”(datetime, not null). The “Orders” table needs columns “OrderID” (int, not null, identity), “Product” (int, not null), “OrderedBy” (int, not null), “OrderedOn” (datetime, not null), and “Quantity” (int, not null).
Spoiler
All three tables should now be showing in object explorer, as in the screenshot above. However, if you examine them, you’ll notice there are no Primary Keys on any table. We want the ID field (first column) from each table to be the Primary Key, so we need to add them. Right-click a column on the “Users” table, select “Modify” and you’re taken back to the designer. Then, right-click the column selector to the left of the “UserID” column and select “Set Primary Key”; there should now be a yellow key next to it. Do the same for the “Products” and “Orders” tables, setting the “ProductID” and “OrderID” columns as Primary Keys.
You might have been sharp enough to pick up on the fact that we’ve created some columns that will connect to another column in another table. We’ll set these as Foreign Keys now. Right-click the “Keys” folder for the “Products” table, and select “New Foreign Key…” You’ll see a “Foreign Key Relationships” dialog like the screenshot below.
Spoiler
Click the ellipsis (“…”) button and you’ll see a “Tables and Columns” dialog. Change the “Primary key table” to “Users”, set the left column to “UserID” and the right to “AddedBy”. That means that when a Product gets added, it will need to reference the UserID of the user who added it. Click “OK”, then “Close”.
Spoiler
Do the same linking the “Product” field on the “Orders” table to the “ProductID” field on the “Products” table, and the “OrderedBy” field on the “Orders” table to the “UserID” field on the “Users” table. You will have to add a second relationship in the “Tables and Columns” dialog or you will overwrite your previous relationship. Our database is complete, for now.
The Data Access Layer
Connecting to the Database
So, we have a simple database for our application. Now, we need an application for our simple database. Using Visual Studio, let’s create a new Console project named “EntityTutorial”. The first thing I’m going to do is add another project to this solution: a Class Library called “DataAccessLayer”. I’m also going to delete the “Class1” file generated with that library.
First, we need an EDMX: this is a file that defines the mapping to the database. These are really easy to generate in Visual Studio. Right-click your “DataAccessLayer” project and select “Add”, then “New Item…” An “Add New Item” dialog will appear. Select “ADO.NET Entity Data Model” and name it “TutorialModel.edmx”.
Spoiler
When you click “Add”, you’ll be taken to the “Entity Data Model Wizard”. First, select “Generate From Database” and click “Next”. Then, you’ll need to create a new connection to your database: Select “New Connection”, ensure your Data Source is “Microsoft SQL Server (SqlClient)” and select the “Server name” and “Database name” fields. Note as per my previous warning, if using Visual Studio Express you may have to use Visual Web Developer Express to create this connection.
Spoiler
Click “Test Connection”, and hopefully it will succeed. Click “OK” and in the previous part of the Wizard, you’ll see a new connection string, as well as a checkbox asking if you want to save the connection string. Make sure you have, so you can substitute a different database location further down the line. Click “Next, and the Wizard will retrieve information from your database. Select all the Tables and click “Finish”.
Spoiler
Once the EDMX has been created, you’ll see it looks similar to the database we made, including the relationships (as indicated by the dotted lines). Great! We have a connection from our application to the database.
Generate the Entity Classes
Next, we want to generate the classes we will use in our application that will be modelled on our database. We will also generate a “DbContext” class that will give us read/write access to the database so we can get and manipulate these objects from storage, as well as save any state changes.
To begin with, you’ll need to get the “Entity Framework 4.x [or 5.x] DbContext Generator” component from the Online Gallery in Extension Manager. Then, right-click your EDMX and select “Add Code Generation item…” Select “EF [ver] DbContext Generator” and set the name as “TutorialModel.tt” and click “Add”.
Seconds later, this should have finished. You’ll have two new items in the Solution Explorer: “TutorialModel.Context.tt” and “TutorialModel.tt”. If you expand the “TutorialModel.Context.tt” you should also see “TutorialModel.Context.cs”.
Below the “TutorialModel.tt” you have “Order.cs”, “Product.cs”, “TutorialModel.cs” and “User.cs”.
Open the User.cs class; you’ll see it has properties to match each database field, and an ICollection to represent the relationships. Also check out the “TutorialModel.Context.cs” class; this inherits from DbContext, giving us access to the underlying database. However, this project will need to reference Entity Framework; add a reference to the project and navigate to where the DLL is saved on your machine.
A quick note: NEVER make changes to these classes. They’re generated automatically, and can be overwritten when regenerated. Later, we’ll learn how to extend these classes.
Using the Classes
Creating a New User
At some point we’re going to need to use what we’ve made here, so let’s add a reference in our EntityTutorial console application to our DataAccessLayer project. You’ll also need to add a reference to Entity Framework. Now, in the Program.cs class file, add a method with the following signature:
1 | private static bool AddUser(string userName, string firstName, string lastName, string emailAddress) |
This helper method will allow us to easily create a user and save it in the database. It will also return true if the save was successful.
We need to create a context. The good news is, the DbContext class implements the IDisposable interface, so we can wrap it in a using statement, like so:
1 | using (var dbContext = new EntityTutorialEntities()) |
That forces the context to be disposed of implicitly when out of scope. Within curly braces, we then need to create an object of type User (I’m going to use an object initializer to save effort):
1 | var user = new User |
2 | { |
3 | UserName = userName, |
4 | FirstName = firstName, |
5 | LastName = lastName, |
6 | EmailAddress = emailAddress |
7 | }; |
Now we need to add this User object to the database context’s Users collection.
1 | dbContext.Users.Add(user); |
The database context class has methods we can invoke as well as the collections of objects representing the tables. The SaveChanges() method will, unsurprisingly, save changes to the database, and its return value is the number of changes saved.
1 | var changesSaved = dbContext.SaveChanges(); |
Finally, we will return true if the changesSaved variable has a value of greater than or equal to 1.
1 | return changesSaved >= 1; |
The whole method looks like this:
Spoiler
Let’s test that works. Add this line to the Main method in your console app’s Program.cs:
1 | var addedUser = AddUser("MrShoes", "Mister", "Shoes", "mrshoes@shoefits.com"); |
Hopefully, you understand what that line does: it creates a user with the user name “MrShoes”, first name “Mister”, last name “Shoes”, email address “mrshoes@shoefits.com”, and the variable addedUser holds a Boolean value indicating whether the changes were saved or not. We probably want to output something to the screen.
1 | Console.WriteLine(addedUser ? "A user was added." : "No user could be added."); |
2 | Console.ReadKey(); |
Run the console application and let’s see the result.
Spoiler
Oh no! An unhandled exception! The message for this exception is: "No connection string named 'EntityTutorialEntities' could be found in the application config file." Yes, we do have an app.config with that information in the class library; we just need to copy the file into the console project. You can easily do that in VS by right-clicking the App.config file and selecting “Copy”, then right-click the console project and select “Paste”. Run it again and you should see:
Spoiler
Seems like it’s been successful. Let’s check the Users table in the database.
Spoiler
You’ll see a new entry in this table matching what we added in code; even better, the row has a UserID property that was automatically generated.
Getting a User from the Database
Obviously, we need to be able to read from the database as well as writing to it. We use the same DbContext object to access the database entities, which will automatically be mapped to the classes we’ve created.
If you wanted to get all the Users, for example, you’d use:
1 | var allUsers = dbContext.Users; |
The variable allUsers would then be of type DbSet
1 | var usersAfterSeven = dbContext.Users.Where(u => u.UserID > 7); |
In this case, the “usersAfterSeven” variable would be of type IQueryable
1 | var firstUser = dbContext.Users.FirstOrDefault(); |
The above line will return a single User object, the first in the database, or null if there are no Users stored. What’s useful is that you can also supply a lambda expression as an argument to the .FirstOrDefault extension method. For example, if you want to get a User by its UserName property, you’d use:
1 | return dbContext.Users.FirstOrDefault(u => u.UserName == 1); |
That will return our User from the database. Let’s create another method in our console app to help us get Users by UserName.
Spoiler
Now let’s try this out. Change your Main method to match:
Spoiler
Run that, and you should get this output:
Spoiler
Creating a New Product
We’re going to create a method that will help us easily add Products to the database. This method will also take an object of type User as a parameter. Create a method with the following signature:
1 | private static bool AddProduct(string productName, string description, double price, User addedBy) |
We need to create the database context as before.
1 | using (var dbContext = new EntityTutorialEntities()) |
Now, within curly braces, we use an object initializer to create a Product object.
1 | var product = new Product |
2 | { |
3 | ProductName = productName, |
4 | Description = description, |
5 | Price = price, |
6 | AddedOn = DateTime.Now, |
7 | AddedBy = addedBy.UserID |
8 | }; |
You notice that for the AddedBy property, we’re accessing the UserID of the User object we’ve passed as a parameter. This will ensure we are creating the correct relationship between entities. We’re also using DateTime.Now to store as the AddedOn date. As before, we need to add the Product to the context’s Products collection.
1 | dbContext.Products.Add(product); |
The method should look like this, after we’ve finished it off the same way we did AddUser:
Spoiler
Change your Main method like so:
Spoiler
Run this, then check your database.
Spoiler
We now have a new Product, it has been assigned its own ProductID, and the AddedBy matches the User’s UserID field.
Getting a Product from the Database
Returning the Products is done the same as with Users. Four our purposes, we’ll use a helper method:
Spoiler
You might also want a method that lets you easily get the Products that were created by a User. Because the relationship exists in the database, Entity Framework will allow us to access them simply as a property of a User object.
Spoiler
Creating a New Order
By now, you won’t be surprised to hear that we’re going to make a helper method just like our others to create Orders.
Spoiler
We’ll add an order from our Main method.
Spoiler
Run this and check your Orders table in the database.
Spoiler
You can see it has been added, and the ProductID and OrderedBy field match the User and Product we created earlier.
Dealing with Changes
Often, especially when developing a new product, the database might change. Whether it’s new tables, new columns, or stored procedures, if you wish to use them in your application you’ll need to update the mapping in your EDMX. Fortunately, that’s just as easy as it was to generate the EDMX in the first place.
Adding a New Column
In SQL Server Management Studio, right-click your Users table and select “Design”. Add a new column “LastActive” of type datetime, and allow nulls. Close and save this table.
Spoiler
The column now exists in the table, but not our Entities nor our mapping. Return to the EDMX in Visual Studio, right-click and select “Update Model from Database…” You’ll then be shown the Update Wizard.
Spoiler
Click “Finish” and it will refresh the current tables. After a while, you’ll see the “LastActive” field has been added to your EDMX. Check your User.cs file and… oh, it hasn’t changed. Not yet, at least. Save your EDMX and check again.
Spoiler
OK, we now have the LastActive property. It’s important to remember that the code generation tool won’t be re-run until the EDMX is saved.
Adding a New Table
Sometimes an entirely new table has been added to the database, and you’ll need to start using it in your application. This is just as easy as adding a column.
To start with, in SQL Server Management Studio, we’ll create a new table called “News”, with the columns ItemID(int, not null, identity), Date(datetime, not null), Heading (varchar(200), not null), Body (text, not null), and AddedBy(int, not null). Set ItemID as the Primary Key, and create a relationship linking AddedBy to the UserID field in the Users table.
We’ll need to map this in our EDMX. Go back to Visual Studio, right-click the EDMX and select “Update Model from Database…” This time, you’ll be able to select the “News” table to add, so do that and select “Finish”.
Spoiler
The “News” table has now been mapped to entities, and the relationship has been created. Save, and you should now have a News.cs class file.
Spoiler
You should also see a new collection named “News” in the User class.
Spoiler
Adding a Stored Procedure
Sometimes, the database will have stored procedures that you’ll need to call. This is also possible using the mapping of Entity Framework, and it makes calling a stored procedure as simple as invoking a method.
In SQL Server Management Studio, navigate to “Programmability” -> “Stored Procedures” in your database. Right-click and select “New Stored Procedure”. Replace the template text with the following:
Spoiler
Run this and the procedure will be created. You will be able to see it in the Stored Procedures folder. Now, let’s add it to our mapping. Again, go to Visual Studio, right-click your EDMX and select “Update Model from Database…” Now you will be able to select the “DeleteUser” stored procedure; do so, then click “Finish”.
This time, you won’t see anything visually added to your model. That makes sense, since we’ve only added a stored procedure and not an object. We need to use “Function Import” to add this to our context class. Go to the “Model Browser” window in VS (if you can’t view it, right-click on the EDMX and select “Model Browser”) and, there in the Stored Procedures folder you should see our “DeleteUser” procedure. Right-click it and select “Add Function Import”.
Spoiler
The “Add Function Import window will now appear. We don’t need to change anything here, we’re not returning anything and we don’t need to change the name. Click “OK”, then save your EDMX. The EntityTutorialEntities class should now look like this:
Spoiler
Ok, we can see that all the Entities we’ve created are collected here, and we now have a DeleteUser method. How do we call it?
1 | dbContext.DeleteUser("MrShoes"); |
Extending the Classes
I mentioned earlier that you should never change the automatically generated classes. That’s because any update will rerun the code generator, and will therefore overwrite any changes you make. However, the people who made the code generator knew you might need to extend the classes, and so they made them partial classes.
To extend the classes, then, you only need to create a new class with the same name in the same namespace, and also make it partial. That way, you can access all the properties and methods of the generated classes and create your own.
Create a new UserExtend.cs file in your DataAccessLayer project, so we can extend the User entity. Add the keyword to the class definition, and rename it “User” from “UserExtend”.
Let’s give it a property that returns the number of Orders placed by the User.
1 | public int NumberOfOrders { get { return Orders.Count; } } |
Now you see we can access the Orders collection from this extended class, even though it is defined in a separate file. They are the same class defined in multiple files.
Maybe you want to use this class as an IIdentity so your application can keep track of which user is logged in. Change the class signature:
1 | public partial class User : IIdentity |
Remembering to add the statement:
1 | using System.Security.Principal; |
And implement the interface members:
1 | public string Name{ get { return UserName; } } |
2 | public string AuthenticationType { get { return "Standard"; } } |
3 | public bool IsAuthenticated { get { return true; } } |
Now our generated class implements a useful interface!
Summary
By now you’ll see just how easy it can be to automatically generate a DAL from a database with automatically generated code. Obviously, it’s possible to create much more complex data layers than the one shown, but this should server as a good reference for a starting point.
