SQL Advisor
Flipping the Light(Switch) Fantastic in Visual Studio 11
Creating an application for Web, devices, and the cloud is easy and fast with LightSwitch. In fact, the only difficulty was installing the darn thing.
I've heard a lot about the new LightSwitch in Visual Studio 11 Beta and how it simplifies data-centric application development, so I thought I'd give it a try.
For those not familiar with LightSwitch, Eric Nelson wrote in an MSDN blog: "LightSwitch is targeted at business developers and power users creating custom LOB applications leveraging data from multiple sources that can be easily deployed to the desktop or cloud."
The new LightSwitch "has embraced OData," Microsoft said last month, so I thought I'd try out the new support for OData, which includes the ability to consume OData in your applications as well as produce and deploy your very own OData services for use by other applications.
Earlier this week, Beth Massi http://blogs.msdn.com/b/bethmassi/archive/2012/03/27/lightswitch-in-visual-studio-11-beta-resources.aspx compiled some useful LightSwitch information, including some of her own articles about producing and consuming OData. Massi pointed out the "goodies" in the new release include a new "cosmopolitan shell and theme," extensibility toolkit, documentation and a beta forum where users can ask questions.
A while back I wrote about how easy it was to cook up a LightSwitch application in less than an hour, from downloading the program to producing a query-based "screen" that let me add, edit and delete records from an a SQL Azure database, so I thought I'd revisit that scenario, too.
I also wanted to hook up to the Windows Azure Marketplace DataMarket (one of my favorite Microsoft product names -- quick, say it three times fast without spitting all over yourself!), which I've also written about before in the context of an ASP.NET Web Forms application.
So I wanted to quickly develop a pseudo-app that hooked into three different kinds of external data sources: a straight, public OData feed; a Windows Azure DataMarket OData feed; and a cloud -- in this case SQL Azure-hosted -- database. (Yes, the archaic local database connection is dying fast.)
Downloading the Visual Studio 11 Beta was by far the most time-consuming part of my experiment, taking more than a couple of hours. There's been a lot of user discussion about the new look and feel of Visual Studio, with less chrome, less color, fewer doo-dads and so on. I can't say I like it that much, but I've always been a function-over-form guy. I've never really understood why people cared so much about the minor design aspects of an app. If it works and the UI doesn't get in the way, I'm fine with it. I couldn't care less how colorful it is. And as you can see in Fig. 1, there ain't much color here.
 |
Figure 1. Plain-looking interface for Visual Studio 11, but it's also a beta. (Click image to view larger version.) |
Installation was uneventful and v11 installed side-by-side with v10. Though some haven't been that lucky.
After the lengthy download and setup, cranking up Visual Studio 11 and starting a new project shows that LightSwitch is prominent in your project type choices (see Fig. 2).
 |
Figure 2. LightSwitch is flipped on by default. (Click image to view larger version.) |
After your new project is set up, you're told to "Start with data," with the option of creating a new table locally or attaching to an external data source, which brings up a wizard that gives you choices of Database, SharePoint, OData Service or WCF RIA Service. I started with OData. I plugged in the source address of my OData feed (for example, the public Northwind test database feed address is: http://services.odata.org/Northwind/Northwind.svc/) and then chose which login information to use: None, Windows Credentials or Other Credentials, the latter taking a user name and password. This public Northwind feed doesn't require any login info. I then tested my connection and proceeded to choose which entities I wanted to import and named my data source. Upon clicking Finish I got several warnings (such as the one in Fig. 3) that certain features of the data source weren't available, specifically "many-to-many" relationships:
The relationship between CustomerDemographics and Customers will not be imported. Many-to-Many relationships are not supported.
I was then presented with an Application Designer you can use to add queries or "screens" that are the main UI features of LightSwitch applications. For example, you could add a query (with wizard options -- no coding required) to just find customers in certain geographical regions, and then add a screen to present that list. Or you could just quickly add a screen to present a full table. Many other options are available, of course, but I just wanted a quick-and-dirty screen to prove it works.
So bringing the Customer table to the designer let me choose the type of screen template and the data I wanted to include (see Fig. 3).
 |
Figure 3. Getting down and dirty with the Application Designer. (Click image to view larger version.) |
I then clicked OK and I was ready to test the app. Clicking Start loaded a bunch of symbols and eventually started the app. And there was my screen, showing ALFKI and rest of the familiar cast of Northwind customers. It was that easy. It took much less time to do it than explain it.
For SQL Azure, of course, you have to first sign up (I used the three-month trial) for a Windows Azure account and set up a SQL Azure database. By the way, migrating a database to SQL Azure still seems kind of problematic, judging from my own experience and that of other bloggers on the Web. There are various ways to do that, but the migration wizard I used previously didn't work for me this time. After much trial and tribulation, I ended up connecting to a SQL Server 2008 R2 database on my machine via SQL Server Management Studio and using the scripting function to write a migration script that actually worked fine.
To connect to my Northwind (yes, them again) SQL Azure database, I right-clicked on the project in Solution Explorer and chose Add Data Source, which brought up the familiar list of data source types. For SQL Azure, I chose Database and plugged in the server name in the Connection Properties box that popped up. This server name looks like this: lfi6gi15xg.database.windows.net (I scrambled that mix of initial letters, so this isn't my real server name).
I then chose Use SQL Server Authentication and supplied the user name and password, which you can get from your SQL Server management portal. By the way, check out the new Metro styling of the portal in Fig. 4.
 |
Figure 4. Check out the Metro interface. (Click image to view larger version.) |
I provided the name of the database (it looks up the choices for you on the server you specified), conducted a successful connection test and moved on to the Attach Data Source Wizard where I chose which database objects to import (for the OData feed, remember, it was "entities" instead of "database objects") and name the data source, etc., as was done previously. From there, everything worked as before with the public OData feed.
So far, connecting to and using a public OData feed and my own private SQL Azure database worked flawlessly.
My last test scenario was a DataMarket OData feed. You can sign up to subscribe to a DataMarket feed with a Windows Live ID. I chose one of dozens of free subscriptions available, but there are also dozens of paid and free trial feeds, also. The DataMarket management portal provides you with account details such as a Primary Account Key and Customer ID. It also lets you "explore" datasets, so you can run sample queries, browse data, etc. This is where you get the Service root URL you need to connect with LightSwitch. For example, I subscribed to the free Consumer Legal Articles feed from Lawyers.com, which has a URL of: https://api.datamarket.azure.com/LexisNexis/ConsumerLegalArticles/.
Back in LightSwitch, I went to add another data source, chose OData Service as the type, plugged in the aforementioned Service root URL and chose Other Credentials, using my aforementioned Customer ID as the User name and Primary Account Key as the password. Upon a successful connection test, I proceeded as before.
And it worked! My first LightSwitch in Visual Studio 11 Beta was up and running, connecting to three different external data sources and showing screens that displayed data from each (see Fig. 5).
 |
Figure 5. Ta-da! LightSwitch works as advertised, showing how my application will look whether it's on the user's desktop, on a Web page, or somewhere up there in the cloud. (Click image to view larger version.) |
That's it. Thumbs up! The new LightSwitch in Visual Studio 11 Beta works as advertised, giving you "a simpler and faster way to create professional-quality business applications for the desktop, the Web and the cloud," as Microsoft said on its LightSwitch 2011 trial page, which you can download if you don't want to bother with downloading and installing one of the more full-featured Visual Studio 11 Beta editions.
What do you think of the new Visual Studio and LightSwitch? Did you manage to say Windows Azure Marketplace DataMarket three times fast without spitting all over yourself? Comment here or drop me a line.