Application Security Testing: An Integral Part of DevOps
Introducing the ADO.NET Entity Framework
Database development with the .NET framework has not changed a lot since its first release. Many of us usually start by designing our database tables and their relationships and then creating classes in our application to emulate them as closely as possible in a set of Business Classes or (false) "Entity" Classes, and then working with them in out ADO.NET code. However, this process has always been an approximation and has involved a lot of groundwork.
This is where the ADO.NET Entity Framework comes in; it allows you to deal with the (true) entities represented in the database in your application code by abstracting the groundwork and maintenance code work away from you. A very crude description of the ADO.NET Entity Framework would be "It allows you to deal with database concepts in your code."
In this tutorial, you will go over the following:
- Page 1: Set up the environment and database, generating the Entity Data Model
- Page 2: Basic ADO.NET Entity Framework operations with a form for Payrolls
- Page 3: Adding a little efficiency using another form for Authors
- Page 4: The case for using stored procedures in the Entity Framework
- Page 5: Using stored procedures to perform SELECT operations against the database in the Articles form
- Page 6: Using stored procedures for the INSERT, UPDATE, and DELETE operations in the Articles form
- Page 7: More information and conclusion
Setting Up Your Environment
For this ADO.NET Entity Framework tutorial, you will need the following:
- SP1 for .NET Framework 3.5/Visual Studio 2008 (which you can download here.)
- Some C# knowledge, because the code samples here are in C#
- A little prior knowledge of ADO.NET and SQL
- Approximately 250ml to 350ml of trimethylxanthine, otherwise associatedly known as coffee
Setting Up the Database
You can either create your own project, or refer to the project files attached to this article (bottom of page), but I would recommend starting your own and glancing at the attached project files if you need to. Before you start coding, though, you will need to create the database and its objects that will be used and referred to in this tutorial. The DatabaseScript.zip file contains a .sql script that you need to run against your SQL Express or SQL Server database; this script will generate the database for a theoretical publishing company, inventively named PublishingCompany, and the tables and stored procedures required.
Note: You don't need to use SQL Server. You can use any database you'd like, but then you will need to modify the script to work with the SQL implementation for your database. For the purposes of this tutorial, I will continue to refer to SQL Server as the database.
Generating an Entity Data Model in Your Visual Studio Project
Once you are satisfied that the database has been created and you have had a look through all of the tables and its fields, start by creating a new Windows Forms Application project. I suggest the name of the solution to be SodiumHydroxide. I chose this name because I'm hoping that this project will serve as a good base for your learning. (Chemistry pun alert!)
The very first step is to generate your Entity Data Model from the database that you created earlier; this will serve to be at the core of all your ADO.NET Entity Framework operations. To do this, right-click on the project and add a new item. Add an "ADO.NET Entity Data Model" and call it PublisherModel.edmx to correspond to your database.
The Entity Data Model Wizard shows up and you now can use this to query your database and generate the model diagram, as long as you supply it with the right credentials. In the Wizard, click "Generate from Database" and click Next.
Supply it with the right server name, authentication, credentials, and the database name PublishingCompany.
Yes, I do like to name various entities on my home network after arcane Mesoamerican civilizations. Finally, "Save entity connections settings in App.Config as" should be PublishingCompanyEntities.
In the next dialog box, choose all of the options—tables, views, and stored procedures—and the model will be generated for you. You should end up with this:
This is a graphical representation of the Entity Data Model (EDM) that's generated by the wizard. Note that it isn't exactly a table mapping in the database, but it looks close. You'll also see that the Author entity has an article reference and payroll reference, even though you haven't actually created fields in the Author table; this relationship was derived from the foreign key constraint by the EDM generator.
If you are like me, you probably want to know what's happening behind the scenes; you can right-click on the .edmx file in Solution Explorer and choose to view it with an XML Editor. Even if you aren't interested, I would encourage you to look at the XML anyways, because advanced Entity Framework operations will require you to directly edit the XML file, but not for this tutorial. As you can see, the EDM is essentially an XML file that's generated from the database schema, and which is understood by the Visual Studio designer to give you a graphical representation of your database entities.
On the next page, you will start working on the first form with basic Entity Framework operations.