Prepare

#Using Parameters and Preparing Commands Cassandra allows queries to be executed using parameters. This can be done in two ways:

1. By preparing and executing a query with a parameters
2. (from Cassandra 2.0.0 onward) executing a non-prepared query with parameters

##Parameters in CqlSharp

There are a few notes on the use of parameters with CqlSharp:

• CqlSharp uses the positional parameter markers indicated by a question mark '?' or name ':name' as is custom in Cassandra.
• The order of the parameters added to the Parameters collection must match the order of the parameters defined in your query. This also means that the number of parameters in the collection must match the number of parameters in the query exactly.

The parameters values can be set in three ways:

1. By setting a value based on a parameter addressed by index
2. By setting a value based on a parameter addressed on name. The name should reflect the column value that is set. The name can be specified in the following formats: <keyspace>.<table>.<column>, or <table>.<column> or simply <column>
3. By setting multiple values based on an object. This is done via the Set methods. The class, field and property names are taken into account to map the parameters to the correct fields and/or properties. The mapping can be influenced using CqlTableAttribute, CqlUserTypeAttribute and CqlColumnAttribute annotations. See ObjectMapping for more information.

##Preparing a query and auto-generating parameters The following example shows how a query is prepared, parameters are set, and the query is executed.

CqlSharp will generate the parameter collection for you if you do not add parameters before preparation. This can be done as the Cassandra CQL binary protocol offers the necessary data to create them as part of the prepare result.

const string insertCql = @"insert into Test.MyTable (id,value) values (?,?);";

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

   var cmd = new CqlCommand(connection, insertCql, CqlConsistency.One);
   await cmd.PrepareAsync();

   cmd.Parameters[0].Value = 1;
   cmd.Parameters["value"].Value = "Hello World";
   
   await cmd.ExecuteNonQueryAsync();
}

To prepare a query invoke the PrepareAsync (or Prepare for the synchronous version) method. This will prepare the query at a node in your Cassandra cluster. Note that the preparation is cached, when a query is prepared multiple times, cached preparation results will be returned. Moreover, when connections fail, nodes restart, etc. CqlSharp will automatically re-prepare the provided query.

After preparation, the parameter collection can't be changed in structure, meaning that no parameters can be added, removed, or changed. Only values can be set or read.

##Preparing a query with custom parameters Instead of having CqlSharp generating the parameter collection, you may provide a parameter collection of your own. To do this, the parameter collection must be created prior to the invocation of Prepare(Async).

A reason to create parameters is that you may choose your own parameter names (which may map to better to an object).

const string insertCql = @"insert into Test.MyTable (id,value) values (?,?);";

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

   var cmd = new CqlCommand(connection, insertCql, CqlConsistency.One);
   cmd.Parameters.Add("myKey", CqlType.Int);
   cmd.Parameters.Add("myValue", CqlType.Text);
   await cmd.PrepareAsync();

   cmd.Parameters[0].Value = 1;
   cmd.Parameters["myValue"].Value = "Hello World";
   
   await cmd.ExecuteNonQueryAsync();
}

##Using named parameters Cassandra 2.0.1 and up supports named parameters. This allows you to pick the name of a column. This is especially useful in batch statements where the same column may occur multiple times.

const string insertCql = @"begin batch 
                            insert into Test.BasicFlow (id,value) values(:id1,:value1);
                            insert into Test.BasicFlow (id,value) values (:id2,:value2); 
                           apply batch;";
                           
using (var connection = new CqlConnection(ConnectionString))
{
   await connection.OpenAsync();

   var cmd = new CqlCommand(connection, insertCql, CqlConsistency.One);
   await cmd.PrepareAsync();

   cmd.Parameters["id1"].Value = 0;
   cmd.Parameters["value1"].Value = "Hello 0";
   cmd.Parameters["id2"].Value = 1;
   cmd.Parameters["value2"].Value = "Hello 1";
   await cmd.ExecuteNonQueryAsync();
}

Note that named parameters are still positional parameters in CqlSharp. This means that when a name is used multiple times, multiple parameters with the same name are generated. In that case, when addressing the parameters on the basis of the name, only one value will be set (typically the last occurrence). To access individual parameters with the same name, you must access the parameters by index (number).

##Executing a query with parameters Cassandra 2.0.0 and up supports queries with bound parameters without having them explicitly prepared. This makes it easy to submit binary fields.

const string insertCql = @"insert into Test.MyTable (id,value) values (?,?);";

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

   var cmd = new CqlCommand(connection, insertCql, CqlConsistency.One);
   cmd.Parameters.Add("id", 1);
   cmd.Parameters.Add("value", "Hello World");

   await cmd.ExecuteNonQueryAsync();
}

Note that in this example, parameters are added by name and value. The type of the parameters is derived from the type of the value. See TypeMapping on how this type derivation happens.