Таблицы. Как вывести данные из удаленного источника (по API) в компоненте Таблица

Есть некая внешняя система, у которой есть API. Требуется вывести данные из этого API в таблицу.

1. Указываем в настройках таблицы в поле Источник API коды исходящего запроса API (можно несколько через запятую без пробелов). В базе это поле таблицы as_crud_tables requestGetItems nvarchar(512).

2. Реализуем исходящий вызов /asapi 

Пример запроса Request: 

CREATE PROCEDURE [dbo].[api_falconUpdates_request]
	@parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2)
	@username nvarchar(32)  -- текущий пользователь.
AS
BEGIN
	-- SELECT 1  Msg, Result, Url (адрес, куда будет идти запрос)
	select '' Msg, 1 Result, 'https://falcon.web-automation.ru/api/action/updates?typeID=2' Url 

	-- SELECT 2 PARAMETERS - параметры, которые будут передаваться во внешний источник
	select 1
    --select 'id' name, '5' value, '' [type] -- form (в форме передается), header (в http headers), get запросы передавайте прямо в URL  
END

Все параметры, которые передавались в GetItems таблицы передаются в параметре @parameters этой процедуры

Response процедура без изменений: 

CREATE PROCEDURE [dbo].[api_falconUpdates_response]
	@response nvarchar(max),
	@parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2 - те же что и на request)
	
	@username nvarchar(32)
AS
BEGIN
	-- SELECT 1
	select '' Msg, 1 Result, @response Response
	-- SELECT 2 Внешние действия
END

Проверяем, что запрос отрабатывает хорошо через ссылку для этого элемента (/api/req/{code}).

3. Делаем GetItems для таблицы: 

CREATE PROCEDURE [dbo].[crud_tst-remotetable_getItems]
	@filters ExtendedDictionaryParameter READONLY,   -- ВАЖНО обязательно именно тип ExtendedDictionaryParameter (Key, Value2)
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare @result TABLE( 
		id nvarchar(max),
		name nvarchar(max),
		[desc] nvarchar(max),
		typeID nvarchar(max),
		created nvarchar(max),
		isMain nvarchar(max),
		link nvarchar(max),
		techChange nvarchar(max)
	)
	-- извлекаем ответ api запроса с кодом falconUpdates
	declare @falconUpdatesJSON nvarchar(max)
	select @falconUpdatesJSON = Value2 from @filters where [Key] = 'falconUpdates'    
   
