Search Data with Named Tags and SQL

The Object Queries can also be used to retrieve items from the cache with your query criteria being Named Tags. The query when executed will search the cache according to the Named Tag provided in the SQL query. Before searching, make sure that the item is added to the cache with the Named Tags. Refer to the Add Items with Named Tags section to get a detail on creating and adding Named Tags.

Prerequisites

To learn about the standard prerequisites required to work with all NCache client-side features, please refer to the given page on Client-Side API Prerequisites.
Custom classes/searchable attributes must be indexed as explained in Configuring Query Indexes.
For API details, refer to: ICache, ICacheReader, QueryCommand, ExecuteReader, SearchService.

The following example retrieves the Products from the cache where the values of Named Tags match the provided value.

// Preconditions: Cache is already connected

// User wants to see the details of all the customers having 12% VIP membership discount using SQL query.
// Custom class is query indexed through the NCache Management Center or config.ncconf*/

// Create an SQL Query with the specified criteria
// Make sure to use the Fully Qualified Name for custom class
string query = "SELECT CustomerID,ContactName FROM Alachisoft.NCache.Samples.Data.Customer WHERE VIP_Membership_Discount = 0.12 ";

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

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

// Read results if the result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        // Get the value of the result set
        string customerID = reader.GetValue<string>("CustomerID");
        string customerName = reader.GetValue<string>("ContactName");

        Console.WriteLine($"Customer '{customerName}' with ID '{customerID}' has VIP membership discount.");
    }
}
else
{
    Console.WriteLine($"No VIP members found");
}

// Preconditions: Cache is already connected

// User wants to see the details of all the customers having 12% VIP membership discount using SQL query.
// Custom class is query indexed through the NCache Management Center or config.ncconf*/

// Create an SQL Query with the specified criteria
// Make sure to use the Fully Qualified Name for custom class

// Create an SQL Query with the specified criteria
String query = "SELECT customerID,contactName FROM com.alachisoft.ncache.sample.Customer WHERE VIP_Membership_Discount = 0.12";

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

// Executing the Query
CacheReader reader = cache.getSearchService().executeReader(queryCommand);

// Read results if the result set is not empty
if (reader.getFieldCount() > 0)
{
    while (reader.read()) {
        // Get the value of the result set
        String customerID = reader.getValue("customerID", String.class);
        String customerName = reader.getValue("contactName", String.class);

        System.out.println("Customer '" + customerName + "' with ID '" + customerID + "' has VIP membership discount.");
    }
}
else
{
    System.out.println("No VIP members found");
}

// This is an async method
// Preconditions: Cache is already connected
// Items are already present in the cache with Named Tags
// Custom class is query indexed through the NCache Management Center or config.ncconf
// Create SQL Query with the specified criteria
// Make sure to use the Fully Qualified Name (FQN)
var query = "SELECT $Value$ FROM FQN.Product WHERE FlashSaleDiscount = ?";

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

let parameter = new Map();
parameter.set("FlashSaleDiscount", 0.5);
queryCommand.setParameters(parameter);

// Executing query
var searchService = await this.cache.getSearchService();

var reader = await searchService.executeReader(queryCommand);

// Check if result set is not empty
if (reader.getFieldCount() > 0)
{
    while (reader.read())
    {
        // Get the value of the result set
        var result = reader.getValue(1, ncache.JsonDataType.Object);
    }
}
else
{
    // Null query result set retrieved
}

# Preconditions: Cache is already connected
# Items are already present in the cache with Named Tags
# Custom class is query indexed through the NCache Management Center or config.ncconf

# Create SQL Query with the specified criteria
# Make sure to use the Fully Qualified Name(FQN)
query = "SELECT $Value$ FROM FQN.Product WHERE FlashSaleDiscount = ?"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

parameter = {"FlashSaleDiscount": 0.5}
query_command.set_parameters(parameter)

# Executing query
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)

# Check if result set is not empty
if reader.get_field_count() > 0:
    while reader.read():
        # Get the value of the result set
        result = reader.get_value(Product, 1)
else:
    # None query result set retrieved
    print("Query result is None")

string query = "SELECT Product WHERE this.Supplier = ?";
Hashtable values = new Hashtable();
values.Add("UnitsInStock", 4);
values.Add("Supplier", "Alex");

ICacheReader queryResult = cache.ExecuteReader(query, values, true);
// queryResult contains all keys related to both tags.
if (queryResult.FieldCount > 0)
{
    while (queryResult.Read())
    {
        // perform operations
    }
}
else
{
    // no record exists
}

Note

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

Warning

If you have multiple applications that share the same cache and all of them are supposed to add Named Tags, then make sure that the same Named Tags have homogenous data types. For example, if one client is adding a Named Tag ProductID with a string data type, then all other clients should add values of ProductID only in a string format for the same cache.

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

Additional Resources

NCache provides a sample application for Tags on GitHub.

Search Data with Named Tags and SQL

Prerequisites

Note

Warning

Additional Resources

See Also

Contact Us