Perform Master Detail CRUD operations in ASP.NET Core (Part 1)

Master-detail pages are quite common in many web applications. There are various approaches to building master-detail pages including server side, client side, and hybrid. There are also many third-party controls and plugins that can be used to accomplish this task. It would be interesting for beginners to learn and understand how master-detail pages work and how they can be developed in ASP.NET Core. To that end this multipart article explains how master-detail pages can be developed using purely server side code without relying on any third-party component or library.

Before we go into the technical details of building master-detail pages, I will show you the sample application that we will build in this article series.

We will be using two SQL Server tables namely Teams and TeamMembers. Teams is the master table and contains information about a team such as TeamID, Name, and Description. A Team can have one or more members. The members are stored in TeamMembers table. Thus TeamMembers is a detail table and contains TeamMemberID, Name, and Email.

Take a look at the following image that displays the main page of the application:

As you can see, we display Teams records in a table. You can add a new team by clicking Insert Team button. You can modify existing team information using Manager Teams button. And you can manage team members using Manage Members button.

The following image shows the master user interface when you click on the Manage Team button. 

When you select a Team for editing purpose, that record is highlighted in the master grid and the Team is displayed for editing below the master grid. You can click on Edit to modify the Team details. You can click on Delete to delete that team. And you can hit Cancel to cancel the editing and selection of the team.

When you click on Insert Team button a similar interface is shown that allows you to add a new Team. 

When you click on the Manage Members button for a particular Team from the master grid, a list of team members is shown in the detail grid below the master grid. 

The detail grid also allows you edit a member when you select a member by clicking Manage Member button.

The Insert Member button works similar to Insert Team button but adds a new team member to the selected team.

Now that you have some idea about the application, let's see the structure of Teams and TeamMembers tables. I have added these tables to the Northwind database using Visual Studio Server Explorer but you can create a new database if you so wish.

The Teams table is shown below:

The Teams table consists of three columns - TeamID, Name, and Description. TeamID is an identity column and acts as the primary key of the table.

The TeamMembers table is shown next:

The TeamMembers table contains four columns - TeamMemberID, TeamID, Name, and Email. The TeamMemberID is an identity column and acts as the primary key for the table. TeamID column is a foreign key column that references to the TeamID from the Teams table.

To add the foreign key constraint, open TeamMembers table definitation and right click on the Foreign Keys node.

Click on the Add New Foreign Key menu option so that the required T-SQL clause is added to the T-SQL pane below the table designer. Modify the T-SQL script to resemble the following:

After adding the foreign key constraint as discussed above, close all the open table designers exit the Server Explorer.

Now that our database tables are ready we can go ahead and create an Entity Framework Core model to perform data access.

Begin by creating a new ASP.NET Core MVC project and add NuGet package for Microsoft.EntityFrameworkCore.SqlServer.

Then add a folder called DataAccess under the project root and add three class files to it namely Team.cs, TeamMember.cs, and AppDbContext.cs.

Open TeamMember.cs file and add entity class as shown below:

public class TeamMember
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int TeamMemberID { get; set; }
    public int TeamID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

The TeamMember entity class is quite straightforward and contains the necessary properties matching the TeamMembers table schema.

Next, open Team.cs file and add Team entity class to it as shown below:

public class Team
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int TeamID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    [ForeignKey("TeamID")]
    public List<TeamMember> Members { get; set; }
}

Notice the Members navigation property that will hold the TeamMember entities for a Team under consideration. We will load the Members property explicitly through code as discussed in the next part of this article series.

Finally, open the AppDbContext.cs file and add the AppDbContext class as shown below:

public class AppDbContext:DbContext
{
    public DbSet<Team> Teams { get; set; }
    public DbSet<TeamMember> TeamMembers { get; set; }

    public AppDbContext(DbContextOptions
<AppDbContext> options):base(options)
    {

    }
}

The AppDbContext class houses two DbSet properties namely Teams and TeamMembers.

You can register the AppDbContext with the DI container in the ConfigureServices() method of the application. To do so, open the Startup class and add the following code:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddDbContext<AppDbContext>(o => 
o.UseSqlServer(Configuration.GetConnectionString("AppDb")));
}

Here, we read the database connection string stored in the appsettings.json file and supply it to the UseSqlServer() method.

The connection string stored in the appsettings.json looks like this:

"ConnectionStrings": {
  "AppDb": "data source=.;initial 
catalog=Northwind;integrated security=true" 
}

Make sure to change the database connection string as per your development setup. 

We will discuss the overall web application structure and begin developing the controllers in the next part of this article series. 

That's it for now! Keep coding!!


Bipin Joshi is an independent software consultant, trainer, author, and meditation teacher. He has been programming, meditating, and teaching for 25+ years. He conducts instructor-led online training courses in ASP.NET family of technologies for individuals and small groups. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Ajapa Yoga to interested individuals. To know more about him click here.

Get connected : Facebook  Twitter  LinkedIn  YouTube

Posted On : 01 March 2021


Tags : ASP.NET ASP.NET Core Data Access MVC .NET Framework C# Visual Studio