CODEWITHSUNDEEP
c#sqlsql-server
VAAA
VAAA

Reputation: 15039

Best way to save Excel row reference

I have a C# application with a SQL Server database.

One functionality of the application is to read a big Excel file and go row by row trying to match an ID column with an ID in the database. Once a row matches, then some of the values are stored in a table.

Later the user is able to change the value on that table but I would like have a way to audit the original values. So I was thinking on creating an XML column in that table that will store the original values of the whole Excel row.

I don't need to implement audit trail to the table, just want to keep the Excel reference row in my SQL Server table for future audit.

Any advice?

Upvotes: 1

Views: 49

Answers (1)

Xavier J
Xavier J

Reputation: 4728

External storage of the old value is too hard to maintain, in the long run.

All you need to do is add an "OriginalValue_[fieldname]" column in the SQL table for each value that the user can change. When you populate the table, put the same value in [fieldname] and [OriginalValue_fieldname]. When the user makes changes, you only change [fieldname] but the old value is right in the table.

If this won't work (i.e. you're not allowed to make schema changes), make another table with the ID fields and the original values. Same approach -- no external data.

Upvotes: 1

Related Questions