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:

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

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:
   

   

   Create a query as shown in the next image:

   

   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:

   

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:
   

   

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.
   

   

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

   

   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:
   

   

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:

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: $35.99
Book Price: $59.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

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

   

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:
   

   

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
   

   

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

   

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

   

   The Summary element for Property1 is added as shown:

   

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

   

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

   

   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.
   

   

   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.

    

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

    

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:
    

    

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:

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:

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

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

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:

• Load Testing Using Visual Studio 2008: Part 1 [Article]
• Windows Development Using Visual Studio 2008 [Article]
• Creating a Simple Report with Visual Studio 2008 [Article]
• Microsoft LightSwitch: Querying and Filtering Data [Article]
• Microsoft LightSwitch Application using SQL Azure Database [Article]
• Connecting to Microsoft SQL Server Compact 3.5 with Visual Studio [Article]

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: $35.99
Book Price: $59.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

About the Author :

Books From Packt