version

Web Service Publishing raw mode

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 version 8.x needs to be installed on the web server, install the latest version. You can find the currently installed version in Admin/Softadmin® system information. You can find a link to the latest hosting bundle installer för .NET 8 on this page.

IIS Configuration

The WS folder must be converted to an application in IIS Manager. The application must use an app-pool running in 64 bit mode ("Enable 32-bit applications"=false).

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. These may be added to database.config or database.json (see "Hosting from a different folder").

"Softadmin" is used for any internal calls and require the same permissions to the database as Softadmin.

"SoftadminWebServices" is used to execute calls controlled by the developer, for example the calls to the the procedure of the method, and may have limited access to a specific schema or set of procedures.

Hosting from a different folder

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" } }

Client certificate authentication

Client certificate authentication may be used to map SSL certificates to client-id. To use this feature the IIS setting "Client certificates" in the group "SSL settings" must be set to "Accept" or "Require" and in Softadmin the web service need to be configured with a client account of the client certificate type. The IIS setting must be set to "Accept" in the development environment. In the stage or production environments it should be set to "Require" if all web services for the system should have client certificate authentication, if not it should be set to "Accept".

The entire certificate chain must be trusted by the server. This is usually achieved by acquiring a certificate from a real certification authority, or adding the certificate to the servers trusted certificate store if not generated by a root certificate authority.

Windows authentication (Not recommended)

To use this feature the IIS authentication "Windows authentication" must be enabled and all other authentication types for example "Anonymous authentication" must be disabled. The user will be authenticated in the web servers domain.

The full user name authorized by the IIS will be send in the @ClientId parameter to the procedure, the exact format is up to the IIS, but "DOMAIN\username" is commonly used but is subject to change in future IIS versions.

This authentication may not be combined with other authentication methods for other web services since the applications has to enable Windows authentication in the IIS which affects all services of the application. For this reason and due to it adding unnecessary complexity for the caller compared to the other methods offered it is not recommended.

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 headers (optional)

The response HTTP headers. If used this must be the first table of the result set.

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: HTTP status code (optional)

The response HTTP status code. If used this table needs to be returned after the Response headers table and before the Response body table.

Table count: repeated zero or one time

Row count: exactly one row

Columns

HttpStatusCode mandatory int

The response HTTP status code.

Resultset: Response body (optional)

The response body. If used this must be the last table in the result set, it should also be the last statement since the data will be sent in the response even if errors occur later in the procedure.

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: Multi value query string (input to procedure)

Using this feature indicates bad design of the service, and should not be used if possible.

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. Only called if "Custom error formatting procedure" is explicitly set for the procedure.

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.
RequestBodyTooLarge	The request body size exceeds the specified max request size for the method.
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.

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.WebService_Car_Get_Example
	@ExampleExternalGuidFromPath 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 = @ExampleExternalGuidFromPath
		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)
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;

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.