SQL Server Connector

The SQL Server Connector allows you to connect to a remote SQL Server 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 configured.

Supported data types

The following SQL data types are supported:

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 SQL Server version must be 2008 or later.

  • The credentials must have the db_owner role, allowing 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_id()

Guidelines

SQL 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

SQL Server 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, 01 April 2024

  • Support Update and Insert actions in Stored Procedures

Older

v1.65, 24 Nov 2023

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

v1.64, 18 Oct 2023

  • Added connector logging support

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

v1.63, 27 Sept 2023

  • Support inserts to a table with a unique identifier column

v1.60, 1 Aug 2023

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

v1.44, 25 Oct 2022

  • Added the Data Access Control section

v1.34, 16 Mar 2022

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

v1.33, 14 Feb 2022

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

v1.32, 19 Oct 2021

  • Fixed issue with Primary Key selection in config

v1.31, 15 Oct 2021

  • Fixed filter not being used when fetching record count

v1.30, 05 Oct 2021

  • Fixed queries with a Take parameter failing

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

v1.29, 13 Sep 2021

  • Enabled Insert and Update operations for Stored Proc, though the implementation is same as Read, to be able to invoke Stored Proc under Update Datasources

v1.262, 16 Aug 2021

  • Allowing manual entry of database name to cater for Azure SQL databases

v1.26, 06 Aug 2021

  • Added option to specify SQL query

v1.25, 13 Apr 2021

  • Change the order of SQL Authentication checkbox

v1.24, 09 Apr 2021

  • Added Variable boxes for server, user, and password

v1.23, 30 Oct 2020

  • Read, Write, Insert, and Delete operations handle entity names with symbols or keywords more reliably

v1.22, 08 Oct 2020

  • Repackage

v1.21, 17 Sep 2020

  • Added fix for handling null values in insert and update

v1.20, 26 Aug 2020

  • Initial Release

NextExamples

Last updated