Using WebMatrix with PHP, OData, SQL Azure, Entity Framework and More

I've written before about Microsoft's overtures to the PHP community, with the August release of PHP Drivers for SQL Server being the latest step in an ongoing effort to provide interoperability between PHP and Microsoft technologies.

With a slew of other new products and services released (relatively) recently, such as SQL Azure, OData and WebMatrix, I decided to see if they all work together.

Amazingly, they do. Well, amazing that I got them to work, anyway. And as I've said before, if I can do it, anyone can do it. But that's the whole point: WebMatrix is targeted at noobs, and I wanted to see if a hobbyist could use it in conjunction with some other new stuff.

WebMatrix is a lightweight stack or tool that comes with stripped-down IIS and SQL Server components, packaged together so pros can do quick deployments and beginners can get started in Web development.

WebMatrix is all over PHP. It provides PHP code syntax highlighting (though not IntelliSense). It even includes a Web Gallery that lets you install popular PHP apps such as WordPress (see Ruslan Yakushev's tutorial). Doing so installs and configures PHP and the MySQL database.

I chose to install PHP myself and configure it for use in WebMatrix (see Brian Swan's tutorial).

After doing that, I tested the new PHP Drivers for SQL Server. The August release added PDO support, which allows object-oriented programming.

I used the PDO driver to access the AdventureWorksLTAZ2008R2 test database I have hosted in SQL Azure. After creating a PDO connection object, I could query the database and loop over the results in different ways, such as with the PDO FETCH_ASSOC constant:

(while $row = $sqlquery->fetch(PDO::FETCH_ASSOC) )

or with just the connection object:

foreach ($conn->query($sqlquery) as $row)

which both basically return the same results. You can see those results as a raw array on a site I created and deployed on one of the WebMatrix hosting partners, Applied Innovations, which is offering its services for free throughout the WebMatrix beta, along with several other providers. (By the way, Applied Innovations provided great service when I ran into a problem of my own making, even though the account was free.)

Having tested successfully with a regular SQL Azure database, I tried using PHP to access the same database enabled as an OData feed, courtesy of SQL Azure Labs. That eventually worked, but was a bit problematic in that this was my first exposure to PHP and I haven't worked that much with OData's Atom-based XML feed that contains namespaces, which greatly complicated things.

It was simple enough to grab the OData feed ($xml = file_get_contents ("ODataURL"), for one example), but to get down to the Customer record details buried in namespaces took a lot of investigation and resulted in ridiculous code such as this:

echo $xmlfile->entry[(int)$customerid]->children
('http://www.w3.org/2005/Atom')->content->
children
('http://schemas.microsoft.com/ado/2007/08/dataservices/metadata')->
children
('http://schemas.microsoft.com/ado/2007/08/dataservices')->
CompanyName->asXML();

just to display the customer's company name. I later found that registering an XPath Namespace could greatly reduce that monstrous line, but did require a couple more lines of code. There's probably a better way to do it, though, if someone in the know would care to drop me a line (see below).

Anyway, the ridiculous code worked, as you can see here.

I also checked out the OData SDK for PHP. It generates proxy classes that you can use to work with OData feeds. It worked fine on my localhost Web server, but I couldn't get it to work on my hosted site. Microsoft Developer Evangelist Jim O'Neil, who used the SDK to access the "Dallas" OData feed repository, suggested I needed "admin privileges to configure the php.ini file to add the OData directory to the include variable and configure the required extensions" on the remote host. I'm sure that could be done easily enough, but I didn't want to bother the Applied Innovations people any further about my free account.

So I accessed OData from a WebMatrix project in two different ways. But that was using PHP files. At first, I couldn't figure out how to easily display the OData feed in a regular WebMatrix (.cshtml) page. I guess I could've written a bunch of C# code to do it, but WebMatrix is supposed to shield you from having to do that. Which it does, in fact, with the OData Helper, one of several "helpers" for tasks such as using Windows Azure Storage or displaying a Twitter feed (you can see an example of the latter on my project site). You can find more helpers online.

The OData Helper made it trivial to grab the feed and display it in a WebMatrix "WebGrid" using the Razor syntax:

@using Microsoft.Samples.WebPages.Helpers
@{
var odatafeed = OData.Get("[feedurl]");
var grid = new WebGrid(odatafeed, columnNames : new []{"CustomerID",
"CompanyName", "FirstName", "LastName"});
} 
@grid.GetHtml();

which results in this display.

Of course, using the built-in SQL Server Compact to access an embedded database was trivial:

@{
var db = Database.OpenFile("MyDatabase.sdf");
var query = "SELECT * FROM Products ORDER BY Name";
var result = db.Query(query);
var grid = new WebGrid(result);
 }
@grid.GetHtml();

which results in this.

Finally, I tackled the Entity Framework, to see if I could do a regular old query on the SQL Azure database. I clicked the button to launch the WebMatrix project in Visual Studio and built an Entity Data Model that I used to successfully query the database with both the ObjectQuery and Iqueryable approaches. Both of the queries' code and resulting display can be seen here.

Basically everything I tried to do actually worked. You can access all the example results from my Applied Innovations guest site. By the way, it was fairly simple to deploy my project to the Applied Innovations hosting site. That kind of thing is usually complicated and frustrating for me, but the Web Deploy feature of WebMatrix really streamlined the process.

Of course, WebMatrix is in beta, and the Web Deploy quit working for me after a while. I just started FTPing all my changed files, which worked fine.

I encountered other little glitches along the way, but no showstoppers. For example, the WebMatrix default file type, .cshtml, stopped showing up as an option when creating new files. In fact, the options available for me seemed to differ greatly from those I found in various tutorials. That's quite a minor problem, but I noticed it.

As befits a beta, there are a lot of known issues, which you can see here.

Overall, (much like LightSwitch) I was impressed with WebMatrix. It allows easy ASP.NET Web development and deployment for beginners and accommodates further exploration by those with a smattering of experience.

We'd love to hear your thoughts about WebMatrix, Microsoft's embrace of PHP, or (kind of) new stuff like OData and SQL Azure in general. Comment below or drop me a line.

Posted by David Ramel on 09/30/2010