Business Entity And Data Access Layer In MVC

This article explains the following,

• How to create Business Entities Layer
• How to create Data Access Layer
• How to display data from Database in View
• How Model folder is Optional in MVC.
  

Design your Database
In this article I used the following table and stored procedure.

• Go to Run -> Enter devenv and click on Ok.
• Go to File, New, then Project... or (Ctrl + Shift + N),
• Select Visual C# - Web, then select ASP.NET MVC 4 Web Application.
• Provide the project a name and specify the location and click on OK.
• Select a template as Empty and view engine as Razor.
• Click on OK.

Step 2: Connection string in Web.Config.

• Create the connection string in the Web.Config file as in the following code snippet.
  
  1. <connectionStrings>
  2. <add name="conStr"
  3. connectionString="Password= 1234; User ID=sa; Database=DB_CsharpCorner; Data Source=."
  4. providerName="System.Data.SqlClient"/>
  5. </connectionStrings>

Step 3: Create Business Entities Layer (BE).

• Right click on Solution Explorer and add Class Library project and name it as MVC_BusinessEntities.
• Then change the class name as BE_Employee
• Copy and paste the following code in your BE_Employee.
• Build the Project.
  
  1. using System;
  2. namespace MVC_BusinessEntities
  3. {
  4. public class BE_Employee
  5. {
  6. public int ID
  7. {
  8. get;
  9. set;
  10. }
  11. public string Name
  12. {
  13. get;
  14. set;
  15. }
  16. public string Gender
  17. {
  18. get;
  19. set;
  20. }
  21. public int Salary
  22. {
  23. get;
  24. set;
  25. }
  26. public int DeptId
  27. {
  28. get;
  29. set;
  30. }
  31. }
  32. }

Step 4: Create Data Access Layer (DL).

• Right click on Solution Explorer and add Class Library project and name it MVC_DataAccessLayer.
• Then change the class name as DL_Employee.
• Add the BE project ref to DL.
• Copy and paste the following code in your BE_Employee.
  
  1. using MVC_BusinessEntities;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Configuration;
  5. using System.Data;
  6. using System.Data.SqlClient;
  7. namespace MVC_DataAccessLayer
  8. {
  9. public class DL_Employee
  10. {
  11. public IEnumerable < BE_Employee > Employees
  12. {
  13. get
  14. {
  15. string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
  16. List < BE_Employee > employees = new List < BE_Employee > ();
  17. using(SqlConnection con = new SqlConnection(connectionString))
  18. {
  19. SqlCommand cmd = new SqlCommand("USP_GetAllEmployees", con);
  20. cmd.CommandType = CommandType.StoredProcedure;
  21. //cmd.Parameters.AddWithValue("@DeptId", null);
  22. con.Open();
  23. SqlDataReader rdr = cmd.ExecuteReader();
  24. while (rdr.Read())
  25. {
  26. BE_Employee employee = new BE_Employee();
  27. employee.ID = Convert.ToInt32(rdr["Id"]);
  28. employee.Name = rdr["Name"].ToString();
  29. employee.Gender = rdr["Gender"].ToString();
  30. employee.Salary = Convert.ToInt32(rdr["Salary"]);
  31. employee.DeptId = Convert.ToInt32(rdr["DeptId"]);
  32. employees.Add(employee);
  33. }
  34. }
  35. return employees;
  36. }
  37. }
  38. }
  39. }

Step 5: Add references.

• Build the Dl project.
• And the add the BE and DL references to MVC Project.

Step 6: Add a Controller.

Right click on controller folder, click on Add -> Controller and name it EmployeeController. Then click on Add.



Copy and paste the following code in your EmployeeController.