ASP.NET CORE Database Application

 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] [int] null,  
6.     [YearsExperience] [int] null,  
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 = ON) ON [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 = ON) ON [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')
4. insert into tblSkills
5. (Title) values('C#')
7. insert into tblSkills
8. (Title) values('VB.NET')
10. insert into tblSkills
11. (Title) values('Delphi')
13. insert into tblSkills
14. (Title) values('Java')
16. insert into tblSkills
17. (Title) values('Power Builder')
19. insert into tblSkills
20. (Title) values('COBOL')
22. insert into tblSkills
23. (Title) values('Python')

1. Insert Into tblEmployees
2. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
3. values ('Suhana Kalla','9869569634',2,'11')
5. Insert Into tblEmployees
6. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
7. values ('Ashish Kalla','9869166077',8,'14')
9. Insert Into tblEmployees
10. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
11. values ('Manoj Kalla','9869569634',1,'24')
13. Insert Into tblEmployees
14. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
15. values ('Nirupama Kalla','9969359746',6,'20')
17. Insert Into tblEmployees
18. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
19. values ('Rajesh Bohra','9869166012',7,'28')
21. Insert Into tblEmployees
22. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
23. values ('Murli Vyas','9261166012',5,'18')

The data looks like the following: 

 

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.  

Image 1

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

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:

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

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 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.

Image No.5

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

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 { get; set; }  
13.   
14.         public string EmployeeName { get; set; }  
15.   
16.         public string PhoneNumber { get; set; }  
17.   
18.         public string Skill { get; set; }  
19.   
20.         public int YearsExperience { get; set; }  
21.     }  
22. }  

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 { get; set; }  
13.   
14.     [Display(Name = "Employee Name")]  
15.     public string EmployeeName { get; set; }  
16.   
17.     [Display(Name = "Contact Number")]  
18.     public string PhoneNumber { get; set; }  
19.   
20.     [Display(Name = "Your Skill")]  
21.     public int SkillID { get; set; }  
22.   
23.     [Display(Name = "Years of Experience")]  
24.     public int YearsExperience { get; set; }  
25.    }  
26. }  

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

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 { get; set; }  
13.   
14.     [Display(Name = "Type of Skill")]  
15.     public string Title { get; set; }  
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 { get; set; }  
16.         public DbSet<tblEmployee> tblEmployees { get; set; }  
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.

Add a Controller HomeController.cs. Wait a little. 

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.

Now, let's add a View. 

 

Now right click on the Index method in the above class to add a view.

 

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

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:

 

 

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!