Re: FoxPro to C#: What best method between ODBC, OLE DB or another?
@Arjay - FoxPro is a Jet database, so the classes in the System.Data.SqlClient namespace won't work. He will need to use classes in the System.Data.OleDB or System.Data.Odbc namespaces such as OleDbConnection/OleDbCommand.
If you have or can obtain a native OleDB provider for FoxPro 8 (I know there is one for FP9) I would suggest OleDB over ODBC. Native connectivity, where available, is always going to be faster and more efficient than going through the ODBC connectivity layer.
The mdb file, while much better and more stable than the flat files early versions of FP used, are more suceptable to corruption at the file level than SQL Server. No one can guarantee no data corruption, but your FP app is more likely to corrupt the data than your C# export program. As far as locking during reads, that is up to the access locking paradigm used in FP, not your C# code.
I would suggest...
Use OleDB if you can, it should be more performant than ODBC.
Use transactions on the SQL Server side when writing the data, to ensure record level atomicity.
Use verbose logging to note any record import that has problems and wasn't sucessfully imported (log the actual data, as well as, the reason(s) why there was a problem). This will give you a good basis for fine tuning the process as unforseen data problems are encountered. It may also give you the capability of manually importing problem data once it is fixed.
Keep your code as simple and streamlined as possible. Depending on the amount and complexity of the import data, these kinds of export/import operations can have long run times (you're really not going to know until you can do a trial run).