Create a Web API

Last Updated: August 2020

Introduction

This tutorial teaches the basics of building a Web API with ASP.NET Core in SnapDevelop.

In this tutorial, you learn how to:

Create a Web API Project
Test the Sample API
Add a Database Context
Register the Database Context
Add and Register a Service (.NET DataStore)
Test the Service APIs
Call the Service API from PowerBuilder

The following diagram shows the high-level design of a Web API app.

When the client app sends a user request (an URL) to the Web API, the API controller class handles the request and calls the method in the service class to perform the business logics and data operations.

A Controller is responsible for the initial processing of the request and returning the result of the processing. The controller does not include data access or business logic; it calls the service to perform data operations and business logics.
A Service is responsible for retrieving and creating data models from the data source, performing application specific logic and manipulation, etc. The service is where the core business logic is. A service normally separates its interface from its implementation of that interface.

These concepts will be covered later in this tutorial.

Prerequisites

SnapDevelop 2019 R2
Sample database setup
1. Install SQL Server Express or SQL Server 2012 or later.
2. Download the database backup file (AdventureWorks_for_sqlserver.zip) from https://github.com/Appeon/.NET-Project-Example-Database.
3. Restore the database using the downloaded database backup file.
  
  For how to restore the database, you can refer to the readme file: https://github.com/Appeon/.NET-Project-Example-Database/blob/master/README.md.

Create a Web API Project

Start SnapDevelop and select Create New Project from the Start page. Or, from the File menu, select New and then Project....

In the New Project dialog box, select .NET Core, and in the list of project templates, select ASP.NET Core Web API. Name the project "WebAPI1" and click OK.

The Web API project contains the following folders and files by default.

Program.cs -- This file is the entry point for the application. It sets up the IWebHost, and contains the UseStartup<Startup> line which tells the host to use the Startup.cs file.
Startup.cs -- In this file, the ConfigureServices method configures the services for dependency injection; the UseCoreIntegrated method and UsePowerBuilderIntegrated method in the ConfigureServices method enables the actions in the controller to support PowerBuilder based technology (e.g. DataWindow); the Configure method specifies how the service responds to the requests; the UseDataWindow method in the Configure method parses and loads all DataWindow objects of the current application. Both the ConfigureServices method and Configure methods are standard methods in C# development. For more description, you may refer to https://docs.microsoft.com/en-us/aspnet/core/fundamentals/startup?view=aspnetcore-2.1.
appsettings.json -- This file stores the database connection string and the other app settings.
launchSettings.json -- It is a standard file in ASP.NET Core. This file sets up the environments that SnapDevelop can launch automatically.
SampleController.cs -- This is a sample controller created by default when the Web API project is created. The controller class and the action method in it will be invoked first when a user request comes in.

Test the Sample API

The project template creates a sample API. Open SampleController.cs, right-click in the Load() method, and then select Run Test(s).

In the Web API Tester tool, click Send.

The following JSON is returned:

[
    "value1",
    "value2"
]

In the following sections, we will add a database context and a service (.NET DataStore) to this Web API project.

Add a Database Context

The database context is the class that manages database connections and transactions. This class is created by deriving from the SnapObjects.Data.DataContext class.

Right-click the Web API project and select Add > New Item.... In the item template list select DataContext and name the class AppeonDataContext and click OK.

In the Database Connnection dialog box, click New.

Fill in the database connection information and click OK.

Then back to the Database Connection dialog box, a connection string is automatically generated, click OK.

The connection string is saved in appsettings.json and the AppeonDataContext class is created.

Register the Database Context

In ASP.NET Core, services such as the DataContext must be registered with the dependency injection (DI) container. The container provides the service to controllers.

In the ConfigureServices() method in Startup.cs, right-click where you want to insert the code and select Inject DataContext.

In the Inject DataContext(s) dialog box, make sure WebAPI1 and AppeonDataContext are selected and click OK.

The following code is inserted automatically to the ConfigureServices() method:

services.AddDataContext<AppeonDataContext>(m => m.UseSqlServer(this.Configuration, "AdventureWorks2012"));

Alternatively, you can also register the DataContext by uncommenting the following code and modifying the context name to AppeonDataContext and the database connection name to AdventureWorks2012 in the ConfigureServices() method in Startup.cs:

services.AddDataContext<AppeonDataContext>(m => m.UseSqlServer(Configuration["ConnectionStrings:AdventureWorks2012"]));

Add a Service

Right-click the Web API project and select Add > New Item.... In the item template list select Service (.NET DataStore) and name the service DepartmentService and click OK.

Choose the AppeonDataContext class (created in Add a database context) that will be used to connect to the database. And change the name of the controller to DepartmentsController. (It is a good practice to use plural names for your controllers if any method will return/update/delete more than one row).

Click Add.

The Service (.NET DataStore) template creates the following folders and classes by default.

Services folder: This folder contains the service which performs the data operation on the .NET DataStore model.
- \IDepartmentService.cs: This service defines the IDepartmentService interface which contains the declaration of the following methods by default: RetrieveByKey, Create, Update, DeleteByKey. All of these methods are declared with parameter(s) and return type only, but with an empty body, as shown below:
```
    public interface IDepartmentService
    {
        IDataStore<D_Department> RetrieveByKey(int id);
        int Create(D_Department department);
        int Update(D_Department department);
        int Update(IDataStore<D_Department> department);
        int DeleteByKey(int id);
    }
```
- \Impl\DepartmentService.cs: This service provides the implementation of the IDepartmentService interface. It contains the actual definition of the methods declared in the interface.
  
  Take the RetrieveByKey method in DepartmentService.cs for example. It retrieves one data row from the database according to the specified id, and returns the retrieved data in an instance of the D_Department .NET DataStore model.
```
        public IDataStore<D_Department> RetrieveByKey(int id)
        {
            var dataStore = new DataStore<D_Department>(_dataContext);

            dataStore.RetrieveByKey(id);

            return dataStore;
        }
```
Controllers\DepartmentsControllers.cs: This controller is based on the DepartmentService service. It defines the routing format, the HTTP method, and the action methods corresponding to the service.
```
[Route("api/[controller]/[action]")]
```
[controller] should be replaced with the name of the controller, which by convention is the controller class name minus the "Controller" suffix. For this sample, the controller class name is DepartmentsController, so the controller name is "Departments". [action] should be replaced with the name of the action method in the controller. For example, appending api/departments/retrieveone/{id} to the base URL would cause the RetrieveOne action method in the DepartmentsControllers class to run.
```
        // GET api/departments/retrieveone/{id}
        [HttpGet("{id}")]
        [ProducesResponseType(typeof(D_Department), StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        public ActionResult<D_Department> RetrieveOne(int id)
        {
            var department = _departmentservice.RetrieveByKey(id);

            if (department.Count == 0)
            {
                return NotFound();
            }

            return department.FirstOrDefault();
        }
```
The [HttpGet] attribute denotes a method that responds to an HTTP GET request. "{id}" is a placeholder variable for the unique identifier of the department. When retrieveone is invoked, the value of "{id}" in the URL is provided to the method in its id parameter.

The RetrieveOne action method calls the RetrieveByKey method in the DepartmentService service.
DataWindows\Department.pbt\Department.pbl\D_Department.cs: This is the .NET DataStore model converted from a PowerBuilder DataWindow. It by default maps to the table HumanResources.Department in the AdventureWorks2012 database.

Add the following using statement to Startup.cs:

using WebAPI1.Services;

Register the DepartmentService service by right-clicking in the ConfigureServices() method in Startup.cs, selecting Inject Service, and then selecting the DepartmentService service.

After you click OK, the following line is added automatically to the ConfigureServices() method:

services.AddScoped<IDepartmentService, DepartmentService>();

Test and run the Web APIs

Test the Web APIs

Right-click in the RetrieveOne method of DepartmentsController.cs and then select Run Test(s).

In the Web API Tester tool, input "1" as the value for id and click Send.

The following JSON is returned:

{
    "departmentid": 1,
    "name": "Engineering",
    "groupname": "Research and Development",
    "modifieddate": "2008-04-30T00:00:00"
}

For more information about Web API Tester, please go to this tutorial: https://docs.appeon.com/snapdevelop2019r2/Testing_with_Web_API_Tester/index.html.

Run the Web APIs

Run the Web APIs by pressing Ctrl+F5 in SnapDevelop IDE. SnapDevelop will build the Web API project, start the Web APIs, and access the default Web API: http://localhost:5000/api/sample/load in the Web browser.

If you enter this URL: http://localhost:5000/api/Departments/RetrieveOne/1 in the Web browser, the following JSON is returned:

{"departmentid":1,"name":"Engineering","groupname":"Research and Development","modifieddate":"2008-04-30T00:00:00"}

Call the Web APIs from PowerBuilder

In this tutorial, we create a PowerBuilder app as the client app to access the Web APIs.

Add the JSON DataWindow

A JSON DataWindow is a traditional DataWindow that has a Web API as its data source. You use the same functionalities on the client-side, but the data manipulation is done on the Web API server-side. In this example, we create a PowerBuilder DataWindow and use it as the JSON DataWindow.

Start PowerBuilder and create a template application with no database connection. Then add a new Grid DataWindow that maps to the table: HumanResources.Department in the AdventureWorks2012 database. Add a retrieval argument named departmentId and use it in the SQL WHERE clause to filter data by departmentId. Save the DataWindow object with the name d_department.

Add a DataWindow control to the sheet window. Set its Name to dw_department and DataObject to d_department. And add a Retrieve button for the retrieval operation.

Retrieve the JSON DataWindow

The PowerBuilder RESTClient object provides the ability to access the RESTful Web APIs. It can load the JSON-formatted string returned from the RESTful Web Service APIs into the DataWindow object.

To retrieve data for the JSON DataWindow, you first need to instantiate the RESTClient object. Add an instance variable of the RESTClient object in your window.

RESTClient inv_RestClient

On the Open event of the window, create the object variable.

// Create the RESTClient object variable
inv_RestClient	= CREATE RESTClient

On the Clicked event of the Retrieve button, add the RESTClient.RetrieveOne() function to call the Web API and load the data directly into the DataWindow. (If you intend to Retrieve more than one row, use RESTClient.Retrieve() instead)

// Retrieve the DW using the RESTClient
inv_RestClient.RetrieveOne(dw_department, "http://localhost:5000/api/departments/retrieveone/1")

Now you can run the PowerBuilder application, and click the Retrieve button to retrieve data via the Web API. (Note: Your Web API needs to be running.)