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.
- CREATE TABLE [dbo].[tblEmployees](
- [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
- [EmployeeName] [varchar](50) NULL,
- [PhoneNumber] [varchar](50) NULL,
- [SkillID] [int] null,
- [YearsExperience] [int] null,
- PRIMARY KEY CLUSTERED
- (
- [EmployeeID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- CREATE TABLE [dbo].[tblSkills](
- [SkillID] [int] IDENTITY(1,1) NOT NULL,
- [Title] [varchar](50) NULL,
- PRIMARY KEY CLUSTERED
- (
- [SkillID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
Now, let's add some seed data to our database tables. The following scripts insert data into our database tables.
- insert into tblSkills
- (Title) values('Visual Foxpro')
- insert into tblSkills
- (Title) values('C#')
- insert into tblSkills
- (Title) values('VB.NET')
- insert into tblSkills
- (Title) values('Delphi')
- insert into tblSkills
- (Title) values('Java')
- insert into tblSkills
- (Title) values('Power Builder')
- insert into tblSkills
- (Title) values('COBOL')
- insert into tblSkills
- (Title) values('Python')
- Insert Into tblEmployees
- (EmployeeName,PhoneNumber,SkillID,YearsExperience)
- values ('Suhana Kalla','9869569634',2,'11')
- Insert Into tblEmployees
- (EmployeeName,PhoneNumber,SkillID,YearsExperience)
- values ('Ashish Kalla','9869166077',8,'14')
- Insert Into tblEmployees
- (EmployeeName,PhoneNumber,SkillID,YearsExperience)
- values ('Manoj Kalla','9869569634',1,'24')
- Insert Into tblEmployees
- (EmployeeName,PhoneNumber,SkillID,YearsExperience)
- values ('Nirupama Kalla','9969359746',6,'20')
- Insert Into tblEmployees
- (EmployeeName,PhoneNumber,SkillID,YearsExperience)
- values ('Rajesh Bohra','9869166012',7,'28')
- Insert Into tblEmployees
- (EmployeeName,PhoneNumber,SkillID,YearsExperience)
- values ('Murli Vyas','9261166012',5,'18')
STEP 2. Create a Web Application
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.
Image 1
On the next screen, make sure you've .Net Core and Asp.Net Core 2.0 and Web Application selected.
Press the OK button to create the project.
STEP 3. Project structure view, folders creation
Default view of project looks like the following:
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.
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
- {
- "Logging": {
- "IncludeScopes": false,
- "LogLevel": {
- "Default": "Warning"
- }
- },
- "ConnectionStrings": {
- "MbkDbConstr": "Data Source=(localdb)\\MBK;Database=MbkTest;"
- }
- }
Creating Models
We are going to create following models:
CLASS NAME | DESCRIPTION |
EmployeeContext.cs | To set entity framework. |
tblEmployees.cs | tblEmployees Model. |
tblSkills.cs | tblSkills Model. |
EmployeeViewModel.cs | View Model for employee listing. |
Now right click on Models folder to add a class called EMPLOYEECONTEXT.
After right clicking on Models folder the above options list will appear.
Image No.6
Select Class in the items list and name it EmployeeContext.
Image No.7
Select Class in the items list and name it tblEmployee.
Image No.8
Select Class in the items list and name it tblSkill.
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
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Threading.Tasks;
- using System.ComponentModel.DataAnnotations;
- namespace EmployeeList_EF_DbFirst.Models
- {
- public class EmployeeViewModel
- {
- [Key]
- public int EmployeeID { get; set; }
- public string EmployeeName { get; set; }
- public string PhoneNumber { get; set; }
- public string Skill { get; set; }
- public int YearsExperience { get; set; }
- }
- }
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
- using System;
- using System.Collections.Generic;
- using System.ComponentModel.DataAnnotations;
- using System.Linq;
- using System.Threading.Tasks;
- namespace EmployeeList_EF_DbFirst.Models
- {
- public class tblEmployee
- {
- [Key]
- public int EmployeeID { get; set; }
- [Display(Name = "Employee Name")]
- public string EmployeeName { get; set; }
- [Display(Name = "Contact Number")]
- public string PhoneNumber { get; set; }
- [Display(Name = "Your Skill")]
- public int SkillID { get; set; }
- [Display(Name = "Years of Experience")]
- public int YearsExperience { get; set; }
- }
- }
What is KEY and Display attribute?
DATA-ANNOTATION | DESCRIPTION |
[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
- using System;
- using System.Collections.Generic;
- using System.ComponentModel.DataAnnotations;
- using System.Linq;
- using System.Threading.Tasks;
- namespace EmployeeList_EF_DbFirst.Models
- {
- public class tblSkill
- {
- [Key]
- public int SkillID { get; set; }
- [Display(Name = "Type of Skill")]
- public string Title { get; set; }
- }
- }
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
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Threading.Tasks;
- using Microsoft.EntityFrameworkCore;
- namespace EmployeeList_EF_DbFirst.Models
- {
- public class EmployeeContext : DbContext
- {
- public EmployeeContext(DbContextOptions<EmployeeContext> options) : base(options)
- {
- }
- public DbSet<tblSkill> tblSkills { get; set; }
- public DbSet<tblEmployee> tblEmployees { get; set; }
- }
- }
To learn more about the DbSet class, visit here.
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
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Threading.Tasks;
- using Microsoft.AspNetCore.Builder;
- using Microsoft.AspNetCore.Hosting;
- using Microsoft.Extensions.Configuration;
- using Microsoft.Extensions.DependencyInjection;
- using Microsoft.EntityFrameworkCore;
- using EmployeeList_EF_DbFirst.Models;
- namespace EmployeeList_EF_DbFirst
- {
- public class Startup
- {
- public Startup(IConfiguration configuration)
- {
- Configuration = configuration;
- }
- public IConfiguration Configuration { get; }
- // This method gets called by the runtime. Use this method to add services to the container.
- public void ConfigureServices(IServiceCollection services)
- {
- services.AddMvc();
- //Fetching Connection string from APPSETTINGS.JSON
- var ConnectionString = Configuration.GetConnectionString("MbkDbConstr");
- //Entity Framework
- services.AddDbContext<EmployeeContext>(options => options.UseSqlServer(ConnectionString));
- }
- // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
- public void Configure(IApplicationBuilder app, IHostingEnvironment env)
- {
- if (env.IsDevelopment())
- {
- app.UseDeveloperExceptionPage();
- app.UseBrowserLink();
- }
- else
- {
- app.UseExceptionHandler("/Error");
- }
- app.UseStaticFiles();
- app.UseMvcWithDefaultRoute();
- app.UseMvc(routes =>
- {
- routes.MapRoute(
- name: "default",
- template: "{controller=Home}/{action=Index}/{id?}");
- });
- }
- }
- }
STEP 5. Create a Controller
Now right click on Controllers folder. Select Add -> New Item.
Add a Controller HomeController.cs. Wait a little.
Default code of HomeController.cs
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Threading.Tasks;
- using Microsoft.AspNetCore.Mvc;
- namespace EmployeeList_EF_DbFirst.Controllers
- {
- public class HomeController : Controller
- {
- public IActionResult Index()
- {
- return View();
- }
- }
- }
Now, we'll modify HomeController class.
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:
- public HomeController(EmployeeContext dbContext)
- {
- _dbContext = dbContext;
- }
- Index Action method
- public IActionResult Index()
- {
- var _emplst = _dbContext.tblEmployees.
- Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,
- (e, s) => new EmployeeViewModel
- { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,
- PhoneNumber = e.PhoneNumber, Skill = s.Title,
- YearsExperience = e.YearsExperience }).ToList();
- IList<EmployeeViewModel> emplst = _emplst;
- return View(emplst);
- }
Full code of HomeController.cs
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Threading.Tasks;
- using Microsoft.AspNetCore.Mvc;
- using EmployeeList_EF_DbFirst.Models;
- namespace EmployeeList_EF_DbFirst.Controllers
- {
- public class HomeController : Controller
- {
- private readonly EmployeeContext _dbContext;
- public HomeController(EmployeeContext dbContext)
- {
- _dbContext = dbContext;
- }
- public IActionResult Index()
- {
- var _emplst = _dbContext.tblEmployees.
- Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,
- (e, s) => new EmployeeViewModel
- { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,
- PhoneNumber = e.PhoneNumber, Skill = s.Title,
- YearsExperience = e.YearsExperience }).ToList();
- IList<EmployeeViewModel> emplst = _emplst;
- return View(emplst);
- }
- }
- }
Before we proceed further, let's build the project. Make sure there are no errors.
Now right click again on the Index method method to add a view.
Fill in the above form as per your Model class and Data Context class.
Above NuGet message will appear on the screen.
Code of Index.cshtml
- @model IEnumerable<EmployeeList_EF_DbFirst.Models.EmployeeViewModel>
- @{
- ViewData["Title"] = "Index";
- }
- <h2>Index</h2>
- <p>
- <a asp-action="Create">Create New</a>
- </p>
- <table class="table">
- <thead>
- <tr>
- <th>
- @Html.DisplayNameFor(model => model.EmployeeName)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.PhoneNumber)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.Skill)
- </th>
- <th>
- @Html.DisplayNameFor(model => model.YearsExperience)
- </th>
- <th></th>
- </tr>
- </thead>
- <tbody>
- @foreach (var item in Model) {
- <tr>
- <td>
- @Html.DisplayFor(modelItem => item.EmployeeName)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.PhoneNumber)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.Skill)
- </td>
- <td>
- @Html.DisplayFor(modelItem => item.YearsExperience)
- </td>
- <td>
- <a asp-action="Edit" asp-route-id="@item.EmployeeID">Edit</a> |
- <a asp-action="Details" asp-route-id="@item.EmployeeID">Details</a> |
- <a asp-action="Delete" asp-route-id="@item.EmployeeID">Delete</a>
- </td>
- </tr>
- }
- </tbody>
- </table>
Now remove the Pages folder from the project.
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!
POST Answer of Questions and ASK to Doubt