Blazor Succinctly^®
by Michael Washington

CHAPTER 6

Creating a Data Layer

We will now add additional tables to the database to support the custom code we plan to write. To allow our code to communicate with these tables, we require a data layer.

This data layer will also allow us to organize and reuse code efficiently.

Create the database tables

The first step is to add the database tables we will need.

In the SQL Server Object Explorer, right-click on the database and select New Query.

New Query

Figure 39: New Query

Enter the following script.

Code Listing 21: SQL Script

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[HelpDeskTicketDetails](

[Id] [int] IDENTITY(1,1) NOT NULL,

[HelpDeskTicketId] [int] NOT NULL,

[TicketDetailDate] [datetime] NOT NULL,

[TicketDescription] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_HelpDeskTicketDetails] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[HelpDeskTickets](

[Id] [int] IDENTITY(1,1) NOT NULL,

[TicketStatus] [nvarchar](50) NOT NULL,

[TicketDate] [datetime] NOT NULL,

[TicketDescription] [nvarchar](max) NOT NULL,

[TicketRequesterEmail] [nvarchar](500) NOT NULL,

[TicketGUID] [nvarchar](500) NOT NULL,

CONSTRAINT [PK_HelpDeskTickets] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[HelpDeskTicketDetails]

WITH CHECK

ADD CONSTRAINT [FK_HelpDeskTicketDetails_HelpDeskTickets]

FOREIGN KEY([HelpDeskTicketId])

REFERENCES [dbo].[HelpDeskTickets] ([Id])

ON DELETE CASCADE

ALTER TABLE [dbo].[HelpDeskTicketDetails]

CHECK CONSTRAINT [FK_HelpDeskTicketDetails_HelpDeskTickets]

Click the execute icon.

Click Execute

Figure 40: Click Execute

Close the SQLQuery1.sql window and refresh the view of the tables in the database.

Refresh Database

Figure 41: Refresh Database

You will see that the new tables have been added.

Database Diagram

Figure 42: Database Diagram

The preceding diagram shows the relationship between the newly added tables. A HelpDeskTickets record is created first. As the issue is processed, multiple associated HelpDeskTicketDetails records are added.

Create the DataContext (using EF Core tools)

We will now create the DataContext code that will allow the data service, created in the following steps, to communicate with the database tables we just added.

EF Core Power Tools

Figure 43: EF Core Power Tools

Install EF Core Power Tools from the following link: https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools.

Figure 44: EF Core Power Tools

Right-click on the project node in the Solution Explorer and select EF Core Power Tools, then Reverse Engineer.

Create Connection

Figure 45: Create Connection

Click Add to create a connection to the database if one does not already exist in the drop-down.

After you do that, or if the connection to the database is already in the drop-down, select the database connection in the drop-down and click OK.

Select Tables

Figure 46: Select Tables

Select the HelpDeskTicketDetails table and the HelpDeskTickets table and click OK.

Configure DataContext

Figure 47: Configure DataContext

Set the following values:

Context name: SyncfusionHelpDeskContext
Namespace: SyncfusionHelpDesk
EntityTypes path: Data

Click OK.

DataContext Created

Figure 48: DataContext Created

In the Solution Explorer, you will see the DataContext has been created.

Set the database connection

The DataContext code needs the connection to the database to be set.

Open the Startup.cs file and add the following code to the ConfigureServices section.

Code Listing 22: Database Connection

// To access HelpDesk tables.

services.AddDbContext<SyncfusionHelpDeskContext>(options =>

options.UseSqlServer(

Configuration.GetConnectionString("DefaultConnection")));

Save the file.

Select Build, and then Rebuild Solution.

The application should build without any errors.

Create the SyncfusionHelpDeskService

We will now create the service that will provide all the remaining data access methods we will need for the application.

In the Data folder, add a new file called SyncfusionHelpDeskService.cs with the following code.

Code Listing 23: SyncfusionHelpDeskService

using Microsoft.EntityFrameworkCore;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

namespace SyncfusionHelpDesk.Data

{

public class SyncfusionHelpDeskService

{

private readonly SyncfusionHelpDeskContext _context;

public SyncfusionHelpDeskService(

SyncfusionHelpDeskContext context)

{

_context = context;

}

public IQueryable<HelpDeskTickets>

GetHelpDeskTickets()

{

// Return all HelpDesk tickets as IQueryable.

// SfGrid will use this to only pull records

// for the page that it is currently displaying.

// Note: AsNoTracking() is used because it is

// quicker to execute, and we do not need

// Entity Framework change tracking at this point.

return _context.HelpDeskTickets.AsNoTracking();

}

public async Task<HelpDeskTickets>

GetHelpDeskTicketAsync(string HelpDeskTicketGuid)

{

// Get the existing record.

var ExistingTicket = await _context.HelpDeskTickets

.Include(x => x.HelpDeskTicketDetails)

.Where(x => x.TicketGuid == HelpDeskTicketGuid)

.AsNoTracking()

.FirstOrDefaultAsync();

return ExistingTicket;

}

public Task<HelpDeskTickets>

CreateTicketAsync(HelpDeskTickets newHelpDeskTickets)

{

try

{

// Add a new help desk ticket.

_context.HelpDeskTickets.Add(newHelpDeskTickets);

_context.SaveChanges();

return Task.FromResult(newHelpDeskTickets);

}

catch (Exception ex)

{

DetachAllEntities();

throw;

}

public Task<bool>

UpdateTicketAsync(

HelpDeskTickets UpdatedHelpDeskTickets)

{

try

{

// Get the existing record.

var ExistingTicket =

_context.HelpDeskTickets

.Where(x => x.Id == UpdatedHelpDeskTickets.Id)

.FirstOrDefault();

if (ExistingTicket != null)

{

ExistingTicket.TicketDate =

UpdatedHelpDeskTickets.TicketDate;

ExistingTicket.TicketDescription =

UpdatedHelpDeskTickets.TicketDescription;

ExistingTicket.TicketGuid =

UpdatedHelpDeskTickets.TicketGuid;

ExistingTicket.TicketRequesterEmail =

UpdatedHelpDeskTickets.TicketRequesterEmail;

ExistingTicket.TicketStatus =

UpdatedHelpDeskTickets.TicketStatus;

// Insert any new TicketDetails.

if (UpdatedHelpDeskTickets.HelpDeskTicketDetails != null)

{

foreach (var item in

UpdatedHelpDeskTickets.HelpDeskTicketDetails)

{

if (item.Id == 0)

{

// Create new HelpDeskTicketDetails record.

HelpDeskTicketDetails newHelpDeskTicketDetails =

new HelpDeskTicketDetails();

newHelpDeskTicketDetails.HelpDeskTicketId =

UpdatedHelpDeskTickets.Id;

newHelpDeskTicketDetails.TicketDetailDate =

DateTime.Now;

newHelpDeskTicketDetails.TicketDescription =

item.TicketDescription;

_context.HelpDeskTicketDetails

.Add(newHelpDeskTicketDetails);

}

_context.SaveChanges();

}

else

{

return Task.FromResult(false);

}

return Task.FromResult(true);

}

catch (Exception ex)

{

DetachAllEntities();

throw ex;

}

public Task<bool>

DeleteHelpDeskTicketsAsync(

HelpDeskTickets DeleteHelpDeskTickets)

{

// Get the existing record.

var ExistingTicket =

_context.HelpDeskTickets

.Include(x => x.HelpDeskTicketDetails)

.Where(x => x.Id == DeleteHelpDeskTickets.Id)

.FirstOrDefault();

if (ExistingTicket != null)

{

// Delete the help desk ticket.

_context.HelpDeskTickets.Remove(ExistingTicket);

_context.SaveChanges();

}

else

{

return Task.FromResult(false);

}

return Task.FromResult(true);

}

// Utility

#region public void DetachAllEntities()

public void DetachAllEntities()

{

// When we have an error, we need

// to remove EF Core change tracking.

var changedEntriesCopy = _context.ChangeTracker.Entries()

.Where(e => e.State == EntityState.Added ||

e.State == EntityState.Modified ||

e.State == EntityState.Deleted)

.ToList();

foreach (var entry in changedEntriesCopy)

entry.State = EntityState.Detached;

}

#endregion

}

Register the SyncfusionHelpDeskService

Finally, we need to register this service so that we can make it available to our code pages.

Open the Startup.cs file and add the following code to the ConfigureServices section.

Code Listing 24: SyncfusionHelpDeskService

// To access SyncfusionHelpDeskService

services.AddScoped<SyncfusionHelpDeskService>();

We will later inject this service into our code pages using OwningComponentBase to scope the service to a single webpage.

Build modern Blazor apps

using Syncfusion® Blazor component suite

80+ high performance Blazor components.
Lightweight and user friendly.
Stunning Built-in themes with customization.

Get Your Free Trial Now

Creating a Data Layer

Create the database tables

Create the DataContext (using EF Core tools)

Set the database connection

Create the SyncfusionHelpDeskService

Register the SyncfusionHelpDeskService

DISCLAIMER: Web reader is currently in beta. Please report any issues through our support system. PDF and Kindle format files are also available for download.