Introduction

In this article we will see how to create REST API using ASP.NET Core Web API and new JSON features in Azure SQL Database and SQL Server 2016.
Azure Sql Database and SQL Server 2016 provide built-in JSON support that enables you to easily get data from database formatted as JSON, or take JSON and load it into table. This is good choice for web services that return database data in JSON format, or accept JSON text as parameter and insert JSON into database. With new built-in JSON support in Azure SQL Database, transformation between tables and JSON text become extremely easy.
In this article we will see how easily you can build ASP.NET Core Web API using Azure SQL Database and new JSON functionalities.

What kind of REST Service we are going to build?

This REST service is built using new ASP.NET Core framework. In the latest release of ASP.NET, you can create ASP.NET Core web applications using .Net Framework or .Net Core. Use .Net Core if you need minimal environment that can be used on any platform.

Update:

.Net Core is still under development and we might have some breaking change before RTM, so here I'm using .Net Framework. However, the core principles apply both on ASP.NET Core applications created using .Net Framework and .Net Core.

At the time of writing this article, I have used ASP.NET Core RC1 and then upgraded to RC2 that had some small breaking changes that required me to rewrite the app. Therefore, I choosed to use .Net Framework instead of RC2 Core, because it was more stable at the time of writing this article. In the meantime, .Net Core v1 is released and it should be more stable.

However, the core principles apply both on ASP.NET Core applications created using .Net Framework and .Net Core. Since I'm using JSON functionalities in Azure SQL Database, my app is very lightweight so I can add some minor changes in the app to fit new framework and still the main logic of JSON/SQL transformation would be unchanged. You can easily rewrite this app in node.js or any other framework if you want.

End Update :)

I will not use some complex database structure. We are going to build REST service on simple Todo table that has four columns: Title, Description, Completed, and TargetDate.

Our REST Web Api service will have following HTTP methods:

GET that will return all rows in the table

GET that will return single row with specified id

POST that will create/insert new row in table

PUT that will update all fields/column in the row specified by id

PATCH that will update some fields in the table

DELETE that will delete row by id

Model-less Service

When you create Web API, initially you are getting only Controllers folder where you need to put the code for your services. Web API don't force you to add models views or any other architectural concept.

However, the first thing that people do is to add Model folder with all necessary domain classes used to map tables to C# objects and to define the schema of JSON that will be generated when these objects are serialized. In some case they are adding entire EntityFramework model to access data.

In a lot of cases these models are used just as plain data transfer objects (DTO) that are just used as a schema input for other frameworks that read data from SQL database or serialize JSON responses. Sometime these classes don't even have some significant domain model characteristics or relationships between classes.

JSON functions in Azure SQL Database enable you to keep your service lightweigth and "model-less". If the only purpose for creating model is a "template" for serialization from database to JSON, you don't need to do this if you don't need it. SQL/JSON functions may handle all conversion between JSON and table data.

This way, JSON functions enable you to easily expose your database data to web clients without additional layers of transformation. In this example I will create REST Service that just pass JSON between SQL database and web clients.

Note: I'm not saying that models or ORMs are bad. However, if the core purpose of your REST api is just to expose data as REST endpoints, then you will probably not use all power of ORM/EF. In that case it might be better to try to use JSON in the original form and just pass it to database.

Setup

To run this sample, we would need a database with Todo table and a Web application. In this section is described how you can setup project and database.

Database setup

First you need to create new database in Azure SQL Database or SQL Server 2016 and execute the following script that creates and populates Todo table:Setup.sql

This sql script will create simple Todo table and populate it with three rows.
As an alternative you import bacpac file using SQL Server Management Studio/Import Data-tier application that will restore database and populate table.
One additional thing that you need to do is to set compatibility level to value 130 if it is not already set:

ALTERDATABASE TodoDb SET COMPATIBILITY_LEVEL = 130

Latest compatibility level enables OPENJSON function that we will use in these samples. When you finish this step you will have prepared database with one Todo table.

Application setup

I’m using Visual Studio 2015 Community Edition to create Web API REST service. You can download sample from this article or create new project and choose ASP.NET Core Web Application (.Net Framework):

Then you cna choose Web API type of the project and optionally check host in the cloud check box if you want to host it in Azure.

Now you have ASP.NET Core Web Api project so we can create new REST Service.

Database access component

We would need some classes that read data from database. In this article I will not use Entity Framework or something similar. Since Azure SQL Database will format and parse my JSON data, I can use any simple ADO.NET library that can execute plain SqlCommand.
In this article I’m using small lightweight data access library that wraps basic data access functions. Library is called CLR-Belgrade-SqlClient, which can be downloaded from GitHub - https://github.com/JocaPC/CLR-Belgrade-SqlClient. This is small, lightweight, data access library that just wraps basic ADO.NET classes and methods.

Belgrade SQL Client library follows something like CQRS pattern where commands and query classes are separated. In this library, we have two main classes:

QueryPipe that executes SQL query with FOR JSON clause and streams results into some output stream. In our case this output stream will be Response.Body of Web API controller.

