Countries - Preventing Duplicates
One problem with the adding and editing of countries, so far, is that it is possible to end up with the same country twice. In practice we want to prevent this. There is probably more than one way to solve this problem, but we shall take the path of tackling it at the SQL end and report back to the Blazor application.
There are two stored procedures that need modifying, the 'Countries_Insert' and 'Countries_Update'. However, rather than modifying what we already have we will create two stored procedures and then swap out the existing ones.
Adding a Country
Insert Stored Procedure
In SQL Management Studio create a new stored procedure for inserting countries, called 'spCountries_InsertWithDuplicateChecking', using the code shown below.
USE [CountriesDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCountries_InsertWithDuplicateChecking]
(
@CountryName VARCHAR(50)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Countries
WHERE CountryName = @CountryName
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
INSERT INTO Countries
(
CountryName
)
VALUES
(
@CountryName
)
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
This SQL code departs from the original code in two respects. Firstly it checks whether the Country Name being passed as a parameter already exists. If it does it returns a value of 99, if not it inserts the new record and returns a value of 0. Secondly, it wraps the code within a SQL transaction. The transaction begins on line 16, and a test carried out on line 37 to check whether the insert will be successful; if it passes the transaction is committed, otherwise it is rolled back.
The original SQL to insert 'just' did the insert, however this variation returns a value that the C# code needs to deal with. To see what happens at the SQL level, in SQL Management Studio, select CountriesDb > Programmability > Stored Procedures > spCountries_Insert, right-click and choose 'Execute Stored Procedure'. In the form that opens enter, say, 'France' in the Value column and click 'OK'. The result is that France is added to the database, even though it might already exist. Repeat, selecting the new stored procedure. The result this time is that the return value is 99 and France is not added to the database.
Insert - C# Code
In Visual Studio, we will start by replacing CountriesInsert with CountriesInsertWithDuplicateChecking in
- CountriesService.cs
- ICountriesService.cs
- CountriesAddEdit.razor
and in CountriesService.cs change the stored procedure to spCountries_InsertWithDuplicateChecking.
If the application is run now it will prevent duplicate countries being added, but the user will be unaware that the country was not added. A number of steps are needed to make this user friendly. I have pondered this, and I doubt what I am going to describe is the best solution, but it's about as good as I could get!
As it stands 'CountriesInsert...' procedure, as shown immediately below, returns a boolean and is always set to 'true'. We need to be able to ascertain the return value coming from the SQL. If all this is replaced by the code in the second example we can see that the procedure is changed to return an integer (declared as 'Success'), which in turn is set to the value being returned by the stored procedure '@ReturnValue'.
public async Task<bool> CountriesInsertWithDuplicateChecking(Countries countries)
{
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
parameters.Add("CountryName", countries.CountryName, DbType.String);
// Stored procedure method
await conn.ExecuteAsync("spCountries_InsertWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
}
return true;
} public async Task<int> CountriesInsertWithDuplicatesCheck(string CountryName)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("@CountryName", CountryName, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_InsertWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}In addition we also need to change ICountriesService to reflect the above. Note that we are no longer passing the countries object, but just the CountryName.
Task<int> CountriesInsertWithDuplicatesCheck(string CountryName);The result of these change is that if a record is added successfully the value 0 will be returned, but if a duplicate is found it will be 99. The CountriesAddEdit needs to be amended, firstly, to call the service correctly, but secondly display a warning to the user if the country already exists. We will warn the user using a modal dialog. (Even though the CountryAddEdit is already a modal dialog, it is possible to nest dialogs.)
Add the following to the HTML section of CountriesAddEdit
<SfDialog @ref="DialogWarning" IsModal="true" Width="250px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Warning! </Header>
<Content> This country already exists! It won't be added again. </Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialog" />
</DialogButtons>
</SfDialog>In the code section declare the dialog: SfDialog DialogWarning; and replace the existing code within CountriesSave() as below. Remove NavigationManager.NavigateTo("/"); that used to be the last line in CountriesSave() and (temporarily) move it after the CountriesService.CountriesUpdate line.
if (CountryId == 0)
{
int Success = await CountriesService.CountriesInsertWithDuplicatesCheck(countries.CountryName);
if (Success != 0)
{
//Country Name already exists
await OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}Add the following at the end of the code section to open and close the warning dialog.
private async Task OpenDialog()
{
await this.DialogWarning.Show();
}
private async Task CloseDialog()
{
await this.DialogWarning.Hide();
NavigationManager.NavigateTo("/");
}Save all the files and run to test the changes.
Updating a Country
The approach for Updating a Country will follow the same pattern used for Adding a Country, except that the 'If Exists' will be extended to exclude the current record; this is to prevent an 'error' being displayed if the user doesn't change the name of the country but still saves the record instead of cancelling the edit. It won't be described in detail, but the code is shown below.
Update Stored Procedure
USE [CountriesDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spCountries_UpdateWithDuplicateChecking]
(
@CountryId int,
@CountryName varchar(50)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Countries
WHERE CountryName = @CountryName and CountryId <> @CountryId
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
UPDATE Countries SET CountryName = @CountryName WHERE CountryId = @CountryId
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GOUpdate - C# Code
CountriesService.cs
// Update one Countries row based on its CountriesID (SQL Update)
public async Task<int> CountriesUpdateWithDuplicatesCheck(Countries countries)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("CountryId", countries.CountryId, DbType.Int32);
parameters.Add("CountryName", countries.CountryName, DbType.String);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spCountries_UpdateWithDuplicateChecking", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}ICountriesService.cs
// This is the Countries Interface
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCountries.Data
{
// Each item below provides an interface to a method in CountriesServices.cs
public interface ICountriesService
{
Task<int> CountriesInsertWithDuplicatesCheck(string CountryName);
Task<IEnumerable<Countries>> CountriesGetAll();
Task<Countries> CountriesGetOne(int CountryId);
Task<int> CountriesUpdateWithDuplicatesCheck(Countries countries);
Task<bool> CountriesDelete(int CountryId);
}
}CountriesAddEdit.razor needs the CountriesSave procedure to show the warning if a duplicate would be created by updating an existing record.
// Executes OnValidSubmit of EditForm above.
protected async Task CountriesSave()
{
if (CountryId == 0)
{
int Success = await CountriesService.CountriesInsertWithDuplicatesCheck(countries.CountryName);
if (Success != 0)
{
//Country Name already exists
await this.OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}
else
{
int Success = await CountriesService.CountriesUpdateWithDuplicatesCheck(countries);
if (Success != 0)
{
//Country Name already exists
await this.OpenDialog();
}
else
{
NavigationManager.NavigateTo("/");
}
}
}Full Code
The full code for the changes made in this section can be found by clicking the above heading.