Index Page - Rooms Grid

Introduction

We're going to use the Index page to display and maintain all data apart from Projects.  It will consist of a drop-down list for projects and three cascading grids, one grid each for Room, Wall and Deduction.  Ideally I would have incorporated the Projects into a grid on this page, but space did not permit this, so had to settle for a separate page to maintain Projects and just have a dropdown list on this page.

YouTube Video

Project Drop-Down list

We'll start by making changes to the existing Index page by deleting existing code and adding a drop-down list to allow the user to display and select a Project.

Replace the code with the following:

@page "/"
@using BlazorWallAreaCalculator.Data
@inject IProjectService ProjectService
@inject SfDialogService DialogService

<PageTitle>Wall Area Calculator</PageTitle>

<div class="control_wrapper">
    <SfDropDownList TItem="Project"
                    TValue="string"
                    DataSource="@projects"
                    Placeholder="Select a Project"
                    PopupHeight="200px"
                    PopupWidth="250px">
        <DropDownListFieldSettings Text="ProjectName" Value="ProjectID"></DropDownListFieldSettings>
    </SfDropDownList>
</div>
<hr />

<style>
    .control_wrapper {
        width: 250px;
    }
</style>

@code{
    public IEnumerable<Project>? projects;

    protected override async Task OnInitializedAsync()
    {
        //Populate the list of projects objects from the Project table.
        projects = await ProjectService.ProjectReadAll();
    }
}

In the above we basically add the using statements, provide a PageTitle (which is displayed in the browser's tab heading), a Syncfusion DropDownList in the CSHTML section and the code necessary to populate the dropdown list with project names when the page opens. 

The style section is added to restrict the width of the dropdown list to 250px. Setting the dropdown list's PopupWidth property to 250px only controls the width of the dropdown part, not the width of the initial box.

Services & Interfaces

As we have three grids on the page a lot of the code is likely to be repetitious, and we can use this to our advantage, copying blocks of code and amending to suit the situation. But before we start on the design of the page we know that we will need services and interfaces for the CRUD operations for Room, Wall and Deduction.  We can therefore take the services and interface for Project copy them and then use Find & Replace to substitute 'Project' with 'Room', 'Wall' and 'Deduction'.  It's not quite that simple though, and we will need to make amendments for the different columns in each of the models.  It is also likely that we will need additional services, but we will deal with those as they arise. 

  • Select the Data folder
  • Copy ProjectService and Paste, re-naming it 'RoomService'
  • Open RoomService and Find & Replace 'Project' with 'Room', using 'Match Case' and 'Current Document'
  • Repeat, Find & Replace 'project' with 'room', using 'Match Case' and 'Current Document'

We now need to deal with the fact that the Room.cs model has different fields (columns), i.e. Rooms does not have 'UserEmail' but has ProjectID.  Manually go through the file removing UserEmail, adding in ProjectID.  (Be aware that ProjectID is an int32.)  The RoomUpdate method does not need to include ProjectID as we won't be changing the ProjectID of a Room.

Repeat the routine for IRoomService (copy and paste IProjectService and replace Project with Room).

Repeat the whole process for Wall and Deduction.  (I'm afraid this is a bit tedious! Alternatively, just copy the following!)  Doubtless they will need further amendment!

RoomService

using Dapper;
using System.Data.SQLite;
using System.Data;

namespace BlazorWallAreaCalculator.Data
{
    public class RoomService : IRoomService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public RoomService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";
        private Room? room;

        // RoomCreate
        public async Task<bool> RoomCreate(Room room)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@RoomName", room.RoomName, DbType.String);
            parameters.Add("@ProjectID", room.ProjectID, DbType.Int32);

            sqlCommand = "Insert into Room (RoomName, ProjectID) ";
            sqlCommand += "values(@RoomName, @ProjectID)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }

        // RoomRead
        public async Task<IEnumerable<Room>> RoomReadAll()
        {
            IEnumerable<Room> rooms;

            sqlCommand = "Select * from Room ORDER BY RoomName";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                rooms = await conn.QueryAsync<Room>(sqlCommand);
            }
            return rooms;
        }

        public async Task<int> CountRoomsByName(string RoomName)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@RoomName", RoomName, DbType.String);

            sqlCommand = "Select Count(*) from Room ";
            sqlCommand += "where Upper(RoomName) = Upper(@RoomName)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var countRoom = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
                return countRoom;
            }
        }

        public async Task<int> CountRoomsByNameAndId(string RoomName, int RoomID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@RoomName", RoomName, DbType.String);
            parameters.Add("@RoomID", RoomID, DbType.Int32);

            sqlCommand = "Select Count(*) from Room ";
            sqlCommand += "where Upper(RoomName) = Upper(@RoomName) ";
            sqlCommand += "and RoomID <> @RoomID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var countRoom = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
                return countRoom;
            }
        }


        //RoomUpdate
        public async Task<bool> RoomUpdate(Room room)
        {
            var parameters = new DynamicParameters();

            parameters.Add("RoomID", room.RoomID, DbType.Int32);
            parameters.Add("RoomName", room.RoomName, DbType.String);

            sqlCommand = "Update Room ";
            sqlCommand += "SET RoomName = @RoomName ";
            sqlCommand += "WHERE RoomID  = @RoomID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }


        //RoomDelete
        public async Task<bool> RoomDelete(Int32 RoomID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@RoomID", RoomID, DbType.Int32);

            //PRAGMA is specific to SQLite and this command is required for DELETE CASCADE to work
            sqlCommand = "PRAGMA foreign_keys = ON;";
            sqlCommand += "Delete from Room ";
            sqlCommand += "WHERE RoomID  = @RoomID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }

    }
}

