Sending Form Data to Microsoft SQL Server

Overview

As part of your implementation, you may opt to release the data you capture to destinations like SQL Server for reporting or other uses. This is possible for specifically selected fields or all fields using the SQL Query action found in GlobalForms.

Form Actions

If you are not familiar with Form Actions, they allow you to do different things on a user hitting the submit button, for example creating a user login, performing a login, generating a PDF snapshot and more. An available action is also the ability to perform a SQL Query, which on form submission will execute whatever query you specify. By leveraging this form action, you can execute most any CRUD statement in SQL server as long as it’s a valid query as demonstrated in the example below. You can also call GlobalForms field data using supported notation as well.

Configuring Your Target SQL Server

By default, GlobalForms will target the SQL Server you specify in your “default.json” configuration folder found in your GlobalForms\config directory. That reference is found under the “mssql” key as demonstrated below.

"mssql": {
        "host": "mysqlserver\\instancename",
        "port": "1433",
        "database": "databasename",
        "user": "sqluser",
        "password": "sqlpassword"
        }

Update this file to use a valid SQL server instance, database, SQL user and password if you haven’t already. Do note, TCP/IP connections must be enabled on your SQL server.

Saving Form Data to SQL

In order to release fields to SQL Server you must have two things:

1. A destination such as a table to directly insert to, or a Stored Procedure.

2. A form with capturing the respective fields you would like to release.

For example, if you have a SQL table called “Customers” with the columns “Name” and “Phone” and you want to insert form fields with the Property Name of “name” and “Phone Number”, your form action may look something like this:

As long as you are passing a valid SQL insert statement, GlobalForms will execute the insert on form submission.

Saving All Form Data

If you need to capture all form data, you can take a similar action to above, but aggregate the data into a JSON value, and push the JSON value to SQL. Similar to the example above, you will need a few things, including:

1. A destination such as a table to directly insert to, or a Stored Procedure.

2. A form with data you would like to release into SQL Server.

3. Microsoft SQL Server 2016 or greater.

Constructing the Destination

You can release all data by building a supporting mechinism to store the data, such as a table. Below an example of a stored procedure which can be passed data from your form and will create a table called “GF10DATA” (if none exists) and then insert the data into the same table.

CREATE PROCEDURE [dbo].[GF10PUSH] @DATA NVARCHAR(MAX), @FORMNAME VARCHAR(255)
AS

IF (SELECT COUNT(*) FROM SYS.TABLES WHERE NAME LIKE '%GF10DATA%') = 0
BEGIN
CREATE TABLE GF10DATA 
(
IID BIGINT IDENTITY(1,1),
DATA NVARCHAR(MAX),
FORMNAME VARCHAR(255)
)
END

INSERT INTO GF10DATA ([DATA], FORMNAME)
SELECT @DATA, @FORMNAME

GO

Preparing the Form

In GlobalForms, any data entered into a field will exist in the “data” object. This can contain anything from a single value from a text field control, to multiple rows of data from things like datagrid objects. To push this data, you need to aggregate it into a single control which can then be pushed to SQL server (into the example above).

To do this, we’ll add a hidden text area component on the form with the Property Name of “JSON”.

On my submit button, I’ll add some logic which will take all the form data and inject it into that field as show below.

The raw javascript is as follows:

data.JSON = null;
data.JSON = JSON.stringify(data);

Effectively, on submission this will write all form data to the new text area we created on the form called “JSON”.

Sending the Data

Now that we have the data available, we can post the data to SQL server using a SQL Query form action. This will call the stored procedure above, and allow us to parse this data.

We push the data from the “JSON” field into the Stored Procedure provided above and statically set a form name, this will let us know the source of the submission should we need to refer to it later.

Accessing and Formatting the Data

After submitting the form, the data will be represented as JSON data.

You can build a select statement to parse this data out, for example:

SELECT
IID, FORMNAME,
    JSON_VALUE(DATA, '$.firstName') AS FirstName,
    JSON_VALUE(DATA, '$.lastName') AS LastName,
ContactDetail.Phone
FROM
    GF10DATA
CROSS APPLY
    OPENJSON(DATA, '$.ContactDetails') WITH (Phone NVARCHAR(50) '$.Phone') AS ContactDetail;

In the query above, we refer to objects in the JSON directly with their property name. For example “$.firstName” is the component on the form with the property name “firstName”. “$ContactDetails” is a datagrid which contains the component “Phone” on the form.

The below query will show as follows: