The alternative method of filtering is available in the data controller views. Property “View Filter” allows specifying an expression that will be embedded in the SQL statement composed by the application framework at runtime.
Suppose you want to filter a list of customers by a specific country.
Start the application generator and select your project, choose Design.
Locate the page Customers in Project Explorer, expand container1 and view1 nodes.
Double-click grid1 to active its properties.
Enter the following expression in Filter Expression property of the Sort and Filter section.
Country = 'USA'
Click OK button to save the changes and click Browse button on the tool bar. The generator will start the app in your default browser.
Navigate to Customers page and observe that only customers from the USA are displayed.
Select any customer by clicking on a link in the first column. The form view editForm1 will be activated.
Notice that only customers from the USA are visible when you navigate between records in the form view with the help of navigation buttons.
The form view automatically inherits the value of Filter Expression from the last view that was presented to the user.
Static view filters work great when you need to present several alternative views of data. Users cannot remove the view filters and apply their own criteria on top of the view filter expressions.
View filter can also reference properties of the business rules class associated with the data controller of the view.
For example, you can define the following filter that contains a parameter.
Country = 'USA' and Region = @RegionName
If you generate the application and navigate to the Customers page then you will see the following exception:
View 'grid1' uses a filter with 'RegionName' parameter. Business rules class of the controller must provide a value for this parameter. The filter is defined as (Country = 'USA' and Region = @RegionName).
The application framework does not know how to determine the value of RegionName.
Right-click the data view view1 in the Project Explorer and choose Show Controller option.
Double-click Customers controller on the Controllers tab of Project Explorer.
Enter CustomersBusinessRules in the Handler property of the controller and click save.
Exit the Designer and proceed to generate the project.
Select the project name on the start page of web application generator and click Develop.
Visual Studio or Visual Web Developer will start.
Open ~/App_Code/Rules/CustomersBusinessRules.cs(vb) class and enter the definition of RegionName read-only property.
C#:
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;
namespace MyCompany.Rules
{
public partial class CustomersBusinessRules : MyCompany.Data.BusinessRules
{
public string RegionName
{
get
{
return "OR";
}
}
}
}
Visual Basic:
Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.Xml
Imports System.Xml.XPath
Namespace MyCompany.Rules
Partial Public Class CustomersBusinessRules
Inherits MyCompany.Data.BusinessRules
Public ReadOnly Property RegionName As String
Get
Return "OR"
End Get
End Property
End Class
End Namespace
Navigate to the Customers page and observe that only customers from the state of Oregon are now visible.
Notice that the value returned by RegionName property of the business rules class does not become a part of SQL statement. The application will compose a SELECT statement that incudes the view filter expression text with the resolved names of the data fields. An actual command parameter will be created . The property value will be assigned to the parameter value.
If you want to create a filtering expression as a dynamic fragment of SQL at runtime then consider using dynamic access control rules.
If more than one value needs to be passed to the view filter then consider changing the filter definition as follows (we have replaced “=” with “in”).
Country = 'USA' and Region in @RegionName
Change the property definition to return a list or an array of values.
C#:
public List<string> RegionName
{
get
{
List<string> regions = new List<string>();
regions.Add("OR");
regions.Add("WA");
return regions;
}
}
Visual Basic:
Public ReadOnly Property RegionName As List(Of String)
Get
Dim regions As List(Of String) = New List(Of String)
regions.Add("OR")
regions.Add("WA")
Return regions
End Get
End Property
Customers from the states of Oregon and Washington are now available in the list.
The application framework will list a separate parameter in the composed SELECT statement for each value in the list returned by the RegionName property of the business rules class. In our example the actual fragment of SQL will look as follows:
("Customers"."Country" = 'USA' and "Customers"."Region" in (@RegionName0,@RegionName1))