I need an approach to the problem of preventing inserting duplicate records into the database

Question

A webservice that I call returns a list of data. The data from the webservice is updated periodically, so a call to the webservice done in one hour could return the same data as a call done in an hour. Also, the data is returned based on a start and end date.

We have multiple users that can run the webservice search, and duplicate data is most likely to be returned (especially for historical data). However I don't want to insert this duplicate data in the database.

I've created a db table in which the data is stored (most important columns are)

Id int autoincrement PK  
Date date not null        --The date to which the data set belongs.  
LastUpdate date not null  --The date the data set was last updated.  
UserName varchar(50)      --The name of the user doing the search.

I use sql server 2008 express with c# 4.0 and visual studio 2010. Entity Framework is used as the ORM. If stored procedures could be avoided in the proposed solution, then that will be a plus.

Another way of looking interpreting what I'm asking a solution for is as follows: I have a million unique records in my table. A user does a new search. The search results from the user contains around 300k records of the data that is already in the db. An efficient solution to finding and inserting only the unique records is needed.

A combination of the Date, LastUpdate and UserName makes a record unique.

Answer 1

Well, the obvious solution is to have a unique key on the columns that make the row unique.

A combination of the Date, LastUpdate and UserName makes a record unique.

Alternatively, you might just get rid of the surrogate key and use the above as the primary key (depends upon where else you are using it).

Inserts should be done using Merge, which will allow you to insert the record only when it doesn't already exist.

Answer 2

You have to find out what columns records make unique. Then you usually set a unique constraints on one/multiple columns in the database. This will (depending on db-system) throw some error if you want to insert another record with the same unique columns.

See http://en.wikipedia.org/wiki/Unique_key

Answer 3

Another approach: Add a column to your database, "Hash". It's a SHA-256 or the like--something big enough to be unique. Require that the column be unique.

Answer 4

Try this:

IF (SELECT TOP(1) text FROM the_table WHERE text='yourtext') <> 'yourtext'
begin
 INSERT INTO the_table (text) VALUES ('yourtext')
end

it queries the DB for yourtext and selects it, if it finds a row, it won't fire, if it doesn't find a row, it will insert.

Not reall well tested though.

Answer 5

A simple solution would be to create an in-memory cache supported by a set, which should write the information in the database, only when it's full, and say only 10000 entries leaving room for other 10000 (see it as a queue). Or store it to the DB when the WS call returns different data (provided that different data in calls means that there are no duplicates in it). By using a set, you are assured that no duplicates are present in it.

Answer 6

Your client could track which rows have changed (created/updated/deleted) and only send those changed rows to the server. With a unique key defined, you can perform inserts for the rows identified by your client as new. This way, you only have to send the changed (created/updated/deleted) rows to the database. No need to send the entire data set retrieved if it has not changed. This type of processing is available in EF via the ObjectContext, ObjectStateManager classes.

Answer 7

I've settled on the following solution.
On my sql server table, I added an index. Check the image for the settings

In visual studio, I updated my eddmx (Entity framework file), but when I tried to insert duplicate records, all I got was another error :

System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

So I created a simple stored procedure to update the entity. Added the stored procedure to my edmx file and called that from my code and things are working now and duplicates are ignored.

heres a link that helped me find a solution
http://sqlkit.com/2009/06/17/
http://msdn.microsoft.com/en-us/library/x480ck3b.aspx

Answer 8

Easy,

1. Create a unique name or number for every client that access the db ws.
2. Create a Table named "Downloaded" and insert a row every time a client access the ws and retrieve rows from the database. This row will contain the name of the client and the main table's row unique id.
3. The next time you access the ws for data, alter the select to include as o join, only the rows that were not "downloaded" by this user.

Got it?