Restricting Access to NULL Values

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