Click here to Skip to main content
13,554,611 members
Click here to Skip to main content
Add your own
alternative version

Stats

82.2K views
124 downloads
44 bookmarked
Posted 14 Jun 2016
Licenced CPOL

Building REST services with ASP.NET Core Web API and Azure SQL Database

, 8 Jul 2016
Rate this:
Please Sign up or sign in to vote.
In this article you can see how easily you can easily 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 content

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:

  1. GET that will return all rows in the table
  2. GET that will return single row with specified id
  3. POST that will create/insert new row in table
  4. PUT that will update all fields/column in the row specified by id
  5. PATCH that will update some fields in the table
  6. 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

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 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:

  1. 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.
  2. 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.

JSON in Azure SQL Database

Azure SQL Database provides following functionalities that handle JSON:

JSON in Azure SQL database

Main functionalities that we see on this figure are:

  1. JSON functions that can take a value from JSON text, update JSON text, check is JSON valid, etc.
  2. FOR JSON that formats results of SQL queries as JSON text
  3. OPENJSON 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 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 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 = 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)));

    // Add framework services.
    services.AddMvc();
}

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:

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:

  1. Sql command text (string) or Sql command object (SqlCommand) that will be execute
  2. 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:

// GET api/Todo
[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. 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:

// GET api/Todo/5
[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:

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 required GET methods, so we will proceed with methods that updates data.

Inserting new record into Todo table (POST)

In order to add new Todo item, I need a method that reacts on POST request:

// POST api/Todo
[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 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:

// PUT api/Todo/5
[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 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: 

// PATCH api/Todo
[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 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:

// DELETE api/Todo/5
[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 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):

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.

License

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

Share

About the Author

Jovan Popovic
Program Manager Microsoft
Serbia Serbia
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.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionCS06018 warning Pin
dejannk@gmail.com10-Apr-18 15:09
memberdejannk@gmail.com10-Apr-18 15:09 
QuestionReturn Body on PUT/POST Pin
Member 1100559519-Aug-17 11:36
memberMember 1100559519-Aug-17 11:36 
QuestionHow about authentication on this project? Pin
Member 1030693416-Jan-17 5:45
memberMember 1030693416-Jan-17 5:45 
GeneralSQL/XML ==> SQL/JSON Pin
lapsdba1-Jan-17 5:07
memberlapsdba1-Jan-17 5:07 
QuestionVery useful, I like to combine T-SQL and C#/.net Pin
lapsdba1-Jan-17 5:07
memberlapsdba1-Jan-17 5:07 
QuestionRe: some people have problem with ReadToEnd() Pin
Pablo123413-Nov-16 16:27
memberPablo123413-Nov-16 16:27 
QuestionBelgrade.SqlClient does it support UTF8 output? Pin
Mark Tin1-Oct-16 17:48
memberMark Tin1-Oct-16 17:48 
QuestionRe: Belgrade.SqlClient does it support UTF8 output? Pin
Jovan Popovic9-Dec-16 11:50
mvpJovan Popovic9-Dec-16 11:50 
PraiseGood Article - A new way to use SQL2016 (Json) + Web API (net core) Pin
Mark Tin30-Sep-16 23:20
memberMark Tin30-Sep-16 23:20 
QuestionPlease You help me with this error. Response.Body Pin
Dannover Arroyave M.16-Aug-16 12:16
memberDannover Arroyave M.16-Aug-16 12:16 
QuestionVery Nice Pin
Salome Nunez Marquez26-Jul-16 8:19
memberSalome Nunez Marquez26-Jul-16 8:19 
QuestionNot what it said on the tin Pin
Member 43662208-Jul-16 2:21
memberMember 43662208-Jul-16 2:21 
AnswerRe: Not what it said on the tin Pin
Jovan Popovic8-Jul-16 5:11
mvpJovan Popovic8-Jul-16 5:11 
PraiseWell written Pin
CS20118-Jul-16 1:32
professionalCS20118-Jul-16 1:32 
GeneralRe: Well written Pin
Jovan Popovic8-Jul-16 5:28
mvpJovan Popovic8-Jul-16 5:28 
PraiseGreat article! Pin
Milica Škorić30-Jun-16 2:29
professionalMilica Škorić30-Jun-16 2:29 
GeneralRe: Great article! Pin
Jovan Popovic8-Jul-16 5:29
mvpJovan Popovic8-Jul-16 5:29 
GeneralMy vote of 2 Pin
Fawad Raza28-Jun-16 5:38
memberFawad Raza28-Jun-16 5:38 
GeneralRe: My vote of 2 Pin
CS20118-Jul-16 1:31
professionalCS20118-Jul-16 1:31 
GeneralRe: My vote of 2 Pin
Fawad Raza29-Sep-16 2:08
memberFawad Raza29-Sep-16 2:08 
GeneralRe: My vote of 2 Pin
CS201129-Sep-16 2:50
professionalCS201129-Sep-16 2:50 
GeneralRe: My vote of 2 Pin
Fawad Raza11-Jan-17 1:36
memberFawad Raza11-Jan-17 1:36 
GeneralRe: My vote of 2 Pin
CS201122-Jan-17 17:56
professionalCS201122-Jan-17 17:56 
GeneralRe: My vote of 2 Pin
CS20112-Nov-16 17:55
professionalCS20112-Nov-16 17:55 
GeneralRe: My vote of 2 Pin
Fawad Raza11-Jan-17 1:36
memberFawad Raza11-Jan-17 1: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02-2016 | 2.8.180515.1 | Last Updated 8 Jul 2016
Article Copyright 2016 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid