Delete Data in Cache with SQL and Tags

Similar to get, object queries can also be used to remove items from cache with your query criteria being tags. Object queries let you search the data in your cache and then delete it.

The idea of a special keyword $Tag$ also exists here. ExecuteNonQuery is used in queries containing delete operations. It returns the number of affected rows after query is executed.

Therefore, the three strategies of removing items on tags (i.e. by a specific tag, any tag or all tags) can also be adopted for these queries.

Pre-Requisites for Using SQL Query with Tags

• Include the following namespace in your application:
  • Alachisoft.NCache.Web.Caching
  • Alachisoft.NCache.Runtime
• The application must be connected to cache before performing the operation.
• Cache must be running.
• Make sure that the data being added is serializable.
• To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

SQL DELETE for One Tag

Using object queries, items can be deleted using a single tag.

Following example deletes all the items associated with the tag Beverages using the SQL query.

Warning

Providing Null tag value for the query will throw an ArgumentNullException.

Note

Use fully-qualified name of the class Customer e.g. Data.Customer.

try
{
    // Following tags are created and item is added in the cache with these tags
    Tag[] tags = new Tag[2];
    tags[0] = new Tag("Important Customers");
    tags[1] = new Tag("East Coast Customers");

    // A new hashtable is created for adding the tag list for query searching values
    Hashtable queryValue = new Hashtable();

    // Removing the items associated with the tag
    // Make sure to use the fully qualified class name
    string query = "DELETE Data.Customer WHERE this.$Tag$ = ?";

    // Passing the value 'Important Customers' to the tagValue
    string tagValue = "Important Customers";

    // Add tag value to Hashtable for query searching values.
    queryValue.Add("$Tag$", tagValue);
    int rowsAffected = cache.ExecuteNonQuery(query, queryValue);
    // 'rowsAffected' number of items were removed from cache
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation performed during state transfer
    // Operation Timeout
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

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

SQL DELETE for ANY Tag

Using object queries, if multiple tags are provided in the form of a list, items associated with ANY of the tags from the list are removed.

Following example removes all the items associated with any of the tags from the list using the SQL query.

Warning

Providing Null tag value for the query will throw an ArgumentNullException.

Note

Use fully-qualified name of the class Customer e.g. Data.Customer.

try
{
    // Following tags are created and item is added in the cache with these tags
    Tag[] tags = new Tag[2];
    tags[0] = new Tag("Important Customers");
    tags[1] = new Tag("East Coast Customers");

    // A new hashtable is created for adding the tag list for query searching values
    Hashtable queryValue = new Hashtable();

    // Removing the items associated with the tag 'East CoastCustomers' 
    // OR 'Important Customers'
    // Make sure to use the fully qualified class name
    string query = "DELETE Data.Customer WHERE this.$Tag$ = ? OR this.$Tag$ = ?";

    // If multiple tags are used for query criteria, add tags in an array list
    ArrayList queryTagList = new ArrayList();

    // Passing the values to the tags in the list
    queryTagList.Add("Important Customers");
    queryTagList.Add("East Coast Customers");

    // Add tag list to Hashtable for query searching values.
    queryValue.Add("$Tag$", queryTagList);
    // ExecuteNonQuery processes the query and returns the number of affected rows
    int rowsAffected = cache.ExecuteNonQuery(query, queryValue);
    // 'rowsAffected' number of items were removed from cache
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation performed during state transfer
    // Operation Timeout
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

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

SQL DELETE for ALL Tags

Using object queries, if multiple tags are provided in the form of a list, items associated with ALL of the tags from the list are removed.

Following example removes all the items associated with any of the tags from the list using the SQL query.

Warning

Providing Null tag value for the query will throw an ArgumentNullException.

Note

Use fully-qualified name of the class Customer e.g. Data.Customer.

try
{
    // Following tags are created and item is added in the cache with these tags
    Tag[] tags = new Tag[2];
    tags[0] = new Tag("Important Customers");
    tags[1] = new Tag("East Coast Customers");

    // A new hashtable is created for adding the tag list for query searching values
    Hashtable queryValue = new Hashtable();

    // Removing the items associated with the tag 'Important Customers' 
    // AND 'East Coast Customers'
    // Make sure to use the fully qualified class name
    string query = "DELETE Data.Customer WHERE this.$Tag$ = ? AND this.$Tag$ = ?";

    // If multiple tags are used for query criteria, add tags in an array list
    ArrayList queryTagList = new ArrayList();

    // Passing the values to the tags in the list
    queryTagList.Add("Important Customers");
    queryTagList.Add("East Coast Customers");

    // Add tag list to Hashtable for query searching values.
    queryValue.Add("$Tag$", queryTagList);
    // ExecuteNonQuery processes the query and returns the number of affected rows
    int rowsAffected = cache.ExecuteNonQuery(query, queryValue);
    // 'rowsAffected' number of items were removed from cache
}
catch (OperationFailedException ex)
{
    // Exception can occur due to:
    // Connection Failures
    // Operation performed during state transfer
    // Operation Timeout
}
catch (Exception ex)
{
    // Any other generic exception like ArgumentNullException or ArgumentException
}

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

In order to get more detail on object queries please refer to the SQL Reference for NCache section.

See Also

Using Groups
Add/Update Data in Cache with Tags
Retrieve Data from Cache with Tags
Remove Data from Cache with Tags
Search Data in Cache with SQL and Tags
Named Tags