In this article, you can see how easily you can expose your database data via REST services developed using ASP.NET Core Web API and new JSON support in Azure SQL database and SQL Server 2016.
Table of Contents
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 enable you to easily get data from database formatted as JSON, or take JSON and load it into table. This is a 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 becomes extremely easy.
In this article, we will see how easily we can build ASP.NET Core Web API using Azure SQL Database and new JSON functionalities.
What Kind of REST Service are we 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 chose 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 was 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 a simple Todo table that has four columns: Title, Description, Completed, and TargetDate.
Our REST Web API service will have the following HTTP methods:
GET that will return all rows in the tableGET that will return single row with specified idPOST that will create/insert new row in tablePUT that will update all fields/column in the row specified by idPATCH that will update some fields in the tableDELETE 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 doesn'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 the 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. Sometimes, 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 lightweight 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 passes 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, how you can setup project and database is described.
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
DROP TABLE IF EXISTS Todo
GO
CREATE TABLE Todo (
Id int IDENTITY PRIMARY KEY,
Title nvarchar(30) NOT NULL,
Description nvarchar(4000),
Completed bit,
TargetDate datetime2
)
GO
INSERT INTO Todo (Title, Description, Completed, TargetDate)
VALUES
('Install SQL Server 2016','Install RTM version of SQL Server 2016', 0, '2016-06-01'),
('Get new samples','Go to github and download new samples', 0, '2016-06-02'),
('Try new samples','Install new Management Studio to try samples', 0, '2016-06-02')
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:
ALTER DATABASE 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 the sample from this article or create a new project and choose ASP.NET Core Web Application (.NET Framework):
Then, you can 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 a 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 a 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 the 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 the source code is generic and it can be compiled under any framework (e.g., .NET Core).
Note that this library is not a 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.
JSON in Azure SQL Database
Azure SQL Database provides the following functionalities that handle JSON:
Main functionalities that we see on this figure are:
- JSON functions that can take a value from JSON text, update JSON text, check whether JSON is valid, etc.
FOR JSON that formats results of SQL queries as JSON textOPENJSON that open and parse JSON text and transforms it into table
If you would like to know more about JSON in Azure SQL Database, I would recommend the following article - Friday the 13th - JSON is coming to SQL Server - CodeProject.
Now, we will see how to implement basic CRUD operation in Azure SQL Database using these functionalities.
Selecting Rows From Table
If you want to select data from table and read them as JSON, 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 Single 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 = 3
FOR 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:
Set @todo = '{"Title":"Get new samples",
"Description":"Go to github and download new samples",
"Completed":false,"TargetDate":"2016-06-01T00:00:00"}'
insert into Todo
select *
from OPENJSON(@todo)
WITH( Title nvarchar(30), Description nvarchar(4000), Completed bit, TargetDate datetime2)
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:
set @todo = '{"Title":"Get new samples",
"Description":"Go to github and download new samples",
"Completed":false,"TargetDate":"2016-06-01T00:00:00"}'
update Todo
set Title = json.Title, Description = json.Description,
Completed = json.completed, TargetDate = json.TargetDate
from OPENJSON( @todo )
WITH( Title nvarchar(30), Description nvarchar(4000),
Completed bit, TargetDate datetime2) AS json
where Id = @id
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:
public class TodoController : Controller
{
private readonly IQueryPipe SqlPipe;
private readonly ICommand SqlCommand;
public TodoController(ICommand sqlCommand, IQueryPipe sqlPipe)
{
this.SqlCommand = sqlCommand;
this.SqlPipe = sqlPipe;
}
}
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:
public void ConfigureServices(IServiceCollection services)
{
const string ConnString = "Server=db.database.windows.net;Database=TodoDb;
User Id=usr;Password=pwd";
services.AddTransient<IQueryPipe>( _=> new QueryPipe(new SqlConnection(ConnString)));
services.AddTransient<ICommand>( _=> new Command(new SqlConnection(ConnString)));
services.AddMvc();
}
Here, I’m adding transient services with interfaces, IQueryPipe and ICommand, 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 a mandatory approach, you can use any other dependency injection framework (ninject or autofac) or initialize these objects using any other method. You can even directly initialize Pipe/Command objects:
IQueryPipe SqlPipe = new QueryPipe(new SqlConnection( "Connection string ")));
ICommand SqlCommand = new Command(new SqlConnection("Connection string ")));
Note: If you are using .NET Core, you would need a package or assembly that has SqlConnection class.
Now we can start implementing CRUD methods of REST Web API service. QueryPipe has one method called Stream that streams results of SQL Query into Output stream. It has two parameters:
- SQL command text (
string) or SQL command object (SqlCommand) that will be execute OutputStream where the result of executed SQL query will be flushed
Example of usage of SqlPipe class is shown in the following code:
SqlPipe.Stream("SELECT * FROM sys.tables FOR JSON PATH", Response.Body);
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:
[HttpGet]
public async Task Get()
{
await SqlPipe.Stream("select * from Todo FOR JSON PATH", Response.Body, "[]");
}
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. The third parameter in Stream method defines what should be returned if there is no returned data – in our 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:
[HttpGet("{id}")]
public async Task Get(int id)
{
var cmd = new SqlCommand("select * from Todo where Id = @id FOR JSON PATH,
WITHOUT_ARRAY_WRAPPER");
cmd.Parameters.AddWithValue("id", id);
await SqlPipe.Stream(cmd, Response.Body, "{}");
}
This is also an async method that will stream results of SQL query into Response.Body. Client that calls /api/Todo/1 will see result of SQL query with FOR JSON clause:
The third parameter defines what should be returned if there is no returned data – in this case, empty object. Example of response is shown in the following figure:
Ok, now we have implemented the required GET methods, so we will proceed with methods that update data.
Inserting New Record Into Todo Table (POST)
In order to add new Todo item, I need a method that reacts on POST request:
[HttpPost]
public async Task Post()
{
string todo = new StreamReader(Request.Body).ReadToEnd();
var cmd = new SqlCommand(
@"insert into Todo
select *
from OPENJSON(@todo)
WITH( Title nvarchar(30), Description nvarchar(4000), Completed bit, TargetDate datetime2)");
cmd.Parameters.AddWithValue("todo", todo);
await SqlCommand.ExecuteNonQuery(cmd);
}
You can notice that I just copied OPENJSON query from the 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:
[HttpPut("{id}")]
public async Task Put(int id)
{
string todo = new StreamReader(Request.Body).ReadToEnd();
var cmd = new SqlCommand(
@"update Todo
set Title = json.Title,
Description = json.Description,
Completed = json.completed,
TargetDate = json.TargetDate
from OPENJSON( @todo )
WITH( Title nvarchar(30), Description nvarchar(4000),
Completed bit, TargetDate datetime2) AS json
where Id = @id");
cmd.Parameters.AddWithValue("id", id);
cmd.Parameters.AddWithValue("todo", todo);
await SqlCommand.ExecuteNonQuery(cmd);
}
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 a 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:
[HttpPatch]
public async Task Patch(int id)
{
string todo = new StreamReader(Request.Body).ReadToEnd();
var cmd = new SqlCommand(
@"
update Todo
set Title = ISNULL(json.Title, Title),
Description = ISNULL(json.Description, Description),
Completed = ISNULL(json.Completed, Completed),
TargetDate = ISNULL(json.TargetDate, TargetDate)
from OPENJSON(@todo)
WITH( Title nvarchar(30), Description nvarchar(4000),
Completed bit, TargetDate datetime2) AS json
where Id = @id
");
cmd.Parameters.AddWithValue("id", id);
cmd.Parameters.AddWithValue("todo", todo);
await SqlCommand.ExecuteNonQuery(cmd);
}
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 if 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:
[HttpDelete("{id}")]
public async Task Delete(int id)
{
var cmd = new SqlCommand(@"delete Todo where Id = @id");
cmd.Parameters.AddWithValue("id", id);
await SqlCommand.ExecuteNonQuery(cmd);
}
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 expose few tables from your database, this might be a 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 the body of controller because I want a simple example. In practice, you would move this code in a 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):
CREATE PROCEDURE dbo.InsertTodo(@TodoJson NVARCHAR(MAX))
AS BEGIN
insert into Todo
select *
from OPENJSON(@todo)
WITH( Title nvarchar(30), Description nvarchar(4000),
Completed bit, TargetDate datetime2)
END
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.
- 14th June, 2016: Initial version
