NCache 4.3 - Online Documentation

Object Query Language Syntax

 
NOTE: This feature is not available in NCache Express and Professional edition.
 
NCache supports SQL-like language called Object Query Language (OQL). Syntax of the language is explained with the help of examples below:
 
Using Equal Operator:
 
string queryString = "select NCacheQuerySample.Business.Product where this.Category = ?" ;
Hashtable values = new Hashtable();
values.Add("Category", 10);
ICollection keys = _cache.Search(queryString, values);// Return Keys
IDictionary dict = _cache.SearchEntries(query, values);// Return Keys and values
 
 
Using Multiple Operators:
 
string queryString = "select NCacheQuerySample.Business.Product where this.ProductID < ? AND this.Category = ?" ;
Hashtable values = new Hashtable();
values.Add("ProductID", 10);
values.Add("Category", 4);
ICollection keys = _cache.Search(queryString, values);// Return Keys
IDictionary dict = _cache.SearchEntries(query, values);// Return Keys and values
 
 
Using IN Operator:
 
string queryString = "select NCacheQuerySample.Business.Product where this.ProductID IN (?,?,?);
ArrayList list = new ArrayList();
list.Add(10);
list.Add(4);
list.Add(7);
Hashtable values = new Hashtable();
values.Add("ProductID", list);
ICollection keys = _cache.Search(queryString, values);// Return Keys
IDictionary dict = _cache.SearchEntries(query, values);// Return Keys and values
 
 
Using LIKE Operator:
 
string queryString = "select NCacheQuerySample.Business.Product where this.ProductName LIKE ?;
ArrayList list = new ArrayList();
list.Add("Ch*"); //returns all entries that start with "Ch".
Hashtable values = new Hashtable();
values.Add("ProductName", list);
ICollection keys = _cache.Search(queryString, values);// Return Keys
IDictionary dict = _cache.SearchEntries(query, values);// Return Keys and values
 
Using GROUP BY Clause:
 
For using 'GROUP BY' clause you have to use any aggregate function in your query and ExecuteReader function to get query result.
 
  string queryString = "SELECT this.category, COUNT(Library.Product) WHERE this.productId > ? GROUP BY this.category";
  Hashtable values = new Hashtable();
  values.Add("productId", 5);
  ICacheReader reader= cache.ExecuteReader(queryString, values);
 
  while(reader.Read())
    {
      string category =(string) reader.GetValue(0);
      decimal countVal = (decimal)reader.GetValue(1);
      Console.WriteLine("Category : "+ category + "    Product Count: " + countVal);
    }
 
DELETE Statement:
For the delete statement you have used ExecuteNonQuery function.
 
  string queryString = "DELETE Library.Product WHERE this.productId > ?";
  Hashtable values = new Hashtable();
  values.Add("productId", 5);
  int result = cache.ExecuteNonQuery(queryString, values);
  Console.WriteLine("The number of rows affected: " + result);
 
 
 
 
The following operators are supported in NCache queries:
 
Category
Operator
<Query>
= , == , != , <> , < , > , <=, >=, IN, LIKE , NOT LIKE
Logical Operators
AND , OR , NOT
Aggregate Functions
 
Sum, Count, AVG, Min, Max
Miscellaneous
DateTime.Now , DateTime ("any date time compatible string")
 
 
 
 
See Also
 
© Copyright  2014 Alachisoft. All rights reserved.