Search Data Types in Cache with SQL Queries

Note

This feature is only available in NCache Enterprise Edition.

Data types can be queried in cache if they have searchable attributes associated against them during data type creation. Searchable attributes include:

Groups
Tags
Named Tags

Pre-requisites

Include the following namespaces in your application:
- Alachisoft.NCache.Client
- Alachisoft.NCache.Client.DataTypes
- Alachisoft.NCache.Client.DataTypes.Collections
- Alachisoft.NCache.Client.Services
- Alachisoft.NCache.Runtime.Caching
- Alachisoft.NCache.Runtime.Exceptions
The application must be connected to cache before performing the operation.
Cache must be running.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

The following code sample fetches all items from the cache which have been associated with a specified group "Electronics". The group can be associated using DataTypeAttributes class during data type creation.

Important

If there are multiple lists specified against the group, all of them will be returned in one result, granted that their data types are similar. For multiple cache items, for example if a CacheItem and a list belong to the same group, you need to ensure data is handled accordingly.

try
{
    // Pre-conditions: Cache is already connected
    // Items with groups exist in cache
    // Custom class is query indexed through NCache Web Manager or config.ncconf

    // Search for items with group
    // Provide Fully Qualified Name (FQN) of your custom class 
    string query = "SELECT ProductID FROM FQN.Customer WHERE $Group$ = ?";

    /// Use QueryCommand for query execution
    var queryCommand = new QueryCommand(query);

    // Providing parameters for query
    queryCommand.Parameters.Add("$Group$", "Electronics");

    // Executing QueryCommand through ICacheReader
    ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

    // Check if the result set is not empty
    if (reader.FieldCount > 0)
    {
        while (reader.Read())
        {
            string ProductID = reader.GetValue<string>(1);
            // Perform operations
        }
    }
    else
    {
        // Null query result set retrieved
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures 
        // Operation Timeout
        // Operation performed during state transfer    
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentException, ArgumentNullException    
}

The following code sample fetches all lists from the cache which have been associated with a specified group "Electronics". The group can be associated using DataTypeAttributes class during data type creation.

Important

try
{
    // Pre-condition: Cache is already connected

    // List with this group already exists in cache
    string groupName = "Electronics";

    IDictionary<string, IDistributedList<Product>> result =
                       cache.SearchService.
                       GetGroupData<IDistributedList<Product>>(groupName); 

    if (result != null && result.Count > 0)
    {
        // Iterate over list
        foreach (var item in result)
        {            
            foreach (var i in item.Value)
            {
                // Perform operations
            }
        }
    }
    else
    {
        // No data against the group found
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // NCache specific exception
        // Exception can occur due to:
        // Connection Failures
        // Operation Timeout
        // Operation performed during state transfer
    }
}
catch (Exception ex)
{
    // Any 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 Types by Tags

SQL Query
Tag API

The following code sample fetches all Dictionary instances from the cache which have been associated with a specified tag "Stainless Steel". The tag can be associated using DataTypeAttributes class during data type creation.

try
{
    // Pre-conditions: Cache is already connected
    // Items are already present in the cache with tags
    // Custom class is query indexed through NCache Web Manager or config.ncconf

    // Search for items with tags
    // Use the Fully Qualified Name (FQN) of your own custom class   
    string query = "Select $Value$ FROM FQN.Product WHERE $Tag$ = ?";

    // Use QueryCommand for query execution
    var queryCommand = new QueryCommand(query);
    queryCommand.Parameters.Add("$Tag$", "Stainless Steel");

    // Executing query
    ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

    // Check if result set is not empty
    if (reader.FieldCount > 0)
    {
        while (reader.Read())
        {
            // Get the value of the result set
            Product result = reader.GetValue<Product>(1);

            //reader.GetValue(0) = Cache key
            // reader.GetValue(1) = Product object
        }
    }
    else
    {
        // Null query result set retrieved
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures 
        // Operation Timeout
        // Operation performed during state transfer    
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentException, ArgumentNullException    
}

Important

If there are multiple dictionaries specified against the tag, all of them will be returned in one result dictionary, granted that their data types are similar. For multiple cache items, for example if a CacheItem and a dictionary belong to the same tag, you need to ensure data is handled accordingly.

try
{
    // Pre-condition: Cache is connected

    // Items against this tag exist in cache
    Tag tag = new Tag("Stainless Steel");

    // Get dictionary against tag
    IDictionary<string, IDistributedDictionary<string, Product>> result =     
                        cache.SearchService.
                        GetByTag<IDistributedDictionary<string, Product>>(tag);

    if (result != null && result.Count > 0)
    {
        foreach (var item in result)
        {
            // Perform operations
        }
    }
    else
    {
        // Dictionary does not exist against tag
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // NCache specific exception
        // Exception can occur due to:
        // Connection Failures
        // Operation Timeout
        // Operation performed during state transfer
    }
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

Search Data Types by Named Tags

NCache supports querying on data types if Named Tags have been specified. This uses the $DataType$ keyword which will fetch all data types with the specified Named Tag.

The following code assumes a list has been added with NamedTag "Discount" and fetches it using queries.

try
{
    // Pre-conditions: Cache is already connected 
    // Data Types exist with NamedTag "Discount" and value 0.4

    // Create query
    string query = "SELECT * FROM $DataType$ WHERE Discount = 0.4";

    var queryCommand = new QueryCommand(query);

    // Execute Query to search cache
    ICacheReader queryResult = cache.SearchService.ExecuteReader(queryCommand);

    // queryResult contains all the keys and metadata of result
    if (queryResult.FieldCount > 0)
    {
        while (queryResult.Read())
        {
            // Key of list can be fetched through
            // queryResult.GetValue<string>(0);
        }
    }
    else
    {
        // No data containing the named tag(s) exist
    }
}
catch (OperationFailedException ex)
{
    if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure that the query format is correct
    }
    else
    {
        // NCache specific exception
        // Exception can occur due to:
        // Connection Failures
        // Operation Timeout
        // Operation performed during state transfer
    }
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

Additional Resources

NCache provides sample application for querying at:

GitHub
Shipped with NCache: %NCHOME%\samples\dotnet\SearchUsingSQL

Search Data Types in Cache with SQL Queries

Note

Pre-requisites

Search Data Types by Group

Important

Search Data Types by Tags

Search Data Types by Named Tags

Additional Resources

See Also