VSTO & .NET & Excel

December 6, 2006

Transfer Excel ’07 data to SQL Server 2005 EE with ADO.NET

Filed under: .NET & Excel, SQL Server — Dennis M Wallentin @ 2:17 pm

I have decided to place all code in articles at my English site and refer to them in the blogpost instead of trying to ‘formatting’ the code here. 

An update:
My good local friend Stefan Holmberg had the kindness to give me some additional input about ADO.NET 2.0. In return I have updated the sample in use where the method UpdateBatchSize of the SQLDataAdapter allow us to add all records in a batch mode to the database. 

This post is about how we can:

  • Retrieve data from closed workbooks (Excel 2007) with a new Provider and
  • Update a SQL Server 2005 EE (Express Edition) database with the retrieved data. 

With the release of Office 2007 the Jet OLEDB Provider has been replaced with a new Provider. The new Provider’s name is “Microsoft ACE OLEDB 12.0” and there is the following good news with it:

  • The 255 charachter limitation is removed.
  • It reads both Excel 8 ‘source databases’ (version 2000/2002 (XP) and 2003) and Excel 12 (version 2007) ‘source databases’. It means that the provider can read workbooks in the following file formats XLS, XLSX, XLSM and XLSB.

The provider works with both classic ADO and with ADO.NET. Since this blog is about .NET and Excel I decided to create an example (available on my English site) where we use ADO.NET and with untyped Datasets:

Transfer Excel ’07 data to SQL Server 2005 EE with ADO.NET

The use of Datasets can be discussed but here I find it to be a rather smooth solution, especially when using ‘local’ dataset where the data from it is merged into a ‘public’ dataset.

At present I’ve not yet find any sources that explicit discuss the provider and Excel workbooks as ‘database sources’.  Please let me know if You know of any source related to the subject.

Kind regards,
Dennis

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: