I need to create an Bulk upload utility using ASP.Net and SQL Server. Below is the process for the uploads -
Excel Template wherein user will enter the details. A Tab-delimited output file will be generated using the VBA.
There are 2 tables - one is Temp Table which is replica of the the final table and second is the final table
Using File.OpenText(filePath).ReadLine() - All the Rows from the tab delimited data file will be inserted into DataTable.
using SQLBulkCopy the tab-delimited data file data will be inserted into the Temp Table.
Data will be validated based on the data inserted in the temp table. If the data as errors then the temp table will be cleared else the data will be inserted from the temp table to the final table.
My Issue is that in both the tables there is a column (Name : PeopleKey (Int PrimaryKey)). If the user enters Alphabetic value then the Bulk Utility is failing. Below are the two options in my mind -
1. I can change the DataType in Temp table from int to VARCHAR. So, the data can be inserted at first and then I can validate and get the data corrected. But i am not sure whether it is the right way to fix issue as the source and target tables columns are different.
2. When the data in inserted into the Datatable by following Step 3. So, once the data in inserted into DataTable then i can validate there. Thus the source and target tables Datatype will be same.
Please guide me about the above approach for designing the table.
The general principle of data validation , is that it should be done as close to the source as possible.
In your case that means the Excel template. It should contain as much tests to validate the data as possible. That should already stop users from entering invalid characters in numeric or date columns.
The next validation should happen at the bulk insert into the table, when that fails, the whole file should be rejected. Here happens the check on the data types. I would use a temp table with the same data type columns as in the final table.
The next validation can be done on the records in the temp table. These tests should be a superset of the tests in the Excel template, except the tests on data types.
It is up to you to allow only the valid records into the final table, or reject the whole set after finding at least one problem..
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages