Web service publishing raw mode (beta)

Creates web service endpoints. This is not a component and therefore menu items are not created for it.

Configuration

Setup

Web services are administered from the Web service publishing raw mode (beta) menu item in the Web services subgroup in the Admin menu group.

Server configuration

The .NET Core hosting bundle for version 8.x needs to be installed on the web server.

IIS Configuration

The WS folder must be converted to an application in IIS Manager.

WebServicePublishingUrl must be set to the URL of the the IIS application created for the "WS" folder.

Optional configuration

Connection strings

By default the same connection string is used as for Softadmin. But this can be customized.

Two connection strings named: "Softadmin" and "SoftadminWebServices" may be used to use a different database user for web service calls. "Softadmin" is used for any internal calls. "SoftadminWebServices" is used to execute calls controlled by the developer, for example the calls to the the procedure of the method. These may be added to database.config or database.json (see below).

When the application is hosted in another folder than Softadmin or the developer wants to use specific connection strings for web service publishing the file database.json in the "WS"-folder may be edited. Otherwise the connection strings from database.config in the main folder are used. The format should be:

{ "ConnectionStrings": { "Softadmin": "Add connection string here", "SoftadminWebServices": "Optional. Add connection string here" } }

Hosting as separate application

The web service application can be hosted as a separate application instead of a sub application of Softadmin®. In the IIS create a separate application.

SQL

SQL Call: Get raw response (mandatory)

May modify database: Yes

Parameters

@<PathParameter> string
Any parameter defined in the path of the method. For example {Id}
@<QueryStringParameter> string
Any parameter defined in the querystring of the method. For example ?Id={Id}
@ClientId string
The client-id used to successfully authenticate for the web service.
@RequestBodyBinary binary
Request body as binary if the method is configured to work with binary data. Can be used to handle text using other encodings than UTF-8.

Should not be used for big file uploads.
@RequestBodyText string
Request body text.

Resultset: #HttpHeader (input to procedure)

Any HTTP headers supplied in the request. If the same header is set multiple times it will be concatenated into a comma-separated string.

Table count:
Row count:
Columns
HttpHeaderName mandatory string

The name of the HTTP header.

HttpHeaderValue mandatory string

The value of the HTTP header.

Resultset: Response body (optional)

The response body.

Table count: repeated zero or one time
Row count: exactly one row
Columns
ResponseJson optional string

JSON to be returned as the response body.

ResponseText optional string

Text to return as response body. Should be used if other encoding of content type is desired than for JSON.

ContentType optional string

Content type for the data. May not be used with the "ResponseJson" column, it always uses content type "application/json". Required when using the columns "ResponseText" or "ResponseBinary".

Encoding optional string

Encoding of the text in the "ResponseText" column. Defaults to "utf-8". May not be used with the "ResponseJson" column, it always uses encoding "utf-8".

Resultset: HTTP status code (optional)

The response HTTP status code.

Table count: repeated zero or one time
Row count: exactly one row
Columns
HttpStatusCode mandatory int

The response HTTP status code.

Resultset: Response headers (optional)

The response HTTP headers.

Table count: repeated zero or one time
Row count: zero or more rows
Columns
HttpHeaderName mandatory string

The name of the HTTP header.

HttpHeaderValue optional string

The value of the HTTP header.

Resultset: Multi value query string (input to procedure)

Regular query string parameters are passed as parameters to the procedure, and use the notation {xxx} in the path. See the parameter @<QueryStringParameter> above.

The special temp table #QueryStringParameter will only exist if the path contains the special notation {xxx...}. "..." is specifying the parameter may occur more than once in the query string.

One row will be created for each occurrence of the query string value in the request. The query string ?test=1&test=2 would create 2 rows with the values 1 and 2.

Table count:
Row count:
Columns
QueryStringKey mandatory string

The key of the query string value

QueryStringValue mandatory string

The value of the query string value.

SQL Call: Custom error formatting

Most likely this procedure will not be used, but the error format will need to be added to the OpenAPI-specification document.

The procedure is used to format the error response body for internal errors in a different format than below.

The default error format is JSON in the following format:
{ "ErrorMessage": "<Error message content>", "ErrorCode": "<Error code>" }

May modify database: No

Parameters

@ErrorCode string
Error code to output.
Possible value Description
InternalError An internal error occured.
InvalidQueryString The query string is in invalid format.
Unauthorized The client failed authorization.
@ErrorMessage string
Error message to output.
@HttpStatusCode int
The HTTP status code about to be returned, used if different status codes should have different formatting.
@MethodPath string
Path of the method. Used if different methods should have different formatting.
@RequestBodyBinary binary
The request body, if read before the error occurred and the method uses binary format. May contain any text in invalid format. Recommended to not use, but can be used to give more detailed information about formatting errors.
@RequestBodyText string
The request body, if read before the error occurred and the method uses text format. May contain any text in invalid format. Recommended to not use, but can be used to give more detailed information about formatting errors.

Resultset: Response body (optional)

Table count: repeated zero or one time
Row count: exactly one row
Columns
ResponseJson optional string

JSON to be returned as the response body.

ResponseText optional string

Text to return as response body. Should be used if other encoding of content type is desired than for JSON.

ContentType optional string

Content type for the data. May not be used with the "ResponseJson" column, it always uses content type "application/json". Required when using the columns "ResponseText" or "ResponseBinary".

Encoding optional string

Encoding of the text in the "ResponseText" column. Defaults to "utf-8". May not be used with the "ResponseJson" column, it always uses encoding "utf-8".

Resultset: HTTP status code (optional)

Table count: repeated zero or one time
Row count: exactly one row
Columns
HttpStatusCode mandatory int

The response HTTP status code.

Resultset: Response headers (optional)

Table count: repeated zero or one time
Row count: zero or more rows
Columns
HttpHeaderName mandatory string

The name of the HTTP header.

HttpHeaderValue optional string

The value of the HTTP header.

Examples

Get raw data

ALTER PROCEDURE dbo.dbo.WebService_Car_Get_Example
	@ExternalGuid uniqueidentifier,
	@ClientId nvarchar(max),
	@RequestBodyText nvarchar(max)
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

	SELECT
	(
		SELECT
			C.RegNr,
			C.SoldDateTime,
			C.Price
		FROM
			dbo.Car C
		WHERE
			C.ExternalGuid = @ExternalGuid
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	) AS ResponseJson;
END;

Custom error handling

CREATE PROCEDURE dbo.WebService_ErrorHandling_Example
	@ErrorMessage nvarchar(max),
	@ErrorCode nvarchar(300),
	@HttpStatusCode int,
	@MethodPath varchar(300),
	@RequestBodyText nvarchar(max)
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

	-- Example of changing the name of the "ErrorCode" property to "ErrorCodeInternal"
	SELECT
	(
		SELECT
			@ErrorMessage AS ErrorMessage,
			@ErrorCode AS ErrorCodeInternal 
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	) AS ResponseJson;
END;

Best practice

Big results

Do not return huge amounts of data in a single call but rather partition the data and let the client send multiple calls. Downloading huge responses requires the connection to be held the whole duration and a lost connection may require the client to download the whole response again. This applies to both large files and large text responses.

Versioning

By default the recommendation is to inherit all methods that are not changing to the new version, this way the caller can always be recommended to call the latest version of the API.