[Download Code]
Should the user somehow manage to circumvent the two solutions described above, the last line of defense is at the database. There are two methods that can be employed to prevent a duplicate record from being inserted into the database. For each method, I've moved the SQL code into a stored procedure, since there are now more processing steps involved and these are easier to illustrate in a separate stored procedure. Note however that a stored procedure is not strictly required in order for these methods to work.
The first method is to check whether the record exists in the database table before inserting it. If the record does exist then the user will receive an error message. The stored procedure in Listing 4 first does a lookup in the Employees table to see if the supplied FirstName and LastName combination already exists in the table. If it does then a -1 result will be returned to the calling code. If it does not, then an INSERT is attempted and the @@ERROR value is returned to the calling code. The code in Listing 5 checks the stored procedure's RETURN value and displays a corresponding message to the user.
Listing 4 – Stored Procedure spAddEmployee_UsingExists
CREATE PROCEDURE spAddEmployee_UsingExists
(
@FirstName varchar(50),
@LastName varchar(50)
)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
SELECT
NULL
FROM
Employees WITH (UPDLOCK)
WHERE
FirstName = @FirstName AND
LastName = @LastName
)
BEGIN
SELECT @Result = -1
END
ELSE
BEGIN
INSERT INTO
Employees
(
FirstName,
LastName
)
VALUES
(
@FirstName,
@LastName
)
SELECT @Result = @@ERROR
END
IF @Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @Result
Listing 5 – Server-Side Code Using Exists
Sub Button1_Click(sender As Object, e As EventArgs)
Dim addResult As Integer = 0
addResult = AddEmployee(firstName.Text, lastName.Text)
Select addResult
Case Is = 0
Message.Text = "Success"
Case Is = -1
Message.Text = "Failure - record already exists"
Case Else
Message.Text = "Failure"
End Select
firstName.Text = ""
lastName.Text = ""
End Sub
Function AddEmployee(firstName As String, lastName As String) As Integer
Dim connectionString As String
connectionString = "server='(local)'; trusted_connection=true; database='Northwind'"
Dim dbConnection As New SqlConnection(connectionString)
Dim dbCommand As New SqlCommand
dbCommand.CommandText = "spAddEmployee_UsingExists"
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.Connection = dbConnection
dbCommand.Parameters.Add(New SqlParameter("@FirstName",SqlDbType.NVarchar,10))
dbCommand.Parameters("@FirstName").Value = firstName
dbCommand.Parameters.Add(New SqlParameter("@LastName",SqlDbType.NVarchar,20))
dbCommand.Parameters("@LastName").Value = lastName
dbCommand.Parameters.Add(New SqlParameter("@Result",SqlDbType.Int))
dbCommand.Parameters("@Result").Direction = ParameterDirection.ReturnValue
Dim commandResult As Integer = 1
Try
dbConnection.Open
dbCommand.ExecuteNonQuery
commandResult = CType(dbCommand.Parameters("@Result").Value,Integer)
Catch ex AS SqlException
commandResult = ex.Number
Finally
dbConnection.Close
End Try
Return commandResult
End Function
The second method is to make use of the database table's ability to enforce a unique constraint. To add a unique constraint on the Last Name and First Name columns of the Employee table, run this SQL command in Query Analyzer:
CREATE UNIQUE INDEX [LastFirstUnique] ON Employees ([LastName], [FirstName])
With this method, there is no preliminary check to see if the record already exists; just let the database return an error when it tries to insert a duplicate record and check for this exception. This method of course requires that the database allows for unique constraints. With SQL Server, when the constraint is violated, error code 2601 is raised and returned to the calling code. Note that the stored procedure has been stripped of its initial EXISTS check. The disadvantage of this approach is that relying on exceptions for programming logic is considered to be a bad practice.
To remove the unique constraint on the Employee table created above, run this SQL command in Query Analyzer:
DROP INDEX [dbo].[Employees].[LastNameFirstNameUnique]
Listing 6 – Stored Procedure spAddEmployee_UsingSQLException
CREATE PROCEDURE spAddEmployee_UsingSQLException
(
@FirstName varchar(50),
@LastName varchar(50)
)
AS
INSERT INTO
Employees
(
FirstName,
LastName
)
VALUES
(
@FirstName,
@LastName
)
Listing 7 – Server-Side Code Using SQLException
Sub Button1_Click(sender As Object, e As EventArgs)
Dim addResult As Integer = 0
addResult = AddEmployee(firstName.Text, lastName.Text)
Select addResult
Case Is = 0
Message.Text = "Success"
Case Is = 2601
Message.Text = "Failure - record already exists"
Case Else
Message.Text = "Failure: " & addResult.ToString()
End Select
firstName.Text = ""
lastName.Text = ""
End Sub
Function AddEmployee(firstName As String, lastName As String) As Integer
Dim connectionString As String
connectionString = "server='(local)'; trusted_connection=true; database='Northwind'"
Dim dbConnection As New SqlConnection(connectionString)
Dim dbCommand As New SqlCommand
dbCommand.CommandText = "spAddEmployee_UsingSQLException"
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.Connection = dbConnection
dbCommand.Parameters.Add(New SqlParameter("@FirstName",SqlDbType.NVarchar,10))
dbCommand.Parameters("@FirstName").Value = firstName
dbCommand.Parameters.Add(New SqlParameter("@LastName",SqlDbType.NVarchar,20))
dbCommand.Parameters("@LastName").Value = lastName
Dim commandResult As Integer = 1
Try
dbConnection.Open
dbCommand.ExecuteNonQuery
commandResult = 0
Catch ex AS SqlException
commandResult = ex.Number
Finally
dbConnection.Close
End Try
Return commandResult
End Function
