Microsoft LightSwitch: Querying Multiple Entities

A jump-start book and eBook to application development with Microsoft Visual Studio LightSwitch

 

(For more resources on this topic, see here.)

 

Microsoft LightSwitch makes it easy to query multiple entities and with queries you can fine tune the results using multiple parameters.

In the following, we will be considering the Orders and the Shippers tables from the Northwind database shown next:

Microsoft Visual Studio LightSwitch Business Application Development

What we would like to achieve is to fashion a query in LightSwitch which finds orders later than a specified date (OrderDate) carried by a specified shipping company (CompanyName).

In the previous example, we created a single parameter and here we extend it to two parameters, OrderDate and CompanyName. The following stored procedure in SQL Server 2008 would produce the rows that satisfy the above conditions:

Use Northwind
Go
Create Procedure ByDateAndShprName @ordDate datetime,
@shprName nvarchar(30)
as

SELECT Orders.OrderID, Orders.CustomerID, Orders.
EmployeeID,Orders.OrderDate,
Orders.RequiredDate, Orders.ShippedDate,
Orders.ShipVia, Orders.Freight, Orders.ShipName,
Orders.ShipAddress, Shippers.ShipperID,
Shippers.CompanyName, Shippers.Phone
FROM Orders INNER JOIN
Shippers ON Orders.ShipVia = Shippers.ShipperID
where Orders.OrderDate > @OrdDate and
Shippers.CompanyName=@shprName

The stored procedure ByDateAndShprName can be executed by providing the two parameters (variables), @OrdDate and @shprName, as shown below.

Exec ByDateAndShprName '5/1/1998 12:00:00','United Package'

The result returned by the previous command is shown next copied from the SQL Server Management Studio (only first few columns are shown):

Microsoft Visual Studio LightSwitch Business Application Development

The same result can be achieved in LightSwitch using two parameters after attaching these two tables to the LightSwitch application. As the details of creating screens and queries have been described in detail, only some details specific to the present section are described. Note that the mm-dd-yyyy appears in the result reversed yyyy-mm-dd.

  1. Create a Microsoft LightSwitch application (VB or C#).
    Here project Using Combo6 was created.
  2. Attach a database using SQL Server 2008 Express and bring the two tables, Orders and Shippers, to create two entities, Order and Shipper, as shown in the next screenshot:

    Microsoft Visual Studio LightSwitch Business Application Development

    Create a query as shown in the next image:

    Microsoft Visual Studio LightSwitch Business Application Development

    Here the query is called ByDate. Note that the CompanyName in the Shippers table is distinct.
    The completed query with two parameters appears as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  3. Create a new screen (click on Add Screen in the query designer shown in the previous screenshot) and choose the Editable Grid Screen template.
    Here the screen created is named EditableGridByDate.
  4. Click on Add Data Item… and add the query NorthwindData.ByDate.
    The designer changes as shown next:

    Microsoft Visual Studio LightSwitch Business Application Development

  5. Click on OrderDate parameter on the left-hand side navigation of the screen and drag and drop it just below the Screen Command Bar as shown.

    Microsoft Visual Studio LightSwitch Business Application Development

  6. In a similar manner, drag and drop the query parameter CompanyName below the OrderDate of the earlier step.
    This will display as two controls for two parameters on the screen.
  7. Hold with mouse, drag and drop ByDate below the CompanyName you added in the previous step.
    The completed screen design should appear as shown (some fields are not shown in the display):

    Microsoft Visual Studio LightSwitch Business Application Development

    The previous image shows two parameters. The DataGrid rows show the rows returned by the query. As is, this screen would return no data if the parameters were not specified. The OrderDate defaults to Current Date.

  8. Click on F5 to display the screen as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  9. Enter the date 5/1/1998 directly.
  10. Enter United Package in the CompanyName textbox and click on the Refresh button on the previous screen.

The screen is displayed as shown here:

Microsoft Visual Studio LightSwitch Business Application Development

The above screen is an editable screen and you should be able to add, delete, and edit the fields and they should update the fields in the backend database when you save the data. Also note that the LightSwitch application returned 11 rows of data while the stored procedure in SQL Server returned 10 rows. This may look weird but SQL Server date time refers to PM but Microsoft LightSwitch order date is datetime data type with AM. Entering PM instead of AM returns the correct number of rows.

 

 

(For more resources on this topic, see here.)

 

AutoCompleteBox to set the parameter value

In the previous example if you did not know apriori (upfront) the parameter value to type in you would not be able to display the screen. One way would be to bring all the available parameter values into a control like a combo-box (drop-down) from which you only need to choose one. Beta 1 had a combo-box control that was removed in Beta 2( also in the release version) and a Auto Complete Box was provided in the IDE. In the present example, the parameter CompanyName has only a couple of values and the following steps show how you may replace the text box for CompanyName with an AutoCompleteBox control and then use some code to transfer the text in the AutoCompleteBox to the query parameter box to enter the CompanyName.

  1. Using the same project UsingCombo create a screen with the Search Data Screen template based on the ByDate query (In ByDate query in the query designer, click on Add Screen…).
    Here the screen is named SearchByDate_CName.
  2. Click on Add Data Item in the screen designer; in the Add Data Item displayed click on Queries and choose ByDate in the list, accept the default name ByDate and click on OK. The designer screen appears as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  3. Hold, drag, and drop OrderDate between Rows Layout and the Screen Command Bar (a blue line shows up while you do this) which inserts the Data Picker (default) control below the Screen Command Bar as before.
  4. Hold, drag, and drop ByDate below the label CompanyName. A DataGrid will be added to the screen as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  5. Click on Add Data Item… one more time, bring up the Add Data Item window, and choose items as shown in the next screenshot (click on drop-down handle of Type: and pick Northwind.Shipper). Note you are adding a Local Property

    Microsoft Visual Studio LightSwitch Business Application Development

  6. Click on OK accepting the default name Property1. This adds the Property1 to the View Model as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  7. Hold, drag, and drop Property1 below the Date Picker control as shown in this screenshot:

    Microsoft Visual Studio LightSwitch Business Application Development

    The Summary element for Property1 is added as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  8. Hold CompanyName on the left; drag and drop it below Property1 as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

    If we run the application by hitting F5 we will see a screen as shown.

    Microsoft Visual Studio LightSwitch Business Application Development

    The Property1 Auto Complete Box will function as a Combo box, but this value will not enter the Company Name parameter entry box for the By Date query. At this point, we add a little code to facilitate this.
  9. Add a button to the Property1 command bar as shown by clicking on New Button, which brings up a window where you provide a name (here, CopyFields) for the method and click on OK.

    Microsoft Visual Studio LightSwitch Business Application Development

    The button gets added.
  10. Right-click on the button and pick Edit Execute Code from the drop-down. This opens the code editor page with the template code shown:

    Namespace LightSwitchApplication

       Public Class SearchByDate_CName

             Private Sub CopyFields_Execute()
                ' Write your code here.

              End Sub
        End Class

    End Namespace

    In the procedure, insert the following code:

    Private Sub CopyFields_Execute()
            ' Write your code here.
            Dim x As New String(Me.Property1.ToString)
            Me.CompanyName = x
        End Sub

    When the button is clicked, the text in the Property1 box will be copied to the parameter input box. We could use other controls, or the event from the Auto Complete Box also.
    The same code in C# is shown in the next image and the project is available for download at Packt's website.

    Microsoft Visual Studio LightSwitch Business Application Development

  11. Build the project and hit F5. Click on the handle for Property1 and choose the Company Name as shown:

    Microsoft Visual Studio LightSwitch Business Application Development

  12. Enter the data directly in Order Date and click on the Copy Fields button.
  13. The data grid gets populated by the query response as shown in the next screenshot:

    Microsoft Visual Studio LightSwitch Business Application Development

Note however, that the query is based on existing entities and if the entity set is updated, say by adding a related entity (not necessarily changing the query), the query will become invalid.

 

Static spans

This is a new feature added in Beta 2, which confers the ability to include or exclude the related data when the query is executed. This task of including or excluding the records from the related entities is carried out in the design view of the UI. For example a new entity, Customers was added to the existing entities (Orders and Shippers) in the previous example. As noted earlier Search By Date CName query became invalid. A new screen, based on the same query ByDate was created (here as Search Test). Now, the parametric query result is working as it should. However, since Customers is related to Orders, the query brings in results for Customers as well as what's seen in the next screenshot:

Microsoft Visual Studio LightSwitch Business Application Development

This may take more round trips to the server and this unwanted field in the display can be excluded by using the static spans.

In order to do this, you will have to use the Edit Query link in the View Model (Screen designer) shown next:

Microsoft Visual Studio LightSwitch Business Application Development

When you click on this Edit Query link the following screen is presented.

Microsoft Visual Studio LightSwitch Business Application Development

The new link Manage Included Data is used to include / exclude data from being displayed from related entities as shown here (click on the link).

Microsoft Visual Studio LightSwitch Business Application Development

The default is Auto for all of them which may make the query run slower. You can choose to exclude by making a choice here. Test the options and verify in the displayed screen.

Summary

In this article we took a look at how to query multiple entities in Microsoft Visual Studio LightSwitch.


Further resources on this subject:


Books to Consider

Microsoft Visual Studio LightSwitch Business Application Development
$ 10.00
Implementing Microsoft Forefront Unified Access Gateway 2010 [Video]
$ 10.00
comments powered by Disqus