ASP.NET CORE Database Application

0

 ASP.NET  CORE Database Application

STEP 1. Create Database Tables

First, we're going to create two database tables, tblMembers and tblSkills. These tables store employee details and skills. You can create a new database in SQL Server or add these tables to an existing database.

The following scripts will create these database tables. If you want to use your existing database, you may skip this step. 
  1. CREATE TABLE [dbo].[tblEmployees](  
  2.     [EmployeeID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [EmployeeName] [varchar](50) NULL,  
  4.     [PhoneNumber] [varchar](50) NULL,  
  5.     [SkillID] [intnull,  
  6.     [YearsExperience] [intnull,  
  7. PRIMARY KEY CLUSTERED   
  8. (  
  9.     [EmployeeID] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  11. ON [PRIMARY]  
  1. CREATE TABLE [dbo].[tblSkills](  
  2.     [SkillID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Title] [varchar](50) NULL,  
  4. PRIMARY KEY CLUSTERED   
  5. (  
  6.     [SkillID] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  8. ON [PRIMARY]  

Now, let's add some seed data to our database tables. The following scripts insert data into our database tables.

  1. insert into tblSkills
  2. (Title) values('Visual Foxpro')
  3. insert into tblSkills
  4. (Title) values('C#')
  5. insert into tblSkills
  6. (Title) values('VB.NET')
  7. insert into tblSkills
  8. (Title) values('Delphi')
  9. insert into tblSkills
  10. (Title) values('Java')
  11. insert into tblSkills
  12. (Title) values('Power Builder')
  13. insert into tblSkills
  14. (Title) values('COBOL')
  15. insert into tblSkills
  16. (Title) values('Python')
  1. Insert Into tblEmployees
  2. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  3. values ('Suhana Kalla','9869569634',2,'11')
  4. Insert Into tblEmployees
  5. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  6. values ('Ashish Kalla','9869166077',8,'14')
  7. Insert Into tblEmployees
  8. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  9. values ('Manoj Kalla','9869569634',1,'24')
  10. Insert Into tblEmployees
  11. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  12. values ('Nirupama Kalla','9969359746',6,'20')
  13. Insert Into tblEmployees
  14. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  15. values ('Rajesh Bohra','9869166012',7,'28')
  16. Insert Into tblEmployees
  17. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  18. values ('Murli Vyas','9261166012',5,'18')
The data looks like the following: 
 
Entity Framework Database First In ASP.NET Core


Entity Framework Database First In ASP.NET Core

STEP 2. Create a Web Application

Now, let's create an ASP.NET Core Web Application using Visual Studio. 

We've selected Visual C# > .NET Core > ASP.NET Core Web Application. Give project a name and a location. In our case, we have EmployeeList-EF-DbFist and d:\MBK respectively.  

Entity Framework Database First In ASP.NET Core
Image 1

On the next screen, make sure you've .Net Core and Asp.Net Core 2.0 and Web Application selected. 

    Entity Framework Database First In ASP.NET Core
    Image No.2

    Press the OK button to create the project.

    STEP 3. Project structure view, folders creation

    Default view of project looks like the following:

    Entity Framework Database First In ASP.NET Core
    Image No.3

    Now, we are going to add three folders to support the MVC architecture.

    • Models
    • Views
    • Controllers

    Switch to Solution Explorer and right click on project name and select Add --> New Folder option.

    Above step repeats two more times to add three folders: Models, Views, and Controllers.

    Entity Framework Database First In ASP.NET Core
    Image No.4

    STEP 4. Set Connection, Create Models, Classes

    How to set a Connection Strings in Asp.Net Core?

    You can set a connection string inside appsettings.json file. 

    Code of appsettings.json

    1. {  
    2.   "Logging": {  
    3.     "IncludeScopes"false,  
    4.     "LogLevel": {  
    5.       "Default""Warning"  
    6.     }  
    7.   },  
    8.   "ConnectionStrings": {  
    9.     "MbkDbConstr""Data Source=(localdb)\\MBK;Database=MbkTest;"  
    10.   }  
    11. }  

    Creating Models

    We are going to create following models:

    CLASS NAMEDESCRIPTION
    EmployeeContext.csTo set entity framework.
    tblEmployees.cstblEmployees Model.
    tblSkills.cstblSkills Model.
    EmployeeViewModel.csView Model for employee listing.

    Now right click on Models folder to add a class called EMPLOYEECONTEXT.

    Entity Framework Database First In ASP.NET Core
    Image No.5

    After right clicking on Models folder the above options list will appear.

    Entity Framework Database First In ASP.NET Core
    Image No.6

    Select Class in the items list and name it EmployeeContext.

    Entity Framework Database First In ASP.NET Core
    Image No.7

    Select Class in the items list and name it tblEmployee.

    Entity Framework Database First In ASP.NET Core
    Image No.8

    Select Class in the items list and name it tblSkill.

    Entity Framework Database First In ASP.NET Core
    Image No.9

    Select Class in the items list and name it EmployeeViewModel.

    Now double click on EmployeeViewModel.cs file declare some properties in the class. These will match with out database table columns.

    We are creating a View model for display and creating properties as per display/view required.

    Check our OUR TASK image.

    First add the following namespace in the class.

    using System.ComponentModel.DataAnnotations;

    Code of EmployeeViewModel.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Threading.Tasks;  
    5. using System.ComponentModel.DataAnnotations;  
    6.   
    7. namespace EmployeeList_EF_DbFirst.Models  
    8. {  
    9.     public class EmployeeViewModel  
    10. {  
    11. [Key]  
    12.         public int EmployeeID { getset; }  
    13.   
    14.         public string EmployeeName { getset; }  
    15.   
    16.         public string PhoneNumber { getset; }  
    17.   
    18.         public string Skill { getset; }  
    19.   
    20.         public int YearsExperience { getset; }  
    21.     }  
    22. }  

    Entity Framework Database First In ASP.NET Core
    Image No.10

    EmployeeViewModel properties.

    Now double click on tblEmployee.cs file to define its properties.

    First add following namespace to the class.

    using System.ComponentModel.DataAnnotations;

    Code of tblEmployee.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.ComponentModel.DataAnnotations;  
    4. using System.Linq;  
    5. using System.Threading.Tasks;  
    6.   
    7. namespace EmployeeList_EF_DbFirst.Models  
    8. {  
    9.     public class tblEmployee  
    10.    {  
    11.     [Key]  
    12.     public int EmployeeID { getset; }  
    13.   
    14.     [Display(Name = "Employee Name")]  
    15.     public string EmployeeName { getset; }  
    16.   
    17.     [Display(Name = "Contact Number")]  
    18.     public string PhoneNumber { getset; }  
    19.   
    20.     [Display(Name = "Your Skill")]  
    21.     public int SkillID { getset; }  
    22.   
    23.     [Display(Name = "Years of Experience")]  
    24.     public int YearsExperience { getset; }  
    25.    }  
    26. }  

    What is KEY and Display attribute?

    DATA-ANNOTATIONDESCRIPTION
    [KEY]Key attribute bind property with table as Primary-Key column in table.
    [Display(Name=””)]To display title desired title in view.

    Now double click on tblSkills.cs file to declare its properties.

    First add following namespace to the class.

    using System.ComponentModel.DataAnnotations;

    Code of tblSkill.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.ComponentModel.DataAnnotations;  
    4. using System.Linq;  
    5. using System.Threading.Tasks;  
    6.   
    7. namespace EmployeeList_EF_DbFirst.Models  
    8. {  
    9.     public class tblSkill  
    10.    {  
    11.     [Key]  
    12.     public int SkillID { getset; }  
    13.   
    14.     [Display(Name = "Type of Skill")]  
    15.     public string Title { getset; }  
    16.    }  
    17. }  

    Now double click on EmployeeContext.cs file and declare its properties.

    First add following namespace to the class.

    using Microsoft.EntityFrameworkCore;

    Code of EmployeeContext.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Threading.Tasks;  
    5. using Microsoft.EntityFrameworkCore;  
    6.   
    7. namespace EmployeeList_EF_DbFirst.Models  
    8. {  
    9.     public class EmployeeContext : DbContext  
    10.     {  
    11.         public EmployeeContext(DbContextOptions<EmployeeContext> options) : base(options)  
    12.         {  
    13.   
    14.         }  
    15.         public DbSet<tblSkill> tblSkills { getset; }  
    16.         public DbSet<tblEmployee> tblEmployees { getset; }  
    17.     }  
    18. }  

    To learn more about the DbSet class, visit here.

    Setting the Startup Class

    Double click on Startup.cs, which is located on the root of the folder.

    Add the following namespaces: 

    using Microsoft.EntityFrameworkCore;

    using EmployeeList_EF_DbFirst.Models;

    Above namespaces are used to access models from the Models folder.

    For more detail on Microsoft.EntityFrameworkCore namespace visit this here.

    Code of Startup.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Threading.Tasks;  
    5. using Microsoft.AspNetCore.Builder;  
    6. using Microsoft.AspNetCore.Hosting;  
    7. using Microsoft.Extensions.Configuration;  
    8. using Microsoft.Extensions.DependencyInjection;  
    9. using Microsoft.EntityFrameworkCore;  
    10. using EmployeeList_EF_DbFirst.Models;  
    11.   
    12. namespace EmployeeList_EF_DbFirst  
    13. {  
    14.     public class Startup  
    15.     {  
    16.         public Startup(IConfiguration configuration)  
    17.         {  
    18.             Configuration = configuration;  
    19.         }  
    20.   
    21.         public IConfiguration Configuration { get; }  
    22.   
    23.         // This method gets called by the runtime. Use this method to add services to the container.  
    24.         public void ConfigureServices(IServiceCollection services)  
    25.         {  
    26.             services.AddMvc();  
    27.               
    28.             //Fetching Connection string from APPSETTINGS.JSON  
    29.             var ConnectionString = Configuration.GetConnectionString("MbkDbConstr");  
    30.   
    31.             //Entity Framework  
    32.             services.AddDbContext<EmployeeContext>(options => options.UseSqlServer(ConnectionString));  
    33.         }  
    34.   
    35.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
    36.         public void Configure(IApplicationBuilder app, IHostingEnvironment env)  
    37.         {  
    38.             if (env.IsDevelopment())  
    39.             {  
    40.                 app.UseDeveloperExceptionPage();  
    41.                 app.UseBrowserLink();  
    42.             }  
    43.             else  
    44.             {  
    45.                 app.UseExceptionHandler("/Error");  
    46.             }  
    47.   
    48.             app.UseStaticFiles();  
    49.             app.UseMvcWithDefaultRoute();  
    50.             app.UseMvc(routes =>  
    51.             {  
    52.                 routes.MapRoute(  
    53.                     name: "default",  
    54.                     template: "{controller=Home}/{action=Index}/{id?}");  
    55.             });  
    56.         }  
    57.     }  
    58. }  

    STEP 5. Create a Controller

    Now right click on Controllers folder. Select Add -> New Item.

    Entity Framework Database First In ASP.NET Core

    Add a Controller HomeController.cs. Wait a little. 

    Entity Framework Database First In ASP.NET Core

    Default code of HomeController.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Threading.Tasks;  
    5. using Microsoft.AspNetCore.Mvc;  
    6.   
    7. namespace EmployeeList_EF_DbFirst.Controllers  
    8. {  
    9.     public class HomeController : Controller  
    10.     {  
    11.         public IActionResult Index()  
    12.         {  
    13.             return View();  
    14.         }  
    15.     }  
    16. }  

    Now, we'll modify HomeController class.

    Add the following namespace.

    using EmployeeList_EF_DbFirst.Models;

    Above namespace used to access the Models.

    Add Context things:

    private readonly MemberContext _dbcontext;

    Create a constructor to the HomeController class:

    1.  public HomeController(EmployeeContext dbContext)  
    2.  {  
    3.             _dbContext = dbContext;  
    4.  }  
    5.   
    6. Index Action method  
    7.  public IActionResult Index()  
    8.  {  
    9.             var _emplst = _dbContext.tblEmployees.  
    10.                             Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,  
    11.                             (e, s) => new EmployeeViewModel  
    12.                             { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,  
    13.                                 PhoneNumber = e.PhoneNumber, Skill = s.Title,  
    14.                                 YearsExperience = e.YearsExperience }).ToList();  
    15.             IList<EmployeeViewModel> emplst  = _emplst;  
    16.             return View(emplst);  
    17.   }  

    Full code of HomeController.cs

    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Threading.Tasks;  
    5. using Microsoft.AspNetCore.Mvc;  
    6. using EmployeeList_EF_DbFirst.Models;  
    7.   
    8. namespace EmployeeList_EF_DbFirst.Controllers  
    9. {  
    10.     public class HomeController : Controller  
    11.     {  
    12.   
    13.         private readonly EmployeeContext _dbContext;  
    14.   
    15.         public HomeController(EmployeeContext dbContext)  
    16.         {  
    17.             _dbContext = dbContext;  
    18.         }  
    19.   
    20.         public IActionResult Index()  
    21.         {  
    22.             var _emplst = _dbContext.tblEmployees.  
    23.                             Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,  
    24.                             (e, s) => new EmployeeViewModel  
    25.                             { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,  
    26.                                 PhoneNumber = e.PhoneNumber, Skill = s.Title,  
    27.                                 YearsExperience = e.YearsExperience }).ToList();  
    28.             IList<EmployeeViewModel> emplst  = _emplst;  
    29.             return View(emplst);  
    30.         }  
    31.     }  
    32. }  

    Before we proceed further, let's build the project. Make sure there are no errors.

    Entity Framework Database First In ASP.NET Core
    Now, let's add a View. 
     
    Now right click on the Index method in the above class to add a view.
     
    Entity Framework Database First In ASP.NET Core

    Now right click again on the Index method method to add a view.

    Entity Framework Database First In ASP.NET Core
     
    Entity Framework Database First In ASP.NET Core

    Fill in the above form as per your Model class and Data Context class. 

    Entity Framework Database First In ASP.NET Core

    Above NuGet message will appear on the screen.

    Code of Index.cshtml

    1. @model IEnumerable<EmployeeList_EF_DbFirst.Models.EmployeeViewModel>  
    2.   
    3. @{  
    4.     ViewData["Title"] = "Index";  
    5. }  
    6.   
    7. <h2>Index</h2>  
    8.   
    9. <p>  
    10.     <a asp-action="Create">Create New</a>  
    11. </p>  
    12. <table class="table">  
    13.     <thead>  
    14.         <tr>  
    15.                 <th>  
    16.                     @Html.DisplayNameFor(model => model.EmployeeName)  
    17.                 </th>  
    18.                 <th>  
    19.                     @Html.DisplayNameFor(model => model.PhoneNumber)  
    20.                 </th>  
    21.                 <th>  
    22.                     @Html.DisplayNameFor(model => model.Skill)  
    23.                 </th>  
    24.                 <th>  
    25.                     @Html.DisplayNameFor(model => model.YearsExperience)  
    26.                 </th>  
    27.             <th></th>  
    28.         </tr>  
    29.     </thead>  
    30.     <tbody>  
    31. @foreach (var item in Model) {  
    32.         <tr>  
    33.             <td>  
    34.                 @Html.DisplayFor(modelItem => item.EmployeeName)  
    35.             </td>  
    36.             <td>  
    37.                 @Html.DisplayFor(modelItem => item.PhoneNumber)  
    38.             </td>  
    39.             <td>  
    40.                 @Html.DisplayFor(modelItem => item.Skill)  
    41.             </td>  
    42.             <td>  
    43.                 @Html.DisplayFor(modelItem => item.YearsExperience)  
    44.             </td>  
    45.             <td>  
    46.                 <a asp-action="Edit" asp-route-id="@item.EmployeeID">Edit</a> |  
    47.                 <a asp-action="Details" asp-route-id="@item.EmployeeID">Details</a> |  
    48.                 <a asp-action="Delete" asp-route-id="@item.EmployeeID">Delete</a>  
    49.             </td>  
    50.         </tr>  
    51. }  
    52.     </tbody>  
    53. </table>  

    Now remove the Pages folder from the project.

    STEP 6. Build and Run
     
    Now, press F5 to build and run the project. The output should look like this:
     
    Entity Framework Database First In ASP.NET Core
     
    That's all. 

    NEXT ARTICLE

    You will learn how to bind a Dropdown List in an Asp.Net Core with Entity Framework Core.

    Thank you very much.

    Happy Coding!

      Tags

      Post a Comment

      0Comments

      POST Answer of Questions and ASK to Doubt

      Post a Comment (0)