IRoomService

namespace BlazorWallAreaCalculator.Data
{
    public interface IRoomService
    {
        Task<bool> RoomCreate(Room room);
        Task<IEnumerable<Room>> RoomReadAll();
        Task<int> CountRoomsByName(string RoomName);
        Task<int> CountRoomsByNameAndId(string RoomName, int RoomID);
        Task<bool> RoomUpdate(Room room);
        Task<bool> RoomDelete(Int32 RoomID);
    }
}

WallService

using Dapper;
using System.Data.SQLite;
using System.Data;

namespace BlazorWallAreaCalculator.Data
{
    public class WallService : IWallService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public WallService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";
        private Wall? wall;

        // WallCreate
        public async Task<bool> WallCreate(Wall wall)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@RoomID", wall.RoomID, DbType.Int32);
            parameters.Add("@WallName", wall.WallName, DbType.String);
            parameters.Add("@WallTypeID", wall.WallTypeID, DbType.Int32);
            parameters.Add("@WallTypeName", wall.WallTypeName, DbType.String);
            parameters.Add("@WallLengthMax", wall.WallLengthMax, DbType.Int32);
            parameters.Add("@WallLengthMin", wall.WallLengthMin, DbType.Int32);
            parameters.Add("@WallHeightMax", wall.WallHeightMax, DbType.Int32);
            parameters.Add("@WallHeightMin", wall.WallHeightMin, DbType.Int32);
            parameters.Add("@WallSqM", wall.WallSqM, DbType.Decimal);

