Querying Samples for Operators

Using Equal (=) Operator

string query = "SELECT Product where this.ProductID = ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 1001);
try
{    
  ICacheReader reader = cache.ExecuteReader(query, values);    
  if (reader.FieldCount > 0){      
    while (reader.Read()){            
       Product result = (Product)reader.GetValue(1); //perform operations        
    }    
  }    
  else{       
        //no record exists    
  }    
    reader.Close();
  }
catch(Exception ex)
{
    // handle exception
}

Using Multiple Operators

string query = "SELECT Product where this.ProductID < ? AND this.Category = ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 1002);
values.Add("Category", 4);
try
{    
      ICacheReader reader = cache.ExecuteReader(query, values);    
      if (reader.FieldCount > 0)
      {
        while (reader.Read()){    
          Product result = (Product)reader.GetValue(1);    //perform operations
        }
      }    
      else
      {
        //no record exists
      }    
      reader.Close();
}
catch (Exception ex)
{
    //handle exception
} 

Using IN Operator

 string query = "SELECT Product where this.ProductID IN (?,?,?)";
ArrayList idList = new ArrayList();
idList.Add(1001);
idList.Add(100);
idList.Add(500);Hashtable values = new Hashtable();
values.Add("ProductID", idList);
try
{
    ICacheReader reader = cache.ExecuteReader(query, values);
    if (reader.FieldCount > 0)
    {
      while (reader.Read())
      {            
        Product result = (Product)reader.GetValue(1);    //perform operations
      }
    }
    else
    {
      //no record exists
    }    
    reader.Close();}
catch (Exception ex)
{
    // handle exception
}

Using LIKE Operator

string query = "SELECT Product where this.ProductName LIKE ?";
ArrayList list = new ArrayList();
list.Add("Ch*");

Hashtable values = new Hashtable();
values.Add("ProductName", list);
try
{
    ICacheReader reader = cache.ExecuteReader(query, values);
    if (reader.FieldCount > 0)
    {
      while (reader.Read())
      {            
        Product result = (Product)reader.GetValue(1);    //perform operations
      }
    }
    else
    {
      //no record exists
    }    
    reader.Close();
}
catch (Exception ex){
    // handle exception
} 

Using GROUP BY Clause

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

string query = "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(query, values);
    if (reader.FieldCount > 0)
    {
      while (reader.Read())
      {    
        object category = reader.GetOrdinal("Category");    object result = reader.GetValue(1);    //perform operations        
      }
    }    
    else
    {
      //no record exists
    }    
    reader.Close();
}
catch(Exception ex){
    // handle exception
}

Using ORDER BY Clause

string query = "SELECT Product ORDER BY by this.ProductID = ?";

//OR

string query = "SELECT Product WHERE this.Category = ? ORDER BY this.Category";

Hashtable values = new Hashtable();
values.Add("ProductID", 1001);

try
{
    ICacheReader reader = cache.ExecuteReader(query, values);
    if (reader.FieldCount > 0)
    {
       while (reader.Read())
       {     Product result = (Product)reader.GetValue(1);
         //perform operations
      }
    }
   else
   {
        //no record exists
   }
   reader.Close();
}
catch (OperationFailedException ex)
{
  // handle exception
}

Using DELETE Statement

The Delete statement can only be executed through ExecuteNonQuery:

string query = "DELETE Product WHERE this.ProductID > ?";
Hashtable values = new Hashtable();
values.Add("ProductID", 5);
try
{
    int result = cache.ExecuteNonQuery(query, values);
    //return number of affected rows.
}
catch(Exception ex)
{
    // handle exception
}