Basic CRUD operation on Cassandra NoSQL Database using FluentCassandra 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 FluentCassandra which is a .NET library for the Cassandra database.The zip file can be downloaded from here.After download, build the solution and we will find the "FluentCassandra.dll" inside the "<Drive>\fluentcassandra-master\fluentcassandra-master\src\bin\Debug" folder. Since we are using Windows environment,we will use DataStax for installation of Casandra Database.A nice article about the same is available here

Last time we have seen as how to access Cassandra NoSQL Database using Cassandraemon.This time we will look into the same stuff but using FluentCassandra.

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 reference to FluentCassandra.dll, System.Numerics.

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 environment 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)
{
	Server Server = new Server(host);

	using (var context = new CassandraContext(keyspace: keySpace, server: Server))
	{
		try
		{
			List<EmployeeEntity> lstEmployeeEntities = new List<EmployeeEntity>();
			var records = context.GetColumnFamily(columnFamily);

			//remove null rows
			foreach (dynamic record in records)
			{
				if (!string.IsNullOrEmpty(record.EmployeeName))
				{
					lstEmployeeEntities.Add(new EmployeeEntity
					{
						EmployeeId = record.EmployeeId
						,
						EmployeeName = record.EmployeeName
						,
						Age = record.Age
						,
						Salary = record.Salary
					});
				}
			}
			dgView.DataSource = lstEmployeeEntities;
		}
		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(keyspace: keySpace, server: Server))

where 

host = "127.0.0.1"
, keySpace = "EmployeeRecord"

This creates a Cassandra Context for the "EmployeeRecord" Keyspace on our local Cassandra database.

Next we are interested in picking up the ColumnFamily which is "Employee" in this case.

var records = context.GetColumnFamily(columnFamily);

Now we need to get those records that donot have any null values.Henceforth, we have written the below code snippet

foreach (dynamic record in records)
{
	if (!string.IsNullOrEmpty(record.EmployeeName))
	{
		lstEmployeeEntities.Add(new EmployeeEntity
		{
			EmployeeId = record.EmployeeId
			,
			EmployeeName = record.EmployeeName
			,
			Age = record.Age
			,
			Salary = record.Salary
		});
	}
}

It checks if the "EmployeeName" column has value or not and ignore those that donot have any value.It is needed while deleting a row and then re-populating the grid. And finally we bind it to the grid.

The second program we will focus on 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)
{
	Server Server = new Server(host);
	using (var context = new CassandraContext(keyspace: keySpace, server: Server))
	{
		try
		{
			var records = context.GetColumnFamily(columnFamily);
			dynamic empRecord = records.CreateRecord(Convert.ToInt32(txtId.Text));

			//add some values to the record
			empRecord.EmployeeId = Convert.ToInt32(txtId.Text);
			empRecord.EmployeeName = txtName.Text;
			empRecord.Age = Convert.ToInt32(txtAge.Text);
			empRecord.Salary = Convert.ToInt32(txtSalary.Text);

			context.Attach(empRecord);
			context.SaveChanges();
		}
		catch (Exception ex)
		{
			throw ex;
		}
	}
}

After setting the Cassandra context,we are invoking the "CreateRecord" method by specifying a particular key. Then inserting the new values to the system.Then by using the "Attach" method, we are adding the record to the context.Finally the "SaveChanges" method, made a permanent save in the Cassandra Database.

Cassandra works based on the key's value being passed. If the value in the "CreateRecord" is new, then an insert will happen else and update.The above example was the case of insertion.Let us see the update portion now.

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's value = 1 was already present into the system, cassandra simply performed an update on the record with the new value supplied.

N.B.~We can perform Upsert operation in another way as shown below

Server Server = new Server(host);
using (var context = new CassandraContext(keyspace: keySpace, server: Server))
{
	try
	{
		//Another way of performing insertion
		string query = "INSERT INTO " + columnFamily + " (EmployeeId,EmployeeName,Age,Salary) VALUES( ";
		query += txtId.Text + " , " + "'" + txtName.Text + "'" + " , " + txtAge.Text + " , " + txtSalary.Text + " )";
		context.ExecuteNonQuery(query);
	}
	catch (Exception ex)
	{
		throw ex;
	}
}

The last program will be the deletion

private void btnDelete_Click(object sender, EventArgs e)
{
	Server Server = new Server(host);
	 using (var context = new CassandraContext(keyspace: keySpace, server: Server))
	 {
		 try
		 {
			 var records = context.GetColumnFamily(columnFamily);                    
			 string query = "DELETE FROM " + columnFamily + " WHERE EmployeeId = " + txtId.Text;
			 context.ExecuteNonQuery(query);
		 }
		 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

References

1. Cassandra NoSQL Database: Getting Started
2. Cassandra NoSQL Database, Part 2: Programming
3. FluentCassandra Primer

Conclusion

Hope the basic CRUD operation on Cassandra NoSQL Database using FluentCassandra 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 FluentCassandra.Hope this will be helpful.Zipped file is attached.