Articles → Light Switch 2011 → Queries In Light Switch 2011

Queries In Light Switch 2011

In this tutorial I will explain about queries in lightswitch 2011.

Software Requirement

Before you start reading this tutorial you should make sure that

Visual studio 2010 is installed on your machine.
Visual Studio 2010 service pack 1 is installed on your machine.
Microsoft Visual Studio Light switch 2011 is installed on your machine.
Silverlight runtime version 4 or 5 is installed on your machine

Prerequisite Knowledge

Before you go ahead, it is required that you should know

How to create project using visual studio?
How to create screens in lightswitch?
How to create tables in lightswitch?

What Is A Query?

A query is a request that is sent to a database in order to get information back from the database

Steps Of Execution

In this tutorial I will execute certain steps for the demonstration of query in lightswitch application. These steps are

Create a new project
Create a new table
Add data in the table
Add query on the table
Add parameters in query
Bind grid with query
Bind drop down with query

Let us execute each step

Create A New Project

First of all create a new project. See the screen shot below for project structure

Picture showing the project structure of the lightswitch application

Click to Enlarge

Create A New Table

Create a new table. See the screen shot below for table structure

Picture showing the schema of the student table in the lightswitch table designer

Click to Enlarge

Add Data In The Table

See the screen shot below for table data

Picture showing the editable grid screen with data

Click to Enlarge

Add Query On The Table

For adding a query

Right click on the table and click on Add Query.

Picture showing the add query option in the context menu of the table

Click to Enlarge

A designer screen will open and a new query Query1 will be created. You can rename the query by double click on it

Picture showing the new query added in the solution explorer

Click to Enlarge

Accordingly query name in the solution explorer also changes.

Add Parameters In Query

In the last section we have seen the designer screen for the query. In this section we will add parameter to it. To add the parameter

Click on Add Filter option
A new row will be added from which you can add where condition for the query.

Picture showing the button for adding the filters in the query

Click to Enlarge

Each filter row has 5 columns

In the first column we have a drop down having 2 values i.e. where and where not.
Second column contains the list of all fields in a table on which filter can be applied. You can select any of the field.
Third column is a drop down which contains the list of comparison operators like equal to, not equal to and so on.
Fourth column is for the type of query parameter. It can be a literal i.e. hard coded string, a property or a parameter which can be set at run time.
Fifth column is the value of query parameter.

Picture showing the filters added in the query

Click to Enlarge

For adding a parameter select ‘Parameter’ as query parameter type and select ‘Add new…’ from last drop down

Picture showing adding the value to the filters

Click to Enlarge

Once you click on Add new … option, a new row will be added in Parameters section as shown in figure below

Click to Enlarge

We are done with adding parameters in query. But the last step is to make the parameter optional. It means if the value is assigned to the parameter then all the records matching the value should be displayed in UI else all the records should be displayed in UI.

For doing that

Click on the parameter and press F4
Property window will be opened on the right hand side.
Check the option ‘Is Optional’

Picture showing marking the parameter as optional in the query

Click to Enlarge

Bind Grid With Query

In this section we will see how to bind a grid in lightswitch application from a query.

First of all add a new screen of type Editable grid screen.
Select query as data source of the screen. See the screen shot below

Picture showing creating the new Editable Grid screen with query as data source

Click to Enlarge

Click Ok

Once the screen will be added, you can see the optional parameter on the top of the grid in screen designer.

Picture showing creating a new data field to be used as query parameter

Click to Enlarge

Now run the application. You can see all the records if there is no value in query parameter. Once you enter the value in query parameter, all the records matching the criteria will be populated

Picture showing the data in the grid bound with the query

Click to Enlarge

Bind Drop Down With Query

It is important to note that you can only bind a drop down with a query when the type of dropdown and query are same. For example if we have 2 entities i.e. student and class then you cannot bind a dropdown of type student with the query of type class.

To bind the dropdown, first of all we will add a dropdown on the screen using add data item.

Picture showing adding the new data item in the project

Click to Enlarge

Once the data item is added, drag it onto the screen.

Next step is to add a query. We can add a query in the same way as we have added a drop down. See the screen shot below

Picture showing selecting the the query as data source of the data item

Click to Enlarge

Once the query is added on the screen, click on the dropdown on the screen and press F4. A property window appears as shown in figure below.

Picture showing the query parameter data source in the property window

Click to Enlarge

From the ‘Choices’ option you can select the query as shown in figure above. When you run the application you can see the dropdown bound with the query.

Posted By -	Karan Gupta

Posted On -	Tuesday, August 6, 2013

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**