I have a Windows application that I'm developing using Visual C# 2008 Express Edition. The application downloads a text file from the internet and then updates records in a SQL Server Compact Edition 3.5 database.
The format of the text file looks like this.
3187,Someone,OM,1,1,2475,2475,1026
18471,Another+one,%7EE4E%7E,3,3,3207,3207,931
18377,DUDES,%3Du0%3D,5,5,2250,2250,1066
18382,DESTROYERS,DSTRY,1,1,68,68,3932
The database table that this is being updated/inserted into is like this,
id int primary key
name nvarchar(255)
tag nvarchar(50)
for smallint
against smallint
total int
max int
rank int
So far I've successfully got the code to download the file, put the records into a DataTable, set the id column of the DataTable to the Primary Key, add the table to a DataSet and then use a SqlCeDataAdapter to insert the records. This all works great.
If I download the file the following day, I want to update the existing records, delete ones that have been removed from the file and insert any new records.
The problem I have is that when I execute the update method of the DataAdapter it trys to insert all the records again as the RowState in the DataTable says the record was Added.
It says,
"The Update method resolves your changes back to the data source; however other clients may have modified data at the data source since the last time you filled the DataSet. To refresh your DataSet with current data, use the DataAdapter and Fill method. New rows will be added to the table, and updated information will be incorporated into existing rows. The Fill method determines whether a new row will be added or an existing row will be updated by examining the primary key values of the rows in the DataSet and the rows returned by the SelectCommand. If the Fill method encounters a primary key value for a row in the DataSet that matches a primary key value from a row in the results returned by the SelectCommand, it updates the existing row with the information from the row returned by the SelectCommand and sets the RowState of the existing row to Unchanged. If a row returned by the SelectCommand has a primary key value that does not match any of the primary key values of the rows in the DataSet, the Fill method adds a new row with a RowState of Unchanged."
This doesn't appear to be the case for my situation. I call the Fill method before the Update method and when I check the RowState of a row that exists in the database it is still set to Added.
I have 4-5 files that I'd like to process utilising this same method, all the file/table structures are roughly the same. Record counts vary from 5,000 to 40,000 in a single file.
If someone has any idea of how to overcome this problem I'd be most grateful. Alternatively, if you can suggest a different method that would work better, feel free to enlighten me.
Thanks all.