Automated Table Updates - Strategy Assistance Requested

[Jeff]

I'm actually stuck! Can't believe it... so I'd appreciate some help.

What I'm doing is enabling users of an ASP.NET 1.1 Web application to update
a table in a SQL Server 2000 database. To make their life easier and to
automate the enforcement of validation rules, I'm enabling users to maintain
an Excel file on their local machine that has columns that match those in
the table in the database. When the users want to update the table in the
database, they upload the Excel file where I have logic that then validates
the content and updates the table in the database. Specifically I need for
the table in the database to exactly match the Excel file contents when this
update operation is completed. The columns and data types won't be
changing - only the data in the table.

Upon receiving the Excel file on the Web server, I currently have logic that
reads the Excel file contents into an untyped DataSet. Subsequent logic then
validates the uploaded data. Only if all data data complies with the
validation rules will the data then be transferred to the database.

So far so good. But this is where I'm stuck.

I want to next update the data in the database table with the data in the
untyped DataSet - but I don't want to loop through every row (up to 2500
rows) in the DataSet, as not all rows will be changed.

The following strategy did not work (but shows the sort of thing I'd like to
be able to do):
1. Create/populate DataSet from Excel data (then validate it)
2. Retrieve DataSet populated from SQL db table
3. .Merge() the two DataSets
4. Identify all the rows, and only the rows, that are different - per
DataSet.GetChanges()
5. Send all the changes and only the changes to the database via a
DataAdapter's command objects (e.g., InsertCommand, UpdateCommand,
DeleteCommand) - each of which would call a stored procedure and pass it
parameters to do the actual update.

This strategy does NOT work because merging the two DataSets does not
automatically set the DataRowState value to whatever it would need to be in
order to identify the modified/new/deleted rows (as the RowState of each row
in each DataSet is of course"unchanged" -- meaning the GetChanges() method
won't recognize any row as changed).

Hopefully I'm just missing something obvious about the merge operation.
PLease note that the [preserveChanges] argument to the Merge() method does
nothing to help me here because, again, the original value of RowState is
unchanged... so preserving that value is of no help.

So, any ideas? I don't mind going with a completely different approach. I
primarily want to avoid looping through *every* row and passing all values
to a SP that then determines if an insert or update is required, and then
have separate logic to handle deletes. That just seems like such an
inefficient way to go.

Thanks!

 

[Tapio Kulmala]

You could send the whole dataset ( from step 1) to a storedprocedure as
XML, and let that sp take care of it.

The sp can update existing records, add new records and delete
unnecessary records (propably without a cursor).



****************************************************************
Tapio Kulmala

"Those are my principles. If you don't like them I have others."

- Groucho Marx
****************************************************************

The following strategy did not work (but shows the sort of thing I'd like to
be able to do):
1. Create/populate DataSet from Excel data (then validate it)
2. Retrieve DataSet populated from SQL db table
3. .Merge() the two DataSets
4. Identify all the rows, and only the rows, that are different - per
DataSet.GetChanges()
5. Send all the changes and only the changes to the database via a
DataAdapter's command objects (e.g., InsertCommand, UpdateCommand,
DeleteCommand) - each of which would call a stored procedure and pass it
parameters to do the actual update.

--