A Dynamic Data Website with SQL Azure and the Entity Framework

This is part of a series of entries written about Microsoft’s new SQL Azure database service and the Entity Framework v4.

Following on from my previous posts (check them out before continuing) – this article assumes you have followed steps outlined in the  previous posts to create various models and accounts etc.

Our next step is to create a Dynamic Data website.  If you haven’t come across this yet, it’s most likely because you haven’t been using Visual Studio 2010 or the .Net Framework 4.0.  Recently introduced and compatible with both LINQ-to-SQL and the Entity Framework, this nice site template makes use of the dynamic nature of both LINQ-to-SQL [.dbml] (SqlMetal) and Entity Framework [.edmx] data models.

Continuing within the solution we created in Part 3, we shall now add a Dynamic Data website to our solution.  Open the solution in Visual Studio 2010 Beta 2 and then Right click the solution, “Add –> New Project..”.

Please note: this portion of the solution will also work in conjunction with any other database provider supported by the Entity Framework, not just SQL Azure.  To use a Dynamic Data site all you need is a LINQ-To-SQL or Entity Framework Data Context!  For details, read on..

I’ve called the new project “SQLAzure.Application.Web” and it sits in a subfolder off the root folder level for the solution.  Once you click the OK button, you’ll be presented with the Global.asax.cs (or .vb) which, at first, I found a little unusual.  We’ll explore why shortly.

First things, let’s add a project reference to our DataAccess project.  Right click the References solution folder and add the reference.  You might notice that the project already has the appropriate references for the Entity Data objects.  At this point if you compile, it will build without any errors (and if not, you are off to a bad start indeed!).

Configuration Bliss

There are some configuration options which will need to be set.  First, double click on the web.config and copy your <connectionString> values from the app.config located in the DataAccess project.

Once you’ve updated the Web.Config, save and close it.  You should now be looking at the aforementioned Global.asax.cs (or vb).  To get us up and running, with the very minimum of configuration (and hassle), we’ll keep the default settings (using the ASP Development Server a.k.a Cassini).  Right click the Web project in the Solution Explorer and “Set as StartUp project”.

Web Scaffolding and other treats

Next, in the Global.asax.cs (or .vb) in the method “RegisterRoutes” uncomment all the code lines, remembering to substitute the value (line 31) “YourDataContextType” with the name of the actual data model context (in our case, SqlDataModelContainer) and you’ll also need to add “using SQLAzure.Application.DataAccess;” at the top of the file.

In the first line, change { ScaffoldAllTables = false }); to { ScaffoldAllTables = true });
We can always customize the data model later to only scaffold specific tables (if we want to).

Once this is all done, you should be able to cleanly compile the project/solution.  Assuming you’ve followed all the steps I’ve outlined, your Global.asax.cs should look like the following:

Compile and run the solution (without debugging).  You should notice the ASP Development Server load into the System Tray, and then your default web browser should open and browse to your nice new site.  If you get the following error message:

Server Error in ‘/’ Application.

---

There are no accessible tables. Make sure that at least one data model is registered in Global.asax and scaffolding is enabled or implement custom pages.

It means you haven’t changed the value on Line 31 from false to true:

You *should*, assuming everything has been correctly set, see the following in your web browser, a list of tables in your SQL Azure database!  If you click on the “Albums” link, you should drill into a paginated view of the first 15 records in the Albums table in your nice SQL Azure database in the cloud..

Prepare to be amazed!

Notice the combo boxes in the top left hand corner?  Yes, the site has automatically created those so you can filter the results according to foreign keys in the entiry relationship model – very nice.  Go ahead, select an artist.  I’ve selected “Bob Dylan”:

…but wait, there’s more..

That was pretty effortless, wasn’t it?  You want more functionality?  Of course – click on the “Edit” button for the first result.  You’ll be treated to this “details view” of the record:

You’ll notice, of course, that again the site has detected all the relationships (including many-to-many) and created combo boxes/checkboxes according to the cardinality of the relationships.  You may edit any of these settings and hit the “Update” button – you are now updating your data in the cloud!

Another nice touch is that you can navigate the relationships (hyperlinked) as well.  All the tables are inter-related (where applicable).  I’ll leave you now to play with your shiny new website, backed with a SQL Azure data store.

The big picture

This was, by far, the easiest website-to-database project I’ve ever wired up, period.  The functionality is uncanny for an out-of-the-box template driven site, and what’s more, if you hook it up to a Server instance (as opposed to SQL Azure) you can run SQL Profiler and see that the queries (T-SQL) themselves aren’t terrible, either.

This is amazing value – foreign key filters, paginated data views, complete with full edit/delete functionality.

However, it also has some additional work which should be addressed in any decent implementation, for example, security and brevity (reducing the footprint of the tables).  We could also do with some optimization and customization!  For example, the screen (1) below could obviously be truncated as the results run right off the screen, and clearly look better in the “Details” view (2).

There are many more things which a “production” quality implementation would require.  I may (or may not) go into details in future posts, but off the top of my head you would need to address any of the following:

– Move from Cassini to IIS (should be done early for serious multi-access websites)
– Add a custom App Pool and Identity
– Strong name the assemblies
– Edit all the templates with a scheme
– Restrictions/reductions in the data displayed in “grid views”
– Optimization of some T-SQL to suit the data model design
– Implementing a security model to restrict read/write/deletion of data
– Auditing changes
– Create an automated build and deploy script

However, this is a great start and would probably suit a number of “intranet” style applications.

Looking ahead

In my next entry, we’ll look at some customizations as well as the technology underpinning this great functionality.  I do hope you are enjoying this series of entries, please leave comments!