I have a situation where I receive a set of ASCII files daily. The ASCII files are not a standard CSV or Tab limited record set. I must look for specific characters that may or may not exist in each record and translate them into pipes (|) to create a uniform delimited file. There are other items I have to do to prep the data also. I've said all of that to point out the fact that a straight Import is not an option for me. The files must be programmatically cleaned up prior to any import. Plus, all importing must be done in the evening while no users are on the system.

In Foxpro, I can read one of the ASCII files that is around 600,000 records, prep the data, and import into a DBF in approximately 5 mintues; 10 minutes at the most. I am migrating all of my applications into .NET using ASP and VB. On this particular application, I'm using VB.NET converting the ASCII file and importing into SQL Server. The program is running as I type this message and as best as I can tell, it will take 6 hours or more to complete.

My Question: Is VB.NET and SQL that much slower than Foxpro?

Preparing the ASCII file for import is not a problem. That process is fine. What kills the performance is reading the prepared data and sending it to SQL.

(1) I have a Data Layer that holds a TableAdapter for the Target Table

(2) I have a Class called BLL that contains functions for Adding and/or Updating the record

(3) I use a Stream Reader to access the Converted ASCII file delimted by Pipes (|).

(4) Each record is thrown into an Array to separate my data fields

(5) Once I have my data fields identified, I send this information to a Function in the BLL class that sends the data to the TableAdapter in the Data layer.

I am new to the .NET world and have spent most of the past year reading, studying, and reviewing message boards like this one to determine the best way to develope applications. The concept of separating the presentation, from the data, and business logic made a lot of sense to me so that is how I'm converting all of my applications. However, the performance hit I'm seeing is terrible. Again I ask, is this the normal speed for SQL and VB.NET or is my design way off.

I'm open for suggestions and if you have sample code that reads ASCII data and uploads to SQL, please present that also.

Thanks for any help.