Database servers use a special value to indicate that a column in a table row has no value. This special value is called NULL.
It may require a little consideration when configuring access control rules for your application if you need to filter out or filter in the empty values in a particular column.
The screen shot shows a list of customers with the “empty” Region column.
The following business rule makes it happen. The two calls of RestrictAccess method are demonstrating the passing of a NULL value to the application framework for filtering. You can simplify the method to use one call of RestrictAccess with either null or DBNull.Value passed as a parameter.
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 { [AccessControl("Customers", "Region")] public void NullValueInRegion() { RestrictAccess(null); RestrictAccess(DBNull.Value); } } }
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 <AccessControl("Customers", "Region")> Public Sub NullValueInRegion() RestrictAccess(DBNull.Value) RestrictAccess(Nothing) End Sub End Class End Namespace
Next screen shot shows the effect of the permission property of the AccessControl attribute, which “hides” the rows with the empty Region from the end user.
This method definition will deny access to the customers with empty Region.
C#:
[AccessControl("Customers", "Region", AccessPermission.Deny)] public void NullValueInRegion() { RestrictAccess(null); }
Visual Basic:
<AccessControl("Customers", "Region", AccessPermission.Deny)> Public Sub NullValueInRegion() RestrictAccess(Nothing) End Sub
You can also use an SQL expression referencing the name of the Region field with “is null” or “is not null” operand. You must call method RestrictAccess without parameters to activate the access control filter.
C#:
[AccessControl("Customers", "Region", "[Region] is null")] public void NullValueInRegion() { RestrictAccess(); }
Visual Basic:
<AccessControl("Customers", "Region", "[Region] is null")> Public Sub NullValueInRegion() RestrictAccess() End Sub