SQL Server

Infiniti connects directly to SQL Server tables, views and stored procedures to to retrieve data for use in forms or document generation.

In this tutorial, we will configure a SQL Server data source in Manage.

SQL Server Connection strings

SQL Server Data Sources require a connection string containing at a minimum; the server name, catalog and authentication information as in the sample below.

Password=infiniti#1234;Persist Security Info=True;User ID=infiniti;Initial Catalog=SampleData;Data Source=server001

Talk to your database administrator to ensure you have a valid connection string before attempting to connect to SQL server.

Creating a SQL Server Data Source in Manage

1. From the left hand menu navigate to Data sources and click the "New Data Source" button
2. Select "SQL Server" as the connection type
3. Provide the connection string.
4. Use the "Test Connection" button to confirm that Infiniti is able to establish a connection.
5. Click "Save"

🚧

Important

The username and password should never be provided in the connection string box but should be provided under the Credentials section in the username and password fields.

👍

Good Practice

It is recommended to enable "Allow Connection Export" as it will include the connection string in exported projects which use it, allowing the connection to be exported to a different environment

Connecting to a Table or View

After the connection to SQL Server Data Source has been established it is possible to point to specific tables and views contained on the server, referred to as data objects. The example uses the Employee table below.

Employee : Table creation and record insertion

CREATE TABLE [dbo].[Employee] (
  [EmployeeNo] [int] IDENTITY (1, 1) NOT NULL,
  [FirstName] [nvarchar](100) NULL,
  [LastName] [nvarchar](100) NULL,
  [Title] [nvarchar](100) NULL,
  [Salary] [numeric](7, 2) NULL,
  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  (
  [EmployeeNo] ASC
  ))
INSERT INTO Employee (FirstName, LastName, Title, Salary)
  VALUES ('John', 'Schimdt', 'Solution Engineer', 1200)
INSERT INTO Employee (FirstName, LastName, Title, Salary)
  VALUES ('Lincoln', 'Smith', 'Manager', 1200)
INSERT INTO Employee (FirstName, LastName, Title, Salary)
  VALUES ('Jackson', 'Martin', 'Manager', 1200)

From the data source properties page in Manage, click the "Data Objects" button. This will give a list of configured Data Objects such as tables view or stored procedures.

We will now configure the table Employee as the Table/View Data Object.

1. Click on "New Data Object" and provide:
   1. Object Type: "Table/View"
   2. Data Object Name / Definition: Name of your table or view in SQL e.g. 'employee'
   3. Display Name: A friendly name for the table
2. If the data source is to be filtered by any of the columns add them to the filter fields selection.
3. Click "Save"

Connecting to a Stored Procedure

Infiniti also supports connections to Stored Procedures where the procedures input parameters are provided as filter fields. The example uses the stored procedure below.

SP_CreateEmployee Stored Procedure

CREATE PROCEDURE [dbo].[spGetEmployee]
--Input parameter
@EmployeeNo int
AS
BEGIN
	SET NOCOUNT ON
	SELECT *
	FROM Employee
	WHERE EmployeeNo = @EmployeeNo
END
GO

To connect to a stored Procedure

1. Click on "New Data Object" and provide:
   1. Object Type: "Stored Procedure"
   2. Data Object Name / Definition: The name of the stored procedure together with sample input parameters surrounded by brackets. For example "spGetEmployee(2)"
   3. Display Name: A friendly name for the stored procedure
2. Add all filter fields using the "Add All >>" button. These will be populated at run time.
3. Click "Save"