Generating JSON Web Services from an Existing Database with CodeFluent Entities

• Download CodeFluent Entities

What is CodeFluent Entities?

CodeFluent Entities is a unique product integrated into Visual Studio 2008/2010/2012 which allows developers to generate components such as scripts (e.g. T-SQL, PL/SQL, MySQL, Pg/SQL), code (e.g. C#, VB), web services (e.g. WCF, JSON/REST) and UIs(e.g. ASP.NET, SharePoint, WPF).

The code generation process is model-first and continuous: from your declarative model, a meta-model will be inferred which code generators will then translate into code. Over 20 code generators (a.k.a. "producers") are provided "out of the box" and can be combined to create your own application following your desired architecture, using your desired technologies.  

More on http://www.softfluent.com/products/codefluent-entities 

Who is SoftFluent?

SoftFluent is an innovative company industrializing the software development of data-oriented applications on the .NET platform.

SoftFluent is an innovative software company founded in 2005 by Microsoft veterans, former consultants of Microsoft Consulting Services. SoftFluent is Visual Studio Industry Partner at Premier level & Microsoft Gold Partner certified. It has leveraged its strong experience in building enterprise-class applications to create a Model-First Code Generator optimized for the Microsoft platform.

More on http://www.softfluent.com/about-us 

A Common Scenario 

Let us say that we are facing the following scenario:

• We have a database that we want to expose via a JSON based web service layer, providing CRUD (Create, Read, Update and Delete) operations.  
• We also need to build a back office in order to manage and administrate the data coming from our database.
• We may need, on a future, to access in a different way our database, for example from a Smart Client or expose a SOAP based web services layer (there are always new ideas).
• We need to deploy this system as soon as possible.

Let us start, what we need to do is:

• Build a data access layer capable to load data, create new data, update and delete existing data (and make sure it works).
• Manage validation data (and make sure it works).
• Build a JSON based web service layer.
  • Build every needed service contract and operations.
  • Configure our service contracts to support JSON.
  • Host our services.
  • Make sure it works
• Build a web based client (and make sure it works).
• Lay the foundations so any possible evolution can be supported.
• And everything I have missed.

Or ... We can use CodeFluent Entities to do the plumbing and being sure that it works.

What do we need for this demo?

In order to follow this demo you will need:

• Visual Studio 2008/2010/2012  
• SQL Management Studio 2008/2012
• CodeFluent Entities. You can get a unlimited, full featured and free personal license from here http://www.softfluent.com/products/codefluent-entities/
• The Adventure Works sample database. The download and installation procedure will be shown below.

This demo will be lead using the following tools:

• Visual Studio 2012 Update 1
• SQL Management Studio for SQL Server 2012
• CodeFluent Entities Build 61214.691
• The light-weight version of Adventure Works 2012 (AdventureWorksLT2012_Database.zip) http://msftdbprodsamples.codeplex.com/downloads/get/478215

First of all we need to install the Adventure Works sample database, you will find the 2008 and the 2012 version following this link http://msftdbprodsamples.codeplex.com/releases/view/93587, we will use the light-weight version of the sample database (AdventureWorksLT20XX_Database.zip).

You are not forced to use the Adventure Works database, you can use any database you want but you will need to adapt to your case some steps of this demo.

Once the database .zip file downloaded unzip it, you must find two files:

Open SQL Management Studio and attach the database.

Then click on the 'Add' button and follow the instructions. Make sure SQL Management Studio has the permission to load you file (you can run SQL Management Studio as Administrator).

If you are having problems attaching the database you can find help here. http://msdn.microsoft.com/en-us/library/ms190209(v=sql.110).aspx

Installing CodeFluent Entities is easy. Just start here http://www.softfluent.com/products/codefluent-entities/download 

Let us get started

Once you have downloaded and installed CodeFluent Entities, open Visual Studio (2008 / 2010 / 2012) and create a new project. You will find a new project template category named "CodeFluent Entities". For the purpose of this demo we will choose the "Import Started Wizard" project template and we give a name to our solution, in my case I will call it "AdventureWorksDemo".

The first step in our wizard is to choose an "importer" type, it means that we need to choose where we are getting our data from.

As you can see, CodeFluent Entities provides a wide set of options to import data like Microsoft Access, Microsoft SQL Server, MySQL, Oracle databases, PostgreSQL database and others.

We are going to select the "Microsoft SQL Server 2000 or higher database" importer.

And now, we only need to provide the connection string of our SQL Server database, in my case it looks like this:

server=(local);database=AdventureWorksLT2012;Integrated Security=true

You are able to import you entire database or only a specific set of tables.

You can save your import configuration for a later use if you wish.

Now we need to choose a target architecture.

This is a very interesting step, we can see some of the possible built-in architectures that can be generated by CodeFluent Entities, and of course you can imagine your own architecture by creating a custom CodeFluent Entities project with your relevant set of producers.

For this demo we will choose the ASP.NET Ajax/Json architecture. This will create a N-Tier architecture with a Web Client Application as the user interface using jQuery/jqGrid and JSON automatic web services.

Now we must choose C# or VB.NET as our target domain object language.

Then we need to choose a persistence layer.

We can choose the same database that we have imported or we can tell CodeFluent Entities to create a new database. Using a different database will be useful if we are planning to migrate a database from one technology to another, for example we could easily migrate an Access database or a MySQL database to a Microsoft SQL Server database.

For this demo we will use the imported database as our persistence layer, so we will use the same connection string as previously.

The next step shows the actions that will be performed by our "Import Starter Wizard".

When we click "Next" or "Finish", CodeFluent Entities will start generating a Visual Studio solution containing all the projects needed for our application.

Finally we get a detailed report about the generation of our solution.

Let us take a look at our Visual Studio solution.

What do we have here?

• An empty C# class library project. This project will host our Business Object Model (aka BOM) as well as our services contracts and services implementations.
• An ASP.NET Web project that will have a double role: contain our web client and host our JSON based web services.
• A CodeFluent Entities project that contains our Entity model and a set of Producers for each layer of our architecture.
• An empty SQL Server Project to store the persistence layer SQL scripts.

Right now, all the projects have the standard Visual Studio project template. We need to "tell" the CodeFluent Entities project to generate the content of each project, this is made by "building" the CodeFluent Entities project, but before that let us take a look at it.

The CodeFluent Entities Project

This is what a CodeFluent Entities project looks like.

From top to bottom, the main elements are:

• Surfaces: the design surfaces of our Entity model, kwon as the "Modeler".
• Parts: the XML representation of our Entity model.
• Producers: each layer of our application will be generated by a Producer.
• Model: an inferred representation of our model containing all the related information (Entities, Properties, Methods, Relations, Rules…)

Let us take a closer look to the different Producers:

• SQL Server Producer: this producer will generate and execute all the SQL scripts containing the stored procedures used by CodeFluent Entities to load and update data on the persistence layer, these scripts will be stored on the SQL Server project (AdventureWorksDemo.Persistence).
• BOM Producer: it will generate our Business Object Model, it means, all the C#/.NET classes containing the same logic as our model (Entities, Properties, Rules, Methods…). These classes can be essentially used to do CRUD (Create, Read, Update and Delete).
• Service Producer: this is a BOM sub-producer, it represents a JSON based service layer.
• ASP.NET Ajax Producer: this producer will generate our web client using jQuery and jqGrid. The target project will be AdventureWorksDemo.Client.

Let us open the default modeling surface (double click on the Surface > Default tree item). That will open the modeling surface.

On the top we can access the CodeFluent Entities Ribbon that allows us perform multiple actions on our model. On the modeling surface there is an Entity representation of our imported database. It is not a relational model nor a UML diagram, it is a model oriented representation that relies on concepts like Entities, Properties, Relations, Rules, Attributes, Methods...

Before building our CodeFluent Entities project we will configure our SQL Server Producer so the original database schema is not updated, but only the CodeFluent Entities stored procedures are created. To do that, make double click on the SQL Server Producer item.

The SQL Server Producer properties windows pops up, select the "Advanced Properties" button.

The "Advanced Properties" are now displayed. Make sure that the "Update Schema" property is set to false and the "Update Procedures" property is set to true (default value).

It is already time to build our CodeFluent Entities project. Right click on the CodeFluent Entities project (AdventureWorksDemo.Model) and click "Build". CodeFluent Entities will generate all the code associated to each producer. The Visual Studio Output windows shows the CodeFluent Entities progression while executing each producer.

If you take a look in to your solution you will see that a bunch of code has been generated by CodeFluent Entities.

The Result

Now you have:

• A business object model that manages all the data access aspects providing methods to load, create, update and delete data.
• A JSON based web service layer that can be accessed by any application.
• A web based back office to administrate all your records.

And you have written zero code. Everything is 100% functional and fully extensible.

Discovering the full generated back office web application

In your web site project (AdventureWorksDemo.Client) you may add reference to your BOM project (AdventureWorksDemo).

If you want you can now deploy your web site to IIS or use the integrated Visual Studio Development Server (on Visual Studio 2012: Right click on your web project -> Properties / Web tab -> Servers -> Use Visual Studio Development Server).

Let us set the web site project as the startup project (Right click on AdventureWorksDemo.Client -> Set As StartUp Project).

Just hit F5 and voilà! You may see a full generated back office built in Ajax/jQuery/jqGrid that allows you administrate your records.

You may experience a problem if you are you using Visual Studio 2012 when launching for the first time you web site. If you see an error message like this:

The type or namespace name 'Optimization' does not exist in the namespace 'System.Web'

There is a very simple solution to fix it:

• Open the web.config file on you Web site project.
• Find the line that reference the System.Web.Optimization namespace and comment it like this: <!--<add namespace="System.Web.Optimization" />-->

Now you can Load, Create, Update and Delete any record on your database.

Discovering the JSON based web service layer

The ASP.NET Ajax/jQuery web client uses the generated JSON web services generated by CodeFluent Entities.

Our web services are hosted in our web site application, you can find as many ".svc" files as entities in our model.

These services are standard WCF services, we can find the service contracts and implementations in the BOM project (AdventureWorksDemo) under the folder {namespace}\Services, for example SalesLT\Services.

And this is an example of a service contract interface, we can see that CodeFluent Entities added the suitable attributes so our service is JSON compliant.

We only need to test our services. In order to do this, we will add a Console Application project to our solution.

In the Program.Main method we will just call, for example, the "Customer service" and verify if it works.

Verify that the URI used below matches your web site URL.

static void Main(string[] args)
{
    string uri = "http://localhost:20121/Services/SalesLT/Customer.svc/LoadAll";

    using (WebClient client = new WebClient())
    {
        //We need to specify that we are sending Json contents, or our
        //requests will be rejected by the services
        client.Headers.Add("Content-Type: json; charset=UTF-8");
        client.Encoding = System.Text.Encoding.UTF8;
        try
        {
            string response = client.UploadString(uri, "");
            Console.WriteLine(response);
        }
        catch (WebException ex)
        {
            WebResponse errorResponse = ex.Response;
            using (Stream responseStream = errorResponse.GetResponseStream())
            {
                StreamReader reader = new StreamReader(responseStream, new System.Text.UTF8Encoding());
                String errorText = reader.ReadToEnd();

                Console.WriteLine(errorText);
            }
            throw;
        }
    }
    Console.ReadLine();
}

Make sure that your web application is running, then run your Console Application project (AdventureWorksDemo.ConsoleTest) as following: right click on the project, Debug/Start new instance.

You may get a nice black and white console displaying a semi human readable JSON text. What is important is to know that our service works.

What about changes?

You may want to make your project evolve, for instance: 

• Add a new entity to your model.

• Specify a business validation rule on a specific entity property.

• Build a condensed view for one or more entities.

• Localize your application.

• Provide a SOAP based web service API.

• Create a windows phone application.

• Make a Silverlight or WPF smart client.

• Set up a MVC web site.

• ...

Any change made on your model will be propagated through each layer of your application. CodeFluent Entities provides, for each feature, a default configuration that can be extended and customized to fit your needs.

CodeProject Member Offer

• Please check out this special offer for CodeProject members

Resources

CodeFluent Entities is a mature 7 year old product, you can find several resources and find all you want to know about CodeFluent Entities:

• CodeFluent Entities page: http://www.softfluent.com/products/codefluent-entities
• The download link: http://www.softfluent.com/products/codefluent-entities/download
• The Webcasts: http://www.softfluent.com/community-support/codefluent-entities/webcasts
• The documentation: http://www.softfluent.com/documentation/
• The forum: http://forums.softfluent.com/
• The blog: http://blog.codefluententities.com/
• The self-training videos: http://www.softfluent.com/media---codefluent-entities-step-by-step-training
• SoftFluent web site: http://www.softfluent.com/