Asp Net MVC , Web API and Entity Framework(EF) application

P.Madhanasekaran

But real world applications mostly use database tables to store data. So now we will see an application which stores the data in a MSSQL Server database –table. Here the Web API uses EF to get the above data and provide the Rest services .There were detailed discussions about the “ORM” EF from time to time. A number of artifacts are involved. Please look at the sketch below:

The ORM does a lot of work:

1) ORM maps the table and domain class so that we can think in terms of domain objects when we code or design.
2) It also provides an interface/context to store or retrieve domain objects.
3) Query –tool .When the client queries the interface, the query tool (here LINQ to Entities) is responsible for taking the object /LINQ query and translating it into a SQL query against the database. Once the query returns, Entity Framework is responsible for copying the data into instances of the classes that make up the model .i.e. the ORM populates the domain objects in the interface with data from the DB table.
4) The ORM provides tools to generate the necessary artifacts i.e.1) table 2) domain class 3)   mapping information 4) the interface

 But you have to manually create at-least one: 
a) Table (Database-First) or
b) Code the domain class (Code-First) or
c)  Draw an entity diagram for the domain class using the Tools in the IDE (Model-First).

The Tools will take care of the rest. You can furnish the mapping information or the ORM will follow conventions.
In short, the ORM enables OOP programmers to think in terms of objects and their attributes rather than tables and columns and provides an interface to enable him deal with only domain objects. The ORM uses a query tool like HSQL or LINQ to Entity to translate our object oriented queries into raw SQL. The ORM will interact with the database tables and furnish the data. Actually it populates the domain objects in the interface with the data from DB or stores the domain object from interface into the DB tables. In other words it takes care of what they call “impedance mismatch”. NoSQL people boast they can do without an ORM. But similar “Object Document Mappers (ODM) ” like Mongoose  are used in all significant applications. Articles on Hibernate, NHibernate, EF and Mongoose are available in archives of DIQ site.

EF: Normally in EF we have to add or retrieve domain objects from the DbSet in the “Context” (sub-class of DbContext) and we will have no direct interaction with DB tables. EF also provides for direct interaction with DB tables but you are not expected to use the same, unless you feel the querying capacities of Query tools used “LINQ to Entity” and “Entity SQL” are not sufficient or not fast enough for your purpose and you need “raw” SQL.

Connect to MS SQL Server:  We intend to follow “database-first” approach; i.e. will use the tools in Visual Studio to generate the other artifacts from a database table. We will create a db and a table using SQL Server Management Studio (SSMS). Connect to SQL Server from SSMS. Before that, I would like to give a word of warning.  If you install MSSQL Server 2012 & SQL Server Management studio, please ensure that Visual Studio shell 2010 is not already in the system. Some of the components of SSMS are built upon VS shell 2010 and they may not be installed properly, if your system has separate VS Shell 2010.

When you connect to your SQL Server, your SSMS may look as in Figure1.Your server name may be different. You can click “ Connect”. You will get Figure-2.Right Click on Databases and choose New Database. You will get Figure-3.Give Database Name “EmployeeDb” and Click OK. If you expand databases now, you can see EmployeeDb. Expand it and right click on Tables and click on New Table. You will get Figure-4.

Give the Column Names and Types as under:
Column Name   DataType
Id                            int
Name                  varchar(50)
Department       varchar(50)

Selecting Id, click key Symbol in the tool bar to make it the primary key. As shown in Figure-5 under Column Properties navigate to “Identity Specification” and expand it clicking on it and Choose “Yes”-to auto-generate values for Id. The tool will fill up values as shown in Figure-5.Right click on dbo.Table1tab and choose Save Table1.You will get Figure-6.You can give the name as “Employee” and Click “OK”. If you expand Tables now there will be an entry “dbo.Employee”. You can right click on it and choose “Edit Top 200 Rows”. You will get Figure-7.Give values for Name and Department (Id need not be given, as it will be generated by DB) and use Tab to go to the next records. Add a few records. Now DB and table are ready for use.

Create a project and generate a Model class:

In the Visual Studio Express 2013 for Web, create a New Asp Net MVC4 with support for Web API as we did in our earlier sample. You can name it as “MvcWebEF”. The IDE will generate files and folders. Right Click “Models” folder and choose Add àADO.Net Entity Data Model. In the wizard that appears you can leave the Item name as “Model1” and click “OK”. You will get Figure-8.Click “Next”. In the Wizard that appears click on “New Connection” .In the next Wizard, give the Server name as you gave in SSMS (Earlier Figure-1) and expand “Select or Enter a Database Name”. You may get Figure-9.Select “EmployeeDb”. Please click OK. You may get Figure-10. If you can understand what is there you can feel elated as your knowledge of EF is good. Otherwise there is no problem. The IDE is there to help you in your job. Just remember the name “EmployeeContext”-entity connection settings in “Web.Config” .The IDE will generate the Context class-  sub-class of DbContext-   in this name. Please click “Next”. The next wizard will say that Entity Framework Version5.0 is used. You can click “Next”. In the wizard that appears check Tables and expand it and it may appear as in Figure-11.Click Finish. A number of files will be generated under “Models”. Actually the IDE uses two templates 1) Model.tt to generate the entity class and Model.Context.tt to generate the context. So if you expand Models you can see Employee. cs under Model1.tt and EmployeeContext.cs  under Model1.Context.cs.The Code for the above two files are reproduced below:

Employee.cs

namespace MvcWebEF.Models
{
using System;
using System.Collections.Generic;

public partial class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public string Department { get; set; }
}
}

There is nothing special in the above IDE generated code. The domain class “Employee” has been generated by the template from the table “Employee” and the column names have become automatic properties of the domain/entity class.

namespace MvcWebEF.Models
{
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

public partial class EmployeeContext : DbContext
{
public EmployeeContext()
: base("name=EmployeeContext")
{
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}

public DbSet<Employee> Employees { get; set; }
}
}

  • As mentioned earlier, the name of this class corresponds to the Connection string setting in Web.Config, you gave in the Wizard-Figure-10.

Repository:  Now the Web API can use EF and  we can create the controller of our Web API. But to avoid tight coupling between EF and the controller, we will use a repository. i.e. create one more file “StaffManager.cs”  . Add a “Manager” folder and under it create StaffManager.cs. The code, which is closely related to the database, has been put in this class instead of getting placed in the controller itself .It is given below:

StaffManager.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MvcWebEF.Models;
namespace MvcWebEF.Manager
{
public class StaffManager
{
EmployeeContext Db;
public StaffManager() { Db= new EmployeeContext();
}
public IEnumerable<Employee> GetEmployees()
{
return Db.Employees;
}
}
}

Controller class

Right clicking on Controllers folder you can choose Add à Controller. In the wizard that appears,  give name as “EmployeesController” and choose “Empty API Controller”. You can click “Add”. Only a stub of the Controller- without any method- will be generated, as we have chosen Empty controller. It can be filled up as under:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using MvcWebEF.Manager;
using MvcWebEF.Models;
namespace MvcWebEF.Controllers
{
public class EmployeesController : ApiController
{
public IEnumerable<Employee> GetAll()
{
StaffManager manager = new StaffManager();
return manager.GetEmployees();
}
}
}

Just remember the naming convention in Asp Net Web API.The name of a controller action has to contain a HTTP method name; otherwise you have to affix an attribute to an action to enable to framework to map an incoming request to the action. Here the HTTP Get request will go to GetAll().The action just invokes the GetEmployees() in StaffManager and returns the IEnumerable<Employee> it got from that method.

As in our earlier sample, Asp Net MVC will act as a client for the services provided by above Web API Controller. So you can replace the code in Views/Home/Index.cshtml with the following:

@section scripts{
<script type="text/javascript">
$(function () {
$('#search').click(function () {
$('#employees').empty();
$.getJSON("/api/employees", function (data) {
$.each(data, function (i, employee) {
var content = employee.Id + ' ' + employee.Name;
content = content + ' ' + employee.Department;
$('#employees').append($('<li/>', { text: content }));
});
});
});
});
</script>
}
<div>
<div>
<h1>
Employees Listing
</h1>
<input id="search" type="button" value="Get" />
</div>
<div>
<ul id="employees" />
</div>
</div>

The code is the same as given in the earlier sample and you can see the explanation there.

Run the Project:

You can rebuild the project and Debug it. The Browser will open and Change as in Figure-12.When you click “Get” You can see the records from the table displayed.

Conclusion

The SSMS was used to create a database and a table in MSSQL Server. In the IDE the table was used to generate the Model and a “Context”- sub-class of DbContext. You can issue queries to the DbSet in this file and the Entity Framework will return the data from the database table. But we have used a repository to interact with the Context class instead of coding the Controller to directly interact with the Context. They say, by moving the code closely related to the database to repository classes, controllers can be fully unit-tested. The repository classes can also be integration-tested. There is no change in the JavaScript code in the of ASP Net MVC client to the Web API –“Index.cshtml” . Whether the data was hard-coded, as in earlier sample, in the controller or whether it was got from the EF, the client code remains the same. But as you can see it has only “minimal” features. No attempt even to “add” data was made, as the “Index.cshtml” will become “complex” with a lot of <div> <form> etc. As we will see if we use Angular in place of JQuery we can add more features easily, using a “scaffold” like Angular-seed. But we may have to make a CORS (Cross Origin Resource Sharing) request from a different origin to the Web API. We will see how to do it in the next article.

We will create the client app- in fact HTML-5 application- in NetBeans (NB). HTML-5 actually blends html and CSS with JavaScript. One may not know NB as a JavaScript IDE; but it is in-fact a good JavaScript IDE with a lot of features. You can add a JavaScript file to any NB project through Fileà New. The source editor provides standard features such as code completion and syntax coloring. There is support for debugging and Testing. Attention is invited to the article “Developing HTML5 Applications” on Oracle.com. We can also have the JavaScript library of our choice-JQuery, Angular, Backbone, Ember etc in the HTML-5 application. An HTML5 application project is an application that is displayed in a browser on your desktop or a mobile device and there is support for it in Visual Studio through “LightSwitch” or some third party extensions. But, as we will see, NB is in-fact a good and simpler choice for creating HTML-5 Clients for our Web API Rest Services. In another article, we will see how the same code can access the RESTful web application created with “MongoDB, Express and Node (MEN)” stack.

 

 

 

 

 

Figure 8

Figure 9

 


 

 

This article is written by P. Madanasekaran. For more information you can send an email to sekaran.madhan@gmail.com








}