            sqlCommand = "Insert into Wall (RoomID, WallName, WallTypeID, WallTypeName, " +
                "WallLengthMax, WallLengthMin, WallHeightMax, WallHeightMin, WallSqM) ";
            sqlCommand += "values(@RoomID, @WallName, @WallTypeID, @WallTypeName, " +
                "@WallLengthMax, @WallLengthMin, @WallHeightMax, @WallHeightMin, @WallSqM) ";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }


        // WallRead
        public async Task<IEnumerable<Wall>> WallReadAll()
        {
            IEnumerable<Wall> walls;

            sqlCommand = "Select * from Wall ORDER BY WallName";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                walls = await conn.QueryAsync<Wall>(sqlCommand);
            }
            return walls;
        }

        public async Task<int> CountWallsByName(string WallName)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@WallName", WallName, DbType.String);

            sqlCommand = "Select Count(*) from Wall ";
            sqlCommand += "where Upper(WallName) = Upper(@WallName)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var countWall = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
                return countWall;
            }
        }

        public async Task<int> CountWallsByNameAndId(string WallName, int WallID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@WallName", WallName, DbType.String);
            parameters.Add("@WallID", WallID, DbType.Int32);

            sqlCommand = "Select Count(*) from Wall ";
            sqlCommand += "where Upper(WallName) = Upper(@WallName) ";
            sqlCommand += "and WallID <> @WallID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var countWall = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
                return countWall;
            }
        }


        //WallUpdate
        public async Task<bool> WallUpdate(Wall wall)
        {
            var parameters = new DynamicParameters();

            parameters.Add("WallID", wall.WallID, DbType.Int32);
            parameters.Add("WallName", wall.WallName, DbType.String);
            parameters.Add("@WallTypeID", wall.WallTypeID, DbType.Int32);
            parameters.Add("@WalTypeName", wall.WallTypeName, DbType.String);
            parameters.Add("@WallLengthMax", wall.WallLengthMax, DbType.Int32);
            parameters.Add("@WallLengthMin", wall.WallLengthMin, DbType.Int32);
            parameters.Add("@WallHeightMax", wall.WallHeightMax, DbType.Int32);
            parameters.Add("@WallHeightMin", wall.WallHeightMin, DbType.Int32);
            parameters.Add("@WallSqM", wall.WallSqM, DbType.Decimal);

            sqlCommand = "Update Wall ";
            sqlCommand += "SET WallName = @WallName, " +
                "WallTypeID = @WallTypeID, " +
                "WalTypeName = @WalTypeName, " +
                "WallLengthMax = @WallLengthMax, " +
                "WallLengthMin = @WallLengthMin, " +
                "WallHeightMax = @WallHeightMax, " +
                "WallHeightMin = @WallHeightMin, " +
                "WallSqM = @WallSqM ";
            sqlCommand += "WHERE WallID  = @WallID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }


        //WallDelete
        public async Task<bool> WallDelete(Int32 WallID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@WallID", WallID, DbType.Int32);

            //PRAGMA is specific to SQLite and this command is required for DELETE CASCADE to work
            sqlCommand = "PRAGMA foreign_keys = ON;";
            sqlCommand += "Delete from Wall ";
            sqlCommand += "WHERE WallID  = @WallID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }

    }
}

IWallService

namespace BlazorWallAreaCalculator.Data
{
    public interface IWallService
    {
        Task<bool> WallCreate(Wall wall);
        Task<IEnumerable<Wall>> WallReadAll();
        Task<int> CountWallsByName(string WallName);
        Task<int> CountWallsByNameAndId(string WallName, int WallID);
        Task<bool> WallUpdate(Wall wall);
        Task<bool> WallDelete(Int32 WallID);
    }
}

DeductionService

using Dapper;
using System.Data.SQLite;
using System.Data;

namespace BlazorWallAreaCalculator.Data
{
    public class DeductionService : IDeductionService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public DeductionService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";
        private Deduction? deduction;

        // DeductionCreate
        public async Task<bool> DeductionCreate(Deduction deduction)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@WallID", deduction.WallID, DbType.Int32);
            parameters.Add("@DeductionName", deduction.DeductionName, DbType.String);
            parameters.Add("@DeductionWidth", deduction.DeductionWidth, DbType.Int32);
            parameters.Add("@DeductionHeight", deduction.DeductionHeight, DbType.Int32);

            sqlCommand = "Insert into Deduction (WallID, DeductionName, DeductionWidth, DeductionHeight) ";
            sqlCommand += "values(@WallID, @DeductionName, @DeductionWidth, @DeductionHeight )";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }


        // DeductionRead
        public async Task<IEnumerable<Deduction>> DeductionReadAll()
        {
            IEnumerable<Deduction> deductions;

            sqlCommand = "Select * from Deduction ORDER BY DeductionName";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                deductions = await conn.QueryAsync<Deduction>(sqlCommand);
            }
            return deductions;
        }

        public async Task<int> CountDeductionsByName(string DeductionName)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@DeductionName", DeductionName, DbType.String);

            sqlCommand = "Select Count(*) from Deduction ";
            sqlCommand += "where Upper(DeductionName) = Upper(@DeductionName)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var countDeduction = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
                return countDeduction;
            }
        }

        public async Task<int> CountDeductionsByNameAndId(string DeductionName, int DeductionID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@DeductionName", DeductionName, DbType.String);
            parameters.Add("@DeductionID", DeductionID, DbType.Int32);

            sqlCommand = "Select Count(*) from Deduction ";
            sqlCommand += "where Upper(DeductionName) = Upper(@DeductionName) ";
            sqlCommand += "and DeductionID <> @DeductionID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var countDeduction = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
                return countDeduction;
            }
        }


        //DeductionUpdate
        public async Task<bool> DeductionUpdate(Deduction deduction)
        {
            var parameters = new DynamicParameters();

            parameters.Add("DeductionID", deduction.DeductionID, DbType.Int32);
            parameters.Add("DeductionName", deduction.DeductionName, DbType.String);
            parameters.Add("@DeductionWidth", deduction.DeductionWidth, DbType.Int32);
            parameters.Add("@DeductionHeight", deduction.DeductionHeight, DbType.Int32);

            sqlCommand = "Update Deduction ";
            sqlCommand += "SET DeductionName = @DeductionName, ";
            sqlCommand += "DeductionWidth = @DeductionWidth, ";
            sqlCommand += "DeductionHeight = @DeductionHeight ";
            sqlCommand += "WHERE DeductionID  = @DeductionID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }


        //DeductionDelete
        public async Task<bool> DeductionDelete(Int32 DeductionID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@DeductionID", DeductionID, DbType.Int32);

            //PRAGMA is specific to SQLite and this command is required for DELETE CASCADE to work
            sqlCommand = "PRAGMA foreign_keys = ON;";
            sqlCommand += "Delete from Deduction ";
            sqlCommand += "WHERE DeductionID  = @DeductionID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);                
            }
            return true;
        }


    }
}

IDeductionService

namespace BlazorWallAreaCalculator.Data
{
    public interface IDeductionService
    {
        Task<bool> DeductionCreate(Deduction deduction);
        Task<IEnumerable<Deduction>> DeductionReadAll();
        Task<int> CountDeductionsByName(string DeductionName);
        Task<int> CountDeductionsByNameAndId(string DeductionName, int DeductionID);
        Task<bool> DeductionUpdate(Deduction deduction);
        Task<bool> DeductionDelete(Int32 DeductionID);
    }
}

To complete this we need to register all the above.  Open Program.cs and insert the following in the 'builder' section.

builder.Services.AddScoped<IRoomService, RoomService>();
builder.Services.AddScoped<IWallService, WallService>();
builder.Services.AddScoped<IDeductionService, DeductionService>();

Room Grid

We will now add the Syncfusion grid for rooms.  This follows the pattern established for the grid for Projects.  Add the following under the CSHTML for the Project Dropdown List:

<hr />

<h6><b>Rooms</b></h6>

<SfGrid @ref="RoomGrid"
        DataSource="@rooms"
        AllowSorting="true"
        AllowResizing="true"
        Height="70"
        Toolbar="@RoomToolbaritems">
    <GridColumns>
        <GridColumn Field="@nameof(Room.RoomName)"
                    HeaderText="Room Name"
                    TextAlign="@TextAlign.Left"
                    Width="50">
        </GridColumn>
    </GridColumns>
    <GridEvents OnToolbarClick="RoomToolbarClickHandler" TValue="Room" RowSelected="RoomRowSelectHandler"></GridEvents>
