LightSwitch Data Freedom – Part 5

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.

Intrinsic, or not Intrinsic, that is was the question!

…or at least it has been since LightSwitch launched. Do you connect to an external data source or use an intrinsic database? With more options arriving and techniques advancing for LightSwitch, is this becoming a none-problem?

We certainly like doing database design in LightSwitch for mock-ups, proof-of-concept or prototype applications. We also like LightSwitch for developing demonstrations, POC’s and prototypes.

If only we could transfer LightSwitch assets (screens, entities queries) between projects. Never mind data mash-ups, let’s have some application mash-ups!

• Import pre-built screens samples
• Compose applications from your prototypes
• Backup, copy and restore screens
• Compare projects

In this article we will demonstrate the following, some for the first time publically:

1. Create a HTML5 client with Intrinsic database
2. Create the application screens
3. Deploy the application to IIS and database to SQL Server
4. Export the LightSwitch screens
5. Create a HTML5 client with external data source
6. Import the LightSwitch screens

At the end we will have a fully working copy of our LightSwitch HTML application working against the previously deployed database in SQL Server.

This walkthrough has been certified as code-free and no-lsml-hacking.

What really surprised us is that this demonstration can probably be completed in under 20 minutes. It could be squeezed into a 10 minute Lightning talk I suppose, that would be a mad demo! You could include SQL Azure and SharePoint on-line too comfortably in a 30 minute slot. That is also a scary thought. Well, it is LightSwitch after all 🙂

I created a new LightSwitch HTML C# project, then I chose to add the usual Contact, Product, Order and OrderLine tables into the LightSwitch entity designer. Add a few relationships, nice simple database design.

Sample AppIntrinsic Order Entity in designer and Entity Workbench

Then I created a home screen with tabs for Orders, Contacts and Products. Then I wired-up the TileView tap actions to AddAndEdit default screens. Then add a tab to the Order detail screen to handle multiple order lines. Job done!

Home Screen in Screen Workbench and LightSwitch designer

I added command buttons to add new contacts, products, orders, order lines and substituted the default ‘star’ by the in-built ‘add’ icon. F5 ->

It works! No real surprises so far.

Ok, I swapped out the default logo and splash screen images, I promise, that was all. Then I used the HTML screens to add some test data into the local database.

AppIntrinsic with AddEditOrder

The application is so good that it now passes QA and gets promoted. With that it is expedited for deployment.

Publish the LightSwitch HTML application

Now we have a SQL database appropriately named AppTest.

AppTest Deployed Database in SQL Server

And the deployed application is now accessible in IIS on the localhost.

Deployed application – spot the difference?

I entered some new sample data as the new SQL Server database was not surprisingly empty. The different data helps to show the different databases.

Deployed App AddEditOrder

This post is longer then I expected, but hold on in there for the juicy bits.

We use AIDE for LightSwitch Exchange Hub for exporting the screens. It extracts the screen XML from the client.lsml file and outputs them in VS2013 LightSwitch Preview compatible format.

Exported Screen LSML Files – VS2013 compatible

Five screens with nowhere to go, yet.

I created another new LightSwitch HTML Client C# project and then navigated over to the Attach Data Source Wizard.

Attach Data Source Wizard – Update

So now we have data. It is the external database in the local SQL Server that we previously deployed.

We have no screens. Now let us import the HTML client screens into our new project using the preview of AIDE for LightSwitch Exchange Hub.

AIDE for LightSwitch Exchange Hub – Imported

So now we have an external data source and the five imported screens…

LightSwitch Application with External Database

Unfortunately, the project will not build 😦

But wait, it is not the end of the world. The screens expected ‘Date’ whereas the imported external data source by default has them as ‘DateTime’ – fixed. The product entity had a ‘Money’ column which by default comes in as ‘Decimal’ – fixed. There, a few manual tweaks to that external data source in the LightSwitch designer and it builds!

The Import/Export Entities and Entity Properties will address that in a forthcoming release of AIDE for LightSwitch.

