In the previous lesson, we created an SQL database running on a local server. What we want to do now is use an API within the .NET Framework, called the Entity Framework, to access/update that data via C# code. The Entity Framework is a subset of the entire ADO.NET API. It’s a newer aspect of the larger ADO.NET API that has a lot of the momentum when it comes to handling databases, but is certainly not the only way to access data in an SQL Server database. What the Entity Framework does is it serves up what's called an “Object-Relational Mapper.” Simply stated a mapper takes the data from the database and maps it to native C# structured classes and properties – making it easier to work with in code.
This lesson is based on what we created in the previous lesson, so go ahead and start the project from where we left off. First, right-click on the project name “LocalDbExample” in the Solution Explorer and from the menu select:
Add > New Item
From here, go to the Data templates and select ADO.NET Entity Data Model, calling it “ACMEEntities”:
Next, select the option to model an “EF Designer from database,” since we started with a database and want to create an Entity model from it, rather than first create a model that can then applied to a database:
Then click “Next” on the following screen to simply create a “connection string” to the database you want to have the Entity model based upon. A connection string is held in a file that contains connection details required for an API, like the Entity Framework, to know where and how to go to connect to a database:
And in the following screen, checkmark the database table that you want to include in your Entity model. Since we only have one table – Customers – select that one:
By creating an Entity Data Model, we essentially create a series of entities that represent the data in the actual database, separate from the database itself. This creates a layer of abstraction from the underlying data that deals with all the details of creating, reading, updating and deleting data in the database.
After that, you may get a warning whether or not you want to run this and click “OK” to confirm that. You will then be shown a visual model for the table and its data elements. If you had a variety of tables, they would all be represented here, and may even show connections between them wherever there is a relationship between the tables:
This visual model is available via the “ACMEEntities.edmx” file in the Solution Explorer:
The “.tt” files are generated as template files that will themselves generate a series of classes.
In this case, it'll create a Context class, which acts as a connection to the database. When we ask the database for something, the code that's generated here in this class will house the logic necessary to go out and connect to the database, and perform the actions that we've requested:
Meanwhile, the Customer.cs class was also generated, taking the various data points in the Customer table and converting them into suitable properties. The Entity Framework understands how to convert between.NET data types (string, GUID, and so on), and SQL Server data types (varchar, uniqueidentifier, and so on):
Now create a simple resultLabel, except this time let’s create it directly within the Default.aspx code. We'll create an ASP Label control, give it an ID of resultLabel and runat the server:
The details of that that all means aren't in the scope of this lesson to discuss. Notice, however, that this yields the same result as we would get through creating it in Design view:
Now in the Page_Load() of Default.aspx.cs, lets establish a connection to the database via code. We do this in the same way as we would create a new instance of a class:
This code will tell the Entity Framework that we're interested in getting all of the customer records from the customer table in our database. This querying of the database, retrieving the customer data and storing it in a local variable – customers – happens when this method executes. The last step in this method then iterates through the Name property in each Customer and prints it to the resultLabel:
When you run the application, you should see the result displaying the name data for our lone entry in the Database:
Lesson 60 - Creating an Entity Data Model