</SfGrid>

We are using a data source of 'rooms' so next we need to declare it:

public IEnumerable<Room>? rooms;

We also need to declare the SfGrid:

SfGrid<Room>? RoomGrid;

As we will end up having three grids we will need three separate Tool Bar Items, so we will prefix the toolbaritems with the grid name.

private List<ItemModel> RoomToolbaritems = new List<ItemModel>();

We need to populate the RoomToolbaritems when the page opens, so we add the following to the OnInitializedAsync.

RoomToolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new room", PrefixIcon = "e-add" });
RoomToolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected room", PrefixIcon = "e-edit" });
RoomToolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected room", PrefixIcon = "e-delete" });

The GridEvents section of the Room Grid includes references to two methods, one for tool bar handler and the other for row selected handler.  For the time being we will simply add placeholder code for these methods.  Add the following to the code section.

public async Task RoomToolbarClickHandler(ClickEventArgs args)
{
    if (args.Item.Text == "Add")
    {
        //Code for adding goes here

    }
    if (args.Item.Text == "Edit")
    {
        //Code for editing
            
    }
    if (args.Item.Text == "Delete")
    {
        //Code for deleting
            
    }
}

public async Task RoomRowSelectHandler(RowSelectEventArgs<Room> args)
{
    //{args.Data} returns the current selected records.

}

Save and run the application.  The Index page has the drop-down list of projects and the grid for rooms with buttons to add, edit and delete rooms - but we haven't coded these events yet.

When we add a room we want the room to be linked to the project, and the link is ProjectID.  We therefore need to ensure that the user has selected a Project and then  capture the ProjectID for the selected project.  We will also want the Room grid to display all the rooms for the selected project.  To achieve these objectives we use a property of the drop-down list called 'DropDownListEvents'.  Add the following within the SfDropDownList tags; I suggest just before the closing tag.

<DropDownListEvents TItem="Project" TValue="string" ValueChange="@OnChangeProject"></DropDownListEvents>

This will call a method when the value in the drop down list changes, which we have named 'OnChangeProject', which in turn will assign the value of the ProjectID to a new variable 'SelectedProjectID'.

public async Task OnChangeProject(ChangeEventArgs<string, Project> args)
{
    SelectedProjectID = args.ItemData.ProjectID;  
}

To complete this part, declare the SelectedProjectID by inserting this code in the declarations section.

[Parameter]
public int SelectedProjectID { get; set; } = 0;

This completes the preparation necessary to start coding the toolbar buttons to add, edit and delete rooms.  We will copy the techniques we used for the Projects grid, firstly by adding a dialog box to gather data from the user.  Add the following after the horizontal line under the dropdownlist in the CSHTML section:

<SfDialog @ref="DialogRoom" IsModal="true" Width="420px" ShowCloseIcon="false" Visible="false" AllowDragging="true">
    <DialogTemplates>
        <Header> @dialogTitle</Header>
        <Content>
            <EditForm Model="@roomAddEdit" OnValidSubmit="@RoomSave">
                <div>
                    <SfTextBox Enabled="true" Placeholder="Room Name"
                               FloatLabelType="@FloatLabelType.Always"
                    @bind-Value="roomAddEdit.RoomName">
                    </SfTextBox>
                </div>
                <br /><br />
                <div class="e-footer-content">
                    <div class="button-container">
                        <button type="submit" class="e-btn e-normal e-primary">Save</button>
                        <button type="button" class="e-btn e-normal" @onclick="@CancelRoom">Cancel</button>
                    </div>
                </div>
            </EditForm>
        </Content>
    </DialogTemplates>
</SfDialog>

