Using a session to execute CQL statements

Once you have connected to a Cassandra cluster using a cluster object, you retrieve a session, which allows you to execute CQL statements to read and write data.

Prerequisites

This tutorial uses a CQL3 schema which is described in a post on the DataStax developer blog. Reading that post, could help with some of the new CQL3 concepts used here.

Getting metadata for the cluster is good, but you also want to be able to read and write data to the cluster. The C# driver lets you execute CQL statements using a session instance that you retrieve from the Cluster object. You will add code to your client for:

  • creating tables
  • inserting data into those tables
  • querying the tables
  • printing the results

Procedure

  1. Modify your SimpleClient class.
    1. Add an ISession read-only property.
      public ISession Session { get; private set; }
    2. Get a session from your cluster and store the reference to it.
      Add the following line to the end of the Connect method:
      Session = Cluster.Connect();

    You can execute statements by calling the Execute method on your session object. The session maintains multiple connections to the cluster nodes, provides policies to choose which node to use for each query (round-robin on all nodes of the cluster by default), and handles retries for failed queries when it makes sense.

    Session instances are thread-safe and usually a single instance is all you need per application. However, a given session can only be set to one keyspace at a time, so one instance per keyspace is necessary. Your application typically only needs a single cluster object, unless you're dealing with multiple physical clusters.

  2. Add an instance method, CreateSchema, to the SimpleClient class implementation.
    public void CreateSchema() { }
  3. Add the code to create a new schema.
    1. Execute a statement that creates a new keyspace.

      Add to the CreateSchema method:

      Session.Execute("CREATE KEYSPACE simplex WITH replication " + 
            "= {'class':'SimpleStrategy', 'replication_factor':3};");
      

      In this example, you create a new keyspace, simplex.

    2. Execute statements to create two new tables, songs and playlists.
      Add to the CreateSchema method:
      Session.Execute(
            "CREATE TABLE simplex.songs (" +
                  "id uuid PRIMARY KEY," + 
                  "title text," + 
                  "album text," + 
                  "artist text," + 
                  "tags set<text>," + 
                  "data blob" + 
                  ");");
      Session.Execute(
            "CREATE TABLE simplex.playlists (" +
                  "id uuid," +
                  "title text," +
                  "album text, " + 
                  "artist text," +
                  "song_id uuid," +
                  "PRIMARY KEY (id, title, album, artist)" +
                  ");");
      
  4. Add a virtual instance method, LoadData, to the SimpleCient class implementation.
    public virtual void LoadData() { }
    Declare the LoadData method to be virtual because it will be overriden in the BoundStatementClient later in this tutorial.
  5. Add the code to insert data into the new schema.
    Session.Execute(
          "INSERT INTO simplex.songs (id, title, album, artist, tags) " +
          "VALUES (" +
              "756716f7-2e54-4715-9f00-91dcbea6cf50," +
              "'La Petite Tonkinoise'," +
              "'Bye Bye Blackbird'," +
              "'Joséphine Baker'," +
              "{'jazz', '2013'})" +
              ";");
    Session.Execute(
          "INSERT INTO simplex.playlists (id, song_id, title, album, artist) " +
          "VALUES (" +
              "2cc9ccb7-6221-4ccb-8387-f22b6a1b354d," +
              "756716f7-2e54-4715-9f00-91dcbea6cf50," +
              "'La Petite Tonkinoise'," +
              "'Bye Bye Blackbird'," +
              "'Joséphine Baker'" +
              ");");
    
  6. In the CassandraApplication.Program class Main method, add calls to the CreateSchema and LoadData methods (after the Connect method).
    client.CreateSchema();
    client.LoadData();
    
  7. To the SimpleClient class), add an instance method, QuerySchema, that executes a SELECT statement on the tables and then prints out the results.
    1. Add a virtual instance method, QuerySchema, to the SimpleCient class implementation.
      public virtual void QuerySchema() { }
      Declare the QuerySchema method to be virtual because it will be overriden in the BoundStatementClient and AsynchronousClient classes later in this document.
    2. Add code to execute the query.
      Query the playlists table for one of the two records.
      RowSet results = _session.Execute("SELECT * FROM playlists " +
              "WHERE id = 2cc9ccb7-6221-4ccb-8387-f22b6a1b354d;");
      
      The Execute method returns a RowSet object that holds rows returned by the SELECT statement.
    3. Add code to iterate over the rows and print them out.
      Console.WriteLine(String.Format("{0, -30}\t{1, -20}\t{2, -20}\t{3, -30}",
          "title", "album", "artist", "tags"));
      Console.WriteLine("-------------------------------+-----------------------+--------------------+-------------------------------");
      foreach (Row row in results.GetRows())
      {
          Console.WriteLine(String.Format("{0, -30}\t{1, -20}\t{2, -20}\t{3}",
              row.GetValue<String>("title"), row.GetValue<String>("album"),
              row.GetValue<String>("artist"), row.GetValue<List<String>>("tags").ToString()));
      }
      
  8. Add a new instance method, DropSchema method, that drops a keyspace from the schema.
    public void DropSchema()
    {
        Session.Execute ( "DROP KEYSPACE " + keyspace );
        Console.WriteLine ( "Finished dropping " + keyspace + " keyspace." );
    }
    
  9. Add a new instance method, Close that closes the cluster and disposes of the session.
    public void Close()
    {
        Cluster.Shutdown();
        Session.Dispose();
    }
    
  10. To the CassandraApplication.Program class Main method, add calls to the new QuerySchema, DropSchema, and Close methods.
    client.QuerySchema();