Attaching to SQL Views

In Visual Studio 2013 Update 2  (as well as Office Developer Tools for Visual Studio 2013 – March 2014 Update) some enhancements are available in the area of attaching to SQL Views.  These enhancements were made based on the feedback we received from the community.  With this update, you can now model the primary key for attached SQL Views.  Additionally you are now allowed to perform create, update, and delete (e.g CUD) operations against attached SQL Views.  In this post, I will walk through some scenarios that illustrate the new functionality.

Modeling the Key

I am going to build an application that attaches to the Invoices SQL View defined in the Northwind sample database.  I am going to start by creating a new LightSwitch HTML Application, but you can utilize these new SQL View enhancements in either the LightSwitch HTML Application, LightSwitch Desktop Application (Silverlight) or Cloud Business App (SharePoint).  Next I am going to attach to the Northwind database using the Attach to Existing Data functionality.  If you are unfamiliar with how to connect to a SQL Server Database, you can learn how to on MSDN.

The first thing you may notice when attaching to a SQL views is that the key is more than likely nonsensical.  This is because SQL views do not define primary keys therefore LightSwitch assumes every required field is part of the primary key.

image

This is only the default however and you are free to change this within the entity designer.  To do so, you can toggle the Is Key property in the property sheet for each field as illustrated below. 

image

There are a few rules you should keep in mind when defining a key.

  1. Only required fields can be part of the key.  The LightSwitch design time will enforce this rule.
  2. You must define a least one property to be part of the key.
  3. The key must be unique.  If the key is not unique, the application you build will not behave correctly.

Given these guidelines, I have modified the key for the Invoice entity to be the OrderID and ProductID as illustrated below.

image

There are several reasons that you should consider refining the key for Entities that are based on SQL Views.  The first is that it logically makes sense for your data model.  Secondly, there are several places within the design time experience that depend on what the Entity key is (e.g. the screen designer, the built in singleton queries, the entity relationship dialog, etc.)  A good example is that a Details screens will surface the key properties as screen parameters.  If you want to programmatically open the screen, you will need to specify each of the key values of the entity you want to open the screen for.  If you leave the default key defined which contains every required field, the number of parameters may become unwieldy.

Editable Views

As I eluded to earlier, LightSwitch has support for performing CUD operations on Entities that are backed by SQL Views.  There are several conditions however that determine if a particular CUD operation is supported on a SQL view such as

  1. Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  2. The columns being modified in the view must directly reference the underlying data in the table columns.

The full list of conditions are covered by the Updateable Views section of the Create View MSDN help topic.  If your view definition or CUD operation fails these conditions your end users will get an error when saving.  Because of these conditions, entity based views by default are not editable within LightSwitch.  To enable editable views you must open the entity designer for the view based entity and toggle the Is Editable property.  This will enable create, update, and delete for the entity.

image

If you want fine grained control over which CUD operations are permitted within your application or you want to require a certain set of permissions to perform CUD, you will need to enable the Is Editable property and then provide implementations for the appropriate CanInsert, CanUpdate, and CanDelete methods for your entity.

Summary

When attaching to a SQL View, you now have the ability to model the primary key and are able to perform CUD operations against the view.  If you have any questions or comments, please let us know through the blog comments or by posting in our forum.

– Michael Simons, Software Development Engineer, LightSwitch Team