This chapter focuses on how to connect to a database using Entity Framework. Here, we will discuss how to create a web API using ASP.NET and how to consume these web APIs using AngularJS. This chapter will discuss the following topics:
- Using Entity Framework:
- Creating a data model from an existing database
- Web API:
- Creating a web API using Visual Studio
- Using AngularJS with ASP.NET Web API:
- Retrieving data using Angular JS
SPA is the latest inclination to develop applications. It loads a single HTML page initially and then dynamically updates the portion of the initially loaded HTML page as the user interacts with the page. An SPA application provides rich experience to the user who will otherwise have to reload the entire HTML page, and also reduces the round trips to the server. To develop an SPA application using the AngularJS framework, which includes features that are provided by Angular and work very well with Entity Framework, a web API is created using Entity Framework and provides standard HTTP verbs. These verbs, such as GET, POST, PUT, and DELETE are used to perform CRUD operations.
On the client-side, the AngularJS framework also uses the same standard HTTP verbs to communicate with a server through a web API.
ActiveX Data Objects (ADO), used in a .NET framework; ADO.NET is a set of classes, which are used to expose data access services to a .NET framework developer. ADO.NET contains a set of components that create distribution and data-sharing applications. Using ADO.NET, we can create frontend database clients and data access layers for use by applications. An application developer frequently uses ADO.NET to access databases in relational database systems, such as SQL, Oracle, and so on.
ADO.NET is a very powerful framework to use for accessing data. It has been in the market for many years. ADO.NET Entity Framework is an enhancement to traditional ADO.NET, which enables the developer to develop data access applications by using a conceptual data model instead of directly connecting to the relational database. With the Entity Framework, the amount of code and the maintenance required for data-oriented applications significantly decreases as compared to other applications. Microsoft Entity Framework is an Object-relational mapping (ORM), which eliminates the need for most data access code that a developer usually writes. The ADO.NET Entity Framework provides change tracking, identity resolution, lazy loading, and query translation. This way, developers are more focused on applying business logic instead of data access fundamentals.
The ADO.NET Entity Framework consists of the following:
- Data source: This data source provider connects the ADO.NET interfaces to relational databases, such as SQL, Oracle, and so on.
- Mapping: This translates SQL commands into queries using the native SQL flavor of the database. It also contains a store-specific bridge, which is responsible for translating the generic command tree to a store-command tree.
- Entity Data Model (EDM) parser and view: This enables a developer to program a conceptual data model. It takes the Schema Definition Language (SDL) specification of the data model and maps it with the underlying relational data model.
- Query and update: This converts the queries and filters and updates requests into canonical command trees, which will then be converted into store-specific queries later by the map provider.
- Metadata: This handles all the metadata associated with entities, relationships, and mappings.
- Transactions: This layer will integrate transactional capabilities if the underlying data store does not support the transactions.
- Conceptual API: At runtime, this layer exposes the data model against the conceptual schema. It uses the ADO.NET connections object to bring up the map provider, uses command objects to send the query, and returns the result in the form of
EntityResultSetsorEntitySets. - Disconnected: This uses locally cached datasets and entity sets, which use the ADO.NET framework for infrequently connected applications.
- Embedded database: This ADO.NET framework provides client-side caching and querying of relational databases using lightweight embedded databases.
- Design tools: This mapping designer specifies the mapping between the conceptual schema and relational schema. It also specifies which properties of the entity correspond with which table in the database.
- Programming layer: This layer of the Entity Framework exposes the Entity Data Model (EDM), which can be used with any managed programming languages.
- Object: This automatically generates classes for .NET Common Language Runtime (CLR).
- Web services: This exposes the ADO.NET entities as web services.
- High-level services: These consist of reporting services, which use ADO.NET entities rather than the relational data.
The following figure shows the structure of the ADO.NET Entity Framework:
The ADO.NET framework Entity Data Model (EDM) is a concept that describes the structure of the data, irrespective of its data store. It is a conceptual representation of the relational database in the form of entities (classes) and the relationships between these entities. It is defined in a Domain Specific Language (DSL), which implements the concepts of EDM. Entities and relationships in EDM allow the developer to focus on the conceptual model instead of the relational database schema.
The ADO.NET framework in Visual Studio initially generates one to one mapping between a relational database and the conceptual data model schema. In the relational database, the data is stored in the form of tables and these tables are related to each other using primary and foreign keys. In the conceptual data model, each property of the entity (class) maps to a certain column of the database table. The entities can be related to each other, independent of the relationship in the physical schema. The ADO.NET Entity Framework uses EDM to perform the mapping and uses ESQL to perform queries, operations, and updates on the entities. It then translates these queries into the native SQL flavor of the underlying database. The ADO.NET Entity Framework provides object services, which represent the relational database tables in place of entities as objects with elements and relationships exposed as an entity's properties. These entities (classes) can be accessed with the help of any object-oriented programming language. We can also consume these entities in client-side applications using web services. For example, Windows Communication Foundation (WCF) data services or XML, which are used when entities are serialized for persistent storage or for over-the-wire transfer.
This figure shows the ADO.NET framework mapping:
The entities in Entity Framework are examples of the types of entities. Each entity represents the instance of the objects. For example, customer information can be retrieved from the customer table in the relational database. The entity type defines the class of an entity and the properties it should have. Properties describe the characteristics of the entity by giving it a name and a type. The properties are fully typed and are compatible with the type systems used in Database Management System (DBMS), and this is the common type system of the .NET framework. The properties of Entity can be SimpleType, resembling primitive data types, for example, an integer, string, float, and so on; or ComplexType, which is an aggregate of multiple properties of a simple or complex type.
All entities are accommodated in EntityContainers, which are unique for each project. Each project can contain one or more EntityContainers in order to refer to the entities across multiple namespaces and entity types. EntitySets store multiple instances of one entity type and entity types can have multiple EntitySets.
Entities can be related either by an association relation or a containment relation. For example, let's consider an entity shipment, which is used to bill a customer. It is an association relation, while another entity order, which contains the order details, is a containment relation. Containment relations are also used for inheritance between entities.
Relationships in the ADO.NET framework also provide operations or actions associated with a relation, which permits us to perform actions on the entity in the event an action is being performed. When some operation is done on a related entity, a relationship can be specified on which an action can be taken. The ADO.NET Entity Framework supports the following types of relationships:
- One to One: A row of an entity is linked with only one row of a table in the database. For example, one person has only one national identity card number.
- One to Many: A row of an entity is linked with one or more rows of a table in the database. For example, a single order has many items.
- Many to Many: This refers to a relationship between two entities, when one or more row in a table is associated with more than row in another table in the database. For example, a customer can purchase many different products, and products can have many different customers.
This figure shows the Entity Framework's entity model designer:
The ADO.NET Entity Framework uses a structured query language called Entity SQL, which writes declarative queries, and updates entities and entity relationships at an intangible level. Entity SQL is different from traditional SQL. It does not have explicit constructs to join because EDM is designed for intangible partitioning of data through tables. The EntityClient classes are used to query against the conceptual model. The query pipeline parses the entity SQL query into a command tree, isolating the query across multiple tables, which are handed over to the EnitityClient provider.
The ADO.NET Entity Framework is an object relational database mapper, which enables a .NET developer to easily access objects (classes) generated by the Entity Framework of an existing relational database. We will use the Northwind database, which can be downloaded at http://www.telerik.com/forums/where-to-download-northwind-mdf-and-telerik-mdf and Visual Studio 2013 Professional, though any other version of Visual Studio can also be used. This database consists of several tables, such as products, order details, supplier, categories, territories, region, employee territories, employees, orders, customers, shippers, customer demographics, and customer demo. Save this downloaded Northwind database to your computer. The following steps show you how to connect to the Northwind database:
- Launch Visual Studio. Navigate to File | New | Project, as shown here:
- Expand Visual C# under Templates, and select Web. Give the project a name and then select the location to store the project, as shown here. Click on OK:
- Select the Empty template and check the Web API checkbox. Click on OK:
- In the solution explorer in Visual Studio, right-click on the
App_Datafolder. Select Add and then select Existing Item…. Browse to the downloaded Northwind database (Northwind.mdf). We will keep the Northwind database in the project but you can attach it to the SQL server:
- Right-click on the project and select Manage NuGet Packages. Select Online, search for EntityFramework, and click on Install:
- Right-click on the project and go to Add | New Item. Select Data in the left-hand side menu and ADO.NET Entity Data Model from the right-hand side list, and click on Add:
- Select EF Designer from database and click on Next:
- As you can see in the next screenshot,
northwind.mdfis selected automatically. If you are using the SQL server, click on New Connection and follow the steps to connect to the SQL server:
- Select the tables that you want to import into the Entity Data Model wizard. We've selected all the tables, as shown here. You can also select views and stored procedures. Click on Finish:
