AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=687&pId=-1
Preventing Duplicate Record Insertion on Page Refresh
page
by Terri Morton
Feedback
Average Rating: 
Views (Total / Last 10 Days): 325518/ 452

Introduction

[Download Code]

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

[Download Code]

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

[Download Code]

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

[Download Code]

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

[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

My Recommendation

[Download Code]

Rather than relying on anecdotal evidence for a final recommendation, I set up an Application Center Test (ACT) for each of the successful scenarios described earlier in the article.  ACT is a web application analysis tool included with Visual Studio .NET Enterprise Edition.  It allows you, among many other things, to perform stress tests on your web application by simulating multiple browser sessions submitting multiple requests.  I set up four tests in ACT, each simulating 50 simultaneous users doing the following 1000 times: adding a record and then pressing the Refresh button.  I ran these tests five times and calculated the average process time for each test.

Figure 1

Approach Tested Average Time to Process Test
Using Response.Redirect 11 seconds
Using Session and ViewState Variables 12 seconds
Using EXISTS 8 seconds
Using SQL Exception 8 seconds


As you can see by the chart in Figure 1, the best-performing solutions were the database trapping approaches.  This truly took me by surprise, as the whole reason I investigated approaches that did not involve database access was that I assumed the database hit would be expensive enough to warrant a creative programmatic solution.

Comparing the database trapping approaches, I do not see the SQL Exception trap as a truly viable solution.  As was discussed above, building application logic around exceptions is not good programming practice.  There should certainly be exception handling code regardless, but all exceptions should be handled as true unexpected errors.

Taking all things into consideration, the approach that I would use to prevent previously submitted form data from being reinserted into the database when the user presses the browser's Refresh button is to check the database for the existence of the record prior to attempting to insert a new record.  This is the simplest, best-performing, and most reliable solution I discovered.



©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-06-18 1:22:20 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search