Search Data in Cache with SQL and Tags

NCache provides object queries through which you can search and delete result sets based on the criteria given to the query. To retrieve the data according to your specified criteria, NCache provides you with an extension of SQL. It lets you search the data in your cache based on the criteria on which requirement is based.

A special keyword $Tag$ is used to specify that the condition under consideration uses tags. A hashtable is created for adding the values against this special keyword $Tag$ . A query with the searching criteria is executed using ExecuteReader.

ExecuteReader processes the query on the server side and then sends the result in chunks (as a dictionary containing keys and values) to the client in the tabular form to ICacheReader type of instance.

Similar to the retrieve and delete method,the three strategies of querying items on tags (i.e. by a specific tag, any tag or all tags) can also be adopted for these queries which are explained below.

Pre-Requisites for Using SQL Query with Tags

Include the following namespace in your application:
- Alachisoft.NCache.Web.Caching
- Alachisoft.NCache.Runtime
The application must be connected to cache before performing the operation.
Cache must be running.
Make sure that the data being added is serializable.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Search Data in Cache with One Tag

Using object queries, a single tag can be used to retrieve all the items associated with that tag.

Following example retrieves all the items associated with the tag Beverages using the SQL query.

Warning

Providing Null tag value for the query will throw an ArgumentNullException.

Note

Use fully-qualified name of the class Customer e.g. Data.Customer.

try
{
    // PreCondition: Tag is created and associated with an item
    // Following tags are created and item is added in the cache with these tags
    Tag[] tags = new Tag[2];
    tags[0] = new Tag("Important Customers");
    tags[1] = new Tag("East Coast Customers");

    // A new hashtable is created for adding the tag list for query searching values
    Hashtable queryValue = new Hashtable();

    // Finding by the tag
    // Make sure to use the fully qualified class name
    string query = "SELECT Data.Customer WHERE this.$Tag$=?";

    // Passing the value 'ImportantCustomers' to the tagValue
    string tagValue = "Important Customers";

    // Add tag value to Hashtable for query searching values.   
    queryValue.Add("$Tag$", tagValue);

    // Passing true will return keys and items in result set 
    // Passing false will return only keys
    // ExecuteReader processes the queryValue and sends the result to the client
    ICacheReader queryResult = cache.ExecuteReader(query, queryValue, true);

    // queryResult contains all the keys and    values related to the tag 'Beverages'
    if (queryResult.FieldCount > 0)
    {
        while (queryResult.Read())
        {
            // Since true was passed, the result set returned will be as follows,
            //         reader[0]    :       Contains key
            //         reader[1]    :    Contains customer
        }
    }
    else
    {
    // No item containing the tagValue exists
    }
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation performed during state transfer
    // Operation Timeout
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Search Data in Cache with ANY Tag

Using object queries, if multiple tags are provided in the form of a list, items associated with ANY of the tags from the list are retrieved.

Following example retrieves all the items associated with any of the tags from the list using the SQL query.

Warning

Providing Null tag value for the query will throw an ArgumentNullException.

Note

Use fully-qualified name of the class Customer e.g. Data.Customer.

try
{
    // Following tags are created and item is added in the cache with these tags
    Tag[] tags = new Tag[2];
    tags[0] = new Tag("Important Customers");
    tags[1] = new Tag("East Coast Customers");

    // A new hashtable is created for adding the tag list for query searching values
    Hashtable queryValue = new Hashtable();

    // Finding items associated with any of the tags
    // Can be done by executing either of the provided query
    // Make sure to use the fully qualified class name
    string query = "SELECT Data.Customer WHERE this.$Tag$ = ? OR this.$Tag$ = ?";
    //OR
    string query = "SELECT Data.Customer WHERE this.$Tag$ IN (?,?,?)";

    // If multiple tags are used for query criteria, add tags in an array list
    ArrayList queryTagList = new ArrayList();

    // Instances of tags can also be added for criteria instead of strings
    queryTagList.Add("Important Customers");
    queryTagList.Add("East Coast Customers");

    // Add tag list to Hashtable for query searching values.
    queryValue.Add("$Tag$", queryTagList);
    {
        // Passing true will return keys and items in result set 
        // Passing false will return only keys
        // ExecuteReader processes the query and sends the result to the client
        ICacheReader queryResult = cache.ExecuteReader(query, queryValue, true);

        // queryResult contains all the keys and values related 
        // to any of the tags in the list
        if (queryResult.FieldCount > 0)
        {
            while (queryResult.Read())
            {
                // Since true was passed, the result set returned will be as follows,
                //         reader[0]    :   Contains key
                //         reader[1]    :    Contains customer
            }
        }
        else
        {
            // No item associated with any of the tags exists
        }
    }
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation performed during state transfer
    // Operation Timeout
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Search Data in Cache with ALL Tags

Using object queries, if multiple tags are provided in the form of a list, items associated with ALL of the tags from the list are retrieved. It means that an item is not retrieved unless it contains all the tags in the list.

Following example retrieves all the items associated with all of the tags from the list using the SQL query.

Warning

Providing Null tag value for the query will throw an ArgumentNullException.

Note

Use fully-qualified name of the class Customer e.g. Data.Customer.

try
{
    // Following tags are created and item is added in the cache with these tags
    Tag[] tags = new Tag[2];
    tags[0] = new Tag("Important Customers");
    tags[1] = new Tag("East Coast Customers");

    // A new hashtable is created for adding the tag list for query searching values
    Hashtable queryValue = new Hashtable();

    // Finding items associated with BOTH the tags in the list
    // Make sure to use the fully qualified class name
    string query = "SELECT Data.Customer WHERE this.$Tag$=? AND this.$Tag$=?";

    // If multiple tags are used for query criteria, add tags in an array list
    ArrayList queryTagList = new ArrayList();

    // Instances of tags can also be added for criteria instead of strings
    queryTagList.Add("Important Customers");
    queryTagList.Add("East Coast Customers");

    // Add tag list to Hashtable for query searching values.
    queryValue.Add("$Tag$", queryTagList);

    // Passing true will return keys and items in result set    
    // Passing false will return only keys
    // ExecuteReader processes the query and sends the result to the client
    ICacheReader queryResult = cache.ExecuteReader(query, queryValue, true);

    // queryResult contains all the keys and values related to any of 
    // the tags in the list
    if (queryResult.FieldCount > 0)
    {
        while (queryResult.Read())
        {
            // Since true was passed, the result set returned will be as follows,
            //         reader[0]    :    Contains key
            //         reader[1]    :   Contains customer
        }
    }
    else
    {
        // No item containing all of the tags exists
    }
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation performed during state transfer
    // Operation Timeout
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

In order to get more detail on object queries please refer to the SQL Reference for NCache section.

Search Data in Cache with SQL and Tags

Pre-Requisites for Using SQL Query with Tags

Search Data in Cache with One Tag

Warning

Note

Search Data in Cache with ANY Tag

Warning

Note

Search Data in Cache with ALL Tags

Warning

Note

See Also