All posts by Ariel Neisen

Using SQL Server Data Services

In my previous post I’ve presented my first impressions of SQL Server Data Services. Now it’s time to start coding. In this post, I’ll show how to use the basic operations from SSDS.

Getting started

Remember that SSDS is a service provided by Microsoft. So, the first step is creating an account. You can register here: http://www.microsoft.com/sql/dataservices/default.mspx

After receiving the confirmation you’ll be able to access the services.

Adding the service reference

In order to use the SSDS instance, we need to add a reference to the SOAP service. The URL is http://data.beta.mssds.com/soap/v1?wsdl. Remember that this is a beta instance, so the URL can be changed in any moment.

Creating entities

SSDS has a defined structure: authorities, containers and entities. Then, we need to create those entities in order to use the service.

Creating the proxy

When creating the proxy, you should only submit the client credentials.

Note: Sitka is SSDS’ codename.

public static SitkaSoapServiceClient GetProxy()
{
    SitkaSoapServiceClient proxy = new SitkaSoapServiceClient("SitkaSoapEndpoint");
    proxy.ClientCredentials.UserName.UserName = "---"; //TODO: Add username
    proxy.ClientCredentials.UserName.Password = "----"; //TODO: Add password
    return proxy;
}

Creating authorities

Creating an authority is just a simple call to the service. In this case the Scope will be empty, because we’re creating a new one.

public static Authority CreateAuthority(string id)
{
    Scope scope = new Scope();
    Authority authority = new Authority();
    authority.Id = id;
    GetProxy().Create(scope, authority);

    return authority;
}

Creating containers

Creating a container is very similar to creating an authority. The only difference is that now the container is going to be scoped to an authority, so we need to specify that in the Scope.

public static Container CreateContainer(string id, Authority authority)
{
    Scope scope = new Scope();
    scope.AuthorityId = authority.Id;
    Container container = new Container();
    container.Id = id;
    GetProxy().Create(scope, container);

    return container;
}

Creating entities

Now we’re all set to add entities to our container. Given that the model is very flexible, in order to create entities we only need to provide the id and the values.

Note that the entity will be scoped to an authority and a container.

public static Entity CreateEntity(string id, Dictionary<string, object> values,
        Authority authority, Container container)
{
    Entity entity = new Entity();
    entity.Kind = "default"; //Optional metadata
    entity.Id = id;
    entity.Properties = new Dictionary<string, object>(values);
    SitkaSoapServiceClient proxy = GetProxy();
    Scope scope = new Scope();
    scope.AuthorityId = authority.Id;
    scope.ContainerId = container.Id;
    proxy.Create(scope, entity);

    return entity;
}

Querying entities

Taking into account that the queries are text-based, querying over entities is just a call to the service.

public static Entity[] Query(string query, Authority authority, Container container)
{
    SitkaSoapServiceClient proxy = GetProxy();
    Scope scope = new Scope();
    scope.AuthorityId = authority.Id;
    scope.ContainerId = container.Id;

    return proxy.Query(scope, query);
}

Putting all together

That’s it. We have our data layer implemented without having an SQL server. We’re just using an on-the-cloud service and we don’t have to worry about deploying, maintenance, etc.

Putting all together, we can use the methods defined in this post like this:

static void SSDSSample()
{
    // Creating authority
    Authority authority = CreateAuthority("testauthority");
    // Creating container
    Container container = CreateContainer("testauthority_container", authority);

    // Adding entities
    for (int i = 0; i < 10; i++)
    {
        Dictionary<string, object> props = new Dictionary<string, object>(3) {
                {"name", "name" + i}, {"address", "address" + i}, {"tel", "tel" + i}};
        CreateEntity("person" + i, props, authority, container);
    }

    // Querying entities
    Entity[] retrievedEntities = Query("from e in entities select e", authority, container);
    foreach (Entity entity in retrievedEntities)
    {
        Console.WriteLine(entity.Id);
        foreach (KeyValuePair<string, object> val in entity.Properties)
        {
            Console.WriteLine(val.Key + ": " + val.Value.ToString());
        }
        Console.WriteLine();
    }
    Console.ReadKey();
}

A first glance at SQL Server Data Services

I’ve been researching one of Microsoft’s newest services: SQL Server Data Services (aka SSDS). In this post I will present my first impressions about this technology.

 

Motivation

The motivation of this service is to provide a scalable data storage and query processor utility "on the cloud". Using this approach, the costs of infrastructure and support decreases, since that responsibility relies on Microsoft and not in the software provider.

Besides, more and more new Web applications need to save less complex data, with a high service level agreement (SLA).

 

Implementation

SSDS is built on the SQL Server technology, using the SOAP and REST communication protocols. Some of the features are:

  • No schema. Since the data model is flexible, we now can have heterogeneous entities (serialization becomes more complex).
  • Pay-as-you-grow model. The space used can grow as you need it.
  • SLA covering high availability, performance and redundancy.
  • Easy to develop. In order to use the data we only need to call the appropriate Web Service.
  • The queries are text-based with a C# LINQ pattern.

The organization model is really simple:

  • Customers are the organizations that are using the SSDS. Customers have many Accounts.
  • Accounts are the billing entities. An account has authorities assigned.
  • Authorities are the ones that organize the data containers. It defines the location and security policies of its containers.
  • Containers are the scope for the entity storage and query. For example, each employee may have a container with its personal data.
  • Entities are the storage unit of SSDS, for example the employee’s education information.

Conclusion

I think that this service has a lot of potential. With the growth of Web2.0 and social applications, we need a way of deploying small applications with a reliable data access. Obviously is very expensive to deploy and maintain a data base server, so having that service on-the-cloud is a very interesting alternative.

From the technical point of view, the no-schema structure is very interesting. Still, I guess that it can have a negative impact in the serialization process.

On the other hand, this service is still in working process, so we’ll be awaiting for new features to come.

Using parsers in C#

Most developers must agree that parsing a string in order to build the data structure that it represents is a very tedious task. As a matter of fact is horrible. There’s a lot of code, lots of border cases, lots of bugs; and the list continues. Personally, I’ve spent lots of programming hours dealing with these issues.

Gladly, there’s a field of research in the Computer Science that focuses of parsers and syntax analyzers: the Languages Theory. One of its fields of study tries to find formal, correct and efficient ways to parse strings, and we can use that knowledge (and stop reinventing the wheel).

ANTLR

ANTLR is a tool that generates recognizers for grammatical descriptions in C#, Java and other programming languages. It also offers a GUI to create and debug those grammars. All we need to do is program the data structures and define the desired grammar (don’t worry if you’ve never heard about this, after reading an example you’ll see that is not that difficult).

Example scenario

Suppose that we want to implement an SQL parser: just parsing the query and generating the correct data structure. Let’s keep it simple; focus on a SELECT [fields] FROM [tables] query and don’t worry about the consistency of the queries. We only want to parse the input string.

Data structure

After analyzing the scenario we can infer that we’ll have a SelectQuery that contains Fields and Tables. The class diagram should be:

Class diagram

Grammar

Now let’s take a look at how the grammar is created. Remember that ANTLR is a code generation tool, so we will define the grammar and also some configuration options. A grammar is the formal description of a language. In our case, the language will be all the strings that start with SELECT, then a list of Fields, then the FROM keyword and finally the list of queries. The syntax is really simple, we just define the structure of the correct inputs and the code that we need to add. This grammar is defines like:

   1:  grammar ExampleAnalyzer;
   2:   
   3:  options
   4:  {
   5:      language=CSharp;
   6:  }
   7:   
   8:  @header {
   9:      using ExampleAnalyzer.Model;
  10:      using System.Collections.Generic;
  11:  }
  12:   
  13:  @parser::namespace {ExampleAnalyzer.Parser}
  14:  @lexer::namespace {ExampleAnalyzer.Parser}
  15:   
  16:   
  17:  WHITESPACE : ( 't' | ' ' | 'r' | 'n'| 'u000C' )+ { $channel = HIDDEN; } ;
  18:   
  19:   
  20:  selectQuery returns [SelectQuery result]
  21:      : 'SELECT ' f = fields ' FROM ' t = tables
  22:      {result = new SelectQuery(f, t);};
  23:   
  24:  tables returns [List<Table> result]
  25:      @init{result = new List<Table>();}
  26:      : t1 = table {result.Add(t1);} (',' t2 = table {result.Add(t2);})*;
  27:   
  28:  fields returns [List<Field> result]
  29:      @init{result = new List<Field>();}
  30:      : f1 = field {result.Add(f1);} (',' f2 = field {result.Add(f2);})*;
  31:   
  32:  field returns [Field result]
  33:      : f = VALIDNAME {result = new Field($f.text);};
  34:   
  35:  table returns [Table result]
  36:      : t = VALIDNAME {result = new Table($t.text);};
  37:   
  38:  VALIDNAME
  39:      : (('A' .. 'Z') | ('a' .. 'z'))+;
  • Lines 1 to 14 are configurations options, for instance namespaces, output language, etc.
  • Lines 20 to 22 define the selectQuery structure.
    • Line 20 specifies that the result from this structure is going to be a SelectQuery instance.
    • Line 21 defines the syntax of the structure.
    • Line 22 is the specific code that we need to add in order to create the result instance. In this case we need to create the SelectQuery object. This code is written between curly brackets.
  • Lines 24 to 26 define how the tables should be written. Notice that we can use the regular expressions wildcards like ‘*’ and ‘+’. Also the @init code block. There we can write the initialization code for the data structure.

Then we select the Generate option and add the code to the project. Remember that we also need to add a reference to the runtime library, available at the ANTLR site.

Using the parser

Now all we have to do is use the generated classes (the lexer and the parser).

ExampleAnalyzerLexer lexer = new ExampleAnalyzerLexer(new ANTLRStringStream(input));
ExampleAnalyzerParser parser = new ExampleAnalyzerParser(new CommonTokenStream(lexer));
SelectQuery query = parser.selectQuery();

And that’s it. Now query is the data structure obtained from the input string. Later we can specify how we want to deal with the errors in the input (by defaults, it writes them in the output console).

Query