Attaching to SQL Views

Attaching to SQL Views

  • Comments 29

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

Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post
  • Yes! Updatable Views!  LS Team is Awesome!

  • Very nice!

    Comment/Question:  This makes me wonder how I should/can add views to the intrinsic database ApplicationData?

    Thanks,

    Todd

  • Wow this is great. Very useful.

  • Hi Todd,

    Glad to hear you like this functionality.  LightSwitch does not have any support for views within the intrinsic data source.

    -Michael

  • Seguindo uma linha de pensamento e a cada dia dependendo menos do SSMS

  • Great new feature - LightSwitch is really stepping up now!

  • One of the most anticipated features for database first designs (like our team).

    It is also very important for me that Microsoft keeps staring at all directions and only Sharepoint or intrinsic db, which is crucial for the future of our investment.

    Thank you

  • Another top new feature for LightSwitch! Please continue making my job easier to do! :)

  • Todd,

    You can use views by adding your intrinsic database as external datasource.

    Server name: (localdb)\v11.0

    windows auth

    and then you will see the database. But you need first to start and stop your Lightswitch app so the DB gets attached ;)

  • Very nice!

  • Are the Editable view restrictions noted in the blog implemented on SQL Server or by Lightswitch/EF?  For instance, can we use SQL Server INSTEAD OF triggers on the view to get around restrictions against updating columns from more than one base table?  Or does Lightswitch/EF somehow prevent such updates even before SQL Server gets a chance to see the DML?

  • Andrew,

    LightSwitch does not have any limits in place.  The limitations are from SQL server therefore there are some ways you can mitigate them.

  • I bumped into what appears to be a show-stopper when using an INSTEAD OF INSERT or UPDATE trigger on a View.  The thread is at social.msdn.microsoft.com/.../save-failed-on-sql-view if any of the LS Team would help out by taking a look at the problem...

    Reading from the View is OK, but attempts to INSERT fail with a "data updated by another transaction" error and no data changes at all.

    The ability to potentially use Views as a means to eventually fire off Stored Procedures was what lured me back to LS after major issues trying to use Views and SPs a couple of years back.  I really hope there's a solution as LS is my last hope of being able to develop back-office admin tools for reference data without having to, unhappily, learn VS/ASP.Net/C# etc. from scratch.

  • Hi SAinCA,

    I replied to your forum thread.  Hopefully we can work through your particular issue.  There are cases in which INSTEAD OF INSERT or UPDATE trigger are supported.  We also acknowledge however that there are a lot of scenarios in which they are not going to be supported as well.  CUD support is constrained.  It is constrained by SQL in conjunction with EF and the CUD statements it issues.

  • I don't understand, I mean why create a view if you can only reference one table? The built in table functionality works fine, the only reason we create views is to join multiple tables. I don't understand the benefit of this.

Page 1 of 2 (29 items) 12