Monday, August 20, 2012

Avoid Inserting Duplicate Record on Page Refresh

When asked the question, "How do I prevent previously submitted form data from being reinserted into the database when the user presses the browser's Refresh button?" my instinctive response is, "Why on Earth would the user be pressing the Refresh button?" However, if you have been in application development for any length of time, you will know that end users have no limit to their creative approaches to application navigation. And they will indeed press that Refresh button even though there is no logical reason to do so. So you need to program defensively to handle the problem of a Refresh request.
To duplicate this problem, I set up a simple ASP.NET page that collects first name and last name data and, upon the click of a submit button, inserts it into the Employees table in SQL Server 2000's Northwind database. The default behavior of such a page, without any special programming, is to submit the form data upon the button click, insert the data into the database, and bring the user back to the page with the first name and last name textboxes still populated. Pressing the form's submit button again will reinsert the same data into the database, as will pressing the browser's Refresh button. One would reasonably expect that pressing the submit button again would reinsert the same data into the database; however, this is not the behavior one would expect with the Refresh button.
Listing 1 - Simple Web Form Used for All Tests
<html>
<head>
   <title>Preventing Duplicate Record Insertion on Page Refresh</title>
</head>
<body>
   <form runat="server">
      <p>First Name <asp:TextBox id="firstName" runat="server" />
      <p>Last Name  <asp:TextBox id="lastName" runat="server" />
      <p><asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Add Employee" />
      <p><asp:Label id="Message" runat="server" />
   </form>
</body>
</html> 
The purpose of this article is to discover how to prevent the data from being reinserted into the database when the browser's Refresh button is pressed.
Ideas That Did Not Work

Before we look at solutions that do work, let's look at ideas that did not work. 
Delving into troubleshooting mode, the first idea I had was, "Well, blank out your form elements and you should be good to go'.  But wait, not so fast.  Due to the nature of web pages and posted data, the form values are still held by the browser.  A refresh of the page will post the data again and the data will wind up in your database an additional time.  This has nothing to do with ASP.NET in particular; it is a built-in browser behavior.  The same thing can happen with PHP, ASP, and any other type of web page.
My next idea was to use a hidden field on the form that would be set to an initial value on the first page load.  Before the database insertion is performed, that hidden field would be evaluated and if it contains the expected initial value then the insert would be permitted.  Following the insert the hidden field would then be set to a different value.  However, this again does not work because with the refresh the form's previously posted data is resent, and the hidden field still contains that initial value.
Pulling the next trick out of the bag, I changed the above approach to use a Session variable instead of a hidden form field.  And lo and behold this approach worked.  It actually worked so well that once one record was successfully inserted into the database, no more could be added, even intentionally, since once that Session variable was set there was no way to know when it was okay to reset it to the initial value.  This is not a feasible solution because a web application would likely need to write more than one record to the database.
The last approach I tried without success was disallowing the caching of the page on the user’s browser.  This is accomplished by adding the following to the Page.Load handler:
    Response.Cache.SetExpires(DateTime.Now.AddDays(-1))
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Response.Cache.SetValidUntilExpires(false)

The intent here was to force the user's browser to call for a fresh version of the page each time.  However, this had no effect on the posted data in the header, and the Refresh button still had the effect of reposting the form data.
Now that we have considered a few ideas, and found that they do not work, let's look at some solutions that do work.
Solutions That Work by Clearing the Header

