Query Data in Cache Using SQL

Note

This feature is only available in NCache Enterprise.

NCache provides you with the ability to query indexed cache data through its own SQL-like querying mechanism. It lets you search for keys fulfilling the given criteria and then returns the keys of the result set. You can use ExecuteReader() or ExecuteScalar() to retrieve this data.

Note

No matter which projections you use in your query, the result set will only return keys when getdata is set to false.

Prerequisites

Note

NCache also supports escape sequences including \n,\b, \t, and wild cards * and ? as characters for all NCache-supported SQL operators. To learn more about these escape sequences refer to the NCache SQL-Like syntax guide.

Using ExecuteReader To Query Data in Cache

ExecuteReader performs queries on the cache based on the user-specified criteria. It returns a list of key-value pairs in a data reader which fulfills the query criteria. This key-value pair has a cache key and its respective values.

SQL Query For Keys

Here is a query sample that returns the keys fulfilling the criteria based on ProductID.

Important

Make sure the instance of ICacheReader is always closed after execution, as it is necessary to clean the resources.

// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query which will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
string query = "SELECT * FROM FQN.Product WHERE ProductID > ?";

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

// Providing parameters for query
queryCommand.Parameters.Add("ProductID",50000);

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


// Check if the result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        string result = reader.GetValue<string>(0);
        // Perform operations using the retrieved keys
    }
}
else
{
    // Null query result set retrieved
}
Note

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

Using the SQL IN Operator

To query the cache using this SQL-like query format with the IN operator, you can specify multiple attribute values after the IN keyword. Here's an example that retrieves ProductName and UnitsInStock from the cache having either 10, 20, or 30 UnitsInStock using ExecuteReader.

string query = "SELECT ProductName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (?, ?, ?)";

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

ArrayList unitsArray = new ArrayList();
unitsArray.Add(10);
unitsArray.Add(20);
unitsArray.Add(30);

queryCommand.Parameters.Add("UnitsInStock", unitsArray);

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

// Read results if result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        string productName = reader.GetValue<string>("ProductName");
        int unitsInStock = reader.GetValue<int>("UnitsInStock");

        Console.WriteLine($"Product '{productName}' has {unitsInStock} Units In Stock.");
    }
}

Using the SQL LIKE Operator

NCache allows you to query for a specific pattern in a column through SQL-like query format using the LIKE operator. The two wildcards used with the LIKE operator are:

  1. *: Used as a substitute for zero or more characters in the string.
  2. ?: Used as a substitute for a single character in the string.

Here is an example that searches the cache and retrieves ProductName and UnitPrice using ExecuteReader.

string query = "SELECT ProductName, UnitPrice From FQN.Product WHERE ProductName LIKE ?";


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

queryCommand.Parameters.Add("ProductName", "choco*");

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

// Read results if result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        string productName = reader.GetValue<string>("ProductName");
        double unitPrice = reader.GetValue<double>("UnitPrice");

        Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}");
    }
}
else
{
    Console.WriteLine($"No product found having a name that starts with 'choco'.");
}

Using the SQL GROUP BY Operator

NCache provides you with the ability to group data according to a given criteria through this SQL-like query format using the GROUP BY clause.

Important

Note that the GROUP BY clause cannot be used without an Aggregate function.

The following example retrieves all the products based on their UnitPrice and groups them according to their Category and Count.

string query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice < ? Group By Category";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("UnitPrice", 100.0);

// Executing QueryCommand through ICacheReader
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
        string category = reader.GetValue<string>("Category");
        int count = reader.GetValue<int>("COUNT()");

        Console.WriteLine($"Category '{category}' has '{count}' affordable products.");
    }
}
else
{
    Console.WriteLine($"No category contains affordable products.");
}

Using the SQL ORDER BY Operator

NCache provides you with the ability to sort the data in ascending or descending order, according to the given criteria, through SQL-like query format using the ORDER BY clause. The following example retrieves ProductName and UnitPrice, where ProductName is greater than 100 and sorts them in descending order using ExecuteReader.

string query = "SELECT ProductName,UnitPrice FROM FQN.Product WHERE UnitPrice < ? ORDER BY UnitPrice DESC";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
//changes
queryCommand.Parameters.Add("UnitPrice", 100.0);

// Executing QueryCommand through ICacheReader
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
        string productName = reader.GetValue<string>("ProductName");
        string unitPrice = reader.GetValue<string>("UnitPrice");

        Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}");
    }
}
else
{
    Console.WriteLine($"No products found which are cheaper than 100.");
}

Using the SQL DateTime Operator

NCache provides you with ease of searching the cache by providing parameters in various formats. You can use many functions in a single query for faster cache searching. DateTime can be used in your query to search the cache concerning a particular date or time. For more details on date formats, please refer to the Microsoft DateTime Struct Docs.

Note

Make sure that you specify the date in the correct format.

The following example executes a query that searches for all the orders in the cache whose OrderDate is as specified using ExecuteReader.

string query = "SELECT OrderID FROM FQN.Order WHERE OrderDate = ?";

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

// Define order date
DateTime orderDate = new DateTime(2022, 01, 01);

// Providing parameters to query
queryCommand.Parameters.Add("OrderDate", orderDate);

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

// Read results if result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        // Get the value of the result set
        int orderID = reader.GetValue<int>("OrderID");
        Console.WriteLine($"Order: {orderID} was placed on 1st January, 2022.");
    }
}
else
{
    Console.WriteLine($"No orders were placed on 1st January 2022.");
}

Using the SQL Logical Operator

NCache provides the user with the AND, OR, and NOT logical operators to specify more than one condition after the operator in these queries through this SQL-like query format. Here's an example showing how to use the combination of AND and OR operators in a single query within the WHERE clause using ExecuteReader.

string query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";

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

//Adding Parameters
ArrayList categoryList = new ArrayList();
categoryList.Add("Beverages");
categoryList.Add("Produce");
queryCommand.Parameters.Add("Category", categoryList);
queryCommand.Parameters.Add("UnitsInStock", 100);
queryCommand.Parameters.Add("UnitPrice", 100);

// 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())
    {
        // Get the value of the result set
        string result = reader.GetValue<string>("ProductID");
    }
}
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
}

Using ExecuteScalar to Search Data in Cache

ExecuteScalar executes user queries and returns the first column of the first row in the result set, any additional columns or rows are ignored.

string query = "SELECT AVG(UnitPrice) FROM FQN.Product WHERE Category = ?";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("Category", "Beverages");

// Executing QueryCommand through ICacheReader
decimal average = (decimal)cache.SearchService.ExecuteScalar(queryCommand);

Console.WriteLine($"Average price of Beverages is {average}.");

Additional Resources

NCache provides sample application for SQL Queries on GitHub.

See Also

Query Cache with LINQ
SQL Refrence