Importing excel data into SQL Server 2008 via ASP.NET

rito1
rito1 used Ask the Experts™
on
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?

Thanks,

Rit
Comment
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.

Author

Commented:
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