LightSwitch Data Freedom – Part 2

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.

Walkthrough Shaping your Data

Connecting to disparate data sources is one of the key strengths of Visual Studio LightSwitch and with so much data and so many data sources to consume the possibilities seem endless.

In real-world scenarios it does come with a few limitations which will be addressed by this technique. Do you suffer from:

• My database is complex and has got hundreds of tables
• The database schema design does not conform to standards
• My tables have hundreds of columns
• Performance in LightSwitch design and runtime is awful
• It is no use as I am not allowed to change the database
• I only want a few bits of data
• LightSwitch will not import our database metadata

Design custom logical schemas for use by LightSwitch

The premise behind this is that we present to LightSwitch and Entity Framework our custom database schema which is to be used at design-time.

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.

So it begins…

We start by matching our business requirements to the data entities in the target legacy database.

• Restrict the tables to only those we need in [SalesForce] schema:
  • Contact, Lead, Account
• Restrict the columns to only those required:
  • Done!
• Implement views to add business logic:
  • Only show active leads

With this LightSwitch friendly database schema we have a better experience consuming it within our applications. This technique is not restricted to LightSwitch applications per se as it really applies to the use of Entity Framework wherever it is used.

Script 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 is easier.

Generate T-SQL database script in SSMS

We edit the generated script to change the database name from ‘CRM’ to ‘CRM_View’. We execute this and now have an empty database. At this point our security is all setup by default in our development environment.

Generate T-SQL script for Contact table in SSMS

We edit this generated script to change the database name and delete the columns that are not necessary for our LightSwitch application requirements. We execute this script and now our ‘CRM_View’ database has an empty ‘SalesForce.Contact’ table.

Then we repeat this to create the ‘SalesForce.Lead’ and ‘SalesForce.Account’ tables in the ‘CRM_View’ database.

That is the hardest part over.

Boot LightSwitch, Wire-up and Go

Create a new LightSwitch project, in our example a HTML (Visual C#) template variety. Then run the attach data source wizard and point it at our empty ‘CRM_View’ database.

Connect to our empty database

The whole process to achieve this and create a LightSwitch HTML application took less than 5 minutes as I type fast. So now we have some  T-SQL scripts, an empty ‘CRM_View’ database with three tables and a LightSwitch HTML application in need of a screen.

Create screens to consume the data

Not much to see here, if you know LightSwitch, click-click-clickety-click and five minutes later our first application version with Tabs, Tile Views and detail screens looks like this:

VS2012 Solution Explorer

Robert is your mother’s brother, that F5 moment arrives, but not before switching databases. Locate the server project ‘Web.config’ file and edit the connection string to point at the real database with the data.

Switch databases in Web.config

It works! It performs well at design-time with our light-weight database schema. It performs well at runtime as we only transfer a subset of the contact list data that we need.

LightSwitch HTML Home Page

Even more smashing!

NEXT UP:

LightSwitch Data Freedom – Part 3: Pre-filter the data

PREVIOUS:

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