Using bound statements

The previous tutorial used simple CQL statements to read and write data, but you can also use prepared statements, which only need to be parsed once by the cluster, and then bind values to the variables and execute the bound statement you read or write data to a cluster.

In the previous tutorial, you added a LoadData method which creates a new statement for each INSERT, but you may also use prepared statements and bind new values to the columns each time before execution. Doing this increases performance, especially for repeated queries. You add code to your client for:

  • creating a prepared statement
  • creating a bound statement from the prepared statement and binding values to its variables
  • executing the bound statement to insert data

Procedure

  1. Create a new class, BoundStatementsClient which extends SimpleClient, to the CassandraApplication solution.
  2. Add a using directive for Cassandra.
    using Cassandra;
  3. Add a new method, PrepareStatements, and implement it.
    1. Add two properties, InsertSongPreparedStatement and InsertPlaylistPreparedStatement, to hold references to the two prepared statements you will use in the LoadData method.
      private PreparedStatement InsertSongPreparedStatement;
      private PreparedStatement InsertPlaylistPreparedStatement;
      
    2. In the PrepareStatements method body add the following code:
      InsertSongPreparedStatement = Session.Prepare(
          "INSERT INTO simplex.songs " +
          "(id, title, album, artist, tags) " +
          "VALUES (?, ?, ?, ?, ?);");
      InsertPlaylistPreparedStatement = Session.Prepare(
          "INSERT INTO simplex.playlists " +
          "(id, song_id, title, album, artist) " +
          "VALUES (?, ?, ?, ?, ?);");
      
      Note: You only need to prepare a statement once per session.
  4. Add a new method, LoadData, and implement it.
    public override void LoadData() { }
  5. Add a collection to hold the tags which will be bound to the prepared statement.
    HashSet<String> tags = new HashSet<String>();
    tags.Add("jazz");
    tags.Add("2013");
    
  6. Add code to bind values to the prepared statement's variables and execute it.
    You create a bound statement by calling its constructor and passing in the prepared statement. Use the Bind method to bind values and execute the bound statement on the your session..
    BoundStatement boundStatement = InsertSongPreparedStatement.Bind(
       new Guid("756716f7-2e54-4715-9f00-91dcbea6cf50"),
       "La Petite Tonkinoise'",
       "Bye Bye Blackbird'",
       "Joséphine Baker",
       tags);
    Session.Execute(boundStatement);
    
  7. Add code to create a new bound statement for inserting data into the simplex.playlists table.
    public override void LoadData()
    {
       HashSet<String> tags = new HashSet<String>();
       tags.Add("jazz");
       tags.Add("2013");
       BoundStatement boundStatement = InsertSongPreparedStatement.Bind(
          new Guid("756716f7-2e54-4715-9f00-91dcbea6cf50"),
          "La Petite Tonkinoise'",
          "Bye Bye Blackbird'",
          "Joséphine Baker",
          tags);
       Session.Execute(boundStatement);
       boundStatement = InsertPlaylistPreparedStatement.Bind(
          new Guid("2cc9ccb7-6221-4ccb-8387-f22b6a1b354d"),
          new Guid("756716f7-2e54-4715-9f00-91dcbea6cf50"),
          "La Petite Tonkinoise",
          "Bye Bye Blackbird",
          "Joséphine Baker");
       Session.Execute(boundStatement);
    }
    
  8. In the Main method of the CassandraApplication.Program class, replace the line which instantiates a client from the SimpleClient class to the BoundStatementsClient and add a call to the PrepareStatements method after you have created the schema.
    static void Main(String[] args)
    {
       BoundStatementsClient client = new BoundStatementsClient();
       client.Connect("127.0.0.1");
       client.CreateSchema();
       client.PrepareStatements();
       client.LoadData();
       client.QuerySchema();
       Console.ReadKey();
       client.DropSchema("simplex");
       client.Close();
       client.Close();
    }