Command that executed non-reader queries that will be used for INSERT, UPDATE and DELETE queries.

Nice thing with this library is that it is completely async. Under the hood it uses async methods of ADO.NET classes, which might improve scalability of your code.

In order to download this library you can install Belgrade.Sql.Client using Package Manager in Visual Studio, or type the following command into your Package Manager console:

Install-Package Belgrade.Sql.Client

If this does not work for you, you can download source code from github and compile it into your project: https://github.com/JocaPC/CLR-Belgrade-SqlClient

Nuget package is compiled under .Net Framework 4.6, but source code is generic and it can be compiled under any framework (e.g. .Net Core).

Note that this library not prerequisite to use JSON in new SQl server/Azure SQL Database. It is just a helper library that helps me to write easier code, but you can use any other library that can execute standard SQL commands.

Selecting rows from table

If you want to select data from table and read them as JSON a you just need to add FOR JSON PATH clause at the end of the SQL SELECT query:

select * from Todo
FOR JSON PATH

As a result, instead of table, you would get something like the following JSON.

[
{"Id":1,"Title":"Install SQL Server 2016","Description":"Install RTM version of SQL Server 2016","Completed":false,"TargetDate":"2016-06-01T00:00:00"},
{"Id":2,"Title":"Check what's new","Description":"Go to MSDN to see what is new in SQL Server 2016","Completed":false,"TargetDate":"2016-06-01T00:00:00"},
{"Id":3,"Title":"Get new samples","Description":"Go to github and download new samples","Completed":false,"TargetDate":"2016-06-01T00:00:00"},
{"Id":4,"Title":"Try new samples","Description":"Install new Management studio to try samples","Completed":false,"TargetDate":"2016-06-02T00:00:00"}
]

If you execute this query from your REST Web API, you can simply stream this result to your client because this is probably the output that client expects.

Selecting singe row

Azure Sql Database enables you to select single row (by specifying id of the row) and return it as a single JSON object:

select *
from Todo
where Id = 3FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

If you add WITHOUT_ARRAY_WRAPPER option, Azure SQL Database will remove [ and ] that surround JSON result, and return single JSON object that you can return to client – something like:

{"Id":3,"Title":"Get new samples","Description":"Go to github and download new samples","Completed":false,"TargetDate":"2016-06-01T00:00:00"}

Like in the previous case, you can return this text directly to the client via REST service.

Insert new row

OPENJSON function parses JSON text that you send to database and transforms it into table structure. Then you can simply insert parsed results into table:

OPENJSON will parse JSON text in @todo variable. In WITH clause you can define what keys from JSON text you want to read and these keys will be returned as columns. Then, you just need to select results from OPENJSON and insert them into Todo table.

Update existing row

OPENJSON function, which parses JSON text, can be used to update existing rows:

OPENJSON will parse JSON text in @todo variable and like in the previous example, you can define in WITH clause what keys from JSON text you want to read. Instead of INSERT, we need to UPDATE row in Todo table with results from OPENJSON.

Delete

In order to delete row you don't need JSON, since rows are deleted by id:

DELETE Todo WHERE Id = 3

Now we know how our SQL queries would look, so we just need to add C# code that uses these queries and we will have REST service

Implementing REST Web APi

Ok, now we have database, project, and we know how to use JSON in Azure SQL Database, so we can create REST service that access Todo table.

First, add new controller using New / Controller and call it TodoController. TodoController must have a reference to some classes/services that can execute SQL queries and return JSON. Since I'm using CLR-Belgrade-SqlClient, I would need references to Command and Query objects that will execute SQL commands:

Note: for simplicity reasons, I’m adding reference to these services, but in your application you would probably use some kind of repository instead of services.

In this sample project, I’m using simple built-in ASP.NET 5 dependency injection with constructor injection. In ASP.NET 5, you have new Startup.cs class where you can add all services that will be used by controllers and other components in your applications. Services are added in ConfigureServices method in Startup class:

Here I’m adding transient services with interfaces IQueryPipe and ICommad that are initialized using lambda expression in argument. You just need to set your server, database, and user name in connection string.
This is not mandatory approach, you can use any other dependency injection framework (ninject or autofac) or initialize these object using any other method. You can even directly initialize Pipe/Command objects:

SqlPipe will execute Sql query that has FOR JSON clause and directly stream results into output stream, which is Response.Body in our case because we are returning results to client in the body of response.
Now we can add methods in TodoController that implement basic CRUD operations.

Returning all records from Todo table (GET)

First we will add one method that will be called when user calls GET api/Todo Url. This Url will return all objects in Todo table:

First note that this is async method (async Task) that will stream results of SQL query into Response.Body. Since Belgrade.SqlClient is async library, you can call Stream method with await keyword.
Some web client that calls /api/Todo will see results of SQL queries formatted as JSON. If you call Todo Get method using http://localhost:15194/api/Todo you will get something like:

As you can see, we need one line of code to return data from your in this REST service. Third parameter in Stream method defines what should be returned if there is no returned data – in out case empty array.

Returning single record from Todo table (GET)

Now when we have list of all Todo items, we need one method that returns Todo by id:

