NonQuery

#Executing a query

CqlSharp provides an ADO.NET interface. This implies that the code you write to execute queries basically follows the following steps:

1. Create a CQLConnection
2. Create a Command
3. (Optionally) set a few options (like UseBuffering, PartitionKey)
4. (Optionally) prepare command, and set parameters
5. Execute the command
6. (Optionally) Read results from the CqlDataReader, and dispose the reader
7. Dispose the CqlConnection

This perhaps best explained using a few examples:

##Executing a query directly, synchronously

The following example will create a test keyspace.

const string createKsCql = @"CREATE KEYSPACE Test WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1}";

using (var connection = new CqlConnection("node=localhost"))
{
   connection.Open();

   try
   {
        var command = new CqlCommand(connection, createKsCql);
        command.ExecuteNonQuery();
   }
   catch (AlreadyExistsException)
   {
        //handle your exception here
   }
}

First a CqlConnection is created to a cluster residing on the local host. This uses the connection string format constructor the CqlConnection.

Then "Open" is invoked. This will tell the connection to obtain an active link to your Cassandra cluster. The connection will be responsible for obtaining network connections to nodes in the cluster. When the Exclusive Connection Strategy is chosen, these network connections are exclusively reserved for this CqlConnection. For all others they are shared amongst CqlCommand and CqlConnection instances. In most cases, if the network connection fails, it will be automatically replaced with another connection (to potentially another node in your cluster).

After open, the command is created, linking it to the connection. Execution of non-queries is performed through the ExecuteNonQuery method.

Finally, the CqlConnection is to be disposed. This is to make sure the underlying connection is released.

##Executing non-query directly, asynchronously

Asynchronous invocation of the CqlSharp API is the recommended way of using it. The previous example can be easily re-written with the delightful async/await keywords of .NET 4.5.

const string createKsCql = @"CREATE KEYSPACE Test WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1} and durable_writes = 'false';";

using (var connection = new CqlConnection("node=localhost"))
{
   await connection.OpenAsync();

   try
   {
        var command = new CqlCommand(connection, createKsCql);
        await command.ExecuteNonQueryAsync();
   }
   catch (AlreadyExistsException)
   {
        //handle your exception here
   }
}

Though little differences can be observed, the result may be dramatic in terms of thread behavior. In this version no thread is actively blocked while the query is performed. All this time, the thread may execute other operations (like creating and executing another query). As such many more queries can be executed using the threads available.

Query results

As CqlSharp implements the ADO.NET interfaces, the ExecuteNonQuery and ExecuteNonQueryAsync method invocations will result in a not so meaningful numbers (1 for successful insert, update or delete, -1 for all others). To obtain more specific Cassandra information, you may check the LastQueryResult property of CqlCommand. This property (depending on the query) may result in a

• CqlVoid (no data to return, typically inserts or updates)
• CqlSetKeySpace (result of a 'use' query)
• CqlSchemaChange (result of create/alter/drop queries)
• CqlDataReader (result of an insert/update with an 'if exists' clause (C* >= 2.0.0), where the reader contains information whether the insert was applied, and the final value in the database).
• CqlError (containing the error thrown as a result of the last query (e.g. when a query times out on the server, or when the query could not be parsed).

Cassandra Specific options

CqlSharp implements the basic ADO.Net functionality, but adds a few Cassandra specific options.

UseCASLocalSerial

When using Compare-And-Set (CAS) clauses in your query, like `IF EXISTS`, Cassandra will perform some consistency checks to make sure that a valid comparison is done before a value is set. This check involves querying involved nodes for their state. These nodes may reside in different data-centers. By setting the `UseCASLocalSerial` property to true, only the nodes in the queried datacenter are checked.

EnableTracing

Cassandra can store a trace of all activities performed when a query is executed. When `EnableTracing` is set to true, the value of the `LastQueryResult` property will contain a `TracingId` value. This TracingId is a GUID generated by Cassandra that uniquely identifies the trace generated. This trace can be queried using the helper classes in the `CqlSharp.Tracing` namespace. Tracing has a performance penalty and is disabled by default.

Load

CqlSharp is using load-balancing connection strategies. These strategies define on which connection a query is executed, and when new connections need to be opened, and when connections are to be closed. The load property of a CqlCommand can be used to indicate how 'heavy' a specifc query is. This is input for the balancing strategies, as they will favor connections with less load for new queries. The default load value is 1. This means that queries will be evenly distributed (in count) over the different connections.

IsPrepared

When a query is prepared, this property will return true.

Consistency

Each query in Cassandra can be executed with a different [Consistency level](http://www.datastax.com/documentation/cassandra/2.0/cassandra/dml/dml_config_consistency_c.html). The `Consistency` property can be used to read or set this value. Possible values are: Any, One, Two, Three, Quorum, All, LocalQuorum, EachQuorum, and LocalOne.

PartitionKey

Cassandra distributes data based on hashes row key values. These values are called partition keys. By providing a CqlCommand with the values that make up a partitionkey, CqlSharp will be able to route the query to a node that will be responsible to store the related data. By using partitionkeys, internal Cassandra communication may be avoiding increasing overall performance.

TimeStamp

Cassandra consistency checks and event ordering are based on timestamps. Normally Cassandra will add the server time to all data (model) changes. The TimeStamp property allows clients to provide a timestamp to be used instead of the server time. Note that this feature requires binary protocol v3 or up and is therefore only available on Cassandra version 2.1 and up.