A simple solution is to Response.Redirect back to the same page after the INSERT command is called.  This will call up the page without transmitting any post headers to it.  Using Request.Url.ToString() as the first parameter of Response.Redirect will cause both the URL and the page's querystring to be included in the redirect.  The use of false as the second parameter will suppress the automatic Response.End that may otherwise generate a ThreadAbortedException.  A disadvantage of this approach is that any ViewState that had been built up will be lost.
Listing 2 – Server-Side Code Using Response.Redirect
Sub Button1_Click(sender As Object, e As EventArgs)
    If AddEmployee(firstName.Text, lastName.Text) = 0
        Message.Text = "Success"
        Response.Redirect(Request.Url.ToString(), false) ' will include the querystring
    Else
        Message.Text = "Failure"
    End If
    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 insertString As String
    insertString = "INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName)"
    Dim dbCommand As New SqlCommand
    dbCommand.CommandText = insertString
    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 rowsAffected As Integer = 0
    Dim commandResult As Integer = 1
    Try
        dbConnection.Open
        rowsAffected = dbCommand.ExecuteNonQuery
        If rowsAffected > 0 Then commandResult = 0
    Catch ex AS SqlException
        commandResult = ex.Number
    Finally
        dbConnection.Close
    End Try
    Return commandResult
End Function
As a side note, I thought that Server.Transfer would be a more lightweight alternative to Response.Redirect; however this method does not clear the post data from the header and hence does not solve the refresh problem.
A related approach would be for the form to submit to an intermediate processing page and then Response.Redirect back to the calling page, similar to the classic ASP approach to form processing.  This has the same effect as simply using the Response.Redirect in the Button_Click event so it has the same disadvantages, with the added disadvantage of creating another page for the website developer to manage.
Solutions That Work by Detecting the Browser's Refresh

The next batch of solutions works by determining whether the user has refreshed the page in the browser instead of pressing the form's submit button.  All of these solutions depend on the ability of the website to use Session variables successfully.  If the website uses cookie-based Sessions, but the user's browser does not permit the use of cookies, these solutions would all fail.  Additionally, should the Session expire these solutions would also fail.
A simple way to implement refresh trapping is by the use of a date/time stamp held in a ViewState variable and a date/time stamp held in the user's Session.  When the page is first loaded, a Session variable is populated with the current date/time.  On the page's PreRender event, a ViewState variable is set to the value of the Session variable.  These two values are compared to each other immediately before the database INSERT command is run.  If they are equal, then the command is permitted to execute and the Session variable is updated with the current date/time, otherwise the command is bypassed.  Should the user refresh the page, the ViewState variable will be repopulated from the post header, so then the ViewState and Session variables will no longer hold the same values, and the INSERT command will not run.  Note that ViewState needs to be enabled on the page for this to work; if ViewState is not enabled then a hidden form field may be used instead.
Listing 3 – Server-Side Code Using Session and ViewState Variables
Sub Page_Load (sender As Object, e As EventArgs)
    If Not Page.IsPostBack
        Session("update") = Server.URLEncode(System.DateTime.Now.ToString())
    End If
End Sub

Sub Page_PreRender (sender As Object, e As EventArgs)
    ViewState("update") = Session("update")
End Sub

Sub Button1_Click(sender As Object, e As EventArgs)
    If Session("update").ToString() = ViewState("update").ToString() Then
        If AddEmployee(firstName.Text, lastName.Text) = 0
            Message.Text = "Success"
            Session("update") = Server.URLEncode(System.DateTime.Now.ToString())
        Else
            Message.Text = "Failure"
        End If
    Else
        Message.Text = "Failure - Session"
    End If
    firstName.Text = ""
    lastName.Text = ""
End Sub

A similar approach can be seen in Dino Esposito's article, titled Build Your ASP.NET Pages on a Richer Bedrock.  This article makes some suggestions on creating a custom base Page class that includes, among other things, trapping the browser refresh.  His methodology is a lot more complex than what I describe above, and involves the use of a hidden form field and a Session variable.  The advantage it has is that the code extends the Page class, so the only code that is needed is a simple check of the Boolean property named IsPageRefresh before the INSERT command is executed.  An additional advantage is that the page refresh can be trapped for a variety of scenarios, not just for the prevention of duplicate records which we are concerned with here.
Solutions That Work By Trapping at the Database Level

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();
        }
    }

No comments:

Post a Comment