MsSql.RestApi - The Easiest Way to Build the ASP.NET REST API

Table of Contents

• Introduction
• Background
• Setup
• Generic Services & OData
• Advanced OData Services
• Custom SQL Services
• JQuery DataTables API
• Microservices & Azure Functions

Introduction

Sometimes, it is surprising how much time we spend on creating various REST APIs although this is the problem that we are solving for a lot of years. If you are a classic back-end developer, you have probably created a large number of REST APIs for different font-end applications and components. Although REST API should be a simple function in most of the cases, you probably had to create or modify several layers, create new Model or ViewModel classes, new LINQ/SQL queries for even the simplest REST API. It is strange that we still don’t have a technique that can enable us to more easily create REST API.

Quote:

MsSql.RestApi is a library that can help you to speed-up the process of building powerful REST API with the minimal amount of code.

In this article, you will see something that I believe is the best and the easiest way to create a REST API. MsSql.RestApi is a free, open-source ASP.NET library that enables you to create almost any REST API that you want with the minimal effort. The key features of this library are:

• Two lines of code required to create powerful OData services
• Easy integration with JQuery DataTables working in server-side mode
• Single command required to create API that executes any T-SQL query
• Minimal amount of code required to build micro-services using Azure Functions

The only catch is the version of SQL Server – you need to run your database on SQL Server 2016 (or higher version) or Azure SQL Database (which is the latest version of SQL Server). MsSql.RestApi library works only with these versions of MsSql Database Engine.

If you are interested, read the following sections because this approach might change the way in which you are creating REST API.

Background

Imagine a classic web development architecture with the separation of front-end and back-end code where front-end code, which is written in JavaScript (vanilla JavaScript, Angular, React, JQuery, etc.), calls some back-end REST API endpoints.

If you are working on fairly large system, you would probably need to implement a lot of web services that return information about the entities/objects using various criteria. As an example, you might end up with the following set of services:

• GetCustomerByID, GetCustomerByName, GetCustomersByRegion, GetCustomersByStatus
• GetOrderByID, GetOrdersByCustomer, GetOrdersByStatus
• GetProductByID, GetProductsByType, GetProductsInPriceRange

Once you create them, you would probably need to extend them even further when someone asks you to implement pagination for some end-points by adding skip= and take= parameters, then probably to combine the services like GetCustomersByRegion, GetCustomersByStatus to get customers by region and/or status, so you would need either to extend first or second service or create a new endpoint.

Another approach would be to create one endpoint per concept (for example, database table) and add parameters that specify what you should return:

• /GetCustomers?CustomerID=…&Region=…..&Status=…&OrderBy=
• /GetOrders?OrderID=…&CustomerID=…&Status=…
• /GetProducts?ProductID=…&Type=….&MinPrice=…&MaxPrice=….

In this approach, instead of creating a new service for every new request, you can just add a parameter. Seems better, but still not perfect. Instead of a lot of services, you are ending up with a lot of parameters and you must know how every parameter works with each other. You need to ensure that every combination of parameters works fine with each other without some unexpected results (for example, what happens when a client calls GetCustomers with CustomerID=17&Status=4 if customer with ID=17 doesn’t have status 4? Would it be empty result or error?). In this case, you would need to define some semantic, so the clients can know what happens when they combine parameters (and beware that they can combine everything if they have an opportunity).

In both cases, you will have a problem with some custom services that logically don’t belong to either entry like average sales by region or customer where “sales” is complex calculation that is derived from several different entities. In this case, you would need to create a new end-point.

Whatever way you choose, you would end up with a lot of endpoints or a lot of parameters. After some time, this might become a maintenance hell – you would probably not know what endpoints are used, whether there are some duplicates, etc.

As a back-end developer, you might become reluctant to add new services or parameters because the existing code becomes un-maintainable. As a front-end developer, you might experience a situation where the backend developer either doesn’t create parameters you need or it just takes them a lot of time to do it.

This is the time where you might think about changing the approach and try to generalize and standardize your API.

MsSql.RestApi is a free, open-source ASP.NET library that can help you to be more agile and more quickly develop your REST API services that can serve a broad range of front-end requests. The key benefits of this open-source library are:

• You need to write a few lines of code to create REST API that returns any data with well-defined semantic.
• Built-in OData service implementation with the most important parameters.
• You can easily create custom services.
• Built-in support for JQuery DataTables plugin that enables you to create rich client-side tables.

If you think that this would be useful in your project, let’s take a look at the details.

Setup

In order to add MsSql.RestApi library to your project, you would need to get this library from NuGet using the following command line:

PM> Install-Package MsSql.RestApi

In you are using .NET Core projects, you will need to initialize data access components in Startup class (ConfigureServices method), as it is shown in the following example:

using MsSql.RestApi;

namespace MyApp {
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services) {
            services.AddSqlClient(Configuration["ConnectionStrings:MyConnection"]);
        }
    }
}

Assumption in this example is that your connection string is stored in appsettings.config file under key MyConnection. You can change this part and take connection string from any other location.

Technically, that’s it. You don't need to create some models and classes. This code will configure everything.

Now let’s see how to create some REST API using this package.

Generic Services & OData

Most of the REST API services that you need to create have generic functionalities. You need to provide data about some entity or table and enable clients to filter results set, specify what properties should be shown, sort results, implement some pagination, and repeat this for most of your tables in database. If you don’t want to invent your own protocol or specification, you can use some of the existing protocols such as OAsis OData or Facebook GraphQL.

Both OData and GraphQL enable you to specify some kind of “query” over HTTP where you tell your back-end service what should be returned. The differences between OData and GraphQL are shown in the following example:

http://..../human?
$select=name,height
$filter=id eq 1000
$orderby=
$take=10
$skip=30

{
  human(id: "1000") {
    name
    height
  }
}

In OData, you can specify what records you want to retrieve in SQL-like fashion via URL parameters. There is $select parameter where you specify what properties you want to fetch, $filter parameter where you can specify a condition that the returned records must satisfy, $orderby that defines how the results should be sorted, and $skip/$take that can be used for pagination. In GraphQL, you define the structure of the document that you want to fetch with some conditions injected into the body of document (see id:”1000” in the human object), and the back-end service would return the matching data.

Although OData is an older protocol, I still prefer this approach over GraphQL. I like the fact that my Front-End code can specify exactly what is needed using the rich query/expression specification, as it is shown in the following example:

http://......./StockItems
$select=UnitPrice,TaxRate,ColorName,Size&
$filter=UnitPrice gt 10 and UnitPrice lt 20&
$orderBy=UnitPrice asc&
$skip=20&
$take=10

This API call will fetch information about stock items where unit price is between 10 and 20, order it by unit price, and return the columns' unit price, tax rate, color name, and size. In addition, it will paginate results and return the third 10-item page.

In theory, you can do the same thing with GraphQL, https://www.howtographql.com/graphql-js/8-filtering-pagination-and-sorting/ but OData way is slightly easier if you want to create something like SQL-language over the REST. I like to declare what should be returned as results in parameters that look like SQL language. Now when we know what OData is, let’s see how to implement the service.

Implementing OData Services using MsSql.RestApi

You can easily implement OData service using MsSql.RestApi library to expose your database table data. Imagine that we have a classic database driven development model where tables are already in the database (ideal for database-first development model – but it would also work if you have generated tables using some code-first generator), and we need to implement REST API for them.

Let’s imagine that we have People table with the columns “name,surname,address,town” and we want to create REST API that enables full-query capabilities via URL on this table. We would need to create ASP.NET controller like the one in the following code sample:

using Belgrade.SqlClient;
using System.Threading.Tasks;
 
namespace WideWorldImporters.Controllers
{
    public class ODataController : Controller
    {
        ICommand db = null;
 
        public ODataController(ICommand db)
        {
            this.db = db;
        }
 
        public async Task People()
        {
            TableSpec spec =
                         new TableSpec(schema: "dbo", table: "People", 
                                       columns: "name,surname,address,town");
            await this
                  .OData(spec)
                  .Process(this.db);
        }
    }
}

Setup of MsSql.RestApi will inject services ICommand, IQueryPipe, and IQueryMaper that can be used to query database. This library uses Belgrade SqlClient library to access data in the SQL Server or Azure SQL database. In the example above, I have used command service and standard dependency injection to initialize the service in the controller, and then I’m creating People method that processes OData requests (i.e., read $select, $filter, $orderby parameters), generates the SQL query based on the input parameters, executes that SQL query against the database, and returns the results to the client. The only thing I need is a specification of the table structure with database schema, table name, and the list of columns. OData and Process methods will parse incoming parameters from HTTP request, transform them into SQL query and execute it.

When you create this action, you would be able get the information about people using the OData queries:

http://..../OData/People?$select=name, surname&$orderby=name&$skip=30&$take=10

This single REST endpoint with two statements in the action method will enable your front-end code to fetch the data using various criteria without the need to create new endpoints, new SQL or LINQ queries for other functionalities. With two C# statements, you are getting fully functional service that accesses your database table.

Singleton Result

One of the most common cases that you need to implement is returning single result from a table by id (primary key). OData enables you to do this by specifying a key value after the entity name:

http://..../OData/People(17)

In order to implement fetching single result by key, we first need to register a route in MVC framework that will map this kind of request URL pattern to OData controller – something like:

app.UseMvc(routes =>
{
    routes.MapRoute(
        "odata-single",
        "OData/{action}({id})",
        new { controller = "OData" }
    );
});

Any URL token placed in braces ( ) will be provided to the action methods as is parameter. Now we need to add an optional id parameter to the controller action method that serves OData request, and provide this id to OData method:

public async Task People(int? id)
{
    TableSpec spec =
                 new TableSpec(schema: "dbo", table: "People", 

                               columns: "name,surname,address,town",
                               primaryKey: "PersonID");
 
    await this
          .OData(spec, id: id)
          .Process(this.db);
}

Note one additional change in the table spec. Since OData method will filter record by primary key, we need to specify which column in the table is the primary key. MsSql.RestApi will use this column to filter results.

Adding More OData Services

If you need to create REST endpoints for other tables, you can just use the same pattern and create new controller methods that provide data for StockGroups, StockItems, Orders, Invoices, etc.

public async Task StockGroups()
{
    var spec = new TableSpec("WebApi","StockGroups", "StockGroupID,StockGroupName");

    await this.OData(spec).Process(this.db);
}

public async Task StockItems()
{
    var spec = new TableSpec
               ("WebApi", "StockItems", "StockItemID,StockItemName,SupplierName,
     SupplierReference,ColorName,OuterPackage,
                       UnitPackage,Brand,Size,LeadTimeDays,QuantityPerOuter,
     IsChillerStock,Barcode,TaxRate,UnitPrice,
                    RecommendedRetailPrice,TypicalWeightPerUnit,
     MarketingComments,InternalComments,CustomFields,QuantityOnHand,BinLocation,
     LastStocktakeQuantity,LastCostPrice,ReorderLevel,
                  TargetStockLevel,SupplierID,ColorID,
     UnitPackageID,OuterPackageID");

    await this.OData(spec).Process(this.db);
}

public async Task Invoices()
{
    var spec = new TableSpec("WebApi", "Invoices", "InvoiceID,InvoiceDate,
    CustomerPurchaseOrderNumber,IsCreditNote,
                                TotalDryItems,TotalChillerItems,DeliveryRun,
    RunPosition,ReturnedDeliveryData,ConfirmedDeliveryTime,
                                     ConfirmedReceivedBy,CustomerName,
    SalesPersonName,ContactName,ContactPhone,ContactEmail,
                                SalesPersonEmail,DeliveryMethodName,
    CustomerID,OrderID,DeliveryMethodID,ContactPersonID,
                       AccountsPersonID,SalespersonPersonID,
    PackedByPersonID");

    await this.OData(spec).Process(this.db);
}

public async Task SalesOrders()
{
   var spec = new TableSpec(schema: "WebApi", table: "SalesOrders", 
   columns: "OrderID,OrderDate,CustomerPurchaseOrderNumber,ExpectedDeliveryDate,
   PickingCompletedWhen,CustomerID,CustomerName,PhoneNumber,FaxNumber,WebsiteURL,
   DeliveryLocation,SalesPerson,SalesPersonPhone,SalesPersonEmail");
 
   await this.OData(spec).Process(this.db);
}

As you can see, you need to repeat a small method with two statements – one that defines the table structure, and the other that processes OData requests. No data access, no queries (SQL or LINQ), no additional tests. Two lines of code per service would provide you most of the functionalities that your front-end code would need.

Since the code for different services is very similar, you can easily generate a large number of OData services for all tables in your database. You just need to define schema, table name, and the column names that should be exposed as OData service.

I’m using T4 templates to generate the code for larger databases:

public partial class ODataController: Controller
{
    ICommand db = null;

    public ODataController(ICommand sqlCommandService)
    {
       this.db = sqlCommandService;
    }

<# foreach(var t in config) {#>

    [HttpGet]
    public async Task <#= t.Table #>()
    {
       var spec = new TableSpec("<#= t.Schema #>",
       "<#= t.Table #>", "<#= t.Columns #>");
       await this.OData(spec).Process(this.db);
    }

<# } #>

}

In this template, I assume that config is an array of objects with properties Schema that represent the database schema where table is placed, Table that contains the name of the table, and Columns that contains the comma-separated list of table columns.

The only thing that you need to do is to define the names and schemas of the tables with the columns in some config array, and this T4 template will automatically generate all necessary OData endpoints. This way, we can easily create a large number of OData services for very complex databases.

Advanced OData Services

Standard OData parameters would probably enable you to implement 80% of your requests needed for the front-end code. Most of the requests can be successfully described using $select, $filter, $orderby, and $skip/$take parameters and rich expressions. However, in some cases, you would need more, such as:

1. You might need to include some additional data from the related tables.
2. If you are creating reports or charts, you might need to create analytic queries that group, aggregate and calculate results.

Fortunately, OData services provide these functionalities by using two parameters, $extend and $apply.

$extend Parameter

By default, OData services expose a single table as flat data, but in some cases, you would need to fetch some additional information. As an example, if you are fetching customer data, you might need to get all customer invoices and customer orders. In theory, you could get customer by ID and then send the separate requests that filter orders and invoices by customer id; however, it would be better to get everything from the same call (similar to Include() method in C#/LINQ). OData enables you to specify what related data that should be included in the fetched data using $expand parameter. $expand parameter is a list of related entities that should be fetched together with the main entity, as shown in the following example:

• http://localhost:53026/Customers?$filter=CustomerId eq 1&$expand=Orders,Invoices

This OData query will get the customer with ID 1 and expand the result by including all Orders and Invoices that belong to the customer.

In order to implement OData service that can be extended with related information using MsSql.RestApi library, you would need to specify the relations using AddRelatedTable method that specify that primary entity (Customer) has related tables Orders and Invoices:

[HttpGet("Customers")]
public async Task Customers()
{
    var spec = new TableSpec(schema: "Application", table: "People", columns: "PersonID,
               FullName,PhoneNumber,FaxNumber,EmailAddress,ValidTo")
        .AddRelatedTable("Orders", "Sales", "Orders", "Application.People.PersonID = 
              Sales.Orders.CustomerID", "OrderID,OrderDate,ExpectedDeliveryDate,Comments")
        .AddRelatedTable("Invoices", "Sales", "Invoices", "Application.People.PersonID = 
              Sales.Invoices.CustomerID", "InvoiceID,InvoiceDate,IsCreditNote,Comments");

    await this
            .OData(spec)
            .Process(this.db);
}

Additional information that you need to provide is the query criterion that will be used to join the primary entity with the related entities. That’s everything you need to specify in order to be able to apply $extend parameter.

$extend parameter also enables you to create advanced queries against the included sub-entities. As an example, you can take just top 2 Orders and top 3 Invoices for each customer in the results set, by specifying $top parameter for each extended entity:

http://localhost:53026/Customers(17)?$expand=Orders($top=2),Invoices($top=3)

You can add rich expressions in $filter parameter to define very specific criteria to return related entities.

http://localhost:53026/Customers(17)?$expand=Orders($orderby=OrderDate asc, 
                      $filter=OrderID lt 12000 or (month(OrderDate) gt 4),$top=2)

$extend parameter might be a very helpful tool that enables you to fetch the related entities with the rich syntax for specifying properties of the entity.

$apply

Another functionality that you would probably need are groupings and aggregations. If you have some reports like pie charts, you would probably need to get some aggregated and pre-calculated data.

OData services have $apply parameter where you can specify different aggregations, such as:

• $apply=aggregate(PersonID with min as Minimum)
• $apply=groupby((FullName), aggregate(PersonID with sum as Total))
• $apply=groupby((PhoneNumber), aggregate(PersonID with sum as Total), aggregate(PersonID with min as Minimum))
• $apply=groupby((PhoneNumber, FaxNumber), aggregate(PersonID with sum as Total), aggregate(PersonID with min as Minimum))

The first aggregate returns minimum PersonID from the collection. Part “as Minimum” specifies what should be the name of the property that will be returned. The second one returns sum of PersonID grouped by FullName. The equivalent SQL query would be:

SELECT sum(PersonID) as Total FROM People GROUP BY FullName

You can do most of the things that you can use in classic SQL group by queries and get the data for various reports.

Consuming OData services

Having nice standardized OData service is good, but how to use them? Since OData services are plain Http services, you can just add the parameters in the URL and get whatever you need. In the following example is shown one JQuery ajax call that sends request to OData Customers endpoint and passes results to callback:

$.ajax('/odata/Customers?$apply=groupby((PostalCity),
  aggregate(CustomerID with sum as Total))&$orderby=CustomerID with sum desc&$top=5',
        { dataType: 'json' })
    .done(result => {
        
    });

Since OData is standardized protocol, you might find a number of client API libraries such as o.js or Apache Olingo that enable you to call OData service using their interfaces. An example of the code that calls OData service using o.js library (the library that I'm using to access OData services) is shown below:

o('http://services.odata.org/V4/OData/OData.svc/Products').take(5).skip(2).get( data => {
  console.log(data); //An array of 5 products skipped by 2
}, status => {
  console.error(status); // error with status
});

Another option is to create swagger/OpenAPI specification of the OData service and let swagger to generate various clients. Building swagger spec should be easy task since you have six fixed parameters per each end-point. If you have a massive number of endpoints, you can always use T4 template similar to the one that I used to massively generate OData methods for different entities.

<#@ output extension=".yaml"#>
<#@ template language="C#" hostspecific="True" #>
swagger: "2.0"
info:
  description: "OData service exposing information from WideWorldImporters database."
  version: "1.0.0"
  title: "Wide World Importers"
  termsOfService: "http://swagger.io/terms/"
  contact:
    email: "jovan@acme.com"
  license:
    name: "Apache 2.0"
    url: "http://www.apache.org/licenses/LICENSE-2.0.html"
host: "localhost:64958"
basePath: "/OData"
tags:
<# foreach(var t in config) {#>
<# if(string.IsNullOrEmpty(t.ODataColumns)) continue; #>
- name: "<#= t.Table #>"
  description: "Information about <#= t.Table #>"
<# } #>
schemes:
- "https"
- "http"
paths:
<# foreach(var t in config) {#>
<# if(string.IsNullOrEmpty(t.ODataColumns)) continue; #>
  /<#= t.Table #>:
    get:
      tags:
      - "<#= t.Table #>"
      summary: "Find information about <#= t.Table #>"
      description: "Multiple status values can be provided 
                    with comma separated strings"
      operationId: "<#= t.Table #>"
      produces:
      - "application/json"
      parameters:
      - name: "$select"
        in: "query"
        description: "Selecting the properties that should be returned by service"
        required: false
        type: "array"
        items:
          type: "string"
          enum:<# foreach(var c in t.ODataColumns.Split(',')) {#>
            - "<#= c #>"<# } #>
        collectionFormat: "multi"
      - name: "$orderby"
        in: "query"
        description: "Ordering results by properties"
        required: false
        type: "array"
        items:
          type: "string"
          enum:<# foreach(var c in t.ODataColumns.Split(',')) {#>
            - "<#= c #>"
            - "<#= c #> asc"
            - "<#= c #> desc"<# } #>
        collectionFormat: "multi"
      - name: "$top"
        in: "query"
        description: "Selecting the properties that should be returned by service"
        required: false
        type: "integer"
      - name: "$skip"
        in: "query"
        description: "Selecting the properties that should be returned by service"
        required: false
        type: "integer"
      - name: "$apply"
        in: "query"
        description: "aggregation function that should be applied on the results"
        required: false
        type: "string"
      - name: "$filter"
        in: "query"
        description: "Condition that returned items must satisfy"
        required: false
        type: "string"
      responses:
        200:
          description: "Provided information about <#= t.Table #>."
        400:
          description: "The OData request is not valid"
        500:
          description: "Cannot process request"
<# } #>

Similar to the previous template code that generated actions, assumption is that config is an array containing table, anc columns for each endpoint definition. Once your tempate generates the specification for each endpoint, you can generate client libraries that access your services using some swagger tool like https://editor.swagger.io/.

Custom SQL Services

If you really need some custom API that cannot be implemented either using standard $filter, $orderby, $select parameters, or even custom $apply and $extend parameters, you always have an option to create your own custom service.

If you have such a specific query or report, you would probably need to create SQL query that fetches the required data from the database. MsSql.RestApi enables you to easily provide the SQL query that should be executed. The results of the query can be easily returned to the caller (e.g., JavaScript code in the browser).

The following example shows how to implement REST API that executes complex T-SQL query and streams the query results directly into the response body:

public async Task Report1()
{
    await this.db.Sql(
@"select Color as [key], AVG( Price ) as value
from Product
group by Color
having AVG( Price ) > 20
FOR JSON PATH").Stream(Response.Body);
}

Note the FOR JSON clause that instructs SQL Database Engine to return information as JSON document instead for the tabular content. This is good for developing REST API, because you are getting the content that should be sent to the browser. It enables you to directly put JSON response from SQL Server to the web client without any models or Data transfer objects (DTO) that are used just temporary store the data before Newtonsoft JSON.Net serializes DTO object to JSON response that will be returned to the browser. The Command object enables you to provide any T-SQL query that will be executed, and the results will be streamed into the response body.

Note that part where I’m putting SQL aliases as [key] and as value. Without these aliases, the query would return an array with the properties names as columns such as Color. However, I’m using some front-end component such as D3 charts to display the data, I need to handle the fact that these components expect some specific format – in this case D# chart expects an array with the objects containing the properties key and value. Using the SQL aliases, I can easily generate the output format that my front-end component requires.

The following picture shows a pie chart created using D3 library that is populated with the response from the custom SQL Endpoint that returns key and value properties.

If you are a SQL expert or you have someone in your team who can write complex queries, you can just take any query, put FOR JSON clause and flush results to the client. My preference is to create T-SQL stored procedure and just put the procedure name in the query without mixing SQL and C# code – but you might use both approaches.

The following example shows how to create an API that returns some stock items using the criterion defined in parameters by calling a stored procedure that returns JSON as a result:

public async Task Search(string name, string tag, double? minPrice, double? maxPrice, 
int? stockItemGroup, int top)
{
    await this.db
        .Sql("EXEC WebApi.SearchForStockItems @Name, @Tag, @MinPrice, 
              @MaxPrice, @StockGroupID, @MaximumRowsToReturn")
        .Param("Name", DbType.String, name)
        .Param("Tag", DbType.String, tag)
        .Param("MinPrice", DbType.Decimal, minPrice)
        .Param("MaxPrice", DbType.Decimal, maxPrice)
        .Param("StockGroupID", DbType.Int32, stockItemGroup)
        .Param("MaximumRowsToReturn", DbType.Int32, 20)
        .Stream(Response.Body);
}

JQuery DataTable API

Another important functionality that you need to provide in almost every application is showing data in tables. This included filtering data in the table, pagination, sorting results by columns, changing the number of displayed items per page.

One of the best (free and open-source) components that I’m aware of for implementing the rich client-side functionalities on the tables is JQuery DataTables plugin. This plugin can be applied on a plain HTML table to add all necessary functionalities for pagination, sorting, and filtering rows in the table. If you use DataTables plugin, plain HTML table becomes something like this:

DataTables work in two modes:

1. Client-side mode where all rows are pre-loaded in the table (for example in <tbody>) and DataTables do all processing in client-side JavaScript code. This is a good choice for tables that don’t have a lot of data (up to few hundred rows).
2. Server-side mode where you return only the rows that should be currently shown in the table. Whenever a user changes some state in the table (go to the next page, change the column that should sort results) DataTables will send a new request to some API and fetch the new result that should be displayed instead of the current one. This mode is used when you have a lot of records that might be shown, and it would be slow and memory consuming to load everything in the browser.

Implementing server-side mode might be tricky because you need to have API that understands parameters sent by DataTables via some AJAX request, and that returns the result in the format that DataTables expects.

The good news is that MsSql.RestApi understands DataTables protocol. If you want to create an API that processes DataTables requests that fetches the data from Application.People table, and shows the columns FullName, EmailAddress, PhoneNumber, and FaxNumber, you need to add the following action in some controller:

public async Task Table()
{
    var spec = new TableSpec(schema: "Application", table: "People", 
               columns: "FullName,EmailAddress,PhoneNumber,FaxNumber");

    await this
            .Table(spec)
            .Process(this.db);
}

Once you define table specification (only specification of columns that you want to show – you don’t need to include all table data). Table() API will use this metadata to process DataTables requests.

Now, you just need to add a plain empty table that will be a template for showing the people in the page:

<link href="media/css/jquery.dataTables.css" rel="stylesheet" />

<table id="people" class="table table-striped table-bordered" cellspacing="0">
    <thead>
        <tr>
            <th>Name</th>
            <th>Email</th>
            <th>Phone</th>
            <th>Fax</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="media/js/jquery.dataTables.js"></script>

In order to load data from the end-point /People/Table into the empty HTML table, you should add the following code that initializes the HTML table with id “people”, set the server-side API that will provide the data, and define what columns will be shown:

$(() => {
        $("table#people")
            .DataTable({
                "ajax": "/People/Table",
                "serverSide": true,
                "columns": [
                    { data: "FullName" },
                    { data: "EmailAddress", defaultContent: "" },
                    { data: "PhoneNumber", defaultContent: "" },
                    { data: "FaxNumber", defaultContent: "" }                   
                ]
            });
});

As a result, you are getting a fully functional table that loads just the records that need to be displayed without any additional server-side code.

Every time a user performs some action in the table, DataTables will send a new AJAX request to the URL /People/Table with the parameters that describe the new state, and MsSql.RestApi will return only the data that should be displayed.

Building Microservices

The current trend is to break your application into smaller manageable units that can independently scale. Self-contained functional units that are completely decoupled from other functions are called microservices.

Azure Functions are the place designed to put your microservice code that will be called in serverless mode. Instead of provisioning servers specifying

At the time of the writing this article, Azure Functions don’t have some built-in integration with relational databases – they usually target No-SQL services. If you want to leverage benefits of serverless computing and use your relational engine as data storage, this might become complex project. You would need to put your entity framework or some other data access, place your domain model and all model classes in the function and put a lot of code for the simple function.

MsSql.RestApi can help you in this case and enable you to create serverless Azure function that access your database code with the minimal amount of code.

If you liked the approach with OData specification, this might be a good fit with Azure Functions. With MsSql.RestApi, you can put two lines of code described above to create your service:

[FunctionName("SalesOrders")]
public static async Task<IActionResult> SalesOrders(
         [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)] 
          HttpRequest req, ILogger log)
{
         TableSpec spec = new TableSpec(schema: "WebApi", table: "SalesOrders", 
         columns: "OrderID,OrderDate,CustomerPurchaseOrderNumber,ExpectedDeliveryDate,
         PickingCompletedWhen,CustomerID,CustomerName,PhoneNumber,FaxNumber,WebsiteURL,
         DeliveryLocation,SalesPerson,SalesPersonPhone,SalesPersonEmail");

         return await req.OData(spec).GetResult
                      (Environment.GetEnvironmentVariable("SqlDb"));
}

This might be a perfect choice for your microservice. You can implement powerful OData microservice that gives you all necessary functionalities that can filter, sort, group by rows from the SalesOrder table, and on the other side, you are writing just two lines of code. No big models, complex data access frameworks – just two lines of code and you need everything that you want. This code handles just data access logic.

MsSql.RestApi handles just data access logic. Security, CORS, and scalability are handled by Azure Function framework (see authorization level attribute in the function definition). This way, you are getting the best from both worlds – serverless scalability and manageability of Azure Functions, with powerful language that relation engine provides with just a few lines of code.

Points of Interest

As you have seen in this article, working with SQL Server relational database might be easy if you are using MsSql.RestApi library.

MsSql.RestApi is a free, open-source library that extremely simplifies implementation of ASP.NET REST API that reads data from SQL Server databases. My opinion is that this is the perfect choice for fast development of REST API for the existing databases.

It requires really a minimal amount of code and you should try it. If you find that it doesn’t fit your needs, you can always remove this minimal amount of code reimplement using some other technology.

If you know a better way to generate more powerful REST API for SQL server database with less amount of code, please let me know 😊. Otherwise, try this library and check whether it would help you.

This library is used to develop several SQL Server gitHub sample applications, including Wide World Importers sample Web application and Wide World Importers Azure Function Microservice for the official sample database built for SQL Server 2016. If you want to see more code in action, you can download the sample project from SQL Server GitHub repository and examine the code.

History

• 5^th November, 2018: Initial version