Click here to Skip to main content
16,021,294 members
Articles / Web Development / ASP.NET

Cassandra on Windows in Angular MVC API Based Application. Part II

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
25 Feb 2016CPOL3 min read 14K   3   2
Some thoughts about one page angular site based on ASP.NET Web API 2 and Cassandra NOSQL database

Introduction

In the first part of the article, we installed DataStax DevCenter, created keyspace and column families, populated the database with some data and coded a simple application. Now, we will create user defined types and tuples and will get down to the brass tacks of our article - web application based Cassandra.

Background

If our application requires multiple tables, we can simplify our life by declaring user defined types (udf). UDF represents the related fields of information instead of storing the information in a separate table. We can define a group of properties as a type and access them separately or as a single entity. By mapping UDF to .NET type, we can deal with it like with .NET object after that in our application.

A tuple is a fixed-length set of typed positional fields without labels. Tuple is a key-value pair so we can think about tuple as if it were a Dictionary object.

Using the Code

Now, we will create type phone that consists of number as text and tags as set of text. Set is like IEnumerable in .NET.

Here is the sample of creating user defined type:

SQL
CREATE TYPE phone (
    number text,
    tags set<text>
);

Run it in DataStax DevCenter.

In its turn, we can use phone type as a part of another user defined type. Let's create address type.

SQL
CREATE TYPE IF NOT EXISTS address (
    street text,
    city text,
    zip int,
    phones set<frozen<phone>>,
    location frozen<tuple<float,float>>
);

Here, address is UDT with simple properties as street, city, zip as well as with set of UDT phones and tuple of two floats. Don't forget to change our column family to accept these types:

SQL
ALTER TABLE users ADD address frozen<address>;

We can see how DataStax proposes to insert data to table with user defined types in file videodb-udts-tuples.

SQL
INSERT INTO users (username, firstname, lastname, email, password, created_date, address)
VALUES (
    'tsmith',
    'Tom',
    'Smith',
    ['tsmith@gmail.com', 'tom.smith@gmail.com'],
    '5f4dcc3b5aa765d61d8327deb882cf99',
    '2014-02-28 08:00:00',
    {
        street: '202 4th St',
        city: 'San Francisco',
        zip: 94103,
        phones: {
            { number: '212 221 9165', tags: { 'preferred', 'direct line' } },
            { number: '500 310 2342', tags: { 'fax' } }
        },
        location: (37.783205,-122.4026532)
    }
);

Now, after our database is all set, let's come back to Visual Studio and write some code.

Create a new ASP.NET project and select empty templates and specify that you want folders and reference for MVC.

Image 1

We will use angular in our project so we don't need MVC controllers and views. But we will need angular files and folders.

Create folder app in the project tree and add three JavaScript files there - app.js, controllers.js and services.js. Add index.html file to project root and open it. We'll need to add references to some styles and JavaScripts:

HTML
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css">

 <script src="https://code.jquery.com/jquery-2.2.0.min.js"></script>
 <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.0-rc.2/angular.min.js "></script>
 <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.4.9/angular-route.min.js"></script>
 <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.5.0-rc.2/angular-animate.js"></script>
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 <script src="//angular-ui.github.io/bootstrap/ui-bootstrap-tpls-1.1.2.js"></script>

as well as to our local styles and scripts:

HTML
<link rel="stylesheet" href="css/style.css">

<script src="app/app.js"></script>
<script src="app/controllers.js"></script>
<script src="app/services.js"></script>

Our project will be bootstrap based and HTML will look like this:

HTML
<!DOCTYPE html>
<html ng-app="movie">
<head>
    <title></title>
    <meta charset="utf-8" />
    <link href='https://fonts.googleapis.com/css?family=Titillium+Web' 
    rel='stylesheet' type='text/css'>
    <link rel="stylesheet" 
    href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
    <link rel="stylesheet" 
    href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css">
    <link rel="stylesheet" 
    href="css/style.css">
    <link rel="stylesheet" href="css/bootstrapUnited.min.css">

    <script src="https://code.jquery.com/jquery-2.2.0.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.0-rc.2/angular.min.js "></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.4.9/angular-route.min.js"></script>
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.5.0-rc.2/angular-animate.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    <script src="//angular-ui.github.io/bootstrap/ui-bootstrap-tpls-1.1.2.js"></script>
    <script src="https://www.youtube.com/iframe_api"></script>
    <script src="scripts/youtube.js"></script>
    <script src="app/app.js"></script>
    <script src="app/controllers.js"></script>
    <script src="app/services.js"></script>
