AdFactum ObjectMapper .NET Blog

Official blog of the AdFactum ObjectMapper .NET

Concepts for searching in database

Posted by Gerhard Stephan on December 13th, 2006

The AdFactum ObjectMapper .NET makes it technically easy for applications to search for objects in database. Most problem I had, and what I had to learn was not how to search technically, but how to search in a clear conceptual way.

If we are talking about searching, we are talking about two issues.

  1. What to search
  2. Where to search

These two issues aren’t always easy to separate. I wrote this blog entry in order to help you to make your decision if a search condition is a "What" or a "Where".

"Where to search"
A "Where" is always a static property of the search within a search dialog. So If we not would specify any "What to search" constraints, we would get the complete content of the "Where" constraint.

Example:

  • We search within all contacts.
  • We search within all contacts who have sales within the running year.

In our application we would have two search dialogs, or at least two search areas. You see, the "Where" specifies the surrounding context of the search function.

"What to search"
A "What" is always a user defined limitation of the search result. Within the search dialog, the user has the right to change the "What" limitation for his needs.

Example:

  • I want to search contacts where surname is "Meyer"
  • I want to search contacts who are older than 30 years.

The "What" is always a user defined limitation of the search. As a result you can apply every "Where" (search scope) to any "What" (search criterias). So these two can be combined.

Consideration:
Splitting the search scope and the search criterias into distinct cases, offers you the possibility to re-use the search criterias for different use cases within your application.

How to split the search context from the search criterias
With this theoretical background I want to explain how to split the search scope and the search criterias.

    /// <summary>

    /// Interface that defines a scope for a search criteria

    /// </summary>

    public interface ISearchScope

    {

        /// <summary>

        /// Gets the search condition.

        /// </summary>

        /// <value>The search condition.</value>

        ICondition Condition

        {

            get;

        }

    }

The interface used to define a search scope is quite simple. Because a search scope is static, only a Condition must be returned that can be added to the condition of a search criteria. In praxis, the Condition returns a search condition of type ConditionList (which can contain multiple conditions).

    /// <summary>

    /// Interface that defines a search criteria

    /// </summary>

    public interface ISearchCriteria

    {

        /// <summary>

        /// Set the scope for a search criteria.

        /// </summary>

        /// <value>The Scope.</value>

        ISearchScope Scope

        {

            set;

        }

 

        /// <summary>

        /// Gets or sets the order by.

        /// </summary>

        /// <value>The order by.</value>

        OrderBy OrderBy

        {

            get;

        }

 

        /// <summary>

        /// Gets the search condition.

        /// </summary>

        /// <value>The search condition.</value>

        ICondition Condition

        {

            get;

        }

 

    }

The search criteria interface offers the possibility to set a search scope. This is used to set the "Where" Constraint of a search. A concrete implementation must use the result condition of the scope and add it to the result of the search criteria that will be returned by calling the Condition Property.

Example of a search scope that limits the search to active projects:

    /// <summary>

    /// Search criteria only works on active projects

    /// </summary>

    public class ActiveProjects : ISearchScope

    {

        /// <summary>

        /// Gets the search condition.

        /// </summary>

        /// <value>The search condition.</value>

        public ICondition Condition

        {

            get

            {

                return

                    new ConditionList(

                        new AndCondition(typeof(Project), "IsDeleted", false),

                        new Parenthesize(

                            new OrCondition(typeof(Project), "StartDate", QueryOperator.Is, null),

                            new OrCondition(typeof(Project), "StartDate", QueryOperator.LesserEqual, DateTime.Today)),

                        new Parenthesize(

                            new OrCondition(typeof(Project), "EndDate", QueryOperator.Is, null),

                            new OrCondition(typeof(Project), "EndDate", QueryOperator.GreaterEqual, DateTime.Today))

                        );

            }

        }

    }