Declare the variables for the SfDialog and the Room object we will use for manipulating room data.

SfDialog? DialogRoom;
Room roomAddEdit = new Room();

We also need to declare the variable used to provide a meaningful header for the dialog box:

public string dialogTitle = "";

The OnValidSubmit calls a method we have called 'RoomSave'. Again, copying the code structure from ProjectSave add the following method:

protected async Task RoomSave()
{
    if (roomAddEdit.RoomID == 0)
    {
        /// Insert if RoomID is zero.
        //Check for duplicates
        int duplicates = await RoomService.CountRoomsByNameAndProject(roomAddEdit.RoomName, SelectedProjectID);

        if (duplicates == 0)
        {
            roomAddEdit.ProjectID = SelectedProjectID;

            try
            {
                await RoomService.RoomCreate(roomAddEdit);
                //Refresh datagrid
                rooms = await RoomService.RoomReadAll();
                StateHasChanged();
                await DialogRoom.HideAsync();
            }
            catch
            {
                // Display warning message
                await DialogService.AlertAsync("An unexpected error has occured.", "Unknown Error");
                return;
            }
        }
        else
        {
            //Room already exists - warn the user
            await DialogService.AlertAsync("This room already exists; it cannot be added again.", "Warning!");
            return;
        }
    }
    else
    {
        // Record is being edited
        // Check for duplicates
        int duplicates = await RoomService.CountRoomsByNameAndProjectAndId(roomAddEdit.RoomName, roomAddEdit.RoomID, SelectedProjectID);

        if (duplicates == 0)
        {
            try
            {
                await RoomService.RoomUpdate(roomAddEdit);
                //Refresh datagrid
                rooms = await RoomService.RoomReadAll();
                StateHasChanged();
                await DialogRoom.HideAsync();
            }
            catch
            {
                // Display warning message
                await DialogService.AlertAsync("An unexpected error has occured.", "Unknown Error");
                return;
            }
        }
        else
        {
            //Project already exists - warn the user
            await DialogService.AlertAsync("This room already exists", "Room Already Exists");
            return;
        }

    }
}

The above looks fairly complex, but it caters for both Add and Edit, in both cases checking for duplicates, then, in the case of 'Add' assigning the SelectedProjectID to ProjectID of the roomAddEdit object, then calling either RoomCreate or RoomUpdate, repopulating 'rooms with 'RoomReadAll' and closing the dialog.

We need new services and interfaces for the methods used to check for duplicates.  Since we can quite legitimately have rooms called the same in different projects we need to include the ProjectID in the code for checking for duplicates.  We already have methods for counting rooms, but these don't include Project.  Delete the existing count methods and replace with the following methods.

#region CountRooms
public async Task<int> CountRoomsByNameAndProject(string RoomName, int ProjectID)
{
    var parameters = new DynamicParameters();

    parameters.Add("@RoomName", RoomName, DbType.String);
    parameters.Add("@ProjectID", ProjectID, DbType.Int32);

    sqlCommand = "Select Count(*) from Room ";
    sqlCommand += "where Upper(RoomName) = Upper(@RoomName) ";
    sqlCommand += "and ProjectID = @ProjectID";

    using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
    {
        var countRoom = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
        return countRoom;
    }
}

public async Task<int> CountRoomsByNameAndProjectAndId(string RoomName, int RoomID, int ProjectID)
{
    var parameters = new DynamicParameters();
    parameters.Add("@RoomName", RoomName, DbType.String);
    parameters.Add("@RoomID", RoomID, DbType.Int32);
    parameters.Add("@ProjectID", ProjectID, DbType.Int32);

    sqlCommand = "Select Count(*) from Room ";
    sqlCommand += "where Upper(RoomName) = Upper(@RoomName) ";
    sqlCommand += "and ProjectID = @ProjectID ";
    sqlCommand += "and RoomID <> @RoomID";

    using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
    {
        var countRoom = await conn.QueryFirstOrDefaultAsync<int>(sqlCommand, parameters);
        return countRoom;
    }
}
#endregion

Similarly, amend the count lines in IRoomService to the following:

Task<int> CountRoomsByNameAndProject(string RoomName, int ProjectID);
Task<int> CountRoomsByNameAndProjectAndId(string RoomName, int RoomID, int ProjectID);

To use the Room Service insert the appropriate 'using' statement at the top of the code.

@inject IRoomService RoomService

There is a problem with populating the Room grid with RoomReadAll.  We only want to display rooms for the project selected from the drop-down list.  Now we have the 'selectedProjectID' we can replace RoomReadAll with a new service/interface that includes the ProjectID.  As we won't need RoomReadAll replace the method for RoomReadAll in RoomService to the following:

public async Task<IEnumerable<Room>> RoomsReadByProject(int ProjectID)
{
    IEnumerable<Room> rooms;

    var parameters = new DynamicParameters();
    parameters.Add("@ProjectID", ProjectID, DbType.Int32);

    sqlCommand = "Select * from Room ";
    sqlCommand += "WHERE ProjectID  = @ProjectID";

    using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
    {
        rooms = await conn.QueryAsync<Room>(sqlCommand, parameters);
    }
    return rooms;
}

Amend IRoomService likewise:

 Task<IEnumerable<Room>> RoomsReadByProject(int ProjectID);

To complete this part, change the RoomReadAll statements in RoomSave with:

rooms = await RoomService.RoomsReadByProject(SelectedProjectID);

There is one more method we need, RoomCancel.  Add the following to the end of the code:

    void CancelRoom()
    {
        DialogRoom.HideAsync();
    }

With all the above in place we can insert the code into the ToolbarHandler for 'Add'.  (We will tackle Edit and Delete when we have the RoomRowSelectHandler defined.)  For now, insert the following into the Add section:

dialogTitle = "Add a Room";
roomAddEdit = new();
await DialogRoom.ShowAsync(false);

Save all files and test the application works.

We can now turn our attention to Edit and Delete, where we need to know which row the user has selected.  This is handled by the RoomRowSelectHandler; insert the following code into this method.

SelectedRoomID = args.Data.RoomID;
SelectedRoomName = args.Data.RoomName;

And declare these two new variables, the first in the [Parameters] section

public int SelectedRoomID { get; set; } = 0;

And the second under the declaration for DialogTitle.  I don't think position is critical, but it's neater to keep similar declarations next to each other.

 public string SelectedRoomName { get; set; } = string.Empty;

In the RoomToolbarClickHandler enter the following for the Edit option:

if (SelectedRoomID == 0)
{
    await DialogService.AlertAsync("Please select a room.", "No Room Selected");
    return;
}
else
{
    //populate roomAddEdit (temporary data set used for the editing process)
    roomAddEdit = new();
    roomAddEdit.RoomID = SelectedRoomID;
    roomAddEdit.RoomName = SelectedRoomName;
    await DialogRoom.ShowAsync(false);
}

In the above we check to ensure the user has selected a room, and if so open DialogRoom dialog box, populating the roomAddEdit object with the data from the row selected.

We are still left with deleting a room.  We can use the basic code from deleting a project and modifying for a room, as shown below:

//Code for deleting
if (SelectedRoomID == 0)
{
    await DialogService.AlertAsync("Please select a room.", "No Room Selected");
    return;
}
else
{
    bool isConfirm = await DialogService.ConfirmAsync(
        "Are you sure you want to delete " + SelectedRoomName + "?",
        "Delete " + SelectedRoomName);
    if (isConfirm == true)
    {
        await RoomService.RoomDelete(SelectedRoomID);
        //Refresh datagrid
        rooms = await RoomService.RoomsReadByProject(SelectedRoomID);
        StateHasChanged();
        SelectedRoomID = 0;
    }
}

Code

Code for files changed in this post here.

Powered by Publii