Application with External Database after importing screens

So we now have not one but two beautifully crafted twin LightSwitch HTML applications.

That concludes Path #1 – Export/Import facility to transfer assets between LightSwitch projects.

Conclusions

In all seriousness, this is meant to be a gentle introduction to manipulating LightSwitch data sources and opening our minds to the concept of transferring LightSwitch assets between projects.

If you have worked with LightSwitch for sometime you will have adjusted your thinking and mindset to working without features readily available in other Visual Studio project types.

Now is the time to switch you thoughts to embrace the possibilities allowed to you by the AIDE for LightSwitch power tools. You will have to change, but change is good!

in this series…

NEXT UP:

LightSwitch Data Freedom – Part 6: Switch data source by modifying project LSML

or

LightSwitch Data Freedom – Part 6: Intrinsic database, make mine a 6GB one!

PREVIOUSLY:

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

LightSwitch Data Freedom – Part 2: Walkthrough shaping your data

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

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

Advertisement

LightSwitch Data Freedom – Part 4

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.

Intrinsic, or not Intrinsic, that is the question!

…or at least it has been since LightSwitch launched. Do you connect to an external data source or use an intrinsic database?

On the one-hand, designing your database in LightSwitch is quick and convenient, LightSwitch looks after the database upgrades on deployment.

On the other hand, more fully featured database support, perhaps by design, perhaps to support other systems, perhaps for corporate policy.

We certainly like doing database design in LightSwitch for mock-ups, proof-of-concept or prototype applications. If business and technical requirements allow, that may be the end of the intrinsic/external discussion.

With Visual Studio 2013 Preview, LightSwitch projects now come with a database project option. This will change the balance of the decision criteria for the intrinsic/external debate. True, you could achieve similar before but it was more manual and time-consuming. See:

o – Intrinsic Database Management with Database Projects (Chris Rummel)

o – MSDN: Managing the Intrinsic Database for LightSwitch

o – Adding Stored Procs to your LightSwitch Intrinsic Database

This is a good step forward and extends the continued use of the Intrinsic database to more scenarios. But what if this is still not enough? Can you have the convenience of an Intrinsic database for the earlier part of a project and switch to external data source later.

In reality, standard LightSwitch is not going to handle this well. You are almost better off rebuilding your project from scratch, as that is what LightSwitch will force you to do. If you do this at the end of an early prototype project or proof-of-concept stage, it may be an unwanted be achievable step.

An alternative is to stick to using an Intrinsic database and manually reapplying schema changes to it to match any schema changes in the external database. If managed properly, it is a workable solution. Then you stop using LightSwitch to push schema changes out during application deployment.

We have tried two different paths that can delay moving to an external database to much later in a project’s cycle. The main point is to avoid any potentially large volume of rework to make the process quick and safe, if you choose to do so.

Another scenario not covered is in this forum post:

o – MSDN Forum: External Database vs Intrinsic Database

In there is a frequent need to run the LightSwitch application in Visual Studio debug mode against a multi-gigabyte copy of a production database. The logistics of data transfers mean that it is not feasible to move the data into the default intrinsic database. You just want to point the LightSwitch project at the existing restored database instance for debugging.

Path #1 – Export/Import facility to transfer assets between LightSwitch projects

Path #2 – Modify the project LSML to convert the Intrinsic data source to external

The second path sounds scary, and the first path sounds scary but is a bit more palatable. You will be trying this on backup copies of your systems first, right? In reality both techniques are of use in real world scenarios.

Conclusion

That sets the scene for manipulating the data source type in the next blog posts in this series…

NEXT UP:

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

PREVIOUSLY:

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

LightSwitch Data Freedom – Part 2: Walkthrough shaping your data

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

 

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

Adding a Bing Map to your CRM HTML5 LightSwitch Application

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. Brought to you by the original and genuine Power Tools for Visual Studio LightSwitch!

Use a Bing Map to view your CRM contacts

Utilising some existing resources you can quickly and easily create an HTML5 LightSwitch application  accessing your CRM data and displaying your contacts on a map.

1. Accessing your CRM data and creating an HTML5 Application

Follow the steps outlined in the  ‘Visual Studio Lightswitch – HTML5 Dynamics CRM Contacts Browser’  video created by Xpert360 to great a simple CRM contacts browser using the Xpert360 Dynamics CRM adapter.

CRM contact List

You will need to have downloaded the Xpert360 Lightning Data Adapter for Dynamics CRM Online and on-Premise.  You can do this from the Visual Studio Gallery. Follow the steps detailed on this page to get your license and install the extension.

Lightswitch Extensions

 

Adding the Bing map

Using the steps described in the ‘Visualizing List Data using a Map Control’ blog by Heinrich Wendel add a Bing map to a new tab in the browser screen.

CRM contacts on a Bing Map

Getting it to work with CRM data

To get the map working with your CRM data you only need to make a few simple changes to the  ShowItems function in the Browser screen code – Add the relevant references to CRM Data.

ShowItems Function changes

I also removed the popup option so clicking the pushpin on the map will open the view contact screen.

View Contact Screen

That’s it – easy.

ooo

Try them and support your LightSwitch tooling ISVs.

Also, do participate in forums and vote on your desired AIDE features. Help us to help you by prioritising the features in the product roadmap. VS2013, check! Export/Import entities and settings, check! Do you want to shape data to exclude columns? How about a localization workbench? Optimization workbench? Update metadata in bulk? That is a flavour of a few forthcoming features and ideas, we always keep a few things secret to surprise you!

ooo

Company information: Xpert360 Ltd, founded in 2009, is an Independent Software Vendor and Custom Solution Provider based in the United Kingdom.

“Xpert360 Lightning” and “AIDE for LightSwitch” are trademarks of Xpert360 Ltd, copyright 2012 – 2013.

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

LightSwitch Data Freedom – Part 1

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.

Freedom from the chains of legacy databases

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

Attach Data Source Wizard

LightSwitch’s attach data source wizard has built-in connectivity to four main types of providers: Database, SharePoint, OData Service, WCF RIA Service.

It does come with a few real and perceived limitations as far as implementation and consumption of data are concerned, but are these limitations just self-imposed? Perhaps they are not as real as they seem.

Big Database, Big Data, Big Problems

By definition if a database exists then it is a legacy database. Yes, even that shiny new one you created yesterday. They come in all sizes, all shapes and comply to various degrees with established design patterns (or not!).

Some real and perceived difficulties:

• 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

You can create your own WCF RIA data service to address some of these concerns but there is another approach that you can try without the need to write any code.

Design custom logical schemas for use by LightSwitch

You can use this technique to great effect with surprisingly fast results, true to the principles of LightSwitch RAD. It does not answer all concerns and problems, in particular, poorly designed databases, but it is a technique well worth considering and adding to your LightSwitch tool bag.

You (or a colleague) will need some basic T-SQL skills to successfully use this technique so you can rule yourself in or out. If you are out then it really is the time to get some basic T-SQL skills.

If you shape your data source and data in a way that is more LightSwitch friendly then you will have a better experience consuming it within your LightSwitch applications.

To cut a long story short let us see an example

Here is a simple but often real scenario

• Legacy CRM database
• 200+ tables
• 10,000+ columns
• We just need a list of contacts with a subset of the columns

Conceptually we need to map the data across to our data view for LightSwitch.

Map the existing database schema across to a subset

The whole process to achieve this and create a LightSwitch HTML application to consume it took less than 5 minutes. So now we have:

Simple home page showing contact list

We did some T-SQL script generation, we made some simple name changes, but we did not write a line of code in LightSwitch!

It works! The legacy database contained unsupported data types that caused LightSwitch to fail to import the metadata at all before.

It performs well at design-time as the big, nasty database schema is no more.

It performs well at runtime as we only transfer a subset of the contact list data that we need.

Smashing!

NEXT UP:

LightSwitch Data Freedom – Part 2: Walkthrough shaping the data

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