Accessing an SQL database from an object-oriented application is quite a frequent task to perform for developers. Poor guys must cope with quite a tedious and lingering process every time: to create so-called entity classes that correspond to database tables, and to map columns to the properties of those classes. As mentioned already, this process is really tedious, since entity classes look very alike when one has already figured out, for instance, how to map the primitive data types of the database management system (such as MS-SQL) to the primitive data types of the object-oriented framework (such as .NET), or how to realize the table relations between entity classes. Of course, a bunch of other questions arises as well, such as, for instance, how data manipulations (insert, delete, update) on entity objects can be executed in the database. A developer may implement his/her own solutions for these problems, or apply others’ ideas or even ready-to-use toolkits. Since these solutions can mostly be automated, many developers have implemented their own solutions so far, and share with the developer community such useful tools that are capable to generate source code from certain kinds of databases. Those tools are called Object-Relational Mapping (ORM) tools.
There exist a lot of ORM tools for .NET. Among them, there exist free tools, such as NHibernate, which uses an SQL-like syntax for queries, and there exist even commercial ones, such as Devart LinqConnect, which provides a link to LINQ.4 We would like to mention here Microsoft’s two own solutions. One is Linq to SQL,
4 NHibernate supports MS-SQL, Oracle Database, PostgreSQL, and MySQL. So do Devart dotConnect and LinqConnect, depending on which release you use; they support the development to special platforms (e.g., Windows Phone, Windows Store) as well, and, furthermore,
which can be considered as an entry-level ORM tool, which puts emphasis on rapid prototyping, (exclusively) on MS-SQL databases. Unfortunately, Microsoft is not always consistent with its own objectives, as illustrated by refusing to add MS-SQL CE 4.0 support to LINQ to SQL (3.5 still supported). Nevertheless, for 4.0 we can use Microsoft’s other ORM tool, LINQ to Entities, which is included by the ADO.NET Entity Framework.
The ADO.NET Entity Framework is an open source ORM framework for .NET. It uses an architecture consists of several abstraction layers, including the database-dependent connector or provider. For numerous database management systems, there exist available (and even official) ADO.NET connectors. Another layer performs a mapping to entity classes, by generating a so-called Entity Data Model (EDM) from a given database, in XML format (EDMX file). Visual Studio offers an Entity Data Model Wizard. The bottom layers of the architecture perform various tasks, from translating (LINQ) queries into SQL, to transaction processing.
There are plenty of books on ADO.NET and Linq to Entities (Freeman & Rattz, 2010). We simply want to give a practical and motivating introduction to this topic, and therefore to show the smashing way to generate entity classes by a few clicks. This can be done even in Visual Studio. Add a new item, a so-called „ADO.NET Entity Data Model‖ to your project, as can be seen in Figure 8.
XVII.8. Creating an ADO.NET EDM in Visual Studio I
After choosing the option to generate a model from an existing database (Figure 9), select the database in the window in Figure 10.
XVII.9. Creating an ADO.NET EDM in Visual Studio II
XVII.10. Creating an ADO.NET EDM in Visual Studio III
In the drop-down list, one can see databases added with Server Explorer before (if you would like to choose another database, push the „New Connection‖ button, which redirects you an already known window). The next form can be seen in Figure 11, on which you can select the database items that you would like to map into your model. Note that, in MS-SQL CE, even stored procedures („Stored Procedures and Functions‖) can be mapped into C#! The option „Pluralize or singularize generated object names‖ corresponds to the opportunity to give names in plural form to collections; e.g., the collection that contains all the chocolats will be called Chocolats.
XVII.11. an ADO.NET EDM in Visual Studio IV
After using the wizard, the model is generated and displayed in the visual form can be seen in Figure 12. Note that relations between tables have been detected in a correct way, i.e., Manufacturer–Chocolat is a one-to-many relation, but Chocolat–Material is many-to-many! In connection with the latter one, it is especially interesting that the ChocolatMaterial junction table does not even appear in the model. Notice furthermore that relations are represented by separate properties („Navigation Properties‖) in the entity classes. For example,
Chocolat.Manufacturer represents the manufacturer of a given chocolat, and Manufacturer.Chocolats the collection of all the chocolats produced by a given manufacturer.5
XVII.12. Entity Data Model
It is worth to browse, in Solution Explorer, the files generated by the wizard. By clicking the Chocolat.edmx file, the graph shown in Figure 12 appears, even though it is an XML file, as mentioned before. In order to see its XML content, right click on the file, click the „Open With‖ menu item, and then select the „XML (Text) Editor‖!
When opening the Chocolat.edmx section, one can see many other files as well. For us, C# files are the most important. As can be seen, our three entities, according to the model, can be found in the Chocolat.cs, Manufacturer.cs, and Material.cs files. It is worth to take a look into these files, only to see what kinds of solutions and classes are used inside. There is an additional, very important C# file (Chocolat.Context.cs) as well, in which one can find the class that represents the database connection and a link to the tables.
Let us now show how to use the generated entity classes (Chocolat, Manufacturer és Material) in C# code. But first, we need to establish a connection to our database; the wizard makes this very easy by generating a separate
„context‖ class (ChocolatEntities). In order to establish a database connection, we do not have anything else to do but instantiating this class. The next code sample shows a common way to do this; perform the instantiation in the App class in your WPF application, and store the ChocolatEntities instance in a static property (which is therefore initialized in the static constructor). By doing so, the instance can be accessed from any location of the source code, during the entire lifetime of the application.
public partial class App : Application
From now on, our database tables can be accessed easily through the corresponding collections in the „context‖
instance (db.Chocolats, db.Manufacturers, and db.Materials). They all can be used in LINQ queries in the conventional way, for example:
var query = from ch in App.db.Chocolats
where ch.Manufacturer.Address.Contains("Hungary") && ch.Materials.Count >= 3
5 In the wizard, we have enabled the „Pluralize or singularize generated object names‖ option, therefore all the collections’ names are in plural form.
orderby ch.Name select ch;
As the example shows, the properties that represent table relations can largely unburden developers; as a matter of fact, they can almost completely avoid the usage of joins.6