Basic CRUD operation on Cassandra NoSQL Database using Cassandraemon and C#

Introduction

Cassandra is an open source scalable and highly available "NoSQL" distributed database management system from Apache.It comes under the Column-Family NoSQL category. In this article we will look into as how we can do a basic CRUD operation using Cassandraemon which is a LINQ Provider for Apache Cassandra.It can be downloaded from here. Since we are using Windows environment,we will use DataStax for installation of Casandra Database.A nice article about the same is available here

Let us create the environment

Let us fire up Visual Studio windows application and create a form as under

Next let us create a "EmployeeEntity.cs" file and add the below code

public class EmployeeEntity
{
	public int EmpId { get; set; }
	public string EmpName { get; set; }
	public int Age { get; set; }
	public int Salary { get; set; }
}

Add the references to Apache.Cassandra.dll,Cassandraemon.dll,Thrift.dll.

Now let us first create database in Cassandra.So open the Casandra Console ("Cassandra CQL Shell") as under

Once the console comes up, let us create a keyspace in Cassandra

CREATE KEYSPACE EmployeeRecord 
WITH strategy_class = 'SimpleStrategy'
AND strategy_options:replication_factor = 1;

Next we need to create the column-family.For creating a column family within the "EmployeeRecord" keyspace, first type "use <keyspace>" (which is "EmployeeRecord" here) as under

 
Use EmployeeRecord;

The next step is to create the column family,issue the below command

CREATE COLUMNFAMILY Employee 
(
        EmployeeId int PRIMARY KEY,
        EmployeeName text,       
        Age int,
        Salary int
);

Just for our testing purpose, we can insert a row and display it as shown under

insert into Employee(EmployeeId,EmployeeName,Age,Salary) 
values(1,'Test Employee',20,5000);

select * from Employee;

Let us now write the program

Now since our environemnt is ready, we are good to start writing our program.But before that, let us learn some key terms of Cassandra.It will be easy for us if we can relate those to relational model as describe under

Some other concepts includes

The first program we will write is the "Select Record". Let us first have a look at the code first

 private void btnSelect_Click(object sender, EventArgs e)
{
	using (var context = new CassandraContext(host, port, keySpace))
	{
		try
		{ 
			var records = (from x in context.ColumnList
						   where x.ColumnFamily == columnFamily
						  select x.ToObject<EmployeeEntity>());
			
			 //remove null rows
			var filteredRecords = records.ToList().Where(i => i != null);

			dgView.DataSource = filteredRecords.ToList();
		}
		catch (Exception ex)
		{
			throw ex;
		}
	}
}

First of all, we need to create a context for the database entities that we are going to save. This is done with the CassandraContext.

using (var context = new CassandraContext(host, port, keySpace))

where 

host = "127.0.0.1"
, port = 9160
, keySpace = "EmployeeRecord"

This creates a Cassandra Context for the "EmployeeRecord" Keyspace on our local Cassandra database.The default port on which Cassandra server runs is 9160.

Next we are interested in picking up the ColumnFamily which is "Employee" in this case.The ToObject<T>basically converts to the "EmployeeEntity" concreate type.

var filteredRecords = records.ToList().Where(i => i != null);

The above line will help us to get only those rows that has values.It is needed while deleting a row and then re-populating the grid. Else some null/blank rows will appear.And finally we bind it to the grid.

The second program will speak about the "Upsert Record". It means we can do both Insert as well as Update.Let us first have a look at the code first

private void btnUpsert_Click(object sender, EventArgs e)
{
	var empRecord = new EmployeeEntity()
	{
		EmployeeId = Convert.ToInt32(txtId.Text)
		 ,
		EmployeeName = txtName.Text
		 ,
		Age = Convert.ToInt32(txtAge.Text)
		 ,
		Salary = Convert.ToInt32(txtSalary.Text)
	};

	using (var ctx = new CassandraContext(host, port, keySpace))
	{
		try
		{
			var employeeRecord = new CassandraEntity<List<Column>>()
			.SetColumnFamily(columnFamily)
			.SetKey(Convert.ToInt32(txtId.Text))
			.SetData(empRecord);

			ctx.ColumnList.InsertOnSubmit(employeeRecord);
			ctx.SubmitChanges();
		}
		catch (Exception ex)
		{
			throw ex;
		}
	}
}

The code is pretty self explanatory.But one key point is

SetKey(Convert.ToInt32(txtId.Text))

Cassandra works based on the key's value being passed. If the value in the "SetKey" is new, then an insert will happen else and update.Let us see our statement.

We will insert another record to our system

We inserted a new record whose Id = 2.Since, it is a new Key value henceforth, an insert happened.Now we will modify the record, whose Key = 1 as under.

As can be seen that, since the Key value = 1 was already present into the system, Cassandra simply performed an update on the record with the new value supplied.

The last program will be the deletion

 private void btnDelete_Click(object sender, EventArgs e)
{
	using (var ctx = new CassandraContext(host, port, keySpace))
	{
		try
		{
			ctx.Column.DeleteOnSubmit(x => x.ColumnFamily == columnFamily && x.Key == Convert.ToInt32(txtId.Text));
			ctx.SubmitChanges();
		}
		catch (Exception ex)
		{
			throw ex;
		}
	}
}

Again it is a straight forward piece of code.We are deleting the record based on the key for the particular Column-Family

Record with Id = 1 has been deleted

The complete code is presented here

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using Apache.Cassandra;
using Cassandraemon;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private string host = "127.0.0.1";
        private int port = 9160;
        private string keySpace = "EmployeeRecord";
        private string columnFamily = "Employee";
        public Form1()
        {
            InitializeComponent();
        }        

        private void btnSelect_Click(object sender, EventArgs e)
        {
            using (var context = new CassandraContext(host, port, keySpace))
            {
                try
                { 
                    var records = (from x in context.ColumnList
                                   where x.ColumnFamily == columnFamily
                                  select x.ToObject<EmployeeEntity>());

                    //remove null rows
                    var filteredRecords = records.ToList().Where(i => i != null);

                    dgView.DataSource = filteredRecords.ToList();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        private void btnUpsert_Click(object sender, EventArgs e)
        {
            var empRecord = new EmployeeEntity()
            {
                EmployeeId = Convert.ToInt32(txtId.Text)
                 ,
                EmployeeName = txtName.Text
                 ,
                Age = Convert.ToInt32(txtAge.Text)
                 ,
                Salary = Convert.ToInt32(txtSalary.Text)
            };

            using (var ctx = new CassandraContext(host, port, keySpace))
            {
                try
                {
                    var employeeRecord = new CassandraEntity<List<Column>>()
                    .SetColumnFamily(columnFamily)
                    .SetKey(Convert.ToInt32(txtId.Text))
                    .SetData(empRecord);

                    ctx.ColumnList.InsertOnSubmit(employeeRecord);
                    ctx.SubmitChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            using (var ctx = new CassandraContext(host, port, keySpace))
            {
                try
                {
                    ctx.Column.DeleteOnSubmit(x => x.ColumnFamily == columnFamily && x.Key == Convert.ToInt32(txtId.Text));
                    ctx.SubmitChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }
}

References

1. Using Apache Cassandra with .NET - Part 1
2. Using Apache Cassandra with .NET - Part 2
3. Getting started using Apache Cassandra

Conclusion

Hope the basic CRUD operation on Cassandra NoSQL Database using Cassandraemon and C# tutorial provided some insight as how to work in Cassandra.It is a vast topic and this article was meant just to cover as how we can play with Cassandra using Cassandra demon.Hope this will be helpful.In the next tutorial we will explore another Cassandra client library i.e. Fluent Cassandra.Zipped file is attached.