.NET: Defensive data programming (Part 2)
Series
- .NET: Defensive data programming (part 1)
- .NET: Defensive data programming (Part 2)
- .NET: Defensive data programming (Part 3)
- .NET: Defensive data programming (Part 4)
Introduction
This article will walk through the process required to prevent inserting duplicate records or update a record. A simple example would be a country table having one record per country. There would be no reason to have a country name entered more than once.
One culprit for adding a duplicate record is inserting directly into a table from SQL-Server Management Studio while another would be providing an administrator the ability to add new records without any form of assertion in an application.
When adding a new record in SQL-Server Management Studio the best way to prevent duplicate records is to create a unique constraint to prevent a duplicate record. The constraint can be used in an application also which will be explored done incorrectly and done correctly.
Incorrectly handling constraint violations with INSERT
Many developers will place a try/catch surrounding the operation which would be to create a connection and command object suitable for inserting a new record and returning false from a method if an exception is thrown. Doing so will still increment the sequence e.g. if the last primary key value was 2 and the insert failed upon the next successful insert the new primary key value would not be 3 but instead 3.
Other developers will think that the best course of action is to write a lot of code to determine why the exception happened even though the message of the thrown exception indicates exactly what the problem is. Their code may follow the pattern below to get information back to the user attempting to add a new record.
C# Example
public bool InsertCountry(string pCountryName, ref int pIdentifier, ref string pError)
{
using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
{
using (var cmd = new SqlCommand() {Connection = cn})
{
var insertStatement = "INSERT INTO dbo.Country (Name) VALUES (@Name);" +
"SELECT CAST(scope_identity() AS int);";
try
{
cmd.CommandText = insertStatement;
cmd.Parameters.AddWithValue("@Name", pCountryName);
cn.Open();
pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
return true;
}
catch (SqlException ex)
{
string message = null;
string tableName = "";
string indexName = "";
/*
* We already know the value but if you want to get
* into some regx this shows how to parse the value.
*/
DuplicateCountryValue = Regex.Match(ex.Message, "\\(([^)]*)\\)").Groups[1].Value;
/*
* Get the table name 'country' which we have in the INSERT INTO
*/
var match = Regex.Match(ex.Message, @"'([^']*)");
if (match.Success)
{
tableName = match.Groups[1].Value;
}
if (ex.Number == 2601)
{
pError = $"Can not add '{DuplicateCountryValue}' into '{tableName}' since it already exists.";
// if you needed the index involved with the error
indexName = GetIndexKeys(cmd, ex.Message, "Country");
}
mHasException = true;
mLastException = ex;
return false;
}
}
}
}
VB.NET Example
Public Function InsertCountry(ByVal pCountryName As String, ByRef pIdentifier As Integer, ByRef pError As String) As Boolean
Using cn = New SqlConnection() With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand() With {.Connection = cn}
Dim insertStatement = "INSERT INTO dbo.Country (Name) VALUES (@Name);" & "SELECT CAST(scope_identity() AS int);"
Try
cmd.CommandText = insertStatement
cmd.Parameters.AddWithValue("@Name", pCountryName)
cn.Open()
pIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
Return True
Catch ex As SqlException
Dim message As String = Nothing
Dim tableName As String = ""
Dim indexName As String = ""
'
' * We already know the value but if you want to get
' * into some regx this shows how to parse the value.
'
DuplicateCountryValue = Regex.Match(ex.Message, "\(([^)]*)\)").Groups(1).Value
'
' * Get the table name 'country' which we have in the INSERT INTO
'
Dim match = Regex.Match(ex.Message, "'([^']*)")
If match.Success Then
tableName = match.Groups(1).Value
End If
If ex.Number = 2601 Then
pError = $"Can not add '{DuplicateCountryValue}' into '{tableName}' since it already exists."
' if you needed the index involved with the error
indexName = GetIndexKeys(cmd, ex.Message, "Country")
End If
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Another exception to watch for is:
Violation of UNIQUE KEY constraint 'UQ_Person1FirstLastName'. Cannot insert duplicate key in object 'dbo.Persons1'. The duplicate key value is (Karen, Payne).
The statement has been terminated.
A developer will tend to wrap this into a Try/Catch and show the error message to the user. While another developer would go to extremes to figure out what want wrong as shown here.
C# example
public bool Update(string pFirstName, string pLastName, int pIdentifier)
{
using (var cn = new SqlConnection() { ConnectionString = SqlConnectionString })
{
var statement = "UPDATE dbo.Persons1 SET FirstName = @FirstName,LastName = @LastName WHERE id = @Id";
using (var cmd = new SqlCommand() { Connection = cn, CommandText = statement })
{
cmd.Parameters.AddWithValue("@FirstName", pFirstName);
cmd.Parameters.AddWithValue("@LastName", pLastName);
cmd.Parameters.AddWithValue("@id", pIdentifier);
try
{
cn.Open();
cmd.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
string message = null;
int pos = 0;
ConstraintValue = Regex.Match(ex.Message, "\\(([^)]*)\\)").Groups[1].Value;
pos = ex.Message.IndexOf(".", StringComparison.Ordinal);
message = ex.Message.Substring(0, pos);
if (message.Contains("Violation of UNIQUE KEY constraint"))
{
message = message.Replace("Violation of UNIQUE KEY constraint", "").Replace("'", "");
pos = message.LastIndexOf("_", StringComparison.Ordinal) + 1;
ConstraintColumnName = message.Substring(pos);
}
HasException = true;
Exception = ex;
return false;
}
}
}
}
Summary of issues
There are several things wrong with this approach, first off the code presented returns the table name and values which violated the constraint and secondly this code overly complicates the method with no true value in return. With all this code on failure, the auto-incrementing sequence is advanced as done in the first idea to wrap the operation in a simple try-catch.
The correct method to handle constraint violations
The proper way to handle preventing duplications against a table constraint is to simply check if the constraint may be validated prior that a brute force insert by querying the table e.g.
SELECT 1 FROM dbo.Country WHERE Name = @Name
If the user entered 'Brazil' and 'Brazil' already existed in table 1 is the returned value and if the country name does not exist the return value is null. If null is returned then proceed with the INSERT, otherwise report the issue back to the caller which may be a button click. If there is a duplicate and recognized as one the INSERT statement is never invoked thus the primary key sequence is not incremented.
C# Example
/// <summary>
/// Insert new record the right way by first determing if the country name
/// is present in the database.
/// </summary>
/// <param name="pCountryName">Country name to insert</param>
/// <param name="pIdentifier">New primary key</param>
/// <param name="pError">Error message on failure</param>
/// <returns>Success</returns>
public bool InsertCountry1(string pCountryName, ref int pIdentifier, ref string pError)
{
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn })
{
var selectStatement = "SELECT 1 FROM dbo.Country WHERE Name = @Name";
var insertStatement = "INSERT INTO dbo.Country (Name) VALUES (@Name);" +
"SELECT CAST(scope_identity() AS int);";
try
{
cmd.CommandText = selectStatement;
cmd.Parameters.AddWithValue("@Name", pCountryName);
cn.Open();
if (cmd.ExecuteScalar() != null)
{
pError = $"Country '{pCountryName}' already in table";
mHasException = false;
return false;
}
cmd.CommandText = insertStatement;
pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
return true;
}
catch (Exception ex)
{
mHasException = true;
mLastException = ex;
return false;
}
}
}
}
VB.NET example
''' <summary>
''' Insert new record the right way by first determing if the country name
''' is present in the database and will not increment the primary key sequence.
''' </summary>
''' <param name="pCountryName">Country name to insert</param>
''' <param name="pIdentifier">New primary key</param>
''' <param name="pError">Error message on failure</param>
''' <returns>Success</returns>
Public Function InsertCountry1(ByVal pCountryName As String, ByRef pIdentifier As Integer, ByRef pError As String) As Boolean
Using cn = New SqlConnection() With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand() With {.Connection = cn}
Dim selectStatement = "SELECT 1 FROM ForumExample.dbo.Country WHERE Name = @Name"
Dim insertStatement = "INSERT INTO dbo.Country (Name) VALUES (@Name);" & "SELECT CAST(scope_identity() AS int);"
Try
cmd.CommandText = selectStatement
cmd.Parameters.AddWithValue("@Name", pCountryName)
cn.Open()
If cmd.ExecuteScalar() IsNot Nothing Then
pError = $"Country '{pCountryName}' already in table"
mHasException = False
Return False
End If
cmd.CommandText = insertStatement
pIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
Return True
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Incorrectly handling constraint violations with UPDATE
What applies for inserting records applies for updating records too but generally will be with multiple columns for the constraint such as first and last name. In the script below for storing data form people a constraint UQ_Person1FirstLastName enforces against a record having first and last name that already exists in the table Person1.
USE [ForumExample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Persons1](
[id] [INT] NOT NULL,
[FirstName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NULL,
[GenderIdentifier] [INT] NULL,
[IsDeleted] [BIT] NULL,
CONSTRAINT [UQ_Person1FirstLastName] UNIQUE NONCLUSTERED
(
[FirstName] ASC,
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Rather than show incorrect methods to handle constraint violations (they are included in the source code) let's look at the proper method to handle constraint issues which use the exact same pattern as done with INSERT.
C# example
/// <summary>
/// Update person the right way by first determing if FirstName and LastName
/// will not produce a duplicate record or increment the next primary key sequence.
/// </summary>
/// <param name="pFirstName">First name to update</param>
/// <param name="pLastName">Last name to update</param>
/// <param name="pIdentifier">Identifying key for person</param>
/// <returns>Success</returns>
public bool PersonUpdate1(string pFirstName, string pLastName, int pIdentifier)
{
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
// see note 1 in information.txt
var statement = "SELECT 1 FROM dbo.Persons1 AS p WHERE p.FirstName = @FirstName AND p.LastName = @LastName ";
using (var cmd = new SqlCommand() { Connection = cn, CommandText = statement })
{
cmd.Parameters.AddWithValue("@FirstName", pFirstName);
cmd.Parameters.AddWithValue("@LastName", pLastName);
try
{
cn.Open();
if (cmd.ExecuteScalar() == null)
{
cmd.Parameters.AddWithValue("@id", pIdentifier);
cmd.ExecuteNonQuery();
return true;
}
else
{
return false;
}
}
catch (SqlException ex)
{
//
// Proposed values for update causing the exception
//
ConstraintValue = Regex.Match(ex.Message, "\\(([^)]*)\\)").Groups[1].Value;
/*
* See note 2 in Information.txt
*/
if (ex.Number == 2601)
{
ConstraintColumns = GetIndexKeys(cmd, ex.Message,"Persons1");
}
mHasException = true;
mLastException = ex;
return false;
}
catch (Exception ex)
{
mHasException = true;
mLastException = ex;
return false;
}
}
}
}
VB.NET example
''' <summary>
''' Update person the right way by first determing if FirstName and LastName
''' will not produce a duplicate record or increment the next primary key sequence.
''' </summary>
''' <param name="pFirstName">First name to update</param>
''' <param name="pLastName">Last name to update</param>
''' <param name="pIdentifier">Identifying key for person</param>
''' <returns></returns>
Public Function Update1(pFirstName As String, pLastName As String, pIdentifier As Integer) As Boolean
Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
Dim statement = "SELECT 1 FROM dbo.Persons1 AS p WHERE p.FirstName = @FirstName AND p.LastName = @LastName "
Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
cmd.Parameters.AddWithValue("@FirstName", pFirstName)
cmd.Parameters.AddWithValue("@LastName", pLastName)
Try
cn.Open()
If cmd.ExecuteScalar() Is Nothing Then
cmd.Parameters.AddWithValue("@id", pIdentifier)
cmd.ExecuteNonQuery()
Return True
Else
Return False
End If
Catch ex As SqlException
'
' Proposed values for update causing the exception
'
ConstraintValue = Regex.Match(ex.Message, "\(([^)]*)\)").Groups(1).Value
If ex.Number = 2601 Then
ConstraintColumns = GetIndexKeys(cmd, ex.Message, "Persons1")
End If
mHasException = True
mLastException = ex
Return False
Catch ex As Exception
mHasException = True
mLastException = ex
Return False
End Try
End Using
End Using
End Function
Error Number
Note that in the examples above there is a check for ex.Number - 2601, where does this comes from? The error number comes from master..sysmessages.
SELECT error ,
description
FROM master..sysmessages
WHERE msglangid = 1033 /* eng */
AND description LIKE '%insert%duplicate%key%'
ORDER BY error;
Results from the query
error description
2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.
2627 Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.
Summary
To implement constraints is easy while there are different views on how to implement assertion within an application. Usually when there is a great deal of code to handle this type of assertion the developer does not have a clear understanding of SQL capabilities and/or rather write lots of code just because they can. This thought process is done in an environment with a single developer or a team of developers will eventually bite them sooner or later.
When working with constraints or any type of assertions with SQL it’s best to first examine all possibilities to best resolve the problem. In this article, you are now equipped with a sound pattern to handle prevention of duplicate records with no side effects.
Source code
Following this link to GitHub project repo.
Building the solution
- Make sure SQL-Server, Express edition or better is installed.
- Run SqlContraintViolations/DataScripts/scriptCurrent.sql
- Change the property DatabaseServer in BaseServerConnections class.
See also
Unique Constraints and Check Constraints
SQL-Server Express Edition