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
Final My recommendation to use session variable for full proof:-
1. How to its work
2. How to write a code
1. How Its Work:- first we comes on our page this will check the session variable
is exists on our page if its not exists then only this will insert the record
after insertion we will set session variable
2. How to write a code:- initially while we come on the insertion screen we check
the session is exists then we will remove from it. and on the insertion we first
check that session is exists on not, if its not exists (null) then we only execute insert
statement and reset the session variable to any string for Example check my code
protected void btnNewAdd_Click(object sender, EventArgs e)
{
mvDepartment.SetActiveView(vAdd);
Session.Remove("Inserted");
}
protected void btnInsert_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (Session["Inserted"] == null)
{
//Write Insert code here
Session["Inserted"] = "Inserted";
}
}
}
Another Way to Execute the code if you don't have additional layer(add button)
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
Session["Inserted"] = "Inserted";
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (Session["Inserted"] != null)
{
//Write Insert Code here
Session.Remove("Inserted");
}
BindData();
}
}
