Delete Data with ExecuteNonQuery
ExecuteNonQuery is an API in NCache that uses a SQL-like DELETE statement to remove data on the basis of specified criteria (indexed attributes).
ExecuteNonQuery efficiently deletes all items that meet a query clause (e.g., DELETE FROM Product WHERE UnitsInStock = 0) and returns the number of impacted rows, in contrast to the conventional Remove function, which requires specified keys.
Important
The Delete statement can only be executed through ExecuteNonQuery.
Prerequisites
Before using the NCache client-side APIs, ensure that the following prerequisites are fulfilled:
- Add the following Maven dependencies for your Java client application in
pom.xml file:
<dependency>
<groupId>com.alachisoft.ncache</groupId>
<!--for NCache Enterprise-->
<artifactId>ncache-client</artifactId>
<version>x.x.x</version>
</dependency>
- Create a new Console Application.
- Install either of the following NuGet packages in your .NET client application:
- Enterprise:
Install-Package Alachisoft.NCache.SDK -Version 4.9.1.0
- Make sure that the data being added is serializable.
- Add NCache References by locating
%NCHOME%\NCache\bin\assembly\4.0 and adding Alachisoft.NCache.Web and Alachisoft.NCache.Runtime as appropriate.
- Include the
Alachisoft.NCache.Web.Caching namespace in your application.
- To learn more about the NCache Legacy API, please download the NCache 4.9 documents available as a .zip file on the Alachisoft Website.
The following example deletes all products from the cache where UnitsInStock is equal to 0, using ExecuteNonQuery:
try
{
// Precondition: Cache is already connected
// Items are already present in the cache
// Provide Fully Qualified Name (FQN) of your custom class
string query = "DELETE FROM FQN.Product WHERE UnitsInStock = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("UnitsInStock", 0);
// Execute query
_cache.SearchService.ExecuteNonQuery(queryCommand);
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation performed during state transfer
// Operation Timeout
}
}
catch (Exception ex)
{
// Any generic exception like ArgumentException, ArgumentNullException
}
try
{
// Precondition: Cache is already connected
// Items are already present in the cache
// Provide Fully Qualified Name (FQN) of your custom class
String query = "DELETE FROM FQN.Product WHERE UnitsInStock = ?";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("UnitsInStock", 0);
// Execute query
int affectedRows = cache.getSearchService().executeNonQuery(queryCommand);
}
catch (OperationFailedException ex)
{
if (ex.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure the query format is correct
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation performed during state transfer
// Operation Timeout
}
}
catch (Exception ex)
{
// Any generic exception like IllegalArgumentException or NullPointerException
}
try:
# Precondition: Cache is already connected
# Items are already present in the cache
# Provide Fully Qualified Name(FQN) of your custom class
query = "DELETE FROM FQN.Product WHERE units_in_stock = ?"
# Use QueryCommand for query execution
query_command = QueryCommand(query)
query_command.set_parameters({"units_in_stock": 0})
# Execute query
rows_affected = cache.get_search_service().execute_non_query(query_command)
except ncache.OperationFailedException as ex:
# NCache specific exception can occur due to:
# - Connection failures
# - Operation performed during state transfer
# - Operation timeout
pass
except Exception as ex:
# Any generic exception
pass
try
{
// This is an async method
// Precondition: Cache is already connected
// Items are already present in the cache
// Provide Fully Qualified Name (FQN) of your custom class
var query = "DELETE FROM FQN.Product WHERE UnitsInStock = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Providing parameters for query
let parameter = new Map();
parameters.set("UnitsInStock", 0);
queryCommand.setParameters(parameters);
// Execute query
const searchService = await cache.getSearchService();
let rowsAffected = await searchService.executeNonQuery(queryCommand);
}
catch (error)
{
// Handle errors
}
try
{
// Using NCache Enterprise 4.9.1
// Precondition: Cache is already connected
// Items are already present in the cache
// Provide Fully Qualified Name (FQN) of your custom class
string query = "DELETE Alachisoft.NCache.Sample.Data.Product WHERE this.UnitsInStock = ?";
// Set parameters
Hashtable parameters = new Hashtable();
parameters.Add("UnitsInStock", 0);
// Execute query
int affectedRows = cache.ExecuteNonQuery(query, parameters);
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation performed during state transfer
// Operation Timeout
}
}
catch (Exception ex)
{
// Any generic exception like ArgumentException, ArgumentNullException
}
Note
To ensure the operation is fail-safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
Additional Resources
NCache provides a sample application for SQL Queries on GitHub.
See Also
.NET: Alachisoft.NCache.Client.Services namespace.
Java: com.alachisoft.ncache.runtime.caching namespace.
Python: ncache.client.services class.
Node.js: Cache class.
