I would appreciate some input and advice on the following situation.
I have a number of data files which I need to place the data into my
database. Much of the data in the files are, of course, related and I
need to validate the data before putting it into the database. An
additional requirement is that I need to report what lines in these
files are incorrect and kick those out with a reason.
My idea is to do the following. Create a data set of the database
schema, verify the data by attempting to load the data into those
tables, and report any errors. Then, import the correct data into the
database using bulk copy.
In .net 1.1 it seemed that DataSet/DataTable couldn't handle large
amounts of data, and I heard that .net 2.0 has fixed this. So, perhaps
I can load all of the data into the DataSet at once, else, I can either
commit data to the database X amount at a time. OR, I could validate
the data, and remove the data from the DataSet. Then import all the
data into the database that is valid.
Does anyone see a problem with this or have a better solution? Thanks.