Example of a base class that can be used to derive search criterias:

 

    /// <summary>

    /// Base class used for every search

    /// </summary>

    public class SearchCriteria : ISearchCriteria

    {

        private ISearchScope scope = null;

 

        /// <summary>

        /// Sets the scope.

        /// </summary>

        /// <value>The scope.</value>

        public ISearchScope Scope

        {

            set { scope = value; }

        }

 

        /// <summary>

        /// Gets or sets the order by.

        /// </summary>

        /// <value>The order by.</value>

        public OrderBy OrderBy

        {

            get { return null; }

        }

 

        /// <summary>

        /// Gets the search condition.

        /// </summary>

        /// <value>The search condition.</value>

        public virtual ICondition Condition

        {

            get

            {

                return scope.Condition;

            }

        }

    }

 

Example of a class that searches for projects:

    /// <summary>

    /// Project search Criterias

    /// </summary>

    public class ProjectSearchCriteria : SearchCriteria   

    {

        private string name = "";

        private DateTime startedAfter = DateTime.MinValue;

           

        /// <summary>

        /// Gets the search condition.

        /// </summary>

        /// <value>The search condition.</value>

        public override AdFactum.Data.ICondition Condition

        {

            get

            {

                ICondition result = base.Condition;

               

                if (name.Length>0)

                    result.Add(

                        new AndCondition(typeof(Project), "Name",
                            QueryOperator.Like_NoCaseSensitive, Name)

                        );

               

                if (startedAfter != DateTime.MinValue)

                    result.Add(

                        new AndCondition(typeof(Project), "StartDate",
                            QueryOperator
.GreaterEqual, StartedAfter)

                        );

                   

                return result;

            }

        }

 

        /// <summary>

        /// Gets or sets the name.

        /// </summary>

        /// <value>The name.</value>

        public string Name

        {

            get { return name; }

            set { name = value; }

        }

 

        /// <summary>

        /// Gets or sets the started after.

        /// </summary>

        /// <value>The started after.</value>

        public DateTime StartedAfter

        {

            get { return startedAfter; }

            set { startedAfter = value; }

        }

    }

Consideration of the example above:

As you can see, you can attach several search scopes to the project search criterias. Thus allows you to search within different scopes, e.g. search only in active projects or search in all projects without changing the project search criterias.

I think this is a great possibility to build complex search functionality without overloading the search criterias by context or scope changes.

Wish you all the best
Gerhard

4 Responses to “Concepts for searching in database”

  1. cqwydz Says:

    thinks for your great work ,but i can’t find some more examples ,especially query api document.
    how can i parse the expression “select * from Person where Not Name = ‘Mike'”

    Best regards,
    cqwydz

  2. Gerhard Stephan Says:

    Hi (unkown) :),

    I’m going to write a new post where I’m going to explain how to query such data.

    But for short: A Where is called a Condition when using the AdFactum ObjectMapper .NET. There’re several conditions, like ConditionList, AndCondition, OrCondition, InCondition, NotInCondition and more.

    What you’re searching for is a AndCondition with a NotEqual Query Operator, like:

    new AndCondition (typeof(Person),’Name’, QueryOperator.NotEqual, ‘Mike’);

    Hope I could help you.

    Cheers
    Gerhard

  3. Alex Says:

    How about fulltext search?

  4. Gerhard Stephan Says:

    Database Engines have sometimes very different ways to do a fulltext search. That’s why the ObjectMapper .NET does not support it natively. What you can do is, executing a native SQL Select where you put your SELECT String into the Execute Method.


    /*
    * Get the low level Native Persister from the SQL Persister
    */
    INativePersister nativePersister = sqlPersister as INativePersister;

    // First example without Parameter
    nativePersister.Execute("EXEC dbo.SayHello");

    // Second example is using Parameters
    nativePersister.ExecuteWithParameter("EXEC dbo.SayHello @p01", "Peter");

    Also have a look at the query API of the ObjectMapper .NET
    http://blog.objectmapper.net/2007/01/22/how-to-build-sql-queries/

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>