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.
[Update 2019 – Check my online course on Entity Framework!]
In this course, you will learn all about Entity Framework, what it is, how it works, what are its available workflows and their pros and cons. Also, via comprehensive live demonstrations, we will build many apps using Visual Studio with C# and Entity Framework.Enroll Now with Discount!
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#
On the following screenshot, you can see the Database Diagram representing the database objects (just tables) of the Pubs sample database that are used for the demos in this series of posts. Note that the TopAuthors table was added manually by me as I needed a new table for supporting my demo for Entity Splitting 🙂
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.
The following screen allows selecting which database objects will be included in the EDM. The selected tables and views will be automatically mapped to the generated EDM. You can also define the model namespace:
Note: During the automated generation of the EDM, the mappings are automatically constructed for the selected tables and Views.
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
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):
By the way, let’s modify the properties of the Save icon and set Enabled to True (we need to add an event to this button/icon for allowing us to be able to post any changes performed on the data during the runtime/testing phase).
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!
Featured Online Courses:
- Essential SQL Server Administration Tips
- The Philosophy and Fundamentals of Computer Programming
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- SQL Server Fundamentals (SQL Database for Beginners)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
Check our other related Entity Framework and .NET articles:
- Entity Framework: Getting Started (Ultimate Beginners Guide – Online Course)
- The ADO .NET Entity Framework
- Screencast: How to Create an Entity Data Model
- Screencast: Using Inheritance in the ADO .NET Entity Framework
- Entity Framework: Code First
- The Net.Tcp Port Sharing Service service on Local Computer started and then stopped
- Using the C# SqlParameter Object for Writing More Secure Code
- Cannot declare instance members in a static class
- Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
- Microsoft SSIS Service failed to start. Configuration system failed to initialize
- What’s New in SQL Server 2019
- Learn Azure SQL Database: Creating your First Database
- Getting Started with SQL Server on Linux
- How to Create an Azure SQL Server Virtual Machine
- How to Backup a SQL Server Database from On-Premises to Azure Storage
- What is Azure Advisor?
Check our latest software releases!
Easily generate SQL code snippets with Snippets Generator!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Secure your SQL Server instances with DBA Security Advisor.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).