Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET / ASP.NET4.0

Use SQL to develop your REST API

Rate me:
Please Sign up or sign in to vote.
4.92/5 (21 votes)
20 Jan 2017CPOL6 min read 84.2K   3.4K   28   32
Fast and simple REST development for SQL devs

Introduction

A walk thru for creating a Visual Studio 2015 solution for a WebApi using TSQL Stored Procedures

Background

If you have a SQL Server Database and want mobile apps to work with your data, you need an API.

With Microsoft tooling that means writing a WebApi application. Typically you develop your API in C# writing code using Linq and Entity Framework. You structure the application with datamodels and repositories.

I want to show you a different aproach. Develop a WebApi backend using Stored Procedures.

I will use my own open source nuget package apirIO

REST, Resources and HTTP

REST is about resources. A resource is an object such as a product or a list of products.

HTTP is used to manipulate resources with words such as GET, PUT, POST, DELETE.

Verb URI Description
GET http://myserver/products Retrieve an array of products
GET http://myserver/products/1 Retrieve product with ID=1
PUT http://myserver/products/1 Update product 1 with values in body
POST http://myserver/products Add product
DELETE http://myserver/products/99  Remove product

Resources and SQL

Using SQL we develop procedures to Create, Read, Update and DELETE products.

The HTTP verbs we need are POST, GET, PUT, DELETE

By convention the procedure is prefixed with API, then the resource name and then the HTTP word. Fo the sample  products Resource we need to implement these stored procedures:

SQL
CREATE PROCEDURE API_Products_POST(@Name VARCHAR(100))
CREATE PROCEDURE API_Products_GET(@ID int = NULL)
CREATE PROCEDURE API_Products_PUT(@ID,@Name VARCHAR(100))
CREATE PROCEDURE API_Products_DELETE(@ID int)

The sample table

First create a database then the products sample table.

SQL
CREATE TABLE Products
(
    ProductID int IDENTITY(1,1) PRIMARY KEY,
    ProductName varchar(100) NOT NULL
)
GO
INSERT INTO Products(ProductName) VALUES ('Widget 1')
GO
INSERT INTO Products(ProductName) VALUES ('Widget 2')
GO

Reading and defining a resource.

In the GET procedure we return one or a list of resources. This procedure also defines the structure of the resurce.

SQL
CREATE PROCEDURE API_Products_Get (@ID int = NULL) 
AS
    SELECT ProductId, ProductName 
        FROM Products 
        WHERE ProductID = @ID OR  @ID IS NULL

Updating a resource

An update procedure will respond to the Put HTTP verb. In this example we want to be able to change the name of the product.

SQL
CREATE PROCEDURE API_Products_Put(@ID int, @ProductName VARCHAR(100))
AS
    UPDATE Products SET ProductName = @ProductName 
    WHERE ProductID = @ID

This may be a good place to introduce some error handling. We see that the two parameters have no default values. A runtime error will occur if is no value is set.

We could check for a valid @ID

SQL
ALTER PROCEDURE API_Products_Put(@ID int, @ProductName VARCHAR(100))
AS
    IF NOT EXISTS(SELECT ProductID FROM Products 
    WHERE ProductID = @ID) 
    BEGIN
            RAISERROR('Unknown Product',1,1)
            RETURN 400
    END
    UPDATE Products SET ProductName = @ProductName 
    WHERE ProductID = @ID
    RETURN 200 –- OK

The RAISERROR uses a severity level of 1 which means a warning in TSQL. The execution continues and returns 400 which will be the HTTP return code. The message “Unknown Product” is returned to the caller as a message. If the UPDATE was successful a 200 is returned.

Note: If you leave out the RETURN 200 a zero will be returned which will be translated to 200 before returning to the caller.

Creating a resource

The POST procedure may be simply

SQL
CREATE PROCEDURE API_Products_Post(@ProductName VARCHAR(100))
AS
    INSERT INTO Products(ProductName) VALUES(@ProductName)

It is useful to be able to return the ID of the newly created row. We can do this like:

SQL
CREATE PROCEDURE API_Products_Post(
@ProductName VARCHAR(100), @NewId int OUTPUT)
AS
    INSERT INTO Products(ProductName) VALUES(@ProductName)
    SET @NewId  = @@IDENTITY
    RETURN 200

Apir constructs the URI of the new Product and returns it in the HTTP header to the client.

Deleting a resource

Finally the delete procedure may be simple:

CREATE PROCEDURE API_Products_Delete(@ID int)
AS
    DELETE FROM Products WHERE ProductID = @ID

Creating the WebApi site

With the four Stored Procedures we have all the code needed for the Product resource.

Lets use Visual Studio 2015 to create the Site.

Using the standard ASP.NET Web Application template we create SqlWebApi

SelectTemplate

Start a simple WebApi project

SelectWebApi

When the project is ready, from the tools menu start package manager console

Install-Package ApirIO

Add a connection to you database with connection string in web.config

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=localhost\sqlexpress;Initial Catalog=myDatabase;Integrated Security=True" />
  </connectionStrings>

Start you WebApi

How many lines of c# code have we written? None, and we are ready to test the API

Just hit F5 to start a debug run of the project.

You will get the standard HomePage. If you click on API in the heading, the ApiControllers are shown. The example ValuesController are there and the new products.

Try running the API_Products_Get procedure from your browser by entering the URL

http://localhost:50608/api/products

You will get a list of products in XML or JSON depending on your browser.

Swagger test and documentation harness

Swashbuckle is a great nuget package that will give you a GUI for testing the API

From the tools menu start package manager console

Install-Package Swashbuckle -Version 5.5.3

Restart you project and open the URL

http://localhost:50608/swagger

You have added the Swagger GUI for the API.

How ApirIO works

When the app starts ApirIO analyses your API* procedures and generates c# code. The generated controllers inherit from ASP.NET ApiControllers. 

The code is compiled and loaded into the project at runtime. The c# source file is located in the App_Data folder by default.

If ApirIO is unable to compile the generated c# code an error log, swaError.txt, is written to the same folder.

ApirIO uses ADO.NET for database access. The connection named "DefaultConnection" is used.

The code is generated each time you start the project. If you add stored procedures or changes parameters, you will need to restart the app.

Documenting the API

c# developers have documented code with XML comments for years. These comments are used by tools such as Visual Studio and also Swashbuckle. ApirIO lets you comment the stored procedures and moves any comments to the generated c# code. It is a great for API documentation since it can be used in Swagger and lives with the database.

Lets change the procedure for inserting new products to make it more production ready.

SQL
--- <summary> 
--- Add a new product to the database
--- </summary>  
--- <remarks> A link to the new product is returned  </remarks> 
--- <response code="201">OK</response>
--- <response code="521">Bad productname</response>
--- <response code="522">Product with name exists</response>
CREATE PROCEDURE API_Products_Post(
        @ProductName VARCHAR(100), 
        @NewId int OUTPUT)
AS
    IF (@ProductName IS NULL OR LEN(@ProductName) < 2) 
    BEGIN
            RAISERROR('Bad product name',1,1)
            RETURN 521
    END
    IF EXISTS (SELECT ProductId FROM Products WHERE ProductName = @ProductName)
    BEGIN
            RAISERROR('Product with that name already exists',1,1)
            RETURN 522
    END

    INSERT INTO Products(ProductName) VALUES(@ProductName)
    SET @NewId  = @@IDENTITY
    RETURN 201

One code change is needed for adding XML comments to Swagger. In the App_Start\SwaggerConfig.cs file at line 100 insert the line:

//c.IncludeXmlComments(GetXmlCommentsPath());
c.IncludeXmlComments(AppDomain.CurrentDomain.GetData("DataDirectory").ToString() + "\\xmlComments.xml");

You will also need using "System" for AppDomain.

Try adding products. You will get a 201 when you have successfully added a product. Watch the Response Headers. It will have

"location": "http://localhost:50608/api/products/2",

which is the URI of the created resource.

Summary

Whith a few simple procedures we have created a REST Api for the products table.

In production you should create an API user and limit access to be able to execute the API* procedures.

The resulting API gets all the goodies of the ASP.NET ApiController class 

You did not have to write a single line of c#

The Asp.Net site created will work for any database with API_* procedures. For another Api project just copy the web site and change the connection string.