</head>
<body>
 
        <nav class="navbar navbar-default navbar-fixed-top">
            <div class="container">
                <!-- Brand and toggle get grouped for better mobile display -->
                <div class="navbar-header">
                    <button type="button" class="navbar-toggle collapsed" 
                    data-toggle="collapse" 
                    data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
                        <span class="sr-only">Toggle navigation</span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                        <span class="icon-bar"></span>
                    </button>
                    <a class="navbar-brand" href="#">Movie</a>
                </div>

                <!-- Collect the nav links, forms, and other content for toggling -->
                <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
                    <ul class="nav navbar-nav">
                        <li class=""><a href="/#/users">Users 
                        <span class="sr-only">(current)</span></a></li>
                        <li class=""><a href="/#/videos">Videos 
                        <span class="sr-only">(current)</span></a></li>
                        <li><a href="/users">Link</a></li>
                        <li class="dropdown">
                            <a href="#" class="dropdown-toggle" 
                            data-toggle="dropdown" role="button" 
                            aria-haspopup="true" aria-expanded="false">Dropdown 
                            <span class="caret"></span></a>
                            <ul class="dropdown-menu">
                                <li><a href="#">Action</a></li>
                                <li><a href="#">Another action</a></li>
                                <li><a href="#">Something else here</a></li>
                                <li role="separator" class="divider"></li>
                                <li><a href="#">Separated link</a></li>
                                <li role="separator" class="divider"></li>
                                <li><a href="#">One more separated link</a></li>
                            </ul>
                        </li>
                    </ul>
                    <form class="navbar-form navbar-left" role="search">
                        <div class="form-group">
                            <input type="text" class="form-control" placeholder="Search">
                        </div>
                        <button type="submit" class="btn btn-default">Submit</button>
                    </form>
                    <ul class="nav navbar-nav navbar-right">
                        <li><a href="#">Link</a></li>
                        <li class="dropdown">
                            <a href="#" class="dropdown-toggle" 
                            data-toggle="dropdown" role="button" 
                            aria-haspopup="true" aria-expanded="false">Dropdown 
                            <span class="caret"></span></a>
                            <ul class="dropdown-menu">
                                <li><a href="#">Action</a></li>
                                <li><a href="#">Another action</a></li>
                                <li><a href="#">Something else here</a></li>
                                <li role="separator" class="divider"></li>
                                <li><a href="#">Separated link</a></li>
                            </ul>
                        </li>
                    </ul>
                </div><!-- /.navbar-collapse -->
            </div><!-- /.container-fluid -->
        </nav>
    
    <div class="container" id="main_cont">
        <div class="" ng-view></div>
    </div>
</body>
</html>

We start from the server side of the application. Do you remember our simple program from Part I? We will use  that code here. Create folder Core and place CassandraEngine class there. Add UserService class to that folder as well.

C#
public class UserService
   {
       protected readonly ISession session;
       protected readonly IMapper mapper;
       protected readonly CassandraEngine engine;

       public UserService()
       {
           engine = new CassandraEngine();
           session = engine.GetSession();
           mapper = new Mapper(session);
           session.UserDefinedTypes.Define(
               UdtMap.For<Phone>());
           session.UserDefinedTypes.Define(
               UdtMap.For<Address>());
       }

       public string GetUsers(int page)
       {
           string json = string.Empty;
           var usersModel = new UsersModel();
           var getUsersPrepareStatement = new SimpleStatement("SELECT * FROM users;");
           var rows = session.Execute(getUsersPrepareStatement);
           List<User> users = new List<User>();

           foreach (var row in rows)
           {
               users.Add(new User
               {
                   username = row.GetValue(row.GetColumn("username").Type, 0) == null ?
                   "" : row.GetValue(row.GetColumn("username").Type, 0).ToString(),
                   address = row.GetValue<Address>(1) == null ? null : row.GetValue<Address>(1),
                   timestamp = row.GetValue(row.GetColumn("created_date").Type, 2) == null ?
                   "" : row.GetValue(row.GetColumn("created_date").Type, 2).ToString(),
                   email = row.GetValue(row.GetColumn("email").Type, 3) == null ?
                   new string[0] : row.GetValue(row.GetColumn("email").Type, 3) as string[],
                   firstname = row.GetValue(row.GetColumn("firstname").Type, 4) == null ?
                   "" : row.GetValue(row.GetColumn("firstname").Type, 4).ToString(),
                   lastname = row.GetValue(row.GetColumn("lastname").Type, 5) == null ?
                   "" : row.GetValue(row.GetColumn("lastname").Type, 5).ToString(),
                   password = row.GetValue(row.GetColumn("password").Type, 6) == null ?
                   "" : row.GetValue(row.GetColumn("password").Type, 6).ToString(),
               });
           }

           usersModel.users = users;
           json = JsonConvert.SerializeObject(usersModel);

           return json;
       }
   }

We can see here how UDT is dealt on the server side:

SQL
mapper = new Mapper(session);
session.UserDefinedTypes.Define( UdtMap.For<Phone>());
session.UserDefinedTypes.Define( UdtMap.For<Address>());

It's time to build our model. Create Model folder. A good name for model files I think , and add three classes there: Phone, Address, User.

SQL
public class Phone
  {
      public string number { get; set; }
      public IEnumerable<string> tags { get; set; }
  }

  public class Address
  {
      public string street { get; set; }
      public string city { get; set; }
      public int zip { get; set; }
      public Tuple<float, float> location { get; set; }
      public IEnumerable<Phone> phones { get; set; }
  }

  public class User
  {
      public string username { get; set; }
      public string firstname { get; set; }
      public string lastname { get; set; }
      public IEnumerable<string> email { get; set; }
      public string password { get; set; }
      public string timestamp { get; set; }
      public Address address { get; set; }
  }

Now, we add an api contoller.

Make Api folder, right click it and select Controller. After that, choose Web Api 2 Controller with read/write actions.

Image 2

Call it UserController and open it. We are interested in Get method. Put in it this code:

C#
string res = UserService.GetUsers(1);
return res;

and change method return type to string. Add these rows in your file as well:

C#
private static UserService _userService;

  protected UserService UserService
  {
      get
      {
          if (_userService == null)
              _userService = new UserService();
          return _userService;
      }

  }

Our server side is ready. To test it, compile the project and make sure you can browse to http://localhost:59673/api/users

Of course, your port may be different, and see something like this:

Image 3

If yes, you've done everything OK, if not something has gone wrong. But I'm sure we can cope with the problem.

Next time, we'll finish the project by adding angular part of it and we will see it in action.

Thank you for reading!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Israel Israel
C# developer

e-mail: otark777@yahoo.com

Comments and Discussions

 
QuestionPrimitive 'Get All Users' in example Pin
otark77719-Mar-16 23:32
otark77719-Mar-16 23:32 
Hi LMorgan
I do fire page argument. It happens on clicking next page button in users table. Of course if you have the table long enough for displaying next, prev buttons:
$scope.pageChanged = function () {
console.log($scope.currentPage);
getUsers($scope.currentPage);
};
By default current page is 0:
$scope.currentPage = 0;

Accordingly to sorting/ordering in Cassandra the answer wouldn't be so simple. As far as I understand it cassandra is not designed to sort all the table by some column like a traditional databases. Think that cassandra can store millions of rows, would you like to page through all that data by clicking next? I don't think so. First you will want to query some reasonable amount of rows and after that you can order it.
Cassandra offers you a possibility to do that by declaring one of the columns as primary key and one of the columns like clustering key.
But in the query you have to equate the primary key to something.
like this :
select * from users where username = 'tcod' order by lastname;

In our case it has no sense to use order by because we have only one row with username = 'tcod'
But primary key may be a date and the query will be like this:

select * from users where created_date > '2016-01-01 00:00:00' order by lastname;

This query should work if you declared created_dateas primary key and lastnameas clustering key
otark

QuestionPrimitive 'Get All Users' in example Pin
LMorg5218-Mar-16 19:36
LMorg5218-Mar-16 19:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.