Azure SQL Connector

The Azure SQL Connector allows you to connect to a remote Azure SQL database and retrieve data as requested by the application.

You can use this to provide or modify data in your application. It is mostly used to handle contextual data (that is not expected to change often), but can also provide live data in real-time if the database it is connected to is configured to do so.

To enable live Updates, the database should have the service broker functionality enabled and allow the creation of queues and table triggers by the user whose credentials are being used.

Supported data types

The following SQL data types are supported:

String
Numeric
Date and Time
Other

  • char

  • varchar

  • text

  • nchar

  • nvarchar

  • ntext

  • bit

  • smallint

  • int

  • bigint

  • decimal

  • numeric

  • smallmoney

  • money

  • float

  • real

  • datetime

  • datetime2

  • smalldatetime

  • date

  • uniqueidentifier

  • xml

Live Updates

This Connector supports Live Updates, i.e. it can update the entity to display the most up-to-date information. The following is required to activate this capability:

  • The Selected Entity must be a Database Table. Stored Procedures, Views, and Queries are not supported.

  • The credentials must have the db_owner role, which will allow the Connector to use the SQLTableDependency library.

  • Service Broker must be enabled. You can check by executing the below query:

SELECT is_broker_enabled FROM sys.databases WITH (NOLOCK) WHERE database_id = db_i

Guidelines

Query Guidelines

Semicolon

A semicolon is the standard way to separate each SQL statement in database systems, allowing more than one SQL statement to be executed in the same call to the server. However, it is not necessary for a single query.

An incorrect syntax error will appear when a single query with a semicolon is specified on the script box.

Subqueries

SQL Subqueries or "query within a query" is a tool that allows us to perform tasks more efficiently by having only one query instead of several. However, this feature is not yet supported by this Connector.

Recommendation

Due to the validation of the input query, it is highly recommended that a query is copied and pasted into the script box rather than typing it.

Stored Procedure Guidelines

Stored Procedures provide advantages to the database administrator when conducting CRUD (Create, Read, Update, and Delete) operations across multiple tables within a single transaction, or when data validation before CRUD operations is necessary - a functionality not attainable by directly accessing tables or table views as data sources.

This connector supports Read, Update, and Insert actions in Stored Procedures. The Stored Procedure requires specific input parameters for Update or Insert actions.

Update

  • UpdateKey - contains the primary key value of the updated row

    • The expected value from the connector would be a JSON string as per the below example

      • {"PrimaryKey":PrimaryKeyValue} e.g. {"Id": 1}

        • The Primary Key is set when configuring the page data source

  • UpdateValues - contains the updated columns and values

    • The expected value from the connector would be a JSON string as per the below example

      • {"ColumnName": Values, "ColumnName2":Values, ....} e.g. {"FirstName" : "John", "LastName" : "Doe"}

Insert

  • InsertValues - contains the new values to be inserted

    • The expected value from the connector would be a JSON string as per the below example

      • {"ColumnName": Values, "ColumnName2":Values, ....} e.g. {"FirstName" : "John", "LastName" : "Doe"}

The following is a simple example of a Stored Procedure designed to perform multi-table CRUD operations. This Stored Procedure lists users from the UserTable (Read/Refresh), updates (Update), and inserts records into UserTable (Insert), and logs each Update/Insert operation in the TransactionLog table.

CREATE PROCEDURE [dbo].[sp_StoredProcExample]
(
   @UpdateKey NVARCHAR(MAX) = NULL,
   @UpdateValues NVARCHAR(MAX) = NULL,
   @InsertValues NVARCHAR(MAX) = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    IF( @UpdateKey IS NULL AND @UpdateValues IS NULL AND @InsertValues IS NULL)
        SELECT * FROM UserTable
    ELSE IF (@UpdateKey IS NOT NULL AND @UpdateValues IS NOT NULL AND @InsertValues IS NULL)
    BEGIN
    -- Update
    BEGIN TRANSACTION
        BEGIN TRY
	    DECLARE @sql NVARCHAR(MAX)

            SET @sql = 'UPDATE UserTable SET ';

            SELECT @sql = @sql + [key] + ' = ''' + [value] + ''', '
            FROM OPENJSON(@UpdateValues)
		
            SET @sql = LEFT(@sql, LEN(@sql) - 1);

            SELECT @sql = @sql + ' WHERE ' + [key] + ' = ' + [value]
            FROM OPENJSON(@UpdateKey)

            EXEC sp_executesql @sql;
		
            INSERT INTO TransactionLogs ([Action], [Key],[Value]) VALUES ('Update', @UpdateKey, @UpdateValues)
            COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
            ROLLBACK TRANSACTION
       END CATCH
    END
    ELSE
    BEGIN
    --Insert
    BEGIN TRANSACTION
        BEGIN TRY
            INSERT INTO UserTable
            SELECT * FROM OPENJSON(@InsertValues)
            WITH
            (FirstName NVARCHAR(255) '$.FirstName', LastName NVARCHAR(255) '$.LastName ', IsAdmin BIT '$.IsAdmin', Email NVARCHAR(255) '$.Email')
		
            INSERT INTO TransactionLogs ([Action], [Key], [Value]) VALUES ('Insert', @@IDENTITY, @InsertValues)
            COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
            ROLLBACK TRANSACTION
       END CATCH
    END
END

Pre-requisites

Azure SQL database credentials are required to use this Connector.

Current Version

Please contact XMPro if you're looking for an older version of this Connector.

Release Notes

v2.00, 16 Aug 2024

  • Updated System.Data.SqlClient from v4.7.0 to v4.8.6 for the CVE-2024-0056 security update

v1.70, 26 Apr 2024

  • Support Update and Insert action in Stored Procedures

Older

v1.65, 08 Dec 2023

  • Fixed connector logging error - entries were not consistently logged to App Designer

v1.64, 24 Oct 2023

  • Added connector logging support

  • Moved accessibility and username properties from the Access group to a new group, Usage

v1.63, 18 Oct 2023

  • Support inserts to a table with a unique identifier column

  • Added Show Distinct Values Without Access Control Column property, which allows the output payload to exclude the column used for access control

  • Added the Data Access Control section

  • Fixed insert failing when an int or bigint auto-increment column existed and an "instead of" insert trigger was used

  • Fixed updating a row(s) with a composite key

  • Fixed issue with Primary Key selection in config

  • Fixed filter not being used when fetching record count

  • Fixed queries with a Take parameter failing

  • Fixed queries with a Skip parameter but without a Take parameter not skipping any records

  • Support Insert and Update Stored Proc operations, though the implementation is the same as Read, to invoke Stored Proc under Update Datasources

v1.262, 17 Aug 2021

  • Initial Release

NextExample

Last updated