--Пример ответа: set @falconUpdatesJSON = '
--{"errorCode":0,"data":[{"id":1375,"name":"Доработка механизма обрезки фото (для thumb варианта фото) ","desc":"","type":"Исправление ошибки","created":"2020-05-17T09:22:32.977","isMain":false,"link":"","techChange":null},{"id":1362,"name":"Дерево. Поправлен момент с работой редиректа и выпадающем меню по пункту","desc":"","type":"Исправление ошибки","created":"2020-05-06T19:14:55.46","isMain":false,"link":"","techChange":null},{"id":1360,"name":"Исправлен момент с пейджингом в каталогах Lists","desc":"","type":"Исправление ошибки","created":"2020-05-06T16:55:47.803","isMain":false,"link":"","techChange":null},{"id":1359,"name":"Поправлен момент взаимодействия модального окна и popover окна (в быстром добавлении для таблиц)","desc":"","type":"Исправление ошибки","created":"2020-05-06T16:00:49.217","isMain":false,"link":"","techChange":null},{"id":1347,"name":"Исправлена ошибка с изменением статусов в Канбан доске","desc":"","type":"Исправление ошибки","created":"2020-04-22T11:44:51.287","isMain":false,"link":"","techChange":null},{"id":1345,"name":"Исправлена ошибка в параметрах импорта + добавлен параметр itemID для импорта/экспорта","desc":"","type":"Исправление ошибки","created":"2020-04-21T12:49:48.353","isMain":false,"link":"","techChange":null},{"id":1341,"name":"Исправлена критичная ошибка с фильтрами таблицы","desc":"Если были установлены на странице 2 таблицы с разными фильтрами, фильтры одной таблицы влияли на другую (JS ошибка). ","type":"Исправление ошибки","created":"2020-04-19T11:11:11.263","isMain":false,"link":"","techChange":null},{"id":1338,"name":"Уточнение логики по полю формы типа Таймер","desc":"Возможность перезапуска поля, исправление учета code и itemID в as-timer","type":"Исправление ошибки","created":"2020-04-17T21:41:04.297","isMain":false,"link":"","techChange":null},{"id":1329,"name":"Поправлено сохранение одиночного поля для поля формы Набор чекбоксов","desc":"","type":"Исправление ошибки","created":"2020-04-10T14:04:02.38","isMain":false,"link":"","techChange":null},{"id":1327,"name":"Правка ошибки в Метриках (boards)","desc":"","type":"Исправление ошибки","created":"2020-04-02T20:47:23.627","isMain":false,"link":"","techChange":null},{"id":1314,"name":"Выгрузка таблицы в Excel - удаление тегов html и поправлена граница таблицы","desc":"","type":"Исправление ошибки","created":"2020-03-30T15:46:03.927","isMain":false,"link":"","techChange":null},{"id":1313,"name":"Исправлена неточность при печати таблицы (добавлена перезагрузка страницы)","desc":"","type":"Исправление ошибки","created":"2020-03-30T15:44:43.24","isMain":false,"link":"","techChange":null},{"id":1312,"name":"Исправлена ошибка работы скриптов после экспорта таблицы в Excel","desc":"","type":"Исправление ошибки","created":"2020-03-30T15:43:56.543","isMain":false,"link":"","techChange":null},{"id":1308,"name":"Исправлена ошибка с сохранением HTML через inline edit в таблице","desc":"","type":"Исправление ошибки","created":"2020-03-25T16:04:09.103","isMain":false,"link":"","techChange":null},{"id":1297,"name":"Исправлена ошибка пагинации вложенной таблицы","desc":"","type":"Исправление ошибки","created":"2020-03-13T10:52:02.697","isMain":false,"link":"","techChange":null},{"id":1295,"name":"Исправлен момент по перекрытию контекстных  форм и модальных окон","desc":"","type":"Исправление ошибки","created":"2020-03-11T13:14:25.17","isMain":false,"link":"","techChange":null},{"id":1274,"name":"Исправлена критичная ошибка в файловом менеджере при аплоде","desc":"","type":"Исправление ошибки","created":"2020-02-19T22:43:04.627","isMain":true,"link":"","techChange":"В панели управления код файлового менеджера (uploadsadmin) - строчными буквами"},{"id":1268,"name":"Обновлена либа Newtonsoft.JSON","desc":"Системное изменение, обязательно при обновлении ядра внести изменения в web.config","type":"Исправление ошибки","created":"2020-02-16T12:27:33.45","isMain":false,"link":"","techChange":"При обновлениях ядра заменить JSON либу в WebConfig \\n        <assemblyIdentity name=''Newtonsoft.Json'' culture=''neutral'' publicKeyToken=''30ad4fe6b2a6aeed'' ></a>\\n        <bindingRedirect oldVersion=''0.0.0.0-12.0.0.0'' newVersion=''12.0.0.0'' ></bindingRedirect>\\n\\t\\t\\n\\t\\t"},{"id":1263,"name":"Исправлена проблема русских букв в URL (при приведении в нижний регистр)","desc":"","type":"Исправление ошибки","created":"2020-02-13T10:37:41.677","isMain":false,"link":"","techChange":null},{"id":1257,"name":"Исправлен момент по совместному использованию модальной формы и всплывающей формы","desc":"","type":"Исправление ошибки","created":"2020-02-06T11:54:58.187","isMain":false,"link":"","techChange":null}],"outputType":"json","result":true,"msg":""}
--'
	insert into @result 
    SELECT *
    FROM OPENJSON(@falconUpdatesJSON, '$.data')
    WITH (
            id int '$.id',
            name nvarchar(512) '$.name',
            [desc] nvarchar(max) '$.desc',
            [type] nvarchar(512) '$.type',
            created nvarchar(512) '$.created',
            isMain nvarchar(512) '$.isMain',
            link nvarchar(512) '$.link',
            techChange nvarchar(512) '$.techChange'
        )
	-- 1 SELECT - сами данные		
	select * from @result
	order by  id desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;
	
	-- 2 SELECT - кол-во в таблице
	select count(*) from @result	

END

 Примечание: 

• Обязательно используем ExtendedDictionaryParameter.
• Отклик от каждого запроса API хранится в @filters с кодом этого запроса. 
• В примере мы обрабатываем отклик в JSON. Чтобы это корректно работало уровень совместимости SQL должен быть не ниже 130. 

--Как узнать уровень совместимости SQL Server?
SELECT compatibility_level, *     
FROM sys.databases --WHERE name = 'dbName';

--Как установить уровень совместимости
ALTER DATABASE dbName  
SET COMPATIBILITY_LEVEL = 130;  

Подробнее - https://docs.microsoft.com/ru-ru/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

Хорошая статья про разбор JSON через SQL - https://habr.com/ru/post/343062/

В итоге компонент будет сначала собирать данные с внешних источников и передавать их в GetItems в коллекции @filters (каждый response находим по коду запроса API).  

Пример подобной таблицы https://falcon.web-automation.ru/tst-remotetable