Visual Studio LightSwitch Team Blog

OData Apps in Update 2 - Querying Data from Stack Overflow

LightSwitch Team — Thu, 23 May 2013 13:53:31 GMT

Matt Sampson's got a blog about making a LightSwitch HTML App that queries data from the one and only Stack Overflow by attaching to their very cool public OData Service.

(Now you can use a LightSwitch app to keep track of all the LightSwitch related posts on Stack Overflow!)

You can check out the blog post here - OData Apps in Update 2–Querying data from Stack Overflow

I published the HTML App up into azure if you'd like a preview of it - StackOverflow App.

Thanks all - Matt Sampson

Creating a wizard-like experience for HTML client (Andy Kung)

LightSwitch Team — Thu, 16 May 2013 15:00:00 GMT

If you have been playing with the new HTML client, you will know that LightSwitch provides a built-in screen template for creating data. It allows user to input data via a modal dialog. In some scenarios, however, we’d much prefer to guide the user via a step-by-step wizard-like dialog.

In this article, I will show you how to create such experience by customizing the built-in screen template. We will create a simple survey app that that allows users to enter data via a 3-step “wizard.” Something like this:

Let’s Begin!

Set up the database

Create a LightSwitch HTML Application (VB or C#). Name it MySurvey. On the start screen, click Create new table.

Name the table Survey and add 3 String fields: Name, Quest, and Color.

Create a survey list

In Solution Explorer, right click on HTMLClient node to add a screen.

We’d like to create a screen with a list of surveys. In Add New Screen dialog, select Browse Data Screen template, name the screen SurveyList, and pick Surveys as Screen Data. Click OK.

Create a “New Survey” dialog

We’d like to create an add button for the survey list. In Survey List screen, right click on Command Bar node to add a button.

In the Add Button dialog, pick Surveys.addAndEditNew and leave Navigate To as New Screen. This tells the system that this button will add a new item to the survey list via a new screen we’re about to create. Click OK.

LightSwitch will guide us to create an Add/Edit Details Screen. Name the new screen NewSurvey. Click OK.

Define steps for the wizard

In the New Survey screen, you will notice we have one tab group under the Tabs node.

We want to simulate a 3-step wizard by using 3 tabs, showing one tab (step) at a time.

Add 2 new tab groups by right clicking on Tabs node.

Select the first tab group. In Properties, change Name to Step1.

Name the same property to Step2 and Step3 for the other 2 tab groups.

Since we only want to show one question for each step, move Name directly under the first tab group.

Move Quest and Color to Step2 and Step3 tab groups respectively.

Since we have no use of the columns node under the first tab group, delete it.

Hit F5 key to run the application. Click the Add Survey button. You will see we have an add dialog to add a new survey. There are 3 tab groups, each containing a survey question.

Customize the dialog into a “wizard”

To make this more like a step-by-step wizard, we need to do a couple of tweaks. First, we only want to show the Save button in the last step. Second, we want to hide the tab titles to prevent the user from jumping directly to a step.

Close the browser and go back to Visual Studio. Let’s make these changes.

In New Survey screen, select the screen root node. Go to Properties window and check Hide Tab Titles. This will hide the title of each tab group. Also select Browse for Screen Type. This will display a Close button instead of a Save button in the modal dialog.

If you run the application now, you will only see the first tab showing.

All we need to do now is to place a Next button to go to the next step.

Create navigation buttons

Right click on Command Bar node under the first tab to add a button.

In Add Button dialog, select Write my own method and name it ShowStep2. Click OK.

Double click on the new button will take you to the code editor. We will use the showTab API to display the next tab group (step). We will also update the dialog title programmatically as we move from step to step.

myapp.NewSurvey.ShowStep2_execute = function (screen) {

// Write code here.

screen.showTab("Step2");

screen.details.displayName = "Step 2";

};

Go back to the New Survey screen. Follow the same steps to create a ShowStep3 button for the 2^nd tab (step).

myapp.NewSurvey.ShowStep3_execute = function (screen) {

// Write code here.

screen.showTab("Step3");

screen.details.displayName = "Step 3";

};

On the last tab (step), we want a Save button. Add a button to the 3^rd tab (step) and name the method Finish.

Write the following code. The commitChanges API will save the screen and close the dialog.

myapp.NewSurvey.Finish_execute = function (screen) {

// Write code here.

myapp.commitChanges();

};

Run the application and add a survey by following the “wizard. “

Handle validation errors

One problem you might notice is that we don’t prevent you from going into the next step even if there are validation errors. We want to surface these error messages before letting the user continue.

Update the 3 code methods as the following:

myapp.NewSurvey.ShowStep2_execute = function (screen) {

// If no value is entered, manually set the field to null to trigger 'required' validation

if (screen.Survey.Name == null) {

screen.Survey.Name = null;

}

// Go to next step only if there are no validation errors associated with the field.

var contentItem = screen.findContentItem("Name");

if (contentItem.validationResults.length === 0) {

screen.showTab("Step2");

screen.details.displayName = "Step 2";

}

};

myapp.NewSurvey.ShowStep3_execute = function (screen) {

// If no value is entered, manually set the field to null to trigger 'required' validation

if (screen.Survey.Quest == null) {

screen.Survey.Quest = null;

}

// Go to next step only if there are no validation errors associated with the field.

var contentItem = screen.findContentItem("Quest");

if (contentItem.validationResults.length === 0) {

screen.showTab("Step3");

screen.details.displayName = "Step 3";

}

};

myapp.NewSurvey.Finish_execute = function (screen) {

// If no value is entered, manually set the field to null to trigger 'required' validation

if (screen.Survey.Color == null) {

screen.Survey.Color = null;

}

// Save changes only if there are no validation errors associated with the field.

var contentItem = screen.findContentItem("Color");

if (contentItem.validationResults.length === 0) {

myapp.commitChanges();

}

};

There are 2 new things we’re doing here:

In HTML client, we don’t show the required field error message until the value is change by the user. Because of this reason, we are programmatically setting the value back to null. This will trigger the error message to show if the user just click Next button without entering anything.
We only allow you to move to the next step if there are no errors at the current step.

Run the application and click Next without any value. We now prevent you from going into the next step prematurely.

Polish the experience

There are a couple of enhancements we can do:

Change the Display Name of the questions to make it more descriptive (ex. What… is your name?). You can do so via Properties.
Change the Display Name and Icon of the buttons via Properties.

Bonus: Add some Back buttons

You can add some Back buttons to the flow by utilizing the same showTab API. For example, in Step 2, you can include a Back button with the following method:

myapp.NewSurvey.BackStep1_execute = function (screen) {

// Go back to step 1

screen.showTab("Step1");

};

Bonus: Add an optional step

Sometimes the next set of questions is based on a question user answers at the current step. In case you did not get the joke in my example, the 3 questions are taken from the movie Monty Python and the Holy Grail. Let’s add an additional step to ask if people get the joke.

Add a Boolean field GetTheJoke in the Survey table. Uncheck the Required box to make it an optional field.

In New Survey screen, add another tab group (step) and name it OptionalStep.

Drag GetTheJoke field from the data members list under the tab group. Change the Display Name of the field.

Add a Next button in the group by creating a new method. It simply goes to Step 2.

myapp.NewSurvey.OptionalStep_execute = function (screen) {

// Write code here.

screen.showTab("Step2");

screen.details.displayName = "Step 2";

};

In the original ShowStep2 method, let’s add an IF statement before showing Step 2. If the user’s name does not contain “Sir”, let’s ask if they get the joke.

myapp.NewSurvey.ShowStep2_execute = function (screen) {

// If no value is entered, manually set the field to null to trigger 'required' validation

if (screen.Survey.Name == null) {

screen.Survey.Name = null;

}

// Go to next step only if there are no validation errors associated with the field.

var contentItem = screen.findContentItem("Name");

if (contentItem.validationResults.length === 0) {

if (screen.Survey.Name.indexOf("Sir") < 0) {

screen.showTab("OptionalStep");

} else {

screen.showTab("Step2");

screen.details.displayName = "Step 2";

}

};

Run the application and try out the optional step.

Conclusion

We have successfully created a step-by-step wizard-like experience by customizing the built-in screen template! We learned how to:

Create multiple steps by adding multiple screen tabs
Make the dialog looks like a wizard by hiding screen titles and the save button
Navigate to the next step and update title
Save and close the wizard programmatically
Handle validation errors at each step

As King Arthur would say, “You have to know these things when you’re a king, you know.”

-andy

Customizing the SharePoint Chrome Control in LightSwitch

LightSwitch Team — Tue, 14 May 2013 15:07:00 GMT

If you’ve done any LightSwitch apps for SharePoint you may have noticed that we incorporate a version of the SharePoint chrome control. You may not have known what it was (or what it was called), but you do now. You can also customize it and I’ll show you how. I’m also going to cover a few things like navigating to the home page or retrieving the current user, things that apply to non-SharePoint apps as well.

The chrome control is just a little frame at the top of your app that by default let’s you jump quickly back to the SharePoint site that launched the app. Here’s the quick & easy app we created in a previous post called “CustEdit” – you can see the chrome control was put in for us automatically. It also matches the color theme of the SharePoint site the app was launched from – that may or may not be what you want but we’ll cover that in another post. For now we just want to add a little more than what the default provides.

We’ll add:

A Home button that will take the user to the home page of the app
A button that will take the user back to the SharePoint site
The current user’s name and a link to the “my site”
A “Sign Out” link

Let’s get started.

Add createSharePointChrome Function

LightSwitch will create the chrome for you but if it finds a declared JavaScript function called createSharePointChrome() then LightSwitch will use that to render the chrome control instead. The frame of the control will remain the same but you can change the contents to whatever you want. So the first thing we need to do is create this function and make it part of our project. Taking our CustEdit example, perform the following steps:

In Solution Explorer, switch to File View and then Show all Files
Expand the CustEdit.HTMLClient node, right-click on the UserCode node and select Add>New item…
Add a new javascript file and name it customchrome.js
Open up the default.htm file in the code editor and add a reference to the script file:

    <script type="text/javascript" src="UserCode/customchrome.js"></script>

Open up the customechrome.js file and declare the function:

function createSharePointChrome(element, spUrl) {

    //create the links we need for the chrome on O365
}

Press F5 and run your app – you should see an empty chrome. That means LightSwitch found your empty function.

Now, we need some code to render the buttons. The image I want to use for the SharePoint site, I’ll just pull from SharePoint itself. For the home button I want to use the SharePoint app icon that’s part of the SharePoint project. To add that, I’ll just create a link to that png file from the SharePoint project to the Server project. Also, I customized my SharePoint appicon, but you don’t have to do that for this work, the default will work just fine. The key here is that the SharePoint project will want an icon that is 96x96. So if you don’t want to create one, just use the default.

To use a new icon: (skip past this stuff to the next set of bullets if you want to use the default icon)

Still in File View, right-click the CustEdit.SharePoint project node and select Add > Existing item…
Browse to your 96x96 png file and add it to your project
Open the appmanifest.xml file in the SharePoint project
In the Icon text box browse to the png file you added

Now create the link to the file in the server project:

Right-Click on the CustEdit.Server project and select Add >Existing Item…
Browse to the CustEdit.SharePoint project on disk, and select the icon you want to use (mine is custedit.png the default is AppIcon.png)
Click the arrow next to the Add button in the File Open dialog and select Add as link

You’ll now see a reference to the png file in the server project. If you ever change that file in the SharePoint project, it will be automatically updated in the Server Project. If you change the icon to another file, you’ll need to repeat those steps. So now you have this:

Next let’s add code to the createSharePointChrome() function:

//add the links
$("<A class='msls-sharepoint-chrome-link ui-link home-link' href='#'>" +
      "<img style='margin-left: 4px; margin-right: 4px;' height='24' width='24'" +
           "src='../custedit.png'</A>" +
   "<A class='msls-sharepoint-chrome-link ui-link' href='" + spUrl + "'>" +
       "<img style='margin-left: 4px; margin-right: 4px; padding: 4px;'" +
             "src='" + spUrl + "/_layouts/15/images/favicon.ico'</A>").appendTo(element);

//use the built-in LightSwitch method for the home page 
$(".home-link", element).click(function () {
    msls.application.navigateHome();
});

Press F5 to run the app and you’ll see the two image links in the chrome. If you used the default appicon, then of course yours will be different.

Getting the User Name on the Server in the HTML Client

Now we want to add the current user name to the chrome. We’ll also throw in the “Sign Out” link. Note that this isn’t specific to SharePoint apps, any HTMLClient may want to use this technique. We’ll use AJAX to call into the LightSwitchApplication on the server which has easy access to this information.

The first thing we need to do is add a generic http handler to our server. Right-click on the CustEdit.Server project node in Solution Explorer. Select Add>New item… – scroll down to find the Generic Handler and name it GetAppInfo.ashx. In the code editor add the following snippet in the ProcessRequest sub.

VB:

 Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

    Using serverContext = LightSwitchApplication.ServerApplicationContext.CreateContext()
        Using spContext = serverContext.Application.SharePoint.GetHostWebClientContext()
            spContext.Load(spContext.Web)
            spContext.ExecuteQuery()

            context.Response.ContentType = "text/plain"
            context.Response.Write(spContext.Web.Title)
            context.Response.Write(",")
            context.Response.Write(serverContext.Application.User.FullName)
        End Using
    End Using
   
End Sub

C#:

public void ProcessRequest(HttpContext context)
{
    using (ServerApplicationContext serverContext = 
           LightSwitchApplication.ServerApplicationContext.CreateContext())
    {
        using (Microsoft.SharePoint.Client.ClientContext spContext = 
               serverContext.Application.SharePoint.GetHostWebClientContext())
        {
            spContext.Load(spContext.Web);
            spContext.ExecuteQuery();

            context.Response.ContentType = "text/plain";
            context.Response.Write(spContext.Web.Title);
            context.Response.Write(",");
            context.Response.Write(serverContext.Application.User.FullName);
        }
    }            
}

This response will give us the user’s name and the “friendly” name of the SharePoint site that launched the app. We could use the name of the site for a link or some alt text (as in this example), regardless it shows you a way to use the SharePoint CSOM from the client. Now that we have the handler in place, we need some AJAX to call the handler. This will still be part of our createSharePointChrome() function. Let’s just replace what we have now with the entire snippet below.

function createSharePointChrome(element, spUrl) {

    //create the links we need for the chrome on O365
    var mySite = spUrl.replace(".sharepoint", "-my.sharepoint")
    var i = mySite.indexOf(".com") + 4;
    mySite = mySite.substring(0, i) + "/person.aspx";
    var signoutUrl = spUrl + "/_layouts/15/signout.aspx";
    var UserName;

    //call the code on the server
    $.ajax({
        type: 'GET',
        data: {},
        url: '../GetAppInfo.ashx',
        success: function success(response) {
            var n = response.split(",");
            myHostWebName = n[0];
            UserName = n[1];

            //add the links
            $("<A class='msls-sharepoint-chrome-link ui-link home-link' href='#'>" +
                  "<img style='margin-left: 4px; margin-right: 4px;' height='24' width='24'" +
                       "src='../custedit.png'</A>" +
            "<A class='msls-sharepoint-chrome-link ui-link' href='" + spUrl + "'>" +
                  "<img alt='" + myHostWebName + "' " +
                       "style='margin-left: 4px; margin-right: 4px; padding: 4px;'" +
                       "src='" + spUrl + "/_layouts/15/images/favicon.ico'</A>").appendTo(element);

            //use the built-in LightSwitch method for the home page 
            $(".home-link", element).click(function () {
                msls.application.navigateHome();
            });

            //right side links
            $("<DIV style='float:right;padding-right:12px'>" +
                "<A id='myname' " + 
                   "style='padding-top:2px' class='msls-sharepoint-chrome-link ui-link' " +
                   "target='_blank' href='" + mySite + "'>" + UserName + "</A> | " +
                 "<A class='msls-sharepoint-chrome-link ui-link' href='" + signoutUrl + "'>" +
                     "Sign Out</A></DIV>"
                ).appendTo(element);
        }
    });
}

And now your chrome should look like this:

That’s it! Now you know:

what the chrome is
how to customize the chrome with some images
how to change the SharePoint app icon
how to use the Home Page function the LightSwitch HTML client library
how to use AJAX to call into the LightSwitch application and SharePoint CSOM on the server

Hope this helps, your comments are always welcome.

Brian Moore - Program Manager, LightSwitch

Announcing Runtime Update 1 - Supporting jQueryMobile 1.3

LightSwitch Team — Tue, 07 May 2013 16:00:00 GMT

When we took a bet on jQuery, jQueryMobile, and datajs for our HTML client, we recognized that we’d need to adapt to the rapid release cadence these frameworks employ. At the same time, we know that small issues in our application runtime can cause considerable strife. Our response is to start delivering a series of small, servicing releases for our HTML client runtime via NuGet that address known bugs and compatibility issues with emerging versions of jQuery, jQueryMobile, and datajs. Today we’ve released the first update onto the NuGet gallery—Microsoft.LightSwitch.Client.JavaScript.Runtime update 1!

This first update adds support for jQueryMobile 1.3 and jQuery 1.9. Especially jQueryMobile 1.3 brings a lot of improvements and bug fixes. Embracing the mobile-first, instead of mobile-only, approach was the main focus of this release. This was achieved by implementing a popular technique called responsive design. New widgets like tables and panels work efficiently on large screens as well as small phones, enabling data-richer scenarios. Also jQueryMobile and jQueryUI are working closer together with the goal of having even more shared code and conventions.

(picture taken from the jQueryMobile 1.3 announcement)

While we cannot add new design time features as part of a runtime update, you can still start using these new controls today by utilizing the power of our custom controls. A combination of our blog posts around custom controls and jQueryMobile’s excellent documentation will help you to get started. Michael dedicated a complete post to the integration of jQueryMobile controls as custom controls and our post about using a maps control to visualize list data explains the API of the LS collection which you need to implement your own table control.

How to update?

Thanks to NuGet the installation of a runtime update is a simple, two-step process. First, you update the “Microsoft.LightSwitch.Client.JavaScript.Runtime” package, which will automatically pull in the latest supported versions of our dependencies. Second, you bump the version numbers of the updated JavaScript and CSS files in the default.htm.

Important: Due to a known bug with NuGet, JavaScript projects and source control you need to unbind your solution from source control before updating the NuGet packages. Open File –> Source Control –> Advanced –> Change Source Control and unbind your solution and the projects it contains. After performing the following steps bind it again and merge in the new changes.

Start by switching to the file view of the solution explorer. The context menu of the HTML client project contains the entry “Manage NuGet Packages…”. Selecting the “Updates” section of the package manager will show the following screen:

Updating the LightSwitch runtime will automatically update jQuery and jQueryMobile for you. After a quick restart of Visual Studio and reopening the project you need to make a few changes in the default.htm:

light-theme.css to light-theme-1.0.1.css
mlsls-light.css to msls-light-1.0.1.css
jquery.mobile.structure-1.2.0.min.css to jquery.mobile.structure-1.3.0.min.css
msls-1.0.0.min.css to msls-1.0.1.min.css
jquery-1.8.2.min.js to jquery-1.9.1.min.js
jquery.mobile-1.2.0.min.js to jquery.mobile-1.3.0.min.js
msls-1.0.0.min.js to msls-1.0.1.min.js

F5 and you are done. Since NuGet packages are defined on a per project basis, you need to perform those steps for every project you have. Also keep in mind that jQuery and jQueryMobile remove functionality that was previously deprecated with every release. If you are heavily customizing your LightSwitch application using JavaScript code this might affect you. Make sure to carefully study their release notes in order to learn about new features and removed functionality:

If you have any problems with the upgrade or want to learn more about using some of the new features you can find us in the forums or the comments section below.

- Heinrich Wendel, Program Manager LightSwitch Team

ASP.NET SignalR and LightSwitch (Matt Sampson)

LightSwitch Team — Fri, 03 May 2013 13:07:00 GMT

Maybe you've already heard about ASP.NET SignalR and how you can use it to do "real-time" web.

Or how it lets you easily push content from your server up into your HTML5 Client, or back from your client to the server.

But have you seen how you can make SignalR and LightSwitch work together???

If you want to know more, then check out this blog post from Matt Sampson - ASP.NET SignalR and LightSwitch.

Thanks all,

-Matt Sampson

Localizing a LightSwitch Application (Heinrich Wendel)

LightSwitch Team — Thu, 02 May 2013 17:04:00 GMT

With Visual Studio Update 2 we finally added a functionality a lot of you (especially outside the US) have been waiting for, the localization of LightSwitch applications into multiple languages. Until then you had to hardcode a single language into your application. Finally, international companies and companies that are located in countries that use multiple official languages, can provide users with an application in their preferred language.

The concept is simple. For each DisplayName or Description property that you want to localize, you specify a $(ResourceString) instead of the actual string. For each translation, you create a resource file which contains the actual values in the target language. At runtime, we will automatically detect the preferred language based on the user’s browser or operating system setting. In addition to that, strings that you specify on the server, e.g. names for entities or properties, are also propagated to the client, no need to translate them twice. And, you can also localize messages that are created while running business logic on the server or validation rules on the client.

We already published a walkthrough on MSDN which gives you a detailed step by step description. But, there is only one language that everybody in the world understands. It is the language of pictures. I tried to create a visual walkthrough for you guys. Click the picture bellow to start your localization experience (it might take a while to load, there are quite a few pictures in it).

For some reason it doesn’t work as expected, your see strings like “/client/ErrorMessage” or “$(ContactPhone)” in the UI? As so often, the devil lies in the details. In the last part of this blog post I’ll try to summarize the questions and problems that came up most often in our forums:

What are valid keys for resource identifiers? You should only use letters and numbers for resource identifiers. Don’t use spaces and to be absolutely safe stay away from any special characters.
What is the naming convention for Server resource files? The server project uses “Service.resx” (Service, not Server ) for the neutral translations and “Service.LocaleID.resx” for individual languages. Those files live in the root folder of the server project.
What is the naming convention for Silverlight resource files? The Silverlight client uses “Client.resx” for the neutral translations and “Client.LocaleID.resx” for individual languages. Those files live in the root folder of your Silverlight client project.
What is the naming convention for HTML resource files? For the HTML client use “client.lang-LocaleID.resjson” for all languages. Simply use the appropriate locale id for the neutral language. Those files live in the “Content/Resources” folder of your HTML client project.
Where do I specify the neutral language? You can specify the neutral language in your project properties. It defaults to your Visual Studio language when creating a new project.
Why are the naming conventions for HTML and Silverlight/Server different? We tried to follow the conventions of the technologies the individual projects are based on. For the Server and Silverlight we use the .NET conventions and resx files, for HTML we are following the WinJS naming conventions using resjson files.
How does the language detection mechanism work? If you run a Silverlight application we will use the user’s Windows language as preferred language. If you run an HTML app we will use the browser setting.
Can I present users with a choice of languages? Not at the moment, sorry. Usually it is pretty easy to change the language of your browser though and you can also change the language of Windows on a per user basis.
How does the server know about the client’s language? With every HTTP request your browser sends the client language as part of the ACCEPT_LANGUAGES header. ASP.NET will run the thread that is processing the request using the culture specified by the header.
My client shows the raw resource strings, what is wrong? Did you translate both your client and server into the specific language? We will only pick up a language if both projects actually provide translations, otherwise users could end up with a mix of languages in their UI.

That’s it

I tried to keep it a bit shorter this time. As always, use the comments section and our forums for feedback and questions.

- Heinrich Wendel, Program Manager LightSwitch

Publishing LightSwitch apps for SharePoint to the Catalog (Brian Moore)

LightSwitch Team — Mon, 29 Apr 2013 15:11:00 GMT

Ok, your app is done, you’ve learned how to authenticate it and publish it like I demonstrated in my previous post. When you’re done with the publish wizard on a SharePoint app, you’ll see an explorer window open with folders for each version of your app. Probably just 1.0.0.0 so far - in the folder is a .app package. This .app package will always contain the information SharePoint needs to install and run your app. Things like permissions, end points, project artifacts (like lists), etc. This .app package is what you give to SharePoint to make your app available to users.

If you published an autohosted app, then the package also contains the LightSwitch application itself. All the files that make up your web site. If you published a provider-hosted app, then all of those files that make up your web site are already on a live server somewhere (if you did a remote publish) or in a WebDeploy package waiting for you to deploy the package. In the provider-hosted case, you do want to make sure that your LightSwitch app is running before any users install the app package into SharePoint otherwise they’ll just see a 404 of some sort.

So now what do you do with this .app package?

Apps for SharePoint

First, a little background on the app model for SharePoint. Everything on SharePoint 2013 is an app. All the things you might be familiar with (list, libraries, code) are packaged into an app for SharePoint. This way, whenever the user wants something for the site, there’s an app for that. Where do you get apps? From the store. For Office365 there is a public store that contains public apps. Some are free, some have other licensing models. You can also get apps from a private app catalog for your SharePoint site. So basically you have access to public apps and private apps. LightSwitch can build apps for either scenario, though there are some things to be aware of which I’ll cover later.

So when you publish a LightSwitch app for SharePoint, you will publish not only your web application but also the app package that will be uploaded to the App Catalog or SharePoint store. So let’s take a look at this from end to beginning.

The App Catalog

Even though you can put your LightSwitch app in both the app catalog or the SharePoint Store, I’m going to walk through the App Catalog scenario. Your LOB apps written in LightSwitch (especially your first one) are likely to be “private” apps so first things first. When you want to add an app to your site, you can go to the site contents and, well, add an app.

When you click add, you’ll see all the apps that come with SharePoint, all the lists and libraries you might be familiar with. When you put your LightSwitch app in the app catalog, it will show up here as well. Since there are a lot of apps already for your site, you can look for apps just from your catalog by clicking the “From Your Organization” link.

Yours will probably look like this (empty). Also notice the link to the “public” store.

So we need to get our app into our catalog – and if this is the first time you’re working with the catalog, you might not even have a catalog yet. A private app catalog for apps “From Your Organization” is another SharePoint site collection in your tenant. It’s a special site template that any site in your tenant can attach to, to install apps. So to create one we need to go to Site Admin to create an app catalog. First step is to go to SharePoint central admin.

Then go to “apps” and select “App Catalog”

You’ll see an option to create a new catalog or use an existing one, if you don’t have one, just create a new one – you can change anything you need to later. It will take SharePoint a few minutes to provision the new site and the page won’t necessarily refresh on it’s own (in case you’re waiting for that spinning circle to go away to know when it’s done).

Now that you have the catalog you need to get the app in the catalog. Remember this catalog is just another site for SharePoint so you can navigate to it and that’s how you’ll add apps. You can see your app catalog listed in your site collections in the admin center.

Navigate to that URL and you are at your app catalog. Remember that URL because there isn’t really any other way to get to it other than what I just showed you (at least not that *I* know of). Drill down to the “Apps for SharePoint” section and you’ll see any apps you have that can be installed onto a SharePoint site. Since I just created this catalog I don’t have any apps.

Putting Your App in the Catalog

We’re almost there… now we just need to take the .app package we built and put it into the catalog. Just click the “new app” link and you’ll be prompted for the package. When the app is uploaded you will be prompted to fill in the metadata for the app catalog. There is a lot there, but it’s all optional except the name. You can provide a description, change the icon, add images to show case the app. You can even make it a “noteworthy” app that will pop it to the top of the list when users browse the catalog.

Once you’ve saved the app, you’ll see it in the list. Remember that this is our “private” catalog that contains apps just “From Your Organization” but your users will need permission to the app catalog site to see it. First, here’s what you should see in the catalog. Over time you’ll see more apps here and also versions of each app that you can manage throughout your organization.

To grant permission to the catalog you assign permissions to the catalog site just like you do any other SharePoint site collection – through the Site Settings for that site. This will let you determine which users on your site can *see* apps from this catalog. To install apps on a particular site, the user must have permission to install apps on that particular site. So seeing something in the catalog is necessary but not sufficient for installing it. And as you might guess, you may not want all users to install apps and by default they won’t be able to until you grant them permission. So, first they need permission to the catalog site to even see the apps. Then they need permissions on an individual site, to be able to install the app.

And finally, just to add another layer on here – just because an app is installed on a site, doesn’t mean all users can run the app. You may not want everyone to access an app that’s installed and you can again, control that by setting the appropriate permissions in SharePoint. That’s enough about permissions for now – suffice it to say, that just like any other LOB LightSwitch app, you can still control who accesses your application when it’s running on SharePoint, using what will become (if it’s not already) the familiar SharePoint permission model.

After all that, in this example we’re just going to keep using our own user account which owns the site collection for the app catalog and the dev site, but you can drill in here whenever you’re ready ;)

Installing Your App from the Catalog

Once you have the app in the catalog, there is one path your users will go through to install the app, or any app for that matter. Again, the user must have permission to install apps onto the site. Note, that when using a dev site, there are a few ways to install apps onto the site, but for now, let’s walk through what end users will see on non-developer sites. I used a project site template for this example, but it will look similar to most templates when it comes to installing apps. First, go to the site contents:

From there you will have the option to “add an app”. Once you click to add the app you’ll see a list of the apps available to the site. Here you can find your app in the list of all the apps - you see our CustEdit app below.

You can also filter the list to show only the apps in your catalog by choosing “From Your Organization” as we did at the beginning of the blog. Note, that this is the same catalog I was using above, this catalog is shared among all the sites in my tenant – it is “for my organization”. So everyone in my company or tenant can see this app if I give them permission. So you can configure permissions for installing or browsing apps in just about any configuration you can imagine. For example, even though your organization can share a single catalog, you can still restrict who can see particular apps in that catalog. And also restrict who can install apps on a given site. The flexibility that is there though is a bit beyond the scope of what I want to cover here (though it feels like I just covered it so I might as well summarize).

Users need to be granted permission to the app catalog site to be able to see apps “From your Organizaition.” You just navigate to the catalog site, then the site settings and set perms like you do for any other SharePoint site
Users need permission to install apps on a particular SharePoint site (for example Site Owners can install apps, Site Members cannot)
You can restrict who can see an app in the catalog by setting permissions for a particular app. By default anyone who you gave access to in the first bullet above will be able to see the app (perms are inherited) but you can change that
Once an app is installed on a site, you can further restrict who can run that app via permissions on the installed app

But for now, since you likely own everything you’re working with, you really don’t need to set any perms yet.

So that’s the app store experience. Once you install an app from the catalog it will show up under your Site Contents just like every other app installed on that site.

Installing Apps on a Dev Site

You might be wondering by now why you never had to do any of this while you were developing your app. Developer sites in SharePoint give you some extra functionality to make life as a SharePoint developer easier :) and that’s why. But that only works on dev sites, to get apps onto non-dev sites, you need to go through the catalog.

There is, as you might have guessed another way to install an app onto a dev site without going through the catalog. When you F5 your app from VS, the app package is technically installed on the SharePoint dev site and pointing to your application on localhost. VS does all of that for you. If you want to actually run your app from the cloud (in this case) instead of localhost, you can do that on a dev site without putting it into the catalog. You do that by going to the dev site’s “apps in testing” section and selecting “new app to deploy”.

There’s one thing to be aware of going down this path particularly if you’ve been developing your app against this same site. Remember that I said VS installs the app for you while you’re debugging? That means your app is probably still installed on this site and if you try to install it again, SharePoint will tell you it’s already installed. VS doesn’t uninstall it every time you stop debugging because, well, it would take longer to install it again the next time you started debugging, we might as well leave it there -- you probably debug much more often than you do this catalog stuff. Anyway, if you do get that error, just click in the ellipses by your app and remove it, then try the deploy again.

Finally, another option you have (and you may very well want test this out some day) is upgrading an app that’s already installed. This is what would happen if you release a new version into the app catalog and users had installed your app. They would get notification that a new version is available and SharePoint would upgrade your app. This would include upgrading your autohosted database if you had one. To try that out, go up to the ribbon and select “Upgrade an app”.

The SharePoint Store

You probably want me to wrap this up by now, but I do want to make a quick mention of the SharePoint Store or the public catalog. The process for getting your apps into the Office 365 SharePoint Store is, as you might well imagine, very different. I’m not going to cover that yet. A few notable things if you want to try this yourself before I get to it. You will need to make some changes to the manifest to get the store to “accept” your app. The error will be descriptive but if you need help leave a comment or post in the forum and we can try to cover it briefly. Also, the SharePoint Store does not *yet* accept autohosted SharePoint apps. That means you’d have to host the LightSwitch application yourself and deal with multi-tenancy in your LightSwitch app, not to mention pay for hosting it. So we have a few things we need to make much easier before I cover that in a post.