You can notice that I just copied OPENJSON query from previous section, and wrapped it into C# code. This is async method that will read JSON from request body, define SqlCommand and provide input JSON as parameter. JSON will be parsed in OPENJSON command and inserted into table.

Note: If you are using .Net Core You would need a package or assembly that has StreamReader class.

If you open some tool that can send Http requests to server like Chrome Poster you might get the following result:

Updating existing record in Todo table (PUT)

Now we need to implement PUT method that updates values in the row specified with id. You can add something like this:

This is async method will read JSON from request body, define SqlCommand and provide input JSON and id as parameters. JSON will be parsed in OPENJSON command and row with the specified id will be updated.

Note: If you are using .Net Core You would need a package or assembly that has StreamReader class.Note: I have found that some people have problem with ReadToEnd() method (works on my machine :) ). You can find alternative approaches to read body of request with MemoryStream.

Partial updates of existing record in Todo table (PATCH)

Many REST services support both PUT and PATCH methods. PATCH method is similar to PUT, but PUT will overwrite everything and put null values if some fields in the input JSON are missing, while PATCH will update only those fields that are provided in JSON. Code for PATCH might look like the following code:

You might notice that PATCH is very similar to PUT. Both methods use the similar code and update row in the table by id. The key difference is in ISNULL (json.COLUMN, COLUMN) part.
PUT code will update all cells in the row. If some key:value is not provided in JSON it will insert NULL value because OPENJSON returns NULL if some key that is specified in WITH clause cannot be found.

However, this code will check is value in JSON NULL, and if it is not NULL, this value will be written in the column. If value is NULL then existing column will be written and cell will not be changed. With this simple logic you can send just a single filed that should be updated, and the others will not be changed.

Delete row from table (DELETE)

Finaly, we need a DELETE action that deletes row by id. DELETE action does not require Azure SQL JSON functions that are available in Azure SQL Database, so we need just a simple code:

This method will just get provided id from request, and delete row in Todo table by Id. Now we have complete REST Service with a few lines of code for each method.

Conclusion

With JSON support in Azure SQL Database, it is extremely easy to create REST Web service that accepts or returns JSON. In this article you might see that every REST method is just a few lines of code. You don’t even need something like ORM, class model, etc.
If you need to quickly create small micro-services that exposes few tables from your database this might be good solution for you. With a little effort, you can even generate code for controller.

Recommendations

In this article I have placed data access logic in body of controller because I want simple example. In practice you would move this code in separate data access or repository class and just call it from
In this code I have placed SQL queries in C# code. My recommendation would be to create stored procedures for all these queries and just call stored procedures from code. Procedures will be simple and have one or two parameters (id and/or JSON text):

With stored procedures you will have faster queries and simpler data access logic because you will just put stored procedure name in your C# SqlCommand.
Finally, in this project connection string is placed inline in Startup.cs code but you should move it to some configuration file.

License

Share

About the Author

Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia.
Currently working in Microsoft as Program Manager on SQL Server product.
Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Web and databases, Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Some methods are rewritten and replaced with new methods in later versions of library. However, old methods are kept with warning just to avoid braking changes. My suggestion would be to replace old methods with the new ones.

thanks for this article so far.
But i have a problem with POST and PUT - i need to return data in the body, but as i call sql-data as you described, i will get no results on POST and PUT. I call a stored procedure which returns a json.
How to resolve this?

Sorry for the late reply. Could you describe what is wrong and could you reproduce this error when you execute FOR JSON clause directly in SQL server? One common error is that database column where you store data is defined as VARCHAR instead of NVARCHAR and this might mess some international characters if wrong collation is set on the VARCHAR column.
FOR JSON returns data in NVARCHAR format that is equivalent to UTF-18. SQL server generally cannot work with UTF-8, but NVARCHAR should show any data.

If your client app expects UTF-8 could you change encoding there to UTF-16? In ASP.NET controllers you can set content type using something like:

I would like to know if there is some bug in Belgrade Sql client, but I have used it even in one project where I have displayed Japanese characters in client side with JSON generated using Belgrade SQL Client (sample project is on GitHub[^] ). Inthis project I have server JSON response generated with Belgrade Client to JQuery Datatables component and it worked well, so I'm interested what kind of error you see.

"Net Core is still under development and we might have some breaking change before RTM, so here I'm using .Net Framework. However, the core principles apply both on ASP.NET Core applications created using .Net Framework and .Net Core."

That is very funny!!!!!! What EXACTLY does Core provide that a million applications have not already got ???

I'm not an expert for ASP.NET Core, but i believe that it is rewrite of existing framework that will enable faster and more portable apps.
Since it will be more lightweight, I assume that it would run faster since it will not have unnecessary components.
You can run you apps on Linux servers if you need lower price of licences (.Net core on Linux will be free as any other OSS framework). With new SQL Server on Linux, this stack might reduce your cost if you already have Linux environment and you don't want additional Windows servers just to host ASP.NET and SQL Server.

But, maybe it would be better to post this question somewhere (CodeProject forum, stack overflow) where some expert for .Net Core would give you better answer.