LightSwitch Data Freedom – Part 3

This article is part of a series of posts from Xpert360 Development Team to address real world scenarios and problems faced when designing, creating and implementing Visual Studio LightSwitch applications.

Recap: Design custom logical schemas for use by LightSwitch

…so far we have coerced LightSwitch and Entity Framework into using a custom database schema at design-time. Using this technique we have re-shaped the data and only exposed a compatible subset of tables and views to LightSwitch.

This setup performs well at design-time with our light-weight database schema.

It also performs well at runtime as we only transfer a subset of the data that we need by restricting the columns returned.

The custom schema needs to be compatible at runtime with the existing database schema, as far as Entity Framework is concerned, because at runtime we connect to the real database. For read-only access this is highly compatible but there are some caveats for updates, inserts and deletes. These caveats are the same as you would face designing a WCF RIA Service if you chose that route. If you try to break rules enforced in the database you will get errors, simple.

Pre-Filter your Data

We started by matching our business requirements to the data entities in the target legacy database. Now we move on to the last requirement.

• Implement views to add business logic:
  • Only show active leads

We simply need to create a view that can be mapped through to LightSwitch and appear to be a table.

Script, Edit and Cut example

You can use Visual Studio SQL Server Object Explorer for this part but we chose to use SSMS (SQL Server Management Studio). We script the existing objects as we don’t like typing, besides deleting and minor edits is easier.

We edit the generated script to change the database name from ‘CRM’ to ‘CRM_View’.

We delete the columns that are not necessary for our LightSwitch application requirements. We switch from create VIEW to TABLE. Here are the scripts:

SQL View and Table scripts

We execute the table script and now our ‘CRM_View’ database has an empty ‘SalesForce.ActiveLead’ table.

View-to-Table mapping

That is the hardest part over.

Boot LightSwitch, Wire-up and Go

Our LightSwitch project, in this example is a HTML (Visual C#) template variety, from the previous posts. Run the update data source wizard and select our new table ActiveLead.

ActiveLead table and HTML screen in AIDE for LightSwitch

The whole process to achieve this and create a LightSwitch HTML screen takes less than 10 minutes. It takes longer to explain!

Create screens to consume the data

Not much to see here, if you know LightSwitch, just a few clicks and our new home screen and new details screen look like this:

DataFreedom – ActiveLead Screens

Again, it works! Design-time performance is snappy with our light-weight database schema. Our runtime performance is responsive as we only transfer a subset of the data.

Conclusion

That concludes this foray under the LightSwitch covers. A little database knowledge can be dangerous, but in this case, it can also be very useful. Take backups, stay safe and have fun.That was to be the end of this little series, but things change, so more articles are coming to continue our exploration in the realm of LightSwitch data sources…

NEXT UP:

LightSwitch Data Freedom – Part 4: Intrinsic, or not Intrinsic, that is was the question!

PREVIOUS:

LightSwitch Data Freedom – Part 1: The chains of legacy databases

LightSwitch Data Freedom – Part 2: Walkthrough shaping your data

Advertisement