NCache provides the ability to query the indexed cache data. NCache provides its own indexing mechanism. Indexing for searchable objects and their attributes need to be configured first as explained in earlier sections.
Using API for Querying
The following API is available for using NCache query feature:
public ICollection Search(string query, IDictionary searchValues)
public IDictionary SearchEntries(string query, IDictionary searchValues)
public ICacheReader ExecuteReader(string query, IDictionary searchValues)
Methods
|
Name
|
Return Type
|
Description
|
|
Search
|
ICollection
|
Only return list of keys in result set which fulfill the query criteria.
|
|
SearchEntries
|
IDictionary
|
Return list of key-value pairs in result set which fulfill the query criteria. This key value pair have cache key and their respective value.
|
|
ExecuteReader
|
ICacheReader
|
Return result set for GROUP BY clause in the form of columns which fulfill the query criteria.
NOTE: ExecuteReader is only used with 'GROUP BY' clause and with aggregate queries.
|
Parameters
|
Name
|
Type
|
Description
|
|
Query
|
String
|
Query string but instead of values '?' will be specified by user, e.g., "SELECT Product WHERE this.ProductID == ?"
|
|
searchValues
|
IDictionary
|
Key-value pairs of attribute-names and their values on which your search criteria is based on.
Hashtable searchValues = new Hashtable();
searchValues.Add("ProductID", 1);
|
Adding and Updating Indexed Items
Before searching, items need to be indexed and added in cache. For adding indexed items, the basic APIs of add and insert as mentioned in NCache Basic Operations should be used.
Product product = new Product();
product.ProductID = 1001;
product.ProductName = "Chai";
string key = "Product:" +product.ProductID ;
try
{
cache.Add(key, product);
}
catch (OperationFailedException ex)
{
// handle exception
}
Cache Search Returning Keys
The following code searches the cache and returns a collection of keys. Then, it iterates over all the returned keys and individually fetches the corresponding cached items from the cache.
Hashtable values = new Hashtable();
values.Add("ProductName", "Chai");
values.Add("UnitsInStock", 250);
try
{
// Instead of Product, specify fully qualified name of your custom class.
string query = "SELECT Product where this.ProductName = ?";
ICollection result = cache.Search(query, values);
query = "SELECT Product WHERE this.UnitsInStock > ?";
result = cache.Search(query, values);
query = "SELECT Product WHERE this.ProductName = ? and this.UnitsInStock > ?";
result = cache.Search(query, values);
query = "SELECT Product WHERE Not(this.ProductName = ? and this.UnitsInStock > ?)";
result = cache.Search(query, values);
}
catch (OperationFailedException ex)
{
// handle exception
}
The benefit of this approach is that the query returns keys only. Then, the client application can determine which key it wants to fetch. The drawback of this approach is increased number of trips to cache as most of the items are fetched from cache anyway. And, if the cache is distributed, this may eventually become costly.
Cache Search Returning Items
In some situations when the intention is to fetch all or most of the cached items associated with cache keys, it is better to fetch all the keys and items in one call. Below is an example of such a scenario. It searches the cache and returns a dictionary containing both keys and values. This way, all the data based on the search criteria is fetched in one call to NCache. This is much more efficient way of fetching all the data from the cache. Example is mentioned below.
string queryString = " SELECT Product where this.ProductID = ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 1001);
IDictionary items;
try
{
//Call SearchEntries for fetching result set in key value pairs.
items = cache.SearchEntries(queryString, values);
}
catch (OperationFailedException ex)
{
// handle exception
}
if (items.Count > 0)
{
IDictionaryEnumerator ide = items.GetEnumerator();
while (ide.MoveNext())
{
String key = (String)ide.Key;
Product product = (Product)ide.Value;
// use cache keys and values in your application code here.
}
}
Cache Search with Execute Reader (Group by)
For grouping the result set of aggregate functions on the basis of one or more columns, GROUP BY clause is used in queries. For executing queries with GROUP BY in NCache, ExecuteReader() function is used. It returns result set in tabular form in ICacheReader type of instance. Result of GROUP BY query is not sorted. Columns count and attribute values can be fetched through their names from ICacheReader. Example is as follows:
string queryString = "SELECT this.Category MAX(Product.ProductID) GROUP BY this.Category where this.Category = ?";
Hashtable values = new Hashtable();
values.Add("Category", 4);
ICacheReader result = cache.ExecuteReader(queryString, values);
if (result.ColumnCount > 0)
{
while (result.Read())
{
String category = (String)result.GetValue("Category");
//use other values in your code if needed.
}
}
Here is the complete code sample which uses all search APIs:
try
{
string queryString = "SELECT Product where this.ProductID = ?";
//Query for GROUP BY
string queryString2 = "SELECT this.Category,this.Supplier COUNT(Product.ProductID) GROUP BY this.Category,this.Supplier where this.Category = ?";
Hashtable values = new Hashtable();
values.Add("ProductID",1001);
values.Add("Category", 4);
//Call Search for fetching result set related keys only.
ICollection keys = cache.Search(queryString, values);
//Call SearchEntries for fetching result set in key value pairs.
IDictionary items = cache.SearchEntries(queryString, values);
// Call ExecuteReader for fetching result columns of any query with GROUP BY
ICacheReader result = cache.ExecuteReader(queryString2, values);
if (items.Count > 0)
{
IDictionaryEnumerator ide = items.GetEnumerator();
while (ide.MoveNext())
{
String key = (String)ide.Key;
Product product = (Product)ide.Value;
// use cache keys and values in your application code here.
}
}
}
catch (Exception ex)
{
// can be operation failed due to server lost, state transfer or invalid query format.
//Index not defined exception when attributes are not indexed.
//...
}
Querying Samples for Operators
Some code samples for different queries are given below:
Using Equal Operator
string queryString = "SELECT Product where this.ProductID = ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 1001);
try
{
ICollection keys = cache.Search(queryString, values);
IDictionary items = cache.SearchEntries(queryString, values);
}
catch(Exception ex)
{
// handle exception
}
Using Multiple Operators
string queryString = "SELECT Product where this.ProductID < ? AND this.Category = ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 1001);
values.Add("Category", 4);
try
{
ICollection keys = cache.Search(queryString, values);
IDictionary items = cache.SearchEntries(queryString, values);
}
catch(Exception ex)
{
// handle exception
}
Using IN Operator
string queryString = "SELECT Product where this.ProductID IN (?,?,?)";
ArrayList idList = new ArrayList();
idList.Add(10);
idList.Add(4);
idList.Add(7);
Hashtable values = new Hashtable();
values.Add("ProductID", idList);
try
{
ICollection keys = cache.Search(queryString, values);
IDictionary items = cache.SearchEntries(queryString, values);
}
catch(Exception ex)
{
// handle exception
}
Using LIKE Operator
string queryString = "SELECT Product where this.ProductName LIKE ?";
ArrayList list = new ArrayList();
list.Add("Ch*");
Hashtable values = new Hashtable();
values.Add("ProductName", list);
try
{
ICollection keys = cache.Search(queryString, values);
IDictionary items = cache.SearchEntries(queryString, values);
}
catch(Exception ex)
{
// handle exception
}
Using GROUP BY Clause
For using 'GROUP BY' clause, any aggregate function should be used in query and to get thequery result, ExecuteReader function should be used. GROUP BY clause cannot be used without aggregate function.
string queryString = "SELECT this.Category, COUNT(Product) WHERE this.ProductID > ? GROUP BY this.Category";
Hashtable values = new Hashtable();
values.Add("ProductID", 5);
try
{
ICacheReader reader = cache.ExecuteReader(queryString, values);
while (reader.Read())
{
string category = (string)reader.GetValue(0);
decimal countVal = (decimal)reader.GetValue(1);
}
}
catch(Exception ex)
{
// handle exception
}
Using DELETE Statement
For the delete statement, ExecuteNonQuery function should be used.
string queryString = "DELETE Product WHERE this.ProductID > ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 5);
try
{
int result = cache.ExecuteNonQuery(queryString, values);
//return number of effected rows.
}
catch(Exception ex)
{
// handle exception
}
See Also