what is the best way to upload a csv file into a MS SQL table?

Several approaches:

1. Use SQL Bulk Import Stored Proc and call the stored proc with the file path
2. Use Sql开发者_开发技巧BulkCopy in System.Data.SqlClient dll
3. Read the file line by line and then insert into a table row by row
4. Any other ways?

Which one is best? I just want the user to select a file from asp.net webpage. And then click on Upload button to store the file in DB.

Secondly, do I need to move the file in server's memory before the file is copied into db table?

1. The DB won't know of the file because everything should be decoupled and layered. Saving the file to some shared location adds overhead and tidy ups etc

2. Yes

3. Surely you want this to be an atomic operation. 10k rows would be 10 round trips with a client side transaction running. If not atomic, then you'd need staging tables and tidy ups

4. Parse in c#, send to the DB with a table valued parameter. Otherwise, probably nothing same and/or realistic...