Querying Data in NCache
NCache provides the ability to query indexed cache data through its own indexing mechanism. Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
Using API for Querying
Previously, NCache had the APIs Search
and SearchEntries
for returning the
query result, but they have been marked obsolete now. Preferably use
ExecuteReader
for your application.
The following API is available for using NCache query feature:
public ICacheReader ExecuteReader(string query, IDictionary values);
public ICacheReader ExecuteReader(string query, IDictionary values, bool getData);
public ICacheReader ExecuteReader(string query, IDictionary values, bool getData, int chunkSize);
public int ExecuteNonQuery(string query, IDictionary values);
[Deprecated]
public ICollection Search(string query, IDictionary values)
Reason: Returned the list of keys whose data fulfilled the result criteria. For large scale data, the collection tends to be large, putting a pressure on network cost.
[Deprecated]
public IDictionary SearchEntries(string query, IDictionary values)
Reason: Returned the list of keys with data whose data fulfilled the result criteria. Reason is same as above, with the size of dictionary tending to be exponentially greater than a keys-only collection.
Methods
Name | Return Type | Description |
---|---|---|
ExecuteReader |
ICacheReader |
Returns list of key-value pairs in result set which fulfills the query criteria. This key value pair has cache key and its respective value. You can still get the keys-only result like Search through varying overloads. |
ExecuteNonQuery |
int |
Used in queries containing Delete operations. Returns number of affected rows after query is executed. |
Search |
ICollection |
Only returns list of keys in result set which fulfill the query criteria. |
SearchEntries |
IDictionary |
Return list of key-value pairs in result set which fulfills the query criteria. |
Parameters
Name | Type | Description |
---|---|---|
query |
string |
Query string but instead of values '?' will be specified by user, e.g., SELECT Product WHERE this.ProductID == ? |
values |
IDictionary |
Key-value pairs of attribute-names and their values on which your search criteria is based on. |
getData |
bool |
Flag to indicate whether you want to get the resulting values of the query or not. |
chunkSize |
int |
Size of the chunk which ExecuteReader will send to the user after processing on the server. |
Hashtable values = new Hashtable();
string key = "ProductID";
int value = 1001;
values.Add(key, value);
To utilize the APIs, include the following namespace in your application:
Alachisoft.NCache.Web.Caching.
Cache Search Returning Items
Previously, the whole resulting data was returned after query execution using
SearchEntries
API, which was expensive for the network if the size of the
result was too large. That is why, ExecuteReader is used as it processes the
query on the server side and then sends the result in chunks (as a dictionary
containing keys and values) to the client. As soon as the chunk is exhausted
after being enumerated over, the next chunk is sent over to the client.
ExecuteReader()
returns the result set in tabular form to ICacheReader
type
of instance. Column count and attribute values can be fetched through their
names or through index number from ICacheReader
. Make sure the instance of
ICacheReader
is always closed after execution, as it is necessary to clean the
resources.
string query = "SELECT this.Category, MAX(Prod.Product.ProductID) WHERE this.Category = ? GROUP BY this.Category";
Hashtable values = new Hashtable();
string key = "Category";
int value = 4;
values.Add(key, value);
try
{
ICacheReader reader = cache.ExecuteReader(query, values);
//OR
ICacheReader reader = cache.ExecuteReader(query, values, true);
//OR
ICacheReader reader = cache.ExecuteReader(query, values, true, 50);
string fieldName = "Category";
int indexNumber = 1;
if (reader.FieldCount > 0)
{
while (reader.Read())
{
//you can get value through the field name...
object category = reader.GetOrdinal(fieldName);
//...or through the index number
object result = reader.GetValue(indexNumber);
//perform operations
}
}
else{
//no record exists
}
reader.Close();
}
catch (Exception ex)
{
//handle exception
}
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";
product.UnitsInStock = 250;
product.Category = 4;
string key = "Product:" + product.ProductID;
try
{
cache.Add(key, product);
}
catch (OperationFailedException ex)
{
//handle exception
}
Cache Search Returning Keys
In case you still require to mirror the behavior of Search API, i.e., return
only the keys after a query is executed, you can do so by setting the getData
flag to false in ExecuteQuery
:
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 = ?";
ICacheReader reader = cache.ExecuteReader(query, values, false);
// OR
string query = "SELECT Product WHERE this.ProductName = ? and this.UnitsInStock > ?";
ICacheReader reader = cache.ExecuteReader(query, values, false);
int indexNumber = 0;
if (reader.FieldCount > 0)
{
while (reader.Read())
{
object category = reader.GetValue(0);
//perform operations
}
}
else
{
//no record exists
}
reader.Close();
}
catch (OperationFailedException ex)
{
// handle exception
}