CODEWITHSUNDEEP
c#sqlsql-server
Encryption
Encryption

Reputation: 1897

SQL Server data import dilemma

I have created an application that will be importing CSV files into a database table, and I've got multiple CSV files I need to import into a table in a SQL Server database.

I've got a couple approaches in mind but I'm not sure which is most practical. The application works by asking the user to select the files they want to import (from their local file system) and then they simply click a [Load Files] button. These files may contain 100,000+ rows at times.

What would be better for the above scenario?

  1. Import CSV file into datatable using C# and open-source GenericParser then using a traditional method of BulkCopy to push the datatable to the database

    Note: my concern is the strain on users PC when doing this for files with 100,000+ rows. How will this affect the processing or would it crash the program?

  2. Use Bulk Insert which requires the file name and path. My concern for this option is I'm not sure if the server would be able to process the Bulk Insert command without the physical file being located on the server? The file path would relate to the users local machine. The only time I've used Bulk Insert is when I was logged onto the server itself, which is not possible for this app.

  3. Is there a way to do it with Linq? While I'm not really familiar with Linq if it can be accomplished I'm open to trying it.

Any insight is appreciated. I know what I need to do just not sure of how to accomplish it practically.

Thanks

Upvotes: 1

Views: 504

Answers (3)

eridanix
eridanix

Reputation: 828

To solve your problem, you have to see on in two basic views:

  1. Do you need make some operations with data before insert in into database (some sumarization, correction,...)? If yes, than here is the best way to upload rows from file to object (each row into one object instance). And than you can elegantly move with list of items with Linq.

  2. Do you need only insert rows from file to database as they are? I this case, use process described in point 2 of your question.

I'd prefer to upload file to server before any action. It's more safe.

Upvotes: 0

Justin
Justin

Reputation: 396

1) loader app in .Net is a good choice, generally. 100,000 rows is really not a strenuous workload, especially for simple loads. Only if there is a ton of multiple-table-joins involved in order to look up values on the fly would that really be a big concern.

2) although strictly speaking physical file location is just a performance question, I wouldn't do it. It will introduce administrative headaches.

3) I don't have experience with Linq, I cannot remark.

Just for bonus alternate idea: if you have IIS running somewhere, maybe even on the DB server, you can whip up a lightweight, one-page "webapp" which is just a CGI script with ODBC connection to the DB and the user just feeds the CSV in as a "web/CGI" upload. No utility application to install on user workstations this way.

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97691

My recommendation would be to use the SqlBulkCopy class in .NET. It will allow you to import rows almost as fast at the BULK INSERT statement, but only requires that you populate a DataTable with the rows, and then send them to SQL Server.

Another consideration you might want to look at would be (and this is my personal favorite for simple file import programs) to use PowerShell instead of C#, which has a built-in cmdlet for imporing CSV files. Pretty cool stuff.

Upvotes: 1

Related Questions