Finally…

That’s all for now, but before I go I want to mention one last thing – partially because I seem to get more comments on a blog when I mention Ms. Massi somehow. Beth got engaged a few weeks back and for someone who has done as much for our community as she has, we all certainly wish her the best. Congrats!

- Brian Moore, Program Manager, LightSwitch

Dashboard Reports with LightSwitch, WebAPI and ServerApplicationContext– Part Deux (Steve Lasker)

LightSwitch Team — Thu, 25 Apr 2013 15:04:00 GMT

Overview

With a little homage to Hot Shots Part Deux we’ll drill in a bit deeper utilizing the ServerApplicationContext for creating Dashboard reports. Part 1 is here: Create Dashboard Reports with LightSwitch, WebAPI and ServerApplicationContext

LightSwitch Productivity/Extendability disclaimer: As with Part 1, this isn’t the normal point & click RAD productivity you’ve likely become used to with LightSwitch. This is more of an advanced scenario, showing the extension points we’ve enabled to extend the out of the box features.

In Part 2 I’ll cover:

Adding a second set of charts that enable drilling into monthly data to see a weekly slice of data based on the click events of the chart
Adding date controls to select the date range of the report
Interactively making selections in the screen, changing the data displayed
Calling Stored Procedures from WebAPI

Let’s get started…

Create the AmazingPie Restaurant Point of Sale (POS) database

As with part one, we’ll utilize the AmazingPie POS Sample database. However, this code also assumes you’ve already completed Part 1.

Adding the new Report

We’ll start by adding a WebAPI, allowing us to query based on a time period of Monthly or Weekly.

1. As in Part 1, we’ll need to switch from the logical view to file view so we can interact directly with files.

2. Within the Reports folder, add the new Web API controller

If you’ve followed the previous step, Visual Studio will remember the last few template types you’ve added, and you’ll see a shortcut in your menu:

This will provide an abbreviated prompt. Name our controller SeatedGuestsController

3. Remove all the template Get, Put, Post, Delete methods to make way for our new Report methods.

4. Add the Extension Methods for query operators

Note: this is an important step. Without these extension methods, the LINQ query operators will not be available on the DataWorkspace, and SqlFunctions will require the full namespace

VB
Option Infer On
Imports System.Net
Imports System.Web.Http
Imports Microsoft.LightSwitch
Imports LightSwitchApplication
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Objects.SqlClient

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using Microsoft.LightSwitch;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Objects.SqlClient;

5. Add the following Method to expose our WebAPI Method

The get will capture the period, and we’ll call sub methods for each specific report


VB
Public Class SeatedGuestsController
    Inherits ApiController
    Public Function GetValue(period As String, id As String, 
                             beginDate As DateTime, endDate As DateTime)

        Dim retVal As Object = Nothing
        If period.ToLower() = "monthly" Then
            retVal = GetMonthlyReport(id, beginDate, endDate)
        ElseIf period.ToLower() = "weekly" Then
            retVal = GetWeeklyReport(id, beginDate, endDate)
        End If
        Return retVal
    End Function

    Public Function GetMonthlyReport(location_Id As String, 
                                     beginDate As DateTime, 
                                     endDate As DateTime) As Object
        Return Nothing
    End Function

    Public Function GetWeeklyReport(location_Id As String, 
                                    beginDate As DateTime, 
                                    endDate As DateTime) As Object
        Return Nothing
    End Function
End Class

C#
public object Get(string period, string id, DateTime beginDate, DateTime endDate) {
    object retVal = null;
    if (period.ToLower() == "monthly") {
        retVal = GetMonthlyReport(id, beginDate, endDate);
    } else if (period.ToLower() == "weekly"){
        retVal = GetWeeklyReport(id, beginDate, endDate);
    }
    return retVal;
}

private object GetMonthlyReport(string location_Id, DateTime beginDate, DateTime endDate) {
    return null;
}
private object GetWeeklyReport(string location_Id, DateTime beginDate, DateTime endDate) {
    return null;
}

6. Add the GetMonthlyReport LINQ Query

From Part 1, you’ll remember we instance the ServerApplicationContext within a using block to be sure it gets disposed when we’re done. We also call report.Execute() to make sure the LINQ query is executed before the Context is disposed.

Looks like there is a horse before the cart, so we need to get the ordering correct.

Lastly, we use the SqlFunctions to create a string format of Month/Year which we need for our Chart to display properly. Using SqlFunctions is important as the query is sent to SQL Server, which wouldn’t know how to parse: String.Format("{0}/{1}", x.Key.Month, x.Key.Year)

VB
Public Function GetMonthlyReport(location_Id As String,
                                 beginDate As DateTime,
                                 endDate As DateTime) As Object
   Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

        Dim report = From olc In context.DataWorkspace.AmazingPieData.OrderLifeCycles
                     Where olc.Order.Location.Location_Id = location_Id AndAlso
                           olc.EventType_Id = "Complete" AndAlso
                           olc.EventTime >= beginDate AndAlso
                           olc.EventTime <= endDate
                     Group By olc.EventTime.Year,
                              olc.EventTime.Month,
                              olc.Order.Table.TableType.TableType_Id,
                              olc.Order.Table.TableType.DisplayOrder Into Group
                     Order By Year, Month, DisplayOrder
                     Select MonthYear = SqlFunctions.StringConvert(Month, 2) + "/" +                                         
                                        SqlFunctions.StringConvert(Year, 4),
                            TableType_Id,
                            Seats = Group.Count()

        Return report.Execute()
    End Using
End Function
C#
private object GetMonthlyReport(string location_Id, DateTime beginDate, DateTime endDate) {
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext()) {
    var report = context.DataWorkspace.AmazingPieData.OrderLifeCycles
        .Where(olc => (olc.Order.Location.Location_Id == location_Id) &&
            (olc.EventType_Id == "Complete") &&
            (olc.EventTime >= beginDate) &&
            (olc.EventTime <= endDate))
        .GroupBy(x => new { 
            x.EventTime.Year, 
            x.EventTime.Month, 
            x.Order.Table.TableType.TableType_Id, 
            x.Order.Table.TableType.DisplayOrder })
        .OrderBy(o => new {o.Key.Year, o.Key.Month, o.Key.DisplayOrder})
        .Select(x => new { 
            MonthYear = SqlFunctions.StringConvert((decimal) x.Key.Month, 2) + "/" + 
                SqlFunctions.StringConvert((decimal)x.Key.Year, 4), 
            TableType_Id = x.Key.TableType_Id, 
            Seats = x.Count() 
        });
        return report.Execute();
    };
}

7. Add the GetWeeklyReport LINQ Query

The query is very similar to the Monthly report, however this time we’re grouping by the day of the week using the SqlFunctions TSQL equivalent of DATEPART(DW, EventTime)

VB
Public Function GetWeeklyReport(location_Id As String, 
                                beginDate As DateTime, 
                                endDate As DateTime) As Object

    Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

        Dim report = From olc In context.DataWorkspace.AmazingPieData.OrderLifeCycles
                     Where olc.Order.Location.Location_Id = location_Id AndAlso
                           olc.EventType_Id = "Complete" AndAlso
                           olc.EventTime >= beginDate AndAlso
                           olc.EventTime <= endDate
                     Group By DayOfWeek = SqlFunctions.DatePart("DW", olc.EventTime),
                              olc.Order.Table.TableType.TableType_Id,
                              olc.Order.Table.TableType.DisplayOrder Into Group
                     Order By DayOfWeek, DisplayOrder
                     Select DayOfWeek,
                            TableType_Id,
                            Seats = Group.Count()

        Return report.Execute()
    End Using
End Function

C#
private object GetWeeklyReport(string location_Id, DateTime beginDate, DateTime endDate) {
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext()) {

        var report = context.DataWorkspace.AmazingPieData.OrderLifeCycles
            .Where(olc => (olc.Order.Location.Location_Id == location_Id) &&
                (olc.EventType_Id == "Complete") &&
                (olc.EventTime >= beginDate) &&
                (olc.EventTime <= endDate))
            .GroupBy(x => new { 
                DayOfWeek = SqlFunctions.DatePart("DW", x.EventTime), 
                x.Order.Table.TableType.TableType_Id, 
                x.Order.Table.TableType.DisplayOrder
            })
            .OrderBy(o => new {o.Key.DayOfWeek, o.Key.DisplayOrder})
            .Select(x => new {
                DayOfWeek = x.Key.DayOfWeek,
                TableType_Id = x.Key.TableType_Id,
                Seats = x.Count()
            });
        return report.Execute();
    }
}

Testing with Fiddler

Let’s do our normal Fiddler testing to be sure we have this portion correct before delving into javascript

1. Hit F5 to get the base URL of your app

2. Launch Fiddler and paste the base URL into the Composer

3. Modify it to include the following query string.

http://localhost:30550/reports/seatedGuests/?period=monthly&id=BelSquare%20&beginDate=2010-05-07&endDate=2013-02-23

Note that we have period =monthly, and we’ve set the begin/endDate using the JSON serialized format.
(I’ve removed the time since we zero it out anyway)

Tip: Had I not tested the output with Fiddler, I wouldn’t have noticed that SqlFunctionsStringConvert was padding the month & year

Without setting the string length

Using the StringConvert overload to set the length

MonthYear = SqlFunctions.StringConvert((decimal) x.Key.Month)  
  + "/" +   SqlFunctions.StringConvert((decimal)x.Key.Year)

MonthYear = SqlFunctions.StringConvert((decimal) x.Key.Month, 2)
    + "/" + SqlFunctions.StringConvert((decimal)x.Key.Year, 4)

Adding the Graphs

With our WebAPI Reporting Service tested and ready, let’s add the new graphs

1. Switch back to Logical View

2. Open the Locaion_View screen

3. Select the Report Tab Rows Layout control to get the add control menu and select New Custom Control…

4. Using the property sheet, rename it ServedGuests

5. Choose Write Code –> ServedGuests_render

6. Add the following code to create the chart containers <div/> tags

Note: greyed out code indicates the previous code as the insertion reference point


myapp.Location_View.ServedGuests_render = function (element, contentItem) {
    // Each KendoUI chart needs it's own container
    // One for the Monthly graph
    var customersPerMonthChartContainer = $('<div/>');
    customersPerMonthChartContainer.appendTo($(element));

    // One for the weekly charts.
    var customersPerWeekChartContainer = $('<div/>');
    customersPerWeekChartContainer.appendTo($(element));

7. Add Kendo UI Chart Control for the Monthly Report

Note that for now, we’re hardcoding the date range. We’ll build this up slowly to avoid tripping over coding issues with things like javascript dates. We’ve also placed this within a function, which we call immediately. Later on, we’ll add the ability to change the date range, and for each date change, we’ll want to redraw the chart.


displayMonthlyChart()

function displayMonthlyChart() {
    //setup the URI to the report, with the Location Parameter, and the date range
    var reportsAPI = "../reports/seatedGuests/?period=monthly&id=" + contentItem.screen.Location.Location_Id +
        "&beginDate=2010-07-20&endDate=2013-01-04";

    // Add a Kendo UI BarChart
    customersPerMonthChartContainer.kendoChart({
        // set the theme
        theme: $(document).data("kendoSkin") || "default",
        // Set the sourc to use our WebAPI, with a json payload
        dataSource: {
            transport: {
                read: {
                    url: reportsAPI,
                    dataType: "json"
                }
            },
            // use the TableType_Id to create groupings in the chart
            group: {
                field: "TableType_Id"
            }
        },
        title: {
            text: "Customers Per Month, By Table Type"
        },
        legend: {
            position: "bottom"
        },
        // Set the chart to a column (vertical) report type
        seriesDefaults: {
            type: "column",
            labels: {
                visible: true,
                // the 0:n0 means parameterize the label with the value - first 0
                // and then :n0 means use numeric with 0 decimals
                format: "{0:n0}"
            }
        },
        // map the vertical series to the number of seats, 
        // notice the field: "Seats" matches the name in the json payload returned from our WebAPI
        series: [{
            field: "Seats",
            name: "",
            colorField: "userColor",
            labels: {
                rotation: 320
            }
        }],
        valueAxis: {
            labels: {
                format: "{0}"
            }
        },
        // And the category is grouped by Month, also from our WebAPI json payload
        categoryAxis: {
            field: "MonthYear",
            labels: {
                //format: "{0:n0}"
                format: "{0}",
                rotation: 300
            }
        }
    }); // Close of Kendo Monthly Chart
}

8. Let’s F5 to see how things work so far:

Not so bad. We have quite a lot of data here, visually presented, allowing your users to know where to drill in, looking for more details.

Add Weekly Chart

We can see how the Bel Square location is trending during the year, but what about weekly, for a given month? We’ll now add another chart that will display the averaged seated guests, by day of the week for the month the user clicks on.

1. Add the displayWeeklyChart function, just blow our Monthly chart, and still within the render method

Note: we’re still commenting out the date ranges as we’ll plumb this through once we get the basic charts displayed. I’ve also placed the weekly chart within a function as we only want to show this chart when the user clicks on a time period within the monthly chart. Each time they click a different time slice, we’ll want to update the chart by calling the displayWeeklyChart function.

        }
    }); // Close of Kendo Monthly Chart 
}
function displayWeeklyChart(beginDate, endDate) {
    customersPerWeekChartContainer.fadeIn(500);
    //setup the URI to the report, with the Location Parameter, and the date range
    var reportsAPI = "../reports/SeatedGuests/?period=weekly&id=" + 
        contentItem.screen.Location.Location_Id +
        "&beginDate=2010-07-20&endDate=2013-01-04";
  
    customersPerWeekChartContainer.kendoChart({
        theme: $(document).data("kendoSkin") || "default",
        dataSource: {
            transport: {
                read: {
                    url: reportsAPI,
                    dataType: "json"
                }
            },
            group: {
                field: "TableType_Id"
            }
        },
        title: {
            text: "Customers Per Week, By Table Type"
        },
        legend: {
            position: "bottom"
        },
        seriesDefaults: {
            type: "column",
            labels: {
                visible: true,
                format: "{0:n0}"
            }
        },
        series: [{
            field: "Seats",
            name: "",
            colorField: "userColor"
        }],
        valueAxis: {
            labels: {
                format: "{0}"
            }
        },
        categoryAxis: {
            field: "DayOfWeek"
        }
    });
};

Add a Click Event Handler to the Bar Chart

To drill into the data, we’ll enable click events on the chart. Most charts support this powerful feature.

1. Below the configuration of the Monthly chart, we’ll add the seriesClick event handler


categoryAxis: {
            field: "MonthYear",
            labels: {
                //format: "{0:n0}"
                format: "{0}",
                rotation: 300
            }
        }, // <-- note the comma to continue the configuration of the chart
        // hook up the Click event to drill into weekly reports
        seriesClick: onSeriesClick
    }); // Close of Kendo Monthly Chart
}
function onSeriesClick(e) {
    // Split the category label into Month/Year
    var dateElements = e.category.split("/");
    // javascript uses Month 0 as January - so, start from Jan, 
    // and add the selected month, which needs to be decremented to account for 0 base javascript
    // compared to the rest of the human readable world
    var beginDate = new Date(dateElements[1], parseInt(dateElements[0]) - 1, 1);
    var endDate = new Date(beginDate.getFullYear(), beginDate.getMonth() + 1, 
                           beginDate.getDate(), beginDate.getHours());
    displayWeeklyChart(beginDate, endDate);
}

function displayWeeklyChart(beginDate, endDate) {
    customersPerWeekChartContainer.fadeIn(500);

Adding Date Parameters

We’ll now add begin and endDate controls to narrow the results.

Adding Screen Parameters

We’ll want these values to come from the user, and don’t forget, LightSwitch uses a ViewModel approach

1. Open the Location_View screen

2. In the Reports Tab, add a new Group

Name it dateRange
Change the Group to Column layout
Move it below the Served Guests custom control

3. Add two new screen properties

On the top of the screen designer, select Add Data Item…
Change the Member Type to Local Property

Type = Date
Uncheck Is Required
and name it beginDate

Add another optional local property for endDate with the same options

4. Add beginDate and endDate screen properties as controls on the screen

Below the date Range Columns Layout (grouping), click the add button to add the two local properties

Change the Control Types to Date Picker for both of the screen parameters
You’re screen designer should now look like this…

5. Lets set the default values for the date range properties:

From the Write Code menu, select created

Default the beginning date to the date the location opened for business and the end date to today
While we’re here, we’ll also set the screen display name to the Id of the location


myapp.Location_View.created = function (screen) {
    screen.beginDate = screen.Location.LocationOpened;
    screen.endDate = new Date();
    screen.details.displayName = "Location: " + screen.Location.Location_Id;
};

6. Change our Monthly Chart to use the new screen parameters

Note: we use the Date.toJSON() function to convert the date to a format that will serialize across URLs.


function displayMonthlyChart() {
    if (contentItem.screen.endDate < contentItem.screen.beginDate) return;

    var reportsAPI = "../reports/seatedGuests/?period=monthly&id=" + 
         contentItem.screen.Location.Location_Id +
        "&beginDate=" + contentItem.screen.beginDate.toJSON() +
        "&endDate=" + contentItem.screen.endDate.toJSON();

7. Lets hange the displayWeeklyChart() to also use the date range screen parameters by replacing the fixed date with the begin/end date parameters. Note the toJSON() conversion to make sure we encode the dates for URLs


function displayWeeklyChart() {
    //setup the URI to the report, with the Location Parameter, and the date range
    var reportsAPI = "../reports/SeatedGuests/?period=weekly&id=" + 
        contentItem.screen.Location.Location_Id +
        "&beginDate=" + contentItem.screen.beginDate.toJSON() +
        "&endDate=" + contentItem.screen.endDate.toJSON();

    customersPerWeekChartContainer.kendoChart({

8. We’ll now Databind the date ranges to re-display the monthly report when the dates change. We’ll also hide the chart until the user re-clicks a monthly slice.


// add the whole thing to the LightSwitch custom control
customersByTableTypeContainer.appendTo($(element));

// Databind the date range changes to rebuild the chart
contentItem.dataBind("screen.beginDate", function () {
    customersPerWeekChartContainer.fadeOut(500);
    displayMonthlyChart();
});
contentItem.dataBind("screen.endDate", function () {
    customersPerWeekChartContainer.fadeOut(500);
    displayMonthlyChart();
});

displayMonthlyChart();

Run it and notice that now you can click on a month to get the weekly breakdown and when we change the dates, the charts will refresh.

Using Stored Procedures

Since reporting is typically based on aggregating large amounts of data, and you may be working with existing systems, your DBA may already have several stored procedures created, and you just need to return the results. Lets replace the weekly query with a stored procedure.

Getting the Connection String

To call the stored procedure, we’re going to use the traditional ADO.net objects directly. SqlConnection, SqlCommand and SqlDataReader. Of course, we need a connection string which we’ll read from the ConfigurationManager. This requires an additional reference.

1. From the File view, select Add Reference on the Server project

2. Check off System.Configuration

3. Open the SeatedGuestsController

4. Add the using/import statements for referencing the SQL ADO.net objects and the System.Configuration namespace

VB
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

5. Modify the GetWeeklyReport code to call the stored procedure

VB
Public Function GetWeeklyReport(location_Id As String,
                                beginDate As DateTime,
                                endDate As DateTime) As Object

    Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

        Using conn As SqlConnection =
            New SqlConnection(ConfigurationManager.ConnectionStrings(
                context.DataWorkspace.AmazingPieData.Details.Name).ConnectionString)

            Dim cmd As New SqlCommand()
            cmd.Connection = conn
            cmd.CommandText = "GetSeatedGuestsPerTableTypePerLocationByWeekday"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New SqlParameter("@location_Id", location_Id))
            cmd.Parameters.Add(New SqlParameter("@beginDateRange", beginDate))
            cmd.Parameters.Add(New SqlParameter("@endDateRange", endDate))
            cmd.Connection.Open()

            Using reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                Dim reportResult = (From dr In reader.Cast(Of IDataRecord)()
                                    Select DayOfWeek = dr.GetInt32(0),
                                           TableType_Id = dr.GetString(1),
                                           Seats = dr.GetInt32(2)).ToList()

                Return reportResult
            End Using
        End Using
    End Using
End Function

C#
private object GetWeeklyReport(string location_Id, DateTime beginDate, DateTime endDate) {
    object reportResult = null;
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext()) {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings
               [context.DataWorkspace.AmazingPieData.Details.Name].ConnectionString)) {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "GetSeatedGuestsPerTableTypePerLocationByWeekday";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@location_Id", location_Id));
            cmd.Parameters.Add(new SqlParameter("@beginDateRange", beginDate));
            cmd.Parameters.Add(new SqlParameter("@endDateRange", endDate));
            cmd.Connection.Open();
            // Execute the reader into a new named type to be json serialized
            using (SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) {
                reportResult = reader.Cast<IDataRecord>()
                                .Select(dr => new {
                                    DayOfWeek = dr.GetInt32(0),
                                    TableType_Id = dr.GetString(1),
                                    Seats = dr.GetInt32(2)
                                    }
                                    ).ToList();
            }
        }
        return reportResult;
    }
}

Bonus Round

In case you’re still reading, and not ready to leave, I’ve got one more enhancement. It sure would be nice to display the names of the week.

Changing the Weekly chart to show days of the week

1. In the displayWeeklyChart, add the following code to the displayWeeklyChart function to create an array for the days of the week, and use the Kendo chart dataBound event to convert the array to the categories of the chart.


function displayWeeklyChart(beginDate, endDate) {
    //setup the URI to the report, with the Location Parameter, and the date range
    var reportsAPI = "../reports/SeatedGuests/?period=weekly&id=" + 
        contentItem.screen.Location.Location_Id +
        "&beginDate=" + beginDate.toJSON() +
        "&endDate=" + endDate.toJSON();

    var daysOfWeekJSON = [
                        { "DayOfWeek": "Mon" },
                        { "DayOfWeek": "Tues" },
                        { "DayOfWeek": "Wed" },
                        { "DayOfWeek": "Thur" },
                        { "DayOfWeek": "Fri" },
                        { "DayOfWeek": "Sat" },
                        { "DayOfWeek": "Sun" }];

    customersPerWeekChartContainer.kendoChart({
        theme: $(document).data("kendoSkin") || "black",
        dataSource: {
        },
        categoryAxis: {
            field: "DayOfWeek"
        },
        dataBound: dataBound
    });
  function dataBound(e) {
    var chart = e.sender,
        categories = chart.options.categoryAxis.categories,
        weekNumber;

    for (var i = 0; i < categories.length; i++) {
        weekNumber = categories[i];
        categories[i] = daysOfWeekJSON[weekNumber - 1].DayOfWeek;
    }
  }
};

Wrapping it up

Visual Studio LightSwitch enables rapid productivity for the common components of your app. However, all apps are not created equal. With our extensibility points, developers can take their app in the direction they need, including rich visualizations, leveraging aggregate data from the data source to pixels on the screen.

By leveraging WebAPI, the new aggregate LINQ operators within LightSwitch, the ServerApplicationContext and some great chart controls, we can stitch together some great experiences, building on the “out of the box” features.

With some ADO.net code, we can utilize stored procedures to generate the reports, making sure our DBAs are happy leveraging the power of database query processors. Using parameters, we can narrow the results required by the user, allowing them to drill into specific slices of data.

I hope this blog was helpful and please continue to give us great feedback here and in the forum on what works, what’s tripping you up, and what we should do more of so we can keep you and your customers productive.

Steve Lasker

http://blogs.msdn.com/SteveLasker

Program Manager, Visual Studio LightSwitch

Create Dashboard Reports with LightSwitch, WebAPI and ServerApplicationContext (Steve Lasker)

LightSwitch Team — Mon, 22 Apr 2013 16:38:00 GMT

Overview

Applications are creating and consuming more data than ever before. With so much data, how can your users quickly spot trends and get an overview of what’s important? Just as your car presents a dashboard indicating the top priority items, so can your application.

Matt introduced the ServerApplicationContext in his blog post. This article will build on it to show how to build rich dashboard and reporting apps using LINQ aggregate operators, returning data via WebAPI in a JSON format.

Noticing the length of this post, it’s fair to say this isn’t the normal point & click RAD productivity you’ve likely become used to with LightSwitch. This is more of an advanced scenario, showing the extension points we’ve enabled to extend the out of the box features.

There will be several steps, so let’s recap what we’ll be covering

Create a Restaurant Point Of Sale (POS) database for reporting
Creating an initial Ligthswitch App, adding a few screens to provide navigation to our graphs
Add a LightSwitch custom control to display a Kendo UI DataViz Bar Graph
Add WebAPIs for reporting
Test the WebAPIs with Fiddler
Connect the HTML Client Graph to the WebAPI report

Let’s get started

Create the Amazing Pie Restaurant Point of Sale (POS) database

Download the AmazingPie POS Sample database

Creating the LightSwitch Project

We’ll need a basic app to put the reports within. The app will be fairly straight forward with a few screens using the externally linked Amazing Pie database.

1. First, create a new Lightswtich HTML Application (C# or Visual Basic) named AmazingPieManager

2. Select “Attach to external data source”, connecting to the AmazingPie database we’ve just created

3. Select “Database”

4. Enter the connection information for your AmazingPie database

5. Select all the tables

6. Verify the Data Source is named AmazingPieData, and click [Finish]

Build some viewing screens

Before we create the graphs, we’ll need some screens to navigate around the application .

Locations Browse Screen

1. Right-click on the HTML Client node in the Solution Explorer and select “Add Screen”

2. Select the “Browse Data Screen” template, using the “AmazingPieData.Locations” entity as a Data Source

Personally, I prefer all my screens grouped together by name, then type, so I’ll rename the screen Locations_Browse

3. We’ll change the default display a bit:

Change the Display type from List to Tile List
Reduce the properties to be displayed

Locations View Screen

1. Right-click on the HTML Client node in the Solution Explorer and select “Add Screen”

2. Select the “View Details Screen” template, using the “AmazingPieData.Location” entity as a Data Source

We’ll name this Location_View
Add additional data including Location Tables and click [OK]

3. We’ll change the default display a bit:

Create a Columns Layout group

Move City, State, Zip below this group

Change the Tables to a Tile List
Reduce the properties to Table Id, Table Type and Table Size

I’ve also shortened the list of properties to Type and Size and moved Size last

When done, you’re layout should look like this:

Link the screens through Item Tap

We need to navigate from one screen to another, and we do that with Item Tap

1. Open the Location_Browse screen

2. Select the Tile List for Locations

3. In the property grid, click Item Tap where it says none.

4. Click the dropdown for Choose an existing method

5. Select viewSelected

6. Verify Navigate To: is set to Location View and click [OK]

Add Kendo UI Controls

For the purposes of this post, I’m using Kendo UI DataViz controls. There are many great controls and partners we work with, and the meat of this post will be the same regardless of which controls you use.

1. Download Kendo UI. You can use their 30 day free trial.

2. Copy the Kendo UI .zip file to a location and for the ease of dragging into Visual Studio, extract the files to another directory

3. Copy the Kendo UI css and javascript files

In Visual Studio, toggle from Logical to File View. (Note: the button is a toggle button so you don’t have to actually drop down and select File View)
With the folder you extracted the Kendo UI files, open the styles folder
Drag kendo.dataviz.min.css to your AmazingPieManager.HTMLClient\Content folder
Open the Kendo UI js folder
Drag kendo.dataviz.min.js to your AmazingPieManager.HTMLCLient\Scripts folder

4. Add the style and js file references
Since LightSwitch works as a single page application model, we’ll just need to add the references to the default.htm page

Open default.htm
From the Content\ drag the kendo.dataviz.min.css file just above the </head> close tag
From the \Script directory, drag the kendo.dataviz.min.js below the last js reference
You can also take a moment to add spaces to the page heading of AmazingPieManager
Your project should now look like:

Add Reports Tab

With the Kendo UI Control references added, let’s create a place for our graphs

1. Switch back to the Location_View screen

2. At the top of the screen designer, Right-Click on Tabs and select Add Tab

3. In the properties sheet:

Rename the tab to ReportsTab
Change the Display Name: to Reports

4. Right-Click on the Reports Tab and select Add Custom Control

In the dialog, just select OK for the data path
In the property grid, rename the custom control SeatedGuests

5. Now, add the SeatedGuests render code

With the SeatedGuests Custom Control selected, select SeatedGuests_render from the Write Code tab at the top right of the screen designer

Add a Bar Graph

Before we worry about end to end service calls, we can leverage the simplicity of JSON and create some sample data to display

1. Add the following code to the SeatedGuests_render function


myapp.Location_View.SeatedGuests_render = function (element, contentItem) {
    var sampleData = [
        { "TableType_Id": "Bar", "Seats": 21 },
        { "TableType_Id": "Lounge", "Seats": 23 },
        { "TableType_Id": "Dining", "Seats": 48 }]

2. We’ll now add the <div> to contain the chart, and use jquery to append it to the element passed into the render function


var locationSeatingChartContainer = $('<div/>');
locationSeatingChartContainer.appendTo($(element));

3. Add the code for the Kendo UI DataViz Chart


locationSeatingChartContainer.kendoChart({
    // set the theme
    theme: $(document).data("kendoSkin") || "default",
    // set the datasource to use our sample data for now
    dataSource: {
        data: sampleData
    },
    title: {
        text: "Seats Per Location"
    },
    legend: {
        position: "bottom"
    },
    // Set the chart to a column (vertical) report type
    seriesDefaults: {
        type: "column",
        labels: {
            visible: true,
            // the 0:n0 means parameterize the label with the value - first 0
            // and then :n0 means use numeric with 0 decimals
            format: "{0:n0}"
        }
    },
    // map the vertical series to the number of seats, 
    // notice the field: "Seats" matches the name in the json payload returned from our WebAPI
    series: [{
        field: "Seats",
        name: "",
        colorField: "userColor"
    }],
    valueAxis: {
        labels: {
            format: "{0:n0}"
        }
    },
    // And the category from our WebAPI json payload
    categoryAxis: {
        field: "TableType_Id"
    }
});

4. Now run the app to see how our graph looks

Create the WebAPI to host the Reporting Service

Now that we’ve got the UI working, and a place to display our results, let’s create the WebAPI feed.

If you’re not familiar with WebAPI, you might want to check out the Getting Started series on the ASP.NET blog for a primer on the technology.

Joe Binder introduced WebAPI to LightSwitch projects. I’ll provide the details here for completeness, but I’d suggest reading his post as well.

The basic premise is we’ll create an MVC like convention for calling URLs, routing them to classes that represent services. Depending on the quantity and names of the parameters, the request will be routed to the appropriate method within the class and return our result using the JSON serializer.

A note about Project References

Using WebAPI requires a few additional project references:

Newtonsoft.Json

System.Net.Http

System.Net.Http.Formatting

System.Net.Http.WebRequest

System.Web

System.Web.Http

System.Web.Http.WebHost

To eliminate the need to manually add the project references, we’ll take advantage of the Visual Studio Templates that add these automatically. We’ll add the Web API Controller Class first, as it adds most of the references we need, specifically those needed to configure the Web API routing.

Deployment Note: If you want to publish your app to an IIS server, note that you’ll need one extra component. Using the NuGet Package manager on your Server project, add the NuGet package “Microsoft.Web.Infrastructure”. This is installed already on some IIS machines, but not all of them.

Nested Folders

Joe covered the basics of using WebAPI. As we generate more reports, or use WebAPI for other scenarios, our root folder can get quite filled. To clean things up, we’ll use nested folders and a slightly different configuration for the route.

1. Toggle the View in Solution Explorer to File View

2. We’ll group all our reports under one folder:

Right click on AmazingPieManager.Server and add a new folder named Reports

3. Add the WebAPI Reports Controller

4. Select the AmazingPieManager.Server\Reports folder, and select [add] – [new item]

5. Select Web API Controller Class from the Web Templates and name it SeatingController

6. Before we complete the SeatingController, we’ll add the Route configuration

In the root of the project, select Add New Item
From the Web Templates, add the Global Application Class

7. Configure the route

Using the naming convention of [name]Controller.cs(.vb), we’ll add a route so we can create URIs similar to: http://localhost:22700/reports/Seating/BelSquarelkdsj

Note: For more info on Routes see ASP.net Routing

Add the following using statements to the Global.asax file


VB
Imports System.Web.Routing
Imports System.Web.Http
C#
using System.Web.Routing;
using System.Web.Http;

8. Add the following route to the Application_Start method

VB
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
    RouteTable.Routes.MapHttpRoute(
        "ReportsAPI",
        "reports/{controller}/{id}",
        New With {Key .id = System.Web.Http.RouteParameter.Optional})
End Sub

C#
protected void Application_Start(object sender, EventArgs e) {
    RouteTable.Routes.MapHttpRoute(
            name: "ReportsApi",
            routeTemplate: "reports/{controller}/{id}",
            defaults: new {
                id = System.Web.Http.RouteParameter.Optional
            }
    );
}

VB Note: The MapHttpRoute method is missing from VB intellisense, however it is in the System.Web.Http.dll, so simply type or paste the above text and as long as you’ve followed the steps above for adding the global.asax (adds the System.Web.Http.dll) , and verify you’ve added the imports statements your project should compile.

9. If everything is added properly, your project should now compile.

Testing WebAPI Calls & Results

Although WebPAI calls can be made directly in the browser, many browsers fail to display JSON results by default. It’s also helpful to see the HTTP Results code to debug what’s happening under the covers. For the purposes of this post, I’ll use Fiddler to test and monitor the WebAPI requests rather than fiddle with the browser settings.

As we’re building out our Reports Controller, and testing with Fiddler to initiate the request, we’ll need the base URL configured.

Setting up Fiddler

1. Hit F5 to launch our app and get the root URL. In my case, it’s http://localhost:22700

2. With your app still running Launch Fiddler

3. Select the Composer tab, and enter [your base URL]/reports/Seating/1, then hit [Enter] or the [Execute] button
Remembering our route configuration, we’ve configured all requests that start with reports will use the convention of [name]Controller to route our URL to the SeatingController class. The .NET runtime looks for all types that derive from the ApiController type.
Since our request is a Get, it’s routed to the Get method. Because we passed a parameter, it’s passed to the Get(int id) overload
4. If you double click the Web Session, click on the Raw tab for result formatting, you’ll see “value” returned from our basic WebAPI controller.

Testing a JSON result

With the basics running, let’s crate a simple collection and return it to see how this looks in Fiddler, and ultimately how our HTML client will receive it.

1. Open the SeatingController from our AmazingPieManager.Server project and delete all the generated methods.

2. Since we’re returning an anonymous type, we need to change the return type from string to object

3. Add the following code to construct a collection and return it:


VB
Public Function [Get](id As Integer) As Object
    Dim myList As Dictionary(Of String, Integer) = New Dictionary(Of String, Integer)
    myList.Add("Bar", 21)
    myList.Add("Lounge", 23)
    myList.Add("Dining", 48)

    Dim result = From r In myList
                 Select New With {.TableType_Id = r.Key, .Seats = r.Value}

    Return result
End Function

C#
public object Get(int id) {
    Dictionary<string, int> myList = new Dictionary<string, int>();
    myList.Add("Bar", 21);
    myList.Add("Lounge", 23);
    myList.Add("Dining", 48);
 
    var result = (from r in myList
                    select new { TableType_Id = r.Key, Seats = r.Value });
 
    return result;
}

What we’ve done above is create a simple dictionary of name/value pairs
Then, using LINQ, construct a new type, with named properties, correlating to the key and values of our dictionary.
Because the project defaults to the JSON serializer, we’ll see the results below.

1. Build the project. Note: It’s not actually necessary to hit F5, although you can and set a breakpoint in the Get method.

2. From Fiddler, hit execute to make the HTTP Get Request.

3. This time, Fiddler notices the JSON payload and defaulted the formatting to JSON. You can also switch to TextView, WebView or Raw to see it formatted in a typical JSON format that you may remember seeing earlier when we prototyped our Kendo UI Graph.

Debugging Tip: If you have your service running, you can copy/paste the result into your javascript to test your UI without having to make the service calls

Getting Specific Reports

With the basic WebAPI infrastructure in place, let’s start creating some reports. Reports tend to come in groups. For instance, the manager of Amazing Pie wants to evaluate the effectiveness of seating at various locations. Based on the number of seats at the Bar, Lounge, Dining Room, how often are they filled? Should they possibly expand one area, and reduce another? The manager may look at one or many locations. He may look for trends by month or day of the week. Another report may evaluate the menu and which items are selling. To group the various reports, we’ll create one WebAPI APIController class for each type of report.

Modify the Seating Report Controller

This is where we’re going to capture the total number of seats for each location. Using LINQ, we’re going to:

Query the Tables entity
Filter by the Location passed in
Group by the TableType
Get a Sum of the TableSize for each TableType at each Location

1. Open the SeatingController from the Server\Reports folder

2. Add the Extension Methods for query operators

Note: this is an important step. Without these extension methods, the LINQ query operators will not be available on the DataWorkspace

VB
Option Infer On
Imports System.Net
Imports System.Web.Http
Imports Microsoft.LightSwitch
Imports LightSwitchApplication

C#
using Microsoft.LightSwitch;

3. Remove the additional overloads for Get, Put, Post and Delete methods as we’re only using this ApiController for Getting reports.

4. Change the parameter from int to string in the method we edited earlier
The Location_Id from the Table entity in our AmazingPie datamodel is of type string

5. Add the following LINQ query

VB
Public Function [Get](id As String) As Object
    Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

        Dim reportResult =
            From t In context.DataWorkspace.AmazingPieData.Tables
            Where t.Location.Location_Id = id
            Group By t.TableType.TableType_Id, t.TableType.DisplayOrder Into Group
            Order By DisplayOrder
            Select TableType_Id, Seats = Group.Sum(Function(t) t.TableSize)

        Return reportResult.Execute()
    End Using
End Function

C#
public object Get(string id) {
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext()) {
        var reportResult = context.DataWorkspace.AmazingPieData.Tables
            .Where(t => t.Location.Location_Id == id)
            .GroupBy(g => new {
                g.TableType.TableType_Id
            })
            .Select(g => new {
                TableType_ID = g.Key.TableType_Id.Trim(),
                Seats = g.Sum(t => t.TableSize)
            });
        return reportResult.Execute();
    }
}

Evaluating the LINQ query

To understand how the LINQ query is working here:

We instance the context inside a using block to assure the ServerApplicationContext is disposed.
Using the typed context, we can access the DataWorkspace, and drill into the AmazingPieData entity and the Tables collection.

Remember, Tables is the name of our SQL Table that contains the list of tables in the restaurant. It’s not the collection of SQL Tables.

We pass the id parameter to filter Restaurant Tables by the Location_Id
In order to get a grouping of the subset of properties, we need to create an anonymous type, and create the collection into g. The LINQ query will pass the query to the database, where the group by will be performed inside the database, avoiding all the data streaming to the middle tier for aggregation.
By adding the DisplayOrder to the group by, we can use the TableType.DisplayOrder property to sort our TableTypes by a logical order

Order By Tip: I wanted to sort the graph by Bar, Lounge, Dining. Sorting by TableType would have created Bar, Lounge, Dining. The Lookup table for TableType contains the DisplayOrder for just this purpose. This is a common practice to assure your lists are displayed in a logical sequence such as (OrdrePlaced, OnHold, Picking, Shipped, Returned)

With the GroupBy, we reduce the quantity of rows, with the anonymous return type, we reduce the quantity of columns
Since LINQ queries defer their execution until they’re requested, which is when the WebAPI stack kicks in, outside of the Get(string id) method, we must call execute here to use the ServerApplicationContext before it’s disposed.

Reducing the resultset

With the above query, we’re returning just 3 rows, and two properties. Dramatically smaller than returning all the rows and attempting to summarize this on the client.


[{ "TableType_Id": "Bar", "Seats": 21 },
 { "TableType_Id": "Lounge", "Seats": 23 },
 { "TableType_Id": "Dining", "Seats": 48 }]

In the case of AmazingPie, the largest quantity of tables is 'BelSquare' which has 32 tables. However, when we start aggregating the number of guests for a given restaurant, we may be processing hundreds of thousands of records. Yet, we only need to display a dozen values. Using LINQ, the new LightSwitch Aggregates such as GroupBy, WebAPI and JSON we can leverage the processing power of each tier of the application, minimize the data sent across the internet and still provide rich visuals to our end users.

Testing our Report WebAPI

With the code in place, we’ll first evaluate the results with Fiddler. Unit testing makes it easy to dissect the data before we start connecting the HTML client with javascript.

1. Build your app, or launch it with F5

2. In Fiddler, remembering your base URL, compose the request.
Here’s a visual mapping of what we’ve done so far:

Connecting our HTML Client Graph to the WebAPI Report

With our WebAPI written and tested, we can now replace our SampleData with the WebAPI call

We’ll first need a variable to contain our URL we’ll utilize for our WebAPI Reports call
We’ll then update the KendoChart to use WebAPI/JSON to retrieve the data

1. Rather than switch back to logical view, we can stay in the File view to open our Location_View.js which is located under the UserCode folder.

2. Create the reportsAPI variable. It should look similar to this when complete:
http://localhost:22700/reports/Seating/SeaBallard
We’ll construct the url using the relative path, passing in the reports API path, report class name and the parameter based on the contentItem parameter passed into the SeatedGuests_render function.

3. Below the point where we append the locationSeatingChartContainer, add the following code.

Note, the existing code is greyed for clarity


// Create a <div> to contain our chart
var locationSeatingChartContainer = $('<div id="chartContainer" class="chart-wrapper"  style="width:600px"></div>';
locationSeatingChartContainer.appendTo($(element));

// http://localhost:22700/reports/Seating/SeaBallard
var reportsAPI = "../reports/Seating/" + contentItem.screen.Location.Location_Id;

4. We can now comment out the data: attribute used for our sample data, and provide the transport:attribute which will fetch the data from the WebAP, using the reportsAPI URL.


// Create a <div> to contain our chart
var locationSeatingChartContainer = $('<div id="chartContainer" class="chart-wrapper"  style="width:600px"></div>');
locationSeatingChartContainer.appendTo($(element));
 
// http://localhost:22700/reports/Seating/SeaBallard
var reportsAPI = "../reports/Seating/" + contentItem.screen.Location.Location_Id;
 
locationSeatingChartContainer.kendoChart({
    // set the theme
    theme: $(document).data("kendoSkin") || "black",
    // set the datasource to use our WebAPI reports controller
    dataSource: {
        //data: sampleData,
        transport: {
            read: {
                url: reportsAPI,
                dataType: "json"
            }
        }
    },
    title: {
        text: "Seats Per Location"

5. Run the app and see the results
The Details Page for BelSquare

And the Graph of the tables for BelSquare

Wrapping it up

Whew. That was a lot. I did take some extra time to explain some of the nuances and troubleshooting techniques, which hopefully you’ll find valuable as these were the things I tripped over and will hopefully spare you some pain.

Using LightSwitch, WebAPI and the new ServerApplicaitonContext we were able to:

Expose a WebAPI JSON Payload
Write LINQ queries to aggregate results, processed on the data tier, returning just the data we need to display
Provide fast, read-only data useful for graphs

Thanks,

Steve Lasker
Microsoft Program Manager
Visual Studio LightSwitch

http://blogs.msdn.com/SteveLasker

Using the LightSwitch ServerApplicationContext API (Matt Evans)

LightSwitch Team — Mon, 15 Apr 2013 18:44:22 GMT

The ServerApplicationContext API is a new feature in LightSwitch, available with Visual Studio 2012 Update 2 or later, which allows you to create entirely new ways to call custom business logic on the LightSwitch Server, using the same rich API you're used to working with on the server tier. We previewed this API in the VS 2012 HTML Client Preview 2 release, but we've made a few tweaks since then, and this article discusses the API in a bit more depth.

Background

By default, the only way a client or service can communicate with the LightSwitch server is via the OData protocol, and only to EntitySets and Queries you've created in the Query designer. See LightSwitch as a Data Source.

We recently introduced a feature in the LightSwitch server which allows developers to create alternative entry points on the LightSwitch middle tier (a.k.a. the server). This is very handy if you want the server to communicate with clients that don't understand OData, or you need to return data that isn't shaped like one of your Entities. For instance, if you want to invoke some custom logic on the server, the solution until now has been the "command table" pattern, where you create an entity which is just a conduit for sending work requests to the server. Another common request we get is for a way to generate reports and interactive dashboards. Reports usually aren't shaped like whole entities, but rather projections of entities and aggregates. To solve this sort of reporting problem, people have had to resort to cumbersome mechanisms like custom RIA services in order to be able to transfer non-entity data out of the server.

Eventually it would be nice to have a great inbox experience for reporting and for service operations. However, in the interim, we have introduced the ability for developers to use the LightSwitch API inside of their own web service endpoints. You can add any of the normal ASP.NET web assets to your Server project and create new ways of interacting with the LightSwitch server. You could create something quick and dirty like an ASP.NET Web Form, or something more powerful like a WCF Service or a Web API endpoint. The key point is that you decide the appropriate way you'd like to expose a new service, using the normal Visual Studio gestures for adding and working with those assets.

Technically, it has always been possible to add aspx pages and Web API calls to the LightSwitch server, but there was no easy way for you to use the LightSwitch API inside your custom entry points, so it wasn't a great experience.

With the ServerApplicationContext API, we've made certain scenarios much easier, and we're opening things up to your imagination.

A quick and dirty example

1. Create a new LightSwitch HTML / C# Application
2. Add a new Table, called "Customer". Give it two properties, "Name" and "BirthDate"
3. Add a new Browse Screen for the Customer Table
4. In Solution Explorer, change to "File View"

5. Select the Server project in Solution Explorer
6. Right click on the Server Project, choose "Add" and then choose "New Item"

7. Search for "web form"
8. Add a new asp.net web form named "MakeData.aspx"

9. Expand MakeData.aspx in the Solution explorer. Double click on the code-behind file (MakeData.aspx.cs)
10. Paste the following code into the Page_Load method:

C# Code:

protected void Page_Load(object sender, EventArgs e)
{
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext())
    {
        Customer c = context.DataWorkspace.ApplicationData.Customers.AddNew();
        c.Name = "Good Guy Greg";
        c.BirthDate = DateTime.Today;
        context.DataWorkspace.ApplicationData.SaveChanges();
    }
}

VB Code – note that you'll want to add "Imports LightSwitchApplication" at the top of your VB code files

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Using Context As ServerApplicationContext = ServerApplicationContext.CreateContext()

        Dim c As Customer = Context.DataWorkspace.ApplicationData.Customers.AddNew()
        c.Name = "Good Guy Greg"
        c.BirthDate = Date.Today
        Context.DataWorkspace.ApplicationData.SaveChanges()
     End Using
End Sub

11. Make another web form called "ShowData.aspx". Add a using statement for "Microsoft.LightSwitch".
12. Put the following code into the Page_Load method

C# Code:

protected void Page_Load(object sender, EventArgs e)
{
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext())
    {
        foreach (Customer c in context.DataWorkspace.ApplicationData.Customers)
        {
            Response.Write(c.Id + " " + c.Name + " " + c.BirthDate + "<br>\r\n");
        }
    }
}

VB Code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Using Context As ServerApplicationContext = ServerApplicationContext.CreateContext()
        For Each c As Customer In Context.DataWorkspace.ApplicationData.Customers
            Response.Write(c.Id.ToString() + " " + c.Name + " " + c.BirthDate + "<br>" + vbCrLf)
        Next
    End Using
End Sub

13. F5 your application
14. Note the URI of the app during F5; it should be something like http://localhost:12345/htmlclient/
15. Open a new browser tab, and manually type in the following uri: http://localhost:12345/MakeData.aspx Fix up the port number, as needed.
16. Open a new browser tab, and manually type in the following uri: http://localhost:12345/ShowData.aspx
17. Go back to the original tab, which shows the HTML client. Refresh this tab. You should see new data in your app.

If all went well, you should have seen your entity data in steps 15, formatted as uninteresting text, and again in step 16, inside the HTML client UI.

This example isn't especially interesting, but shows that if you know ASP.NET development, you can now build arbitrary pages and other types of endpoints that read and write to your LightSwitch data, using the LightSwitch API.

API Overview

There are actually two primary ServerApplicationContext classes. One of them is strongly typed for your application, that is, it understands which data sources, entities, and queries are in your specific LightSwitch project. This is what you'll use most of the time. It lets you write code like this:

C# Code:

using (ServerApplicationContext context = ServerApplicationContext.CreateContext())
{
    var v = from c in context.DataWorkspace.ApplicationData.Customers
            where c.Name.Contains("Matt")
            select c;

       foreach (Customer c in v) {
        Response.Write(c.Name + "<br>\r\n");
    }
}

VB Code

Using Context As ServerApplicationContext = ServerApplicationContext.CreateContext()

    Dim v = From c In Context.DataWorkspace.ApplicationData.Customers
            Where c.Name.Contains("Matt")
            Select c

       For Each c As Customer In v
        Response.Write(c.Name + "<br>" + vbCrLf)
    Next
End Using

Note that because we are using the strongly typed model, our context has knowledge of the ApplicationData service and its Customers table, and all of the properties on the Customer entity.

Weakly Typed ServerApplicationContext

There is another ServerApplicationContext class which is weakly typed. It has no compile time knowledge of your entities or other project assets. However, it can access these items at runtime, using the Weakly Typed API. (Read more here). The weakly typed ServerApplicationContext is actually created by a call to the ServerApplicationContextFactory:

C# Code

using (IServerApplicationContext icontext = ServerApplicationContextFactory.CreateContext())
{
    var typ = icontext.DataWorkspace.SecurityData.GetAuthenticationType();
    if (typ.HasFlag(AuthenticationType.Windows) || typ.HasFlag(AuthenticationType.Forms))
    { i++;
    }
}

VB Code – note you'll need to add "Imports Microsoft.LightSwitch.Server" and "Imports Microsoft.LightSwitch.Security" at the top of your source file..

Using icontext As IServerApplicationContext = ServerApplicationContextFactory.CreateContext()
    Dim typ = icontext.DataWorkspace.SecurityData.GetAuthenticationType()
    If (typ.HasFlag(AuthenticationType.Windows) Or typ.HasFlag(AuthenticationType.Forms)) Then
        i = i + 1
    End If
End Using

Suppose that you are writing some sort of extension module that enables generic reporting or import/export scenarios. You want to create a package that anyone can add to their LightSwitch application which will create new Web API endpoints that allow for exporting data as csv files. Because your module needs to work with any possible LightSwitch application, it has no strongly typed model to work with. However, it is straightforward to write weakly typed code which will enumerate the datasources, entitysets, key properties, and so on, allowing you to create a generic module that can operate in any LightSwitch application. The dynamic URL parsing and routing of Web API makes this an especially interesting scenario, e.g. suppose someone requests the following uri:

http://contoso.com/myLightSwitchApp/CsvExporter/Customers

It would be straightforward to write a generic module which implemented this CSV exporter as a Web API endpoint (CsvExporter), which would infer the EntitySet (Customers) to export based on the incoming URI.

As a side note, because the SecurityData data service is available in any LightSwitch application, that dataservice will be available strongly typed even on a weakly typed ServerApplicationContext, as seen in the example above.

Challenge: After you've completely read this article, take another look at the above example where I call ServerApplicationContextFactory.CreateContext. Will the variable i ever get incremented by this code? Why or why not?

Current vs. CreateContext

There are two items of interest on the ServerApplicationContext classes: the Current property and the CreateContext method. The Current property returns the currently in-scope ServerApplicationContext, if one exists. The CreateContext method creates a new ServerApplicationContext for your use.

Unlike DataWorkspaces, which can be "Stacked" so that many are simultaneously in scope, only one ServerApplicationContext can be present for a given logical request on the server. Each incoming request to one of the in-built LightSwitch server endpoints has its own ServerApplicationContext automatically created for the lifetime of the request, and which is used to service all activity for that request. If you were to try to create a second ServerApplicationContext when one was already present, you would get a ContextExistsException.

When you are creating your own entry points into the server, it is typically safe to simply call CreateContext without checking to ensure that Current is null first. This is because the normal LightSwitch server hasn't been called yet; IIS and your code are handling the HTTP request routing and LightSwitch hasn't had the opportunity to initialize anything on your behalf.

On the other hand, if you are inside of normal LightSwitch code on the server, like SaveChanges_Executing or Customer_Inserting, attempting to create a new ServerApplicationContext will always fail, because in these cases, the ServerApplicationContext that the LightSwitch save pipeline has created for its own use will already exist.

In almost all cases where you are defining the web entry point yourself (a webform, Web API, etc), to use a ServerApplicationContext you just do this:

C# Code

using (ServerApplicationContext context = ServerApplicationContext.CreateContext())

{

// my code goes here

}

VB Code:

Using Context As ServerApplicationContext = ServerApplicationContext.CreateContext()

' my code goes here

End Using

Authentication

Because the key usage scenarios for ServerApplicationContext involve creating new service endpoints on the LightSwitch server, by default, if your LightSwitch application is set to use authentication, ServerApplicationContext tries to enforce user authentication. Specifically, if in your web.config, the Authentication mode is Windows or Forms, when your code tries to create a new ServerApplicationContext by calling CreateContext, if there isn't a valid authenticated user already on the HttpContext, your call to CreateContext will throw an exception.

If you know what you are doing and do not want this behavior, you can tell CreateContext to skip the authentication check, by calling it in the following way:

C# Code

using (ServerApplicationContext context = ServerApplicationContext.CreateContext(ServerApplicationContextCreationOptions.SkipAuthentication))

VB Code

Using Context As ServerApplicationContext = ServerApplicationContext.CreateContext(ServerApplicationContextCreationOptions.SkipAuthentication)
' allow in unauthenticated users
' my code goes here
End Using

The SkipAuthentication flag tells CreateContext not to do the authentication check.

Next Steps

There are some more complete end to end examples that use ServerApplicationContext, which you can read about here:

Note that these were written in the HTML Preview 2 timeframe, and so the location of the ServerApplicationContext classes is a little bit different. We've got more blog posts planned with some good reporting examples so stay tuned!

- Matt Evans, Tester, LightSwitch Team