In my previous LightSwitch article we have discussed how to create and use the Summary Property in LightSwitch. Today we will see how to create a Query for Screens and Tables.
What is a Query in LightSwitch?
A query is something that returns data based on some criteria or filter. In LightSwitch, queries can be created and then used for screens or even in code. I want to use queries as a better way of getting only the data I need to immediately see.
Types of Query based on Usage:
There are two types of queries based on where we are using them.
- Screen Based Query
- Table Based Query [Global Query]
Screen Based Query:
Screen based queries are created and used in that particular screen on which the query is created. These queries cannot be used for other than that screen it is created on.
Table Based Query:
Table based queries are created in the Table. Table based queries are sometimes called a Global query. The global query can be accessed throughout the project hence the name Global query.
More specifically, we want to create a query that will only provide us with Person details for a particular state. Let us create a LightSwitch demo application to demonstrate the queries.
Preparing the Solution:
Let us fire up the VS 2010; create a LightSwitch demo application with the name "WorkingWithQueryInLSBeta2" as shown in the figure.
Follow the [No: #] to create a LightSwitch Desktop Application. For more details about how to create a LightSwitch Desktop Application please read this article.
Creating Entity for Application:
Create a table as shown in the screen by selecting the "create a table" option in the start up screen.
Designing the Screen:
First of all we need to add a data screen to our demo application. We should know how to add Screen Data Screen to a LightSwitch application prior to proceeding with designing the screen. To know more about how to add data screens to LightSwitch Applications please read this Article.
Now we know how to do the screen stuff. See the following figure which is the default screen layouts for the screen we have created.
Before adding queries to screen let us see the screen with few data which are not filtered or sorted.
The records displayed in the Search Data Screen above is not filtered or sorted.
Adding a Screen based Query:
As we have discussed just now in this article, we are going to create Screen based queries on the Search Data Screen which we have created.
As shown in the figure, in the screen layout left side panel, we can see People Entity and their Property listed with Methods Close, Refresh and Save.
Simply press the Edit Query link to the screen based Query to the Search Data Screen.
After Clicking on Edit Query link, you will be having a new screen like the following one.
In this Query Designer we can see the options to create Filter, Sort and Parameters. In our Demo application we shall discuss only Sort and Filter.
In Filter's first part we are having the Where and Where Not.
We have to select the Property on which we filter. In our case we are selecting the State.
The above figure shows the relational operator in LightSwitch for making a query.
We have to specify whether the Right hand side value for the condition is Literal, Property or Parameter.
The following figure shows the complete "Filter" to get the List of people where their State is TN.
Additionally, we are going to sort the people based on the State in Ascending order.
Adding Table Based Query:
We can add the query in two ways:
As shown in the figure, from the Solution Explorer right-click on the Entity People and select the option Add Query.
Open the People entity you will have the People designer, on top of this designer Click on the Query button as shown in the figure.
Change the Query1 to PeopleFromTN and add the Query as we did in Screen Based Query.
To use the created Query, we have to create a screen. For demo purpose we are creating an Editable Data Screen as shown in the following figure.
Here the PeopleFromTN is listed as an Entity. Select the PeopleFromTN query as Screen Data.
Hit F5 to see the application in Action.
Application in Action:
The Above shown figure is for the Screen Based Query which returned two People records which have State as TN.
Here we can see that the records displayed here are People who are not in TN i.e. State is not TN.
In this article, we have seen how to create and use the Screen Based Query and Table Based Query in the LightSwitch Screen.
Thanks for spending your precious time here. Please provide your valuable feedbacks and comments, which enable me to give a better article the next time.