Best method for importing csv or excel file to SQL Server 2005 using .net MVC view

I'm looking for the best method for importing csv or excel file to SQL Server 2005 using .开发者_Python百科net MVC.

Thank you.

There's a really good library called FileHelpers which is a) 100% free, b) fully in C#, and it can easily import any kind of text-based file - comma-separated, tab-separated, fixed width and so on.

You should have no trouble using this to load your CSV file into in-memory objects and storing those in SQL Server using ADO.NET.

In FileHelpers, you first need to have a class that describes your data, e.g. a "Customer" class (or whatever it is you're importing).

Then, you can import a file using code something like this:

FileHelperEngine<Customer> engine = new FileHelperEngine<Customer>();
Customer[] dataLoaded = engine.ReadFile(fileName);

Once you have your array of customers, you can either just iterate through that and save the data (typically inside a transaction) with e.g. a stored procedure or a ad-hoc SQL query:

using(TransactionScope ts = new TransactionScope())
{
   foreach(Customer c in dataLoadad)
   {
      SaveCustomer(c);
   }

   ts.Complete();
}

or you could convert the customer array to a DataTable and use SqlBulkCopy to bulk insert that into your SQL Server database - there are lots of options!

UPDATE:
Do you have a [DelimitedRecord] or another of those attributes on your BlackListDevice class?

Fixed this by adding a seperate class for file uploads, works like a charm using the FileHelper.