Avoid resubmit / repeat the request when refresh the page

The explanation of the problem is as below.

Click the “submit” button on a form sends a request to the web server, which includes all the data entered on the form. Not only the URL but also the form data is part of the request, and this request is remembered by the browser. If the user use F5 or clicks “refresh” button, the browser repeats the request, sending the same URL and form data to the web server again. The most disadvantages of this issue is it will insert the same data into the database again.

Solution:
1. For html form
HTML forms can be submitted in two different ways, GET or POST, depending on the “method” attribute of the “form” tag. There is a convention that a GET request has no side-effects; it only fetches data but does not make any changes to the database. On the other hand, if a request changes data it should always use a POST request. As I said, these are only conventions, and there is not much technical difference between them, but a very important difference is that browsers will warn the user if they try to repeat a POST — clicking “refresh” will pop up a dialog box warning the user that this may cause an operation to be repeated, and confirming that they really want to resubmit. The browser does not show this confirmation when refreshing a GET request.

The better solution is the POST+REDIRECT+GET. This splits the database update (POST) and the view (GET) into two operations. Clicking refresh on the browser then merely repeats the GET, which has no side-effects.

2. Tony’s solution
For asp.net, as you just put the server control on the panel, and asp.net engine will wrap your controls to a default form, so you cannot control using get or post methods easily (maybe you can). But you still can follow the upper idea to resolve this issue. The basic idea is get the current url and add more request string as flags, the response.redirect the page.

Step 1: Create a function to generate the url which used for refresh


    Public Shared Function generateRefreshURL(ByVal url As String, ByVal CQID As Integer, ByVal InsertEditFlag As String) As String
        Dim returnstring As String = String.Empty
        Dim startposition As Integer = 0
        Dim endposition As Integer = 0
        If url.Contains("?cqid=") Or url.Contains("&ieflag=") Then
            startposition = url.IndexOf("?") + 1
            endposition = url.IndexOf("&sessionid=")
            url = url.Remove(startposition, endposition - startposition + 1)
        End If
        returnstring = url.Replace("?", "?cqid=" & CQID.ToString.Trim & "&ieflag=" & InsertEditFlag.Trim & "&")
        Return returnstring

    End Function

Step 2: apply the response.redirect to the submit button (save, cancel, insert etc.)

    Protected Sub gdvQuestionEdit_Save()

        Dim questionidstr As String = Me.CurrentQuestionID.ToString.Trim
        If Me.InsertEditFlag.Trim.ToLower = "Insert" Then
            loadOQPQuestions(_OQPSessionId.ToString.Trim, gdvQuestions, "insert")
        Else
            loadOQPQuestions(_OQPSessionId.ToString.Trim, gdvQuestions, "edit")
        End If

        Me.InsertEditFlag = "initial"
        Dim url As String = OQPController.generateRefreshURL(Request.Url.ToString(), Me.CurrentQuestionID, Me.InsertEditFlag)
        Response.Redirect(url, False)

    End Sub

Step 3: you may need to add some logic to handle the query string for refresh url.


    Private Sub OQPQuestions_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        If Not DesignMode Then
            _OId = Request.QueryString("sessionid").Trim
            _ODate = Controller.getODate(Integer.Parse(_OId))
            _verifiedstatus = Boolean.Parse(Request.QueryString("verifiedstatus").Trim)
            _l = Controller.getLByOID(CInt(_OId))

            If String.IsNullOrEmpty(Request.QueryString("cqid")) Then
                Me.CurrentQuestionID = 0
            Else
                Me.CurrentQuestionID = CInt(Request.QueryString("cqid"))
            End If

            If String.IsNullOrEmpty(Request.QueryString("ieflag")) Then
                Me.InsertEditFlag = "initial"
            Else
                Me.InsertEditFlag = Request.QueryString("ieflag")
            End If
        End If

        Me.Form.DefaultButton = Me.defaultfocus.UniqueID

    End Sub

3. Solution 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.

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

4. Solutions That Work By Trapping at the Database Level
Actually the fast and best performance is prohibit this duplicate insert at database side, but this solution only can prohibit the insert, update related to database, if you want to prohibit completely, or even didn’t want to see the pop up a dialog box warning the user that this may cause an operation to be repeated, you still needs to use the upper solution.

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

 

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]


CREATE PROCEDURE spAddEmployee_UsingSQLException
(
      @FirstName varchar(50),
      @LastName varchar(50)
)
AS
      INSERT INTO
            Employees
      (
            FirstName,
            LastName
      )
      VALUES
      (
            @FirstName,
            @LastName
      )

 


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

Performance consideration.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s