I have been working to get some data imported from Excel to a SQL database. Preferably I’m looking for a solution so that end users can perform this themselves. One option that came up on the SQLDownUnder mailing list was to try using LightSwitch.
LightSwitch is a wrapper around .NET that makes it easy to create data centric apps with little or no code. In my case to LightSwitch drag and drop took me about 2 minutes once I knew what I was doing and I had to write one line of code.
My test table was just a simple product and category table with data from AdventureWorks
Through the magic of drag and drop LightSwitch produced a functional screen that allowed me to add and edit records:
It automatically recognised my foreign key relationship and gave me a category dropdown which was nice.
Next I grabbed the excel import plugin for LightSwitch. I couldn’t get the extension to show in VS 2012 but it worked fine in VS 2010.
I enabled the extension, added a button and in the code behind put my one line of code:
I had a spreadsheet with a new product including the name of its category:
The import plugin gave me a mapping screen that even mapped the category name and filled in the correct foreign key:
Overall I’m pretty impressed with what I could in such a short amount of time. The instructions I followed are on the LightSwitch blog.
There are even user friendly validations based on the SQL fields: