Editing & Deleting Data

Editing a Person

Editing a Person will follow a similar pattern to adding, but with a few minor differences. The first difference is that the user will have to select a record from the DataGrid for editing. If the user doesn't select a record before clicking the 'Edit' button we will use the same Warning Dialog as we used previously to warn of a duplicate Contact, but with a different message.

Once a record has been selected we must determine the ID of the record and populate the add/edit dialog with the data to be edited. The possibility still remains that a duplicate record could be created and this must be prevented and the user warned.

YouTube Video

Person_Update Stored Procedure

We will start by changing the SQL stored procedure for updating a Person record to prevent duplicates and report back to the user.

Open SQL Server Management Studio, select the Birthdays database and open a New Query window and copy and paste the following code into it.

USE [Birthdays]
GO

/****** Object:  StoredProcedure [dbo].[spPerson_Update]   ******/
DROP PROCEDURE [dbo].[spPerson_Update]
GO

/****** Object:  StoredProcedure [dbo].[spPerson_Update]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spPerson_Update]  
(
@PersonID uniqueidentifier,
@PersonFirstName nvarchar(50),
@PersonLastName nvarchar(50),
@PersonDateOfBirth date,
@PersonSendReminderTo nvarchar(100)
)  
AS  
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Person  
          WHERE PersonFirstName = @PersonFirstName 
		  and PersonLastName = @PersonLastName
		  and PersonDateOfBirth = @PersonDateOfBirth
		  and PersonSendReminderTo = @PersonSendReminderTo
		  and PersonID <> @PersonID
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE  
      BEGIN  
           UPDATE Person SET PersonFirstName = @PersonFirstName, PersonLastName = @PersonLastName, PersonDateOfBirth = @PersonDateOfBirth, PersonSendReminderTo = @PersonSendReminderTo WHERE PersonID = @PersonID
           set @ResultValue = @@ERROR  
     END  
IF @ResultValue <> 0  
     BEGIN  
            ROLLBACK TRAN  
      END  
ELSE  
      BEGIN  
            COMMIT TRAN  
      END  
RETURN @ResultValue  
GO

Execute the query to drop and re-create the stored procedure. Close the Query window; there is no need to save the query.  This follows the pattern to prevent duplicates when inserting records, but takes into consideration that we mustn't include the record being updated!

PersonService.cs

Modify the PersonUpdate method in PersonService as shown below.  Again this follows the pattern used for PersonInsert.

// Update one Person row based on its PersonID (SQL Update)
public async Task<int> PersonUpdate(Person person)
{
    int Success = 0;
    using IDbConnection conn = new SqlConnection(_configuration.GetConnectionString(connectionId));
    {
        var parameters = new DynamicParameters();
        parameters.Add("PersonID", person.PersonID, DbType.Guid);

        parameters.Add("PersonFirstName", person.PersonFirstName, DbType.String);
        parameters.Add("PersonLastName", person.PersonLastName, DbType.String);
        parameters.Add("PersonDateOfBirth", person.PersonDateOfBirth, DbType.Date);
        parameters.Add("PersonSendReminderTo", person.PersonSendReminderTo, DbType.String);
        parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

        await conn.ExecuteAsync("spPerson_Update", parameters, commandType: CommandType.StoredProcedure);
        Success = parameters.Get<int>("@ReturnValue");
    }
    return Success;
}

IPersonService

Replace the PersonUpdate line with the following:

Task<int> PersonUpdate(Person person);

Index.razor

We need to be able to identify which record a user has selected for editing from the DataGrid. To do this we need to amend the <GridEvents> to include a RowSelectHandler. To do this

  • modify the <GridEvents> to add a 'RowSelected' attribute, as shown below:
<GridEvents RowSelected="RowSelectHandler"  OnToolbarClick="ToolbarClickHandler" TValue="Person"></GridEvents>
  • Declare a new variable of type Guid for the selected PersonID
public Guid SelectedPersonId { get; set; } = Guid.Empty;
  • And add a new method called 'RowSelectHandler'.  Add the following at the end of the code section.
      public void RowSelectHandler(RowSelectEventArgs<Person> args)
    {
        //{args.Data} returns the current selected records.
        SelectedPersonId = args.Data.PersonID;
    }

The user will click on a row in the grid and the above code will determine the PersonID of the row selected.

The next thing to be changed is the ToolbarHandler to handle the user clicking 'Edit'. In the ToolBarHandler code insert the following code under the '//Code for editing goes here' comment.

//Check that a Person has been selected
if (SelectedPersonId == Guid.Empty)
{
    WarningHeaderMessage = "Warning!";
    WarningContentMessage = "Please select a Contact from the grid.";
    Warning.OpenDialog();
}  
else
{
    //populate personaddedit (temporary data set used for the editing process)
    HeaderText = "Edit Contact";
    personaddedit = await PersonService.PersonGetOne(SelectedPersonId);
    await this.DialogAddEditPerson.Show();
}

This checks if SelectedPersonId is 0, i.e. the user hasn't selected a row from the DataGrid, and if so, uses the Warning dialog to display a message informing the user to select a record.

If a user has selected a record, it provides header text for the add/edit person dialog and populates the personaddedit object with data for the relevant record, and lastly displays the dialog ready for editing.

We now need to change the PersonSave method to handle the edit situation. Enter the following code under the '//Item is being edited' comment

int Success = await PersonService.PersonUpdate(personaddedit);
if (Success != 0)
{
    //Person already exists
    WarningHeaderMessage = "Warning!";
    WarningContentMessage = "This Person already exists; it cannot be added again.";
    Warning.OpenDialog();
    // Data is left in the dialog so the user can see the problem.
}
else
{
    //Refresh datagrid
    people = await PersonService.PersonGetAll();
    StateHasChanged();
    await CloseDialog();
    personaddedit = new Person();
    SelectedPersonId = Guid.Empty;
}

PersonService.PersonUpdate attempts the update, but if Success is anything but 0 it warns the user that the Person already exists. Otherwise it updates the record, closes the dialog and resets personaddedit and SelectedPersonId.

Deleting a Person

To 'delete' a record we will be checking that the user has selected a row from the grid, and if they have we will then display a new dialog showing details of the selected item and requesting confirmation that they wish to continue to delete the record.

We will use a new dialog box to ask confirmation that the person should be deleted. Add the new Syncfusion dialog, code below, immediately under the existing dialog (and before the Warningcomponent).

<SfDialog @ref="DialogDeletePerson" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
    <DialogTemplates>
        <Header> Confirm Delete </Header>
        <Content>
            <SfTextBox Enabled="false" Placeholder="Description"
                       FloatLabelType="@FloatLabelType.Always"
                       @bind-Value="personaddedit.PersonFirstName"></SfTextBox>
            <SfTextBox Enabled="false" Placeholder="Description"
                       FloatLabelType="@FloatLabelType.Always"
                       @bind-Value="personaddedit.PersonLastName"></SfTextBox>
            
            <br />
            <br />
            <span class="text-danger">Please confirm that you want to delete this record</span>
        </Content>
    </DialogTemplates>
    <DialogButtons>
        <DialogButton Content="Delete" IsPrimary="true" OnClick="@ConfirmDeleteYes" />
        <DialogButton Content="Cancel" IsPrimary="false" OnClick="@ConfirmDeleteNo" />
    </DialogButtons>
</SfDialog>

This is basically the same as the DialogAddEditPerson, except that I have added <Content> tags around the TextBoxes (I seemed to need this to add text at the bottom for the confirmation message) and different buttons with new OnClick events.

Add the declaration for the new dialog.

SfDialog DialogDeletePerson;

Add the code in the ToolbarClickHandler for the Delete option.

if (args.Item.Text == "Delete")
{
    //Code for deleting                         
    if (SelectedPersonId == Guid.Empty)
    {
        WarningHeaderMessage = "Warning!";
        WarningContentMessage = "Please select a Contact from the grid.";
        Warning.OpenDialog();
    }
    else
    {
        //populate personaddedit (temporary data set used for the editing process)
        HeaderText = "Delete Contact";
        personaddedit = await PersonService.PersonGetOne(SelectedPersonId);
        await this.DialogDeletePerson.Show();
    }
}

This checks that a row has been selected from the DataGrid, using the Warning component, as for Edit, if the user hasn't selected a record.

Assuming a record has been selected the new DialogDeletePerson is opened, using the existing personaddedit object and the PersonService.PersonGetOne.

Add the two new methods to handle the button click events in DialogDeletePerson.

public async void ConfirmDeleteNo()
{
    await DialogDeletePerson.Hide();
    SelectedPersonId = Guid.Empty;
}

public async void ConfirmDeleteYes()
{
    bool Success = await PersonService.PersonDelete(SelectedPersonId);
    if (Success == false)
    {
        WarningHeaderMessage = "Warning!";
        WarningContentMessage = "Unknown error has occurred - the record has not been deleted!";
        Warning.OpenDialog();
    }
    else
    {
        await this.DialogDeletePerson.Hide();
        people = await PersonService.PersonList();
        this.StateHasChanged();
        personaddedit = new Person();
        SelectedPersonId = Guid.Empty;
    }
}

Clicking 'Cancel' closes the dialog without any further action, and resets the SelectedPersonId variable. (This is to clear it from memory, to ensure a new row is selected if the user wants to Edit a record, for example.)

If the user confirms the deletion we use the existing PersonDelete method, passing to it the parameter for 'SelectedPersonId'. The record delete should succeed, so the final code closes the dialog, refreshes PersonList and resets the personaddedit object and SelectedPersonId.

Save all files and run the application...

References

Code changes for this article : Editing & Deleting Data

Powered by Publii