Importing excel data into SQL Server 2008 via ASP.NET

rito1 used Ask the Experts™
Hi All,

What would be an effective solution to enable a client to import excel data into SQL Server via an ASP.NET interface.

The interface will expect the excel data to be in a particular column format and will need to validate that all data is available within the excel file being imported.

I did develop a solution about 1.5 years ago which utilised the SQLBULKCOPY class... Is this still a sound solution or are there better methods to use?


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SqlBulkCopy is indeed an effective method.  I also use a method whereby the XLS file is uploaded into a DataTable, then verified row-by-row and inserted that way (ensuring that prior to this, BEGINTRANSACTION is used to allow roll-back of the whole thing if it fails).
Now with 2008, it is possible to create a data type that mirrors an ASP.NET datatable, and you can then pass the DataTable in ASP.NET as a parameter value.  There are many tutorials and examples for this online.


Excellent thanks Rouchie. I'll start my research on SQL Server 2008 data types. This sounds pretty cool!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial