Microsoft LightSwitch: Querying Multiple Entities

Exclusive offer: get 50% off this eBook here
Microsoft Visual Studio LightSwitch Business Application Development

Microsoft Visual Studio LightSwitch Business Application Development — Save 50%

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

€28.99    €14.50
by Jayaram Krishnaswamy | September 2011 | Enterprise Articles Microsoft

A Query is a question you pose to the database so as to retrieve a specific piece of information in the database. The querying in LightSwitch can be best understood by looking at how it is structured. Querying in LightSwitch can be carried out using the built-in Query Designer but more advanced querying can be carried out using code.

This article by Jayaram Krishnaswamy, author of Microsoft Visual Studio LightSwitch Business Application Development, describes the use of the built-in Query Designer in the IDE to query the entities in the data sources.

 

(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.

 

Microsoft Visual Studio LightSwitch Business Application Development A jump-start book and eBook to application development with Microsoft Visual Studio LightSwitch
Published: September 2011
eBook Price: €28.99
Book Price: €46.99
See more
Select your format and quantity:

 

(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:


Microsoft Visual Studio LightSwitch Business Application Development A jump-start book and eBook to application development with Microsoft Visual Studio LightSwitch
Published: September 2011
eBook Price: €28.99
Book Price: €46.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Software Testing using Visual Studio 2010
Software Testing using Visual Studio 2010

Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Getting Started with Microsoft Application Virtualization 4.6
Getting Started with Microsoft Application Virtualization 4.6

Microsoft Azure: Enterprise Application Development
Microsoft Azure: Enterprise Application Development

Microsoft Visio 2010 Business Process Diagramming and Validation
Microsoft Visio 2010 Business Process Diagramming and Validation

Microsoft SharePoint 2010 Development with Visual Studio 2010 Expert Cookbook
Microsoft SharePoint 2010 Development with Visual Studio 2010 Expert Cookbook

Microsoft Dynamics Sure Step 2010
Microsoft Dynamics Sure Step 2010

Microsoft Data Protection Manager 2010
Microsoft Data Protection Manager 2010


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software