Included in the download is the sample project with a loadable database. App_Data\ApirTest.sql contains a script to build the table and procedures. Try running it and then goto http://localhost:60361/swagger 

Points of Interest

I find that writing and maintaining backend logic is easier in TSQL than C#. Even after several Entity Framework projects I find TSQL simpler and quicker. TSQL procedures can be unit tested. They live with the database and do not get out of sync with the database as easy. 

Stored Procedures have been available on the SQL Server for a very long time. They have hardly changed. Microsoft tools for database access are frequently updated.

For a long time I believed new developers should learn c#. Now, javascript and SQL may be all you need for mobile and web development.

 

 

License

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


Written By
Software Developer
Norway Norway
My name is Ole Oren

I am a developer living in Oslo, Norway. I have been working with ASP.NET since version 1.1. Currently I develop mobile business apps using Ionic, Angular and SQL Server.


Comments and Discussions

 
QuestionHow to add Security Pin
Member 937918730-Apr-20 1:08
Member 937918730-Apr-20 1:08 
QuestionResponse with child nodes(1:m) Pin
Member 105758778-Jan-19 23:25
Member 105758778-Jan-19 23:25 
AnswerSolving problems with ApirIO Error Pin
Ckrotky14-Oct-18 4:10
Ckrotky14-Oct-18 4:10 
QuestionCompile Error Pin
Member 139409057-Aug-18 22:43
Member 139409057-Aug-18 22:43 
AnswerRe: Compile Error Pin
Member 1104720515-Sep-18 7:31
Member 1104720515-Sep-18 7:31 
QuestionAdd Security Pin
Member 1389288423-Jul-18 9:06
Member 1389288423-Jul-18 9:06 
QuestionPOST with multiple parameters in the request body Pin
Member 1385642511-Jul-18 8:16
Member 1385642511-Jul-18 8:16 
QuestionError when debugging Pin
Member 138968793-Jul-18 6:27
Member 138968793-Jul-18 6:27 
AnswerRe: Error when debugging Pin
Member 138968794-Jul-18 7:07
Member 138968794-Jul-18 7:07 
QuestionApir.exe Pin
apiegoku2-Jul-18 7:14
apiegoku2-Jul-18 7:14 
QuestionRefresh SPs? Pin
Member 1369729627-Feb-18 0:43
Member 1369729627-Feb-18 0:43 
AnswerRe: Refresh SPs? Pin
dubstylee6-Apr-18 11:23
dubstylee6-Apr-18 11:23 
QuestionAnother Compile Error Pin
Member 1342390122-Sep-17 5:06
Member 1342390122-Sep-17 5:06 
AnswerRe: Another Compile Error Pin
Ole Oren 14-Apr-18 23:41
Ole Oren 14-Apr-18 23:41 
QuestionHow to package and deploy? Pin
Member 1328423328-Jun-17 22:15
Member 1328423328-Jun-17 22:15 
AnswerRe: How to package and deploy? Pin
Member 1122202721-Nov-17 8:11
Member 1122202721-Nov-17 8:11 
GeneralRe: How to package and deploy? Pin
Member 1122202721-Nov-17 8:20
Member 1122202721-Nov-17 8:20 
Questionpretty cool Pin
Sacha Barber1-Feb-17 9:11
Sacha Barber1-Feb-17 9:11 
GeneralGood job! Pin
HaGever25-Jan-17 2:36
HaGever25-Jan-17 2:36 
Thanks for the inspiration!
QuestionError during compilation Pin
igaviotis24-Jan-17 21:39
professionaligaviotis24-Jan-17 21:39 
AnswerRe: Error during compilation Pin
Ole Oren 25-Jan-17 4:27
Ole Oren 25-Jan-17 4:27 
GeneralRe: Error during compilation Pin
igaviotis26-Jan-17 22:53
professionaligaviotis26-Jan-17 22:53 
GeneralRe: Error during compilation Pin
Ole Oren 26-Jan-17 23:28
Ole Oren 26-Jan-17 23:28 
GeneralRe: Error during compilation Pin
igaviotis1-Feb-17 19:17
professionaligaviotis1-Feb-17 19:17 
GeneralRe: Error during compilation Pin
Andy R Campbell1-Apr-20 14:59
Andy R Campbell1-Apr-20 14:59 

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.