What's the best method to stop duplicate inserts into the database

Question

I have a forum application using asp.net, c#, sql server and linq to sql. It suffers from multiple inserts, I think when the insert button is pressed but also maybe when the browser is refreshed or back is clicked.

What's the most comprehensive way, or ways to prevent this.

UPDATE: I currently use response.redirect after inserting text. I think maybe the correct approach, upon reading the responses, is to disable the button immediately with javascript? Followed by saving unique value on hidden field (or session?) on the form. Create a field in the db for this value and check this unique value against this field?

Thanks

Answer 1

1. Assign a UNIQUE cookie or hidden control to each page
2. Create a UNIQUE field in your table that holds this cookie or control value.
3. If a record is inserted with the same cookie, the INSERT will fail and you can process (or ignore) this condition.

Answer 2

Select a natural primary key from one or more columns of the input data stream. Declare a primary key constraint on the table, using these columns. If you already have a primary key constraint on a surrogate key, and you don't want to remove it, declare a UNIQUE constraint on the natural key instead.

This will cause an error when the application attempts to insert a duplicate row into the table. You will however have to program the app to field the error, and do the right thing.

Answer 3

When I've a page that does inserting or updating I typically do a Response.Redirect to another page after the update is completed, even if you just Response.Redirect to same page. That way if the page is refreshed you aren't re-posting your data.

Answer 4

Note that your problem isnt really specific to your database. You have a problem with multiple browser requests beeing sent, and you must get around that issue. Either use the redirect pattern as stated above by Dave or use Cookie signing

One example can be found here http://aspalliance.com/711_Preventing_Duplicate_Record_Insertion_on_Page_Refresh_or_Postback_of_a_Web_Form

Answer 5

use a primary key or unique constraint, if you can't do that I suggest you use the new MERGE statement which will insert if it doesn't exist or update if it does exists (don't know your business rules so that might not work in your situation) MERGE is SQL Server 2008 and up

Answer 6

Assign a unique ID in the form for the comment, and record it with the comment in the database. Perform a check on comments with that unique ID before performing your insert if it doesn't exist.

To allow a user to have several replies on a site active at once, you could allocate the token server-side initially, put that into a list of active posting tokens on the session for that user, put it into the hidden field on the comment form, and only allow a form submission to insert into the database if that token is in the list on the session (and remove it from the list on the session after successfully inserting into the database). This saves you on ever storing the posting token in the database as above, which is a little easier but tatty.

Answer 7

Put a unique index on the natural key of the data to prevent duplicates from being entered. If required put it against all the fields of the table (except any date oriented ones that would receive the system date at the time of the insert or fields used only by the back end such as insertedby which would contain the user id of the person doing the insertion)except the id.

Answer 8

Check out this ASP.NET AJAX control called PostBack Ritalin from a fellow SO'r Dave Ward. This could help you with an already tested solution to your javascript disabler idea.