SQL Search for Keys

NCache provides you with the ability to query indexed cache data through its own indexing mechanism. It lets you search for keys fulfilling the given criteria and then returns the keys of the result set.

ExecuteReader is used for searching the cache as it processes the query on the server side and then sends the result in chunks to the client. As soon as the chunk is exhausted after being enumerated over, the next chunk is sent over to the client.

Retrieving the keys only is a really helpful approach since you will get all the keys as a result of the query. If you want to perform any further operations on the result set, it can be done on the client cache using the keys by retreiving the keys from the Client Cache using GetBulk. This saves the user the time since operations performed on Client Cache are faster and provides the user with a higher flexibility.

For example, a query when executed, fetches the keys from the cache. These keys can now be used to perform operations on your client cache, given that the data also resides in the client cache. This makes the operations faster and rapid.

Pre-Requisites

Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
Include the following namespace in your application:
- Alachisoft.NCache.Web.Caching
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.

The following example searches the cache for the items which fulfil the query criteria and returns all the keys which fulfil this criteria using ExecuteReader.

try
{
    // Pre-condition: Cache is already connected
    // Items are already present in the cache.

    // Create a query which will be executed on the data set
    // Use the fully qualified name of your own custom class
    string query = "SELECT Alachisoft.NCache.Sample.Data.Product WHERE this.UnitsInStock > ?";

    // Create a new hashtable
    Hashtable values = new Hashtable();

    // Add the values of the UnitsInStock in the hashtable
    values.Add("UnitsInStock", 10);

    // Query Cached Data
    // Specifying false here means only the key is retrieved
    ICacheReader reader = cache.ExecuteReader(query, values, false);

    // Check if the result set is not empty
    if (reader.FieldCount > 0)
    {
        while (reader.Read())
        {
            string keys = reader.GetValue(0) as string;
            // reader.GetValue(0) = Cache key
            // Perform operations using the retrieved keys
        }
    }
    else
    {
        // Null query result set retrieved
    }
}
catch (OperationFailedException ex)
{
    if (ex.Message.Contains("Incorrect query format"))
    {
        // Make sure that the query format is correct
    }
    else if (ex.Message.Contains("Index is not defined"))
    {
        // Make sure that index is defined using Query Indexes through NCache Manager
    }
    else
    {
        // Exception can occur due to:
        // Connection Failures
        // Operation performed during state transfer
        // Operation Timeout
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentException, ArgumentNullException
}

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

Additional Resources

NCache provides sample application for SQL Searching at:

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

SQL Search for Keys

Pre-Requisites

Additional Resources

See Also