Updating database from text file

Here's a way we did it recently at work. A bit of a pain, but it works. What you do is, you first read the updated data to a datatable. You then iterate through the table with a foreach loop like so:

foreach(DataRow row in dataTable.Rows)
{
   row.Delete();
}

What this does, is it marks all of the rows for deletion.

You then call adapter.Update(dataTable);

This will delete the rows from your original source.

You then iterate through all the rows again, and call row.RejectChanges() on the updated datatable.

Now you just proceed to insert all the records in the updated datatable back to the original data source.

Makes sense?

It is different that what you are doing but it seems always to work so I generally use a stored proc that will handle inserts or updates depending upon whether or not the unique ID already exists; here is a very simple example.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[spInsertUpdateInjury] (

 

      @InjuryID                                    INT,

      @InjuryText                                  VARCHAR(50)

)

AS

SET NOCOUNT ON

 

IF EXISTS(SELECT 1 from Injuries WHERE InjuryID=@InjuryID)

UPDATE TblInjuries

SET

      InjuryText=@InjuryText

WHERE

      InjuryID =@InjuryID

ELSE

INSERT Injuries

(

      InjuryText

)

VALUES

(

      @InjuryText

)

SET NOCOUNT OFF