How to: Implement a Full-Text Search

• Dec 28, 2022
• 3 minutes to read

This example shows you how to create full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

 

What Is a Full-Text Search?

A full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate, which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

 

Implementing a Full-Text Search

To implement a full-text search, do the following.

• Install the Full-Text Search sub-component of Microsoft SQL. For detailed information on how to create a Full-Text Search on a database, see Full-Text Search.

• Create a custom function operator by implementing the ICustomFunctionOperatorFormattable interface.

  C#

  using System.Data;
  using DevExpress.Xpo.DB;
  using DevExpress.Data.Filtering;
  
  public class FullTextContainsFunction : ICustomFunctionOperatorFormattable {
      #region ICustomFunctionOperator Members
      // Evaluates the function on the client
      public object Evaluate(params object[] operands) {
          // Full text search is not available on the client and should not be used there
          throw new NotImplementedException();
      }
      public string Name {
          get { return "FullTextContains"; }
      }
      public Type ResultType(params Type[] operands) {
          return typeof(bool);
      }
      #endregion
      #region ICustomFunctionOperatorFormattable Members
      // The function's expression to be evaluated on the server
      public string Format(Type providerType, params string[] operands) {
          // This example implements the function for Microsoft SQL databases only
          if (providerType == typeof(MSSqlConnectionProvider))
              return string.Format("contains({0}, {1})", operands[0], operands[1]);
          throw new NotSupportedException(string.Concat("This provider is not supported: ", 
              providerType.Name));
      }
      #endregion
  }
  

  VB.NET

  Imports System.Data
  Imports DevExpress.Xpo.DB
  Imports DevExpress.Data.Filtering
  
  Public Class FullTextContainsFunction
      Implements ICustomFunctionOperatorFormattable
      #Region "ICustomFunctionOperator Members"
      ' Evaluates the function on the client
      Public Function Evaluate(ParamArray ByVal operands() As Object) As Object
          ' Full text search is not available on the client and should not be used there
          Throw New NotImplementedException()
      End Function
      Public ReadOnly Property Name() As String
          Get
              Return "FullTextContains"
          End Get
      End Property
      Public Function ResultType(ParamArray ByVal operands() As Type) As Type
          Return GetType(Boolean)
      End Function
      #End Region
      #Region "ICustomFunctionOperatorFormattable Members"
      ' The function's expression to be evaluated on the server
      Public Function Format(ByVal providerType As Type, _
      ParamArray ByVal operands() As String) As String
          ' This example implements the function for Microsoft SQL databases only
          If providerType Is GetType(MSSqlConnectionProvider) Then
              Return String.Format("contains({0}, {1})", operands(0), operands(1))
          End If
          Throw New NotSupportedException(String.Concat("This provider is not supported: ", _
          providerType.Name))
      End Function
      #End Region
  End Class
  

 

Using a Full-Text Search (a simple search)

To use a custom function operator in criteria you should first register it via the CriteriaOperator.RegisterCustomFunction method.

C#

using DevExpress.Data.Filtering;

public class Program {
    //...
    public static void Main(string[] arguments) {
        CriteriaOperator.RegisterCustomFunction(new FullTextContainsFunction());
        //...
    }
}

VB.NET

using DevExpress.Data.Filtering;

Public Class Program
    '...
    Public Shared Sub Main(ByVal arguments() As String)
        CriteriaOperator.RegisterCustomFunction(New FullTextContainsFunction())
        '...
    End Sub
End Class

After registering the custom operator, you can use it in your criteria.

C#

private void Form1_Load(object sender, EventArgs e) {
    xpCollection1.Criteria = CriteriaOperator.Parse("FullTextContains(ProjectName, 'Business')");
}

VB.NET

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
    xpCollection1.Criteria = CriteriaOperator.Parse("FullTextContains(ProjectName, 'Business')")
End Sub