This is the first (or maybe the second) of a series of posts dedicated to the ADO .NET Entity Framework. The purpose of these posts is to try to provide the necessary knowledge on what exactly is the Entity Framework, how can be used and how can help for an easier, faster and more efficient Data Access experience with using SQL Server 2008 and Visual Studio 2008 when building a data application.
A relevant post which provided an overview of the ADO .NET Entity Framework can be found here.
Well, let’s start!
All the examples, screenshots, demos, etc., will be based on the following:
- “Pubs” SQL Server Sample Database
- SQL Server 2008
- Visual Studio 2008 with .NET Framework 3.5
- Language: C#
Before describing the above diagram, a few words about the Pubs database. This sample database, originally released by Microsoft for SQL Server 2000, is a small database modeling a book publishing company. You can download it from this link.
So, based on the above diagram we have the following tables:
- titles: contains all the published titles
- authors: contains all the authors of the publishing house
- titleauthor: because the relationship between titles and authors is an M-M, this table is used for resolving it
- TopAuthors: this table references some authors who had a high sales activity and contains some relevant information like sales amount and bonus (yep, they are the lucky authors! 🙂
OK, now let’s proceed and see how we can build a simple C# application in Visual Studio 2008, which uses and introduces the ADO .NET Framework and some of its basic features.
Step 1 – Create a new Visual Studio 2008 Project
The important thing here is to select .NET Framework 3.5.
As these examples are based on C#, I have chosen C# for my project type.
I am going to build a Windows Forms Application for this example.
Step 2 – Add a new item to the project
By right-clicking on the project in the solution explorer, choose Add–>New Item:
The above step will initiate a Visual Studio 2008 wizard (the Entity Data Model Wizard) that will allow you to build very easily your Entity Data Model (EDM); a conceptual schema mapped to a relational model (in our case, the SQL Server database named “Pubs”).
Step 3 – Entity Data Model Wizard – Create your Entity Data Model
Here you are presented with two options: (i) Generate a model from a database, (ii) Create an empty model and thus build everything manually. In our case we just select the first option as we want to generate a conceptual model (Entity Data Model; yep, I will be repeating myself a lot on that! 🙂 that will be mapped to the “Pubs” database. Click Next.
Now you must choose your data connection. You can either create a new connection or use an existing one. After establishing the connection, a very important thing to remember is the name you give for the entity connection string – you are going to reference this later on for getting access to the Entity Data Model, and through the mappings, to the SQL Server Database. The entity connection string is stored and can be found in the App.config file in your project.
In our example application, we just select the authors table, keep the suggested Model Namespace and click on “Finish” (see screenshot below).
Note: During the automated generation of the EDM, the mappings are automatically constructed for the selected tables and Views.
Step 4 – The Entity Data Model Designer
The following screen shows the graphical tool (EDM Designer) within Visual Studio 2008 that is used for visually modifying the generated Entity Data Model. As you can see on the screenshot, I noted three main “components/concepts”. These are: (i) Entity, (ii) Mapping Tool, and (iii) Model Browser.
At this point it is of great importance to explain the above three components. So we can see that on the workspace there is an Entity named authors. So what is an Entity in plain words? As you might have assumed is a representation of the “authors” table which exists in the “Pubs” database. Well that is correct but there is one word missing; conceptual. It is a conceptual representation of the “authors” table in the “Pubs” database. Also, in its simple form, an entity contains scalar properties.
In the Mapping Tool’s view, you can see that the “authors” entity is mapped to the “authors” database. So, on the left you see the table/columns of the relational model (Database) and on the right (after the operator) you can see the scalar properties of the entity that are mapped to the corresponding columns of the table on which the “authors” entity is also mapped.
- We have the “authors” entity mapped to the ”authors” table
- We also have the displayed pairs of the table columns/data types mapped to pairs of the entity’s scalar properties/data types
If you compare the above screenshot with the previous one, you will notice that some things changed. The entity formerly called Authors is now called DemoAuthors. Also the scalar properties au_id, au_lname and au_fname are now called id, lastname and firstname respectively.
The only thing that you must have in mind is that the mappings should always be maintained. In the above example, even though we have changed the name of the entity and some of its scalar properties, the mappings were automatically updated! Well, in some cases where you might perform some really complex modifications, you might have to fine-tune the mappings manually, though after all, I don’t think that this would be a problem. 🙂
Step 5 – Use the EDM for Data Access
Then, we just have to select the object we want to bind to the data source we are creating. At this point select the DemoAuthors entity and click on Next:
That’s it! Click on Finish and voila, you have the data source ready to serve data via the EDM!
Let’s continue by dragging and dropping the DemoAuthors data source onto the form as in the following screenshot (if Data Sources explorer is not visible, you can change this setting from the View menu item):
Then press F7. It is time to write some code!!! 🙂
The two red rectangles contain the code that you should add:
In the second rectangle, after the form’s initialization, we instantiate the EDM object called model (notice the pubsEntities object used here; that’s the connection string to our EDM). We also set the data source for the datagridview. How about the way we reference our data? Quite simple, right?
Let’s also add some more code by going back to the form, double-clicking on the Save icon, and adding code as in the following screenshot:
And now the last step; build the project and run it.
That’s it! Congratulations, you have just built your first ADO .NET Entity Framework C# Application!
When the application starts, data is retrieved from the database via the EDM and displayed on the datagridview just like in the following screenshot:
Also, when you modify a record and click on the Save icon, the underlying event handler code (model.SaveChanges() method call) will post the changes to the Pubs database in SQL Server via the EDM. All the changes are saved with just the invocation of a single method!
As a recap, let’s summarize the concepts presented in this tutorial. Among other, we performed and examined the following:
- What actually is the ADO .NET Entity Framework (EF)
- What is the Entity Data Model (EDM)
- What an Entity is
- How we can create an EDM from a relational database
- What is the Mapping Tool and what the mappings actually do
- What is the Model Browser
- How we can access relational data through the EDM (with a simplified example) via the source code
- How we can post changes to the relational database through the EDM
If I have forgotten anything, you are welcome to drop me a comment! 🙂
Subsequent posts on ADO .NET Entity Framework
As mentioned at the beginning of this post, it is the first in a series of posts dedicated to the ADO .NET Entity Framework and ways of data access through EDM such as Entity SQL (eSQL), Object Services and of course, LINQ to Entities!
In subsequent posts we will have a deeper dive into the Entity Framework and we will see significant features and concepts like: inheritance, entity splitting, complex mapping scenarios, Entity Provider, eSQL, Object Services, LINQ to Entities and much more!
So, check back often for new posts!
Recommended eBooks on SQL Server: