₹2,000.00 ₹300.00
  • 11 students
  • 6 lessons
  • 0 quizzes
  • 10 week duration


Database in MVC

Data Access Layer

A real time project is incomplete without Database. In our project we have not spoken about database layer yet. Today first Lab will be all about database and database layer. Here we will use SQL (Structured Query Language) Server and Entity Framework for creating Database and Database Access layer respectively. What is Entity Framework? EF or Entity Framework is a Microsoft ORM tool. ORM stands for Object Relational Mapping. In RDBMS (Relational Database Management System) world, we speak in terms of Tables and Columns whereas in .NET world (which is an object oriented world), we speak in terms of classes, objects and properties. When we think about any data driven application we end up with following two things:

Write code for communicating with database (called Data Access Layer or Database logic).

Write code for mapping Database data to object oriented data or vice-versa. ORM tool is a tool which will automate these two things. What is Code First Approach? In Entity framework we can follow one of these three approaches:

1. Database First approach – Create database with tables, columns, relations, etc., and Entity framework will generates corresponding Model classes (Business entities) and Data Access Layer code.

2. Model First approach – In this approach Model classes and relationship between them will be defined manually using Model designer in Visual Studio and Entity Framework will generate Data Access Layer and Database with tables, columns, relations automatically.

3. Code First approach – In this approach we will manually create POCO (Plain Old CLR Objects) classes. Relationship between those classes will be defined by means of code. When application executes for the first time Entity framework will generate Data Access Layer and Database with tables, column and relations automatically in the database server. We follow this approach for our Employee project.

What is mean by POCO classes?
POCO stands for “Plain Old CLR (Common Language Runtime) objects”.
POCO classes means simple .NET classes.
In our previous example (as done in Lab 3 while creating a new class Employee inside Model folder) the Employee class was simply a POCO class.

Lab 8 – Add Data Access Layer to the Project
Step 1– Create Database Connect to the SQL Server and create new Database called “SalesERPDB”.
Step 2 – Create ConnectionString Open Web.config file and inside Configuration section add the following section: <connectionStrings>
<add connectionString=”Data Source=(local);Initial Catalog=SalesERPDB;Integrated Security=True” name=”SalesERPDAL” providerName=”System.Data.SQLClient”/>

Step 3 – Add Entity Framework reference Right click the Project >> Manage NuGet Packages. Search for Entity Framework and click install.

Step 4 – Create Data Access layer l Create a new folder called “DataAccessLayer” in the root folder and inside it create a new class called “SalesERPDAL”.
Put “using” statement at the top as follows: using System.Data.Entity; l Derive “SalesERPDAL” class from DbContext class. public class SalesERPDAL: DbContext {}
Step 5 – Create primary key field for Employee class Open Employee class and put “using” statement in the top as follows: using System.ComponentModel.DataAnnotations; Add EmployeeId property in Employee class and mark it as Key attribute. Key attribute is an indication to EF that it is primary key. At runtime when table get created by EF EmployeeId becomes primary key.

public class Employee
public int EmployeeId {get; set;}
public string FirstName {get; set;}
public string LastName {get; set;}
public int Salary {get; set;} }
Step 6 – Define mapping
Put “using” statement at the top of “SalesERPDAL” class. using WebApplication1.Models;
Override OnModelCreating method in SalesERPDAL class as follows:
protected override void OnModelCreating(DbModelBuilder ModelBuilder)
{ ModelBuilder.Entity<Employee>().ToTable(“TblEmployee”);
base.OnModelCreating(ModelBuilder); }
Note: In above code snippet “TblEmployee” represents the table name. It automatically gets created during runtime.
Step 7 – Create property to hold Employees in Database

Create a new property called Employee in “SalesERPDAL” class as follows:

public DbSet<Employee> Employees

DbSet will represent all the employees that can be queried from the database.
Step 8– Change Business Layer Code and get data from Database Open EmployeeBusinessLayer class.
Put “using” statement at the top.
using WebApplication1.DataAccessLayer;
Now change GetEmployees method class as follows:
public List<Employee> GetEmployees()
SalesERPDAL salesDal = new SalesERPDAL(); return salesDal.Employees.ToList();

Step 9 – Execute and Test Press F5 and execute the application. Right now we don’t have any employees in the database so we will see a blank grid. Check the database. You will see a new table called TblEmployee got created with four columns.

Step 9 – Insert Test Data Add some dummy data to TblEmployee table.

Step 10 – Execute and test the application Press F5 and run the application again. Here we go ☺. Talk on Lab 8 What is DbSet?


DbSet represents entity collection that can be queried from the database. When we write a LINQ (Language Integrated Query) (Microsoft)) query against DbSet object, it internally creates the select query and executes against the database. In our case “Employees” is a DbSet of Employee object. As per the mapping code written Inside OnModelCreating method, Employee class is mapped to “TblEmployee” table in DB hence accessing “Employees” makes EF to execute a query against “TblEmployee” table in DB. How are both connection string and data access layer get connected? Mapping will be done based on name. In our example ConnectionString Name and Data Access Layer class name are same. Both are named as “SalesERPDAL”, hence mapping will be done automatically. Can we change the ConnectionString name? Yes, in that case we have to define a constructor in Data Access Layer class as follows:
public SalesERPDAL():base(“NewName”)
{ }
Organize Everything Just to make everything organized and meaningful let’s do couple of changes.


Step 1 – Rename

“TestController” to “EmployeeController”

GetView action method to Index l Test folder (inside Views folder) to Employee
“MyView” View to “Index”
Step 2 – Remove UserName property from EmployeeListViewModel
Step 3 – Remove UserName from View Open Views/Employee/Index.cshtml View and remove UserName from it. In simple words, remove following block of code: Hello @Model.UserName <hr />
Step 2 – Change Index Action Method in EmployeeController Accordingly change the code in Index action in EmployeeController as follows:

public ActionResult Index()
{ …… …… …… employeeListViewModel.Employees = empViewModels;
//employeeListViewModel.UserName = “Admin”; —>Remove this line —>Change1 return View(“Index”, employeeListViewModel);
//—>Change View Name —>Change 2 }
Now at the time of execution URL will “…./Employee/Index” instead of”…/Test/GetView”.

Lab 9 – Create Data Entry Screen Step 1 – Create action method Create an action method called “AddNew” in EmployeeController as follows:
public ActionResult AddNew()
return View(“CreateEmployee”);
Step 2 – Create View Create a View called “CreateEmployee” inside View/Employee folder as follows:
Layout = null;
<!DOCTYPE html>
<head> <meta name=”Viewport” content=”width=device-width” />
<form action=”/Employee/SaveEmployee”

First Name: <input type=”text” id=”TxtFName” name=”FirstName” value=”” />
<br />
Last Name: <input type=”text” id=”TxtLName” name=”LastName” value=”” />
<br />
Salary: <input type=”text” id=”TxtSalary” name=”Salary” value=””/ >
<br />
<input type=”submit” name=”BtnSave” value=”Save Employee” />
<input type=”button” name=”BtnReset” value=”Reset” />


As you can see, markup contains two types of input buttons – submit and button. It also contains a special markup tag called form. We will talk about importance of all of these at end the Lab.
Step 3 – Create a link in Index View Open Index.cshtml and add a hyperlink pointing to AddNew action method. <ahref=”/Employee/AddNew”>Add New</a>
Step 4 – Execute and Test the application Press F5 and execute the application.

Talk on Lab 9
What is the purpose of form tag?
In Day 1 we have discussed on request-response process in Web world. In Web world, end user makes the request and server sends the response.” End user can send a request to server in four ways.
1. With the help of browser address bar- We have seen demonstration many times in this book.
2. With the help of hyperlink – Lab 9 was all about it.
3. With the help of Ajax (Asynchronous JavaScripts and XML) – We will talk about it soon.
4. With the help of submit button and form tag – As soon as the submit button inside the form tag is clicked, a request will be sent to the URL (Uniform Resource Locator) specified in action attribute.
In above example, when save button is clicked you will notice a page refresh and error in response.
Ignore the error. It is because we have not defined SaveEmployee action method in Employee Controller yet. But page refresh and change in address bar URL proves that new request is made.
What is method attribute in Form tag?
It decides the type of request. Request may be one of the following four types – get, post, put and delete. Which method should be used when is completely depends on developer. Technically it doesn’t matter. Each method does the same thing at the end of the day. It’s just a standard defined by industry which says use,

Get – When you want to get something.
Post – When you want to create something.
Put – When you want to update something.
Delete – When you want to delete something.


As a best practice we should always use appropriate method based on requirement but in really get and post become very common in industry. Most of the developers are using get when they want to get something and using post for all other operations. How sending request using Form tag is different from browser address bar or hyperlink? When request is made with the help of Form tag, values of all the input controls are sent with the request. Server performs some processing on these values and send back the response. What about checkbox, radio buttons and drop-down list? Will values of these controls will be sent too? Yes, All input controls (input type=text, type=radio, type=checkbox) and also drop-downs (which represented as “Select” element). How values will be sent to server? When request is of type Get, Put or Delete, values will be sent as Query string parameters. When it’s a post request values will be sent as posted data. What is the purpose of name attribute in input controls?

As discussed before, values of all input controls will be sent along with request when submit button is clicked. It makes server receive more than one value at a time. To distinguish each value separately every value is attached with one key and that key will be simply “name” attribute. Do name and id attribute serve same purpose? Basically “name” attribute will be used internally by HTML (HyperText Markup Language) when the request is being sent whereas “id” attribute will be used by developers inside JavaScript for some dynamic stuffs. Technically in JavaScript, elements can be accessed using name attribute also but it won’t be considered as best practice. What is the difference between “input type=submit” and “input type=button”? Difference is, “input type=submit” will lead to server request whereas “input type=submit” will lead to simple JavaScript function invoke. Simple button won’t do anything by its own. We will write JavaScript on button click if required to do something dynamic.

Lab 10 – Get posted Data in Server-Side/Controllers
Step 1 – Create SaveEmployee Action method Inside Employee Controller create an action method called SaveEmployee as follows.
public string (Employee e)
return e.FirstName + “|”+ e.LastName+”|”+e.Salary;


Step 2 – Execute and Test Press F5 and execute the application.


Talk on Lab 10
How Textbox values are updated in Employee object inside action method? In ASP.NET MVC there is a concept called as Model binder.
1. ModelBinder will execute automatically whenever a request is made to an action method which accepts some parameter. SaveEmployee action method accepts one parameter of type Employee hence request to this action method make to ModelBinder execute.
2. First of all ModelBinder will iterate though all primitive parameters of a method and then it will compare name of the parameter with each key in the incoming data (Incoming data means either posted data or query string). When match is found, corresponding incoming data will be assigned to the parameter. Primitive parameters means parameters with standard data types like int, float, string, DateTime, etc. In our case SaveEmployee method won’t contain any primitive parameters.
3. After that, ModelBinder will iterate through each and every property of each and every class parameter and compares name of each property with each key in incoming data. When match is found, corresponding incoming value will be assigned to the parameter. In our case ModelBinder will iterate through all properties of Employee class and update the values.

What will happen when two parameters are specified, one as “Employee e” and second as “string FirstName”? FirstName will be updated in both primitive FirstName variable and e.FirstName property.
Will ModelBinder work with composition relationship too?
Yes it will, but in that case name of the control should be set as PropertyName.SubPropertyName.
Example Let say we have Customer class and Address class as follows:
public class Customer
public string FName
public Address address
// Control name must be set as address.PropertyName
public class Address
public string CityName
public string StateName

Action method is written as follows:
public ActionResult SaveCustomer
(Customer c)
String fName=c.FName; String CityName=c.address.CityName
In this case name attribute of CityName textbox must be set to address.CityName. HTML will look like below. … … … <input type=”text” name=”FName”> <input type=”text” name=”address.CityName”> <input type=”text” name=”address.StateName”> …. …
Lab 11 – Reset and Cancel Buttons

Step 1 – Add Reset and Cancel buttons Add Reset and Cancel buttons as follows: … … … <input type=”submit” name=”BtnSubmit” value=”Save Employee” /> <input type=”button” name=”BtnReset” value=”Reset” onclick=”ResetForm();” /> <input type=”submit” name=”BtnSubmit” value=”Cancel” /> Note: As you can see, both “Save” and “Cancel” buttons have same “name” attribute value that is “BtnSubmit” and both are submit buttons. Step 2 – Define ResetForm function In Head section of HTML add a script tag and inside that create a JavaScript function called ResetForm as follows: <script> function ResetForm() { document.getElementById(‘TxtFName’).value = “”; document.getElementById(‘TxtLName’).value = “”; document.getElementById(‘TxtSalary’).value = “”; } </script>


Step 3 – Implement Cancel click in EmployeeController’s SaveEmployee action method. Save and Cancel, both are submit buttons and both are placed inside same form tag. Clicking any of

them make a new request to SaveEmployee action method because Form tag’s action attribute is set to SaveEmployee action method. Only difference will be, when Save button is clicked posted data will contain one special entry with key “BtnSubmit” and value “Save Employee” whereas in case of Cancel button is clicked, key will remain as “BtnSubmit” but value will be “Cancel”. Hence next step in the Lab will be changing SaveEmployee as follows:
public ActionResult SaveEmployee
(Employee e, string BtnSubmit)
{ switch (BtnSubmit)
{ case “Save Employee”:
return Content(e.FirstName + “|” + e.LastName + “|” + e.Salary);
case “Cancel”: return RedirectToAction(“Index”);
return new EmptyResult();

Note: You will also notice one new thing in code – RedirectToAction. For now just take it as a way to redirect from one action method to another. We will talk about it in detail later.
Step 4 – Execute the application. Press F5 and execute the application. Navigate to the AddNew screen by clicking “Add New” link.
Step 5 – Test Reset functionality


Step 6 – Test Save and Cancel functionality


Talk on Lab 11
Why same name is given to both Save and Cancel buttons? As you know, as soon as submit button is clicked, a request is sent to the server. It won’t be just a simple request. It will hold values of all the input controls as well. Here submit button is also an input control. Hence value of the submit button will be sent too with request. Only difference is, HTML won’t send values of all submit buttons. It will send value of that submit button which caused request. When Save button will be clicked, value of Save button that is “Save Employee” will be sent with the key “BtnSubmit” and when Cancel button is clicked, value of Cancel button that is “Cancel” will be sent with the key “BtnSubmit”. In Action method, Model Binder will do remaining work. It will update the parameter values with values in input data. As you can see our SaveEmployee action method is added with one new parameter of string type with name BtnSubmit. This parameter will be automatically set to either “Save Employee” or “Cancel” based on which button is clicked. What are the other ways to implement multiple submit buttons? There are many ways.
I would like to discuss three of them.

1. Hidden Form element Step 1 – Create a hidden form element in View as follows: <form action=”/Employee/CancelSave” id=”CancelForm” method=”get” style=”display:none”> </form> Step 2 – Change Submit button to normal button and post above form with the help of JavaScript. <input type=”button” name=”BtnSubmit” value=”Cancel” onclick=”document.getElementById(‘CancelForm’).submit()” /> 2. Change action URL dynamically using JavaScript <form action=”” method=”post” id=”EmployeeForm” > … … <input type=”submit” name=”BtnSubmit” value=”Save Employee” onclick=”document.getElementById(‘EmployeeForm’).action = ‘/Employee/ SaveEmployee’” /> … <input type=”submit” name=”BtnSubmit” value=”Cancel” onclick=”document.getElementById(‘EmployeeForm’).action = ‘/Employee/ CancelSave’” />

3. Ajax Put simple button instead of submit button. In the onclick event of button make an Ajax request.

Why we have not used input type=reset for implementing Reset functionality? Input type=reset control won’t clear the values, it just sets the value to default value of a control. Example <input type=”text” name=”FName” value=”Sukesh”> In above example, default value of control is “Sukesh”. If we use input type=reset for implementing Reset functionality then by default “Sukesh” will be set in the textbox every time “reset” button is clicked. What if names are not matching with property names of the classes? This is a very common question during interViews. Let say we have HTML as follows. First Name: <input type=”text” id=”TxtFName” name=”FName” value=””/><br/> Last Name: <input type=”text” id=”TxtLName” name=”LName” value=”” /><br /> Salary: <input type=”text” id=”TxtSalary” name=”Salary” value=”” /><br /> Now our Model class contains property names as FirstName, LastName and Salary. Hence default ModelBinder won’t work here. In this situation we have following three solutions:

Inside action method, retrieve posted values using Request.Form syntax and manually construct the Model object as follows:
public ActionResult SaveEmployee()
Employee e = new Employee();
e.FirstName = Request.Form[“FName”];
e.LastName = Request.Form[“LName”];
e.Salary = int.Parse(Request.Form[“Salary”]) …. ….
Use parameter names and create Model object manually as follows:
public ActionResult SaveEmployee(string FName, string LName, int Salary)
Employee e = new Employee(); e.FirstName = FName; e.LastName = LName; e.Salary = Salary; …. ….

Create Custom Modelbinder and replace default ModelBinder as follows:

Step 1 – Create Custom Model Binder
public class MyEmployeeModelBinder: DefaultModelBinder
protected override object CreateModel(ControllerContext ControllerContext, ModelBindingContext bindingContext, Type ModelType)
Employee e = new Employee();
e.FirstName = ControllerContext.RequestContext.HttpContext. Request.Form[“FName”]; e.LastName = ControllerContext.RequestContext.HttpContext. Request.Form[“LName”];
e.Salary = int.Parse(ControllerContext.RequestContext. HttpContext.Request.Form[“Salary”]); return e;
Step 2- Replace default ModelBinder with this new ModelBinder
public ActionResult SaveEmployee([ModelBinder(typeof(MyEmployeeModelBinder))]
Employee e, string BtnSubmit)
{ …… What does RedirectToAction do? It generates RedirectToRouteResult. Just like ViewResult and ContentResult (discussed

Just like ViewResult and ContentResult (discussed in Day 1) RedirectToRouteResult is a child of ActionResult. It represents the redirect response. When we return this result from an action method, internally MVC engine sends HTTP (HyperText Transfer Protocol) 302 response to the browser, which causes the browser to make HTTP get request to the specified action method. Note: Here browser is responsible for new request hence URL will get updated to new URL. What is EmptyResult? One more child of ActionResult. When browser receives EmptyResult as a response, it simply displays blank white screens. EmptyResult represents “No Result”. In our example, this situation won’t happen. Just to make sure that all code paths returns a value EmptyResult statement was written. Note: When ActionMethod return type is void, it is equivalent to EmptyResult. Lab 12 – Save Records in Database and Update Grid
Step 1 – Create SaveEmployee in EmployeeBusinessLayer public Employee SaveEmployee(Employee e)

SalesERPDAL salesDal = new SalesERPDAL();
salesDal.Employees.Add(e); salesDal.SaveChanges(); return e; }
Step 2 – Change SaveEmployee Action method In EmployeeController change the SaveEmployee action method code as follows:
public ActionResult SaveEmployee(Employee e, string BtnSubmit)
switch (BtnSubmit)
case “Save Employee”: EmployeeBusinessLayer empBal = new EmployeeBusinessLayer(); empBal.SaveEmployee(e);
return RedirectToAction(“Index”); case “Cancel”: return RedirectToAction(“Index”);
return new EmptyResult();

Step 3 – Execute and Test Press F5 and execute the application. Navigate to Data entry screen and enter valid values and click “Save Employee” button.
Lab 13 – Add Server-Side Validation

In ASP.NET MVC server-side validations will be performed with the help of DataAnnotation attributes. They are simple attributes which will be attached to the properties of a class. Before we dig into the Lab, first let’s understand a little bit more on ModelBinder. In Lab 10 we understood how ModelBinder updates the Employee object with the posted data. l But this is not the only functionality performed by ModelBinder. ModelBinder also updates ModelState. ModelState encapsulates the state of the Model. o ModelState has a property called IsValid which determines whether all the properties of Model (that is Employee object) gets successfully updated or not. o It holds validation errors. Example, ModelState[“FirstName “]. Errors will contain all errors related to First Name. o It holds the incoming value (Posted data or Query String data) as well. How ModelBinder works with primitive data types? When Action method contains primitive type parameter, ModelBinder will compare name of the each parameter with each key in the incoming data (Incoming data means either posted data or query string).






















Leave a Reply

Your email address will not be published. Required fields are marked *