{"id":765,"date":"2015-12-03T09:27:55","date_gmt":"2015-12-03T08:27:55","guid":{"rendered":"https:\/\/sii.pl\/blog\/?p=765"},"modified":"2023-07-20T14:17:51","modified_gmt":"2023-07-20T12:17:51","slug":"cassandra-with-c-fluent-query-builder","status":"publish","type":"post","link":"https:\/\/sii.pl\/blog\/en\/cassandra-with-c-fluent-query-builder\/","title":{"rendered":"Cassandra with C# Fluent Query Builder"},"content":{"rendered":"\n

Cassandra is a popular NoSql database technology which allows an easy configuration of many instances (nodes) providing high scalability and performance. To start work, visit planet cassandra<\/a>\u00a0to get the community edition or use directly UNIX command prompt following Installing Cassandra on Ubuntu<\/a>.
There is a driver that lets you work with .NET technology with C# syntax wery well, just download the nuget package using command prompt “Install-Package CassandraCSharpDriver” or use the GUI version:<\/p>\n\n\n\n

\"Download<\/a><\/figure>\n\n\n\n

<\/p>\n\n\n\n

DevCenter<\/a> is worth checking, this tool allows a user to create and run Cassandra Query Language (CQL<\/a>) against Apache Cassandra. It’s similiar to MS ManagementTools, but unfortunately the free version is very poor.<\/p>\n\n\n\n

In this article I am not going to focus on Cassandra database engine itself, but how to use it from .NET Framework, so it’s dedicated to .NET Developers.  I’ve chosen a fluent interface builder pattern<\/strong> to encapsulate the logic of a batch <\/a>statement (Insert, Update, Delete) that combines multiple data operations into one logical and atomic statement.<\/p>\n\n\n\n

Firstly, let’s set up a keyspace and create a session<\/p>\n\n\n

\nvar builder = Cluster\n.Builder()\n.AddContactPoint(\"127.0.0.1\")\n.WithQueryOptions(new QueryOptions().SetConsistencyLevel(ConsistencyLevel.One))\n.WithLoadBalancingPolicy(new RoundRobinPolicy())\n.WithDefaultKeyspace(\"Cassandra_Demo\");\n \nvar cluster = builder.Build();\n_session = cluster.ConnectAndCreateDefaultKeyspaceIfNotExists();\n<\/pre><\/div>\n\n\n
Now we’re ready to put together C# and Cassandra so, let’s write a method to create a simple User table:<\/p>\n\n\n
\npublic static void CreateUserTableIfNotExists(ISession session)\n{\nsession.Execute(\"CREATE TABLE IF NOT EXISTS User (\nid UUID PRIMARY KEY,\nfirstname varchar,\nlastname varchar,\ncountry varchar,\nisactive boolean);\"\n);\n}\n<\/pre><\/div>\n\n\n
To populate our table with some data, I am going to create a query using fluent api, the builder class can set up multiple parameters and takes the following form<\/p>\n\n\n
\npublic class GenerateUsersQueryBuilder : QueryBuilderBase\n{\nprivate int _limit;\nprivate string _country;\nBatchType _batchType = BatchType.Unlogged;\n\npublic GenerateUsersQueryBuilder SetLimit(int limit)\n{\n_limit = limit;\nreturn this;\n}\npublic GenerateUsersQueryBuilder SetBatchType(BatchType batchType)\n{\n_batchType = batchType;\nreturn this;\n}\n\npublic GenerateUsersQueryBuilder FromCountry(string country)\n{\n_country = country;\nreturn this;\n}\n\npublic override CassandraQuery Build()\n{\nvar sb = new StringBuilder();\nsb.AppendLine("BEGIN " + (_batchType == BatchType.Counter ?\n"COUNTER " : "UNLOGGED ") + "BATCH");\n\nfor (int i = 0; i < _limit; i++)\n{\nsb.AppendLine(string.Format(\n"INSERT INTO user (id, firstname, lastname, country, isactive) VALUES\n({0}, '{1}', '{2}', '{3}', {4})",\nGuid.NewGuid(),\nstring.Format("FirstName{0}", i + 1),\nstring.Format("LastName{0}", i + 1),\n_country,\ni % 2 == 0));\nsb.AppendLine(";");\n}\n\nsb.Append("APPLY BATCH");\n\nreturn new CassandraQuery() { Text = sb.ToString() };\n}\n}\n<\/pre><\/div>\n\n\n
Now we are able to create and populate the table with the following code<\/p>\n\n\n
\nQueryTools.CreateUserTableIfNotExists(_session);\n\nCassandraQuery query = new GenerateUsersQueryBuilder().FromCountry(\"Canada\")\n.SetLimit(_limit).SetBatchType(BatchType.Unlogged)\n.Build();\n\n_session.Execute(query.Text);\n<\/pre><\/div>\n\n\n
Of course later on, we want to fetch and display results in the output programmatically, so I created some helper method to map data from the rowset into domain model<\/p>\n\n\n
\nRowSet rsUsers = _session.Execute(string.Format("SELECT * FROM user LIMIT {0}", _limit));\n \nvar users = new List<UserModel>();\nforeach (var userRow in rsUsers)\n{\nusers.Add(ReflectionTools.GetSingleEntryDynamicFromReader<UserModel>(userRow));\n}\n \nforeach (UserModel user in users)\n{\nConsole.WriteLine("{0} {1} {2} {3} {4}",\nuser.Id, user.FirstName, user.LastName, user.Country, user.IsActive);\n}\n<\/pre><\/div>\n\n\n
As we see, the code works well<\/p>\n\n\n\n
\"fluent<\/a><\/figure>\n\n\n\n
Full source code from this article is avaliable here<\/a>.<\/p>\n\n\n
\n            \n
\n    \n
\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n    <\/div>\n    \n
\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n            
\n            \n\n
<\/div>\n        <\/div>\n    <\/div>\n<\/div>\n                \n\n
\n            5\/5 ( votes: 2)    <\/div>\n    <\/div>\n","protected":false},"excerpt":{"rendered":"
Cassandra is a popular NoSql database technology which allows an easy configuration of many instances (nodes) providing high scalability and … Continued<\/a><\/p>\n","protected":false},"author":3,"featured_media":1140,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_editorskit_title_hidden":false,"_editorskit_reading_time":0,"_editorskit_is_block_options_detached":false,"_editorskit_block_options_position":"{}","inline_featured_image":false,"footnotes":""},"categories":[1320],"tags":[1425,1488,1489,1490],"class_list":["post-765","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hard-development","tag-c-en","tag-cassandra-en","tag-cql-en","tag-datastax-en"],"acf":[],"aioseo_notices":[],"republish_history":[],"featured_media_url":"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2015\/12\/20_Cassandra_v2.jpg","category_names":["Hard development"],"_links":{"self":[{"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/posts\/765"}],"collection":[{"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/comments?post=765"}],"version-history":[{"count":2,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/posts\/765\/revisions"}],"predecessor-version":[{"id":23000,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/posts\/765\/revisions\/23000"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/media\/1140"}],"wp:attachment":[{"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/media?parent=765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/categories?post=765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sii.pl\/blog\/en\/wp-json\/wp\/v2\/tags?post=765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}