Easy interactions with MS SQL Server Databases

Reading time: 6 minutes
Low-code platforms are an efficient and effective solution for accessing and managing data in a Microsoft SQL Server (MSSQL) database.  Linx provides an easy way to connect to a database and interact with it in multiple ways, all while removing the need for complex coding. 

Linx natively allows interaction with an MSSQL database via the Database plugin. Using this plugin, you can:

  • Execute custom SQL statements
  • Bulk copy data to a specific table
  • Execute stored procedures (and pass in parameters)
  • Begin transactions (to facilitate performing multiple database operations on the same transaction)

This functionality can be used by dragging the required function from the plugins tab onto the function you are building.

Interacting with MS SQL databases


Pairing database access to other standard functionality in Linx, allows for quick development across several business areas. These include 

  • Building an API to provide the data
  • Building ETLs
  • Generating reports
  • Sending Emails
  • Automating business processes
  • Read data from an API or message queue and store it in a database for later use


Executing SQL statements

By using the ExecuteSQL function, you can connect to a database and execute a custom SQL script. The statement can be simple or complex, depending on your requirements and SQL aptitude. You can:

  • Read from databases (SELECT)
  • Write to databases (INSERT)
  • Maintain existing records (UPDATE)
  • Remove records (DELETE)
  • Perform complex scripts with CASE statements, IF statements and any functions that your instance of SQL Server can process (T-SQL)
Specific SQL Logic and complex statements

Additionally, you can pass in parameters (illustrated above) from a function, meaning that you can specify what values you need to write, specify filters and more. 

It is not essential to have a mastery of SQL to carry out basic operations. Linx includes a tool that will create templates for Select, Insert or Update requests without needing in-depth knowledge of SQL.

Generate simple SQL statements from SQL objects

You will need to provide a connection string to interact with the database. This can be provided as either a string, or you can use the connection editor to build up the connections string for your SQL Server database:

Easily connect to a database using the connection editor
If you will be using the connection string frequently in many different functions it is recommended that you create a Setting for the connection string. A setting is a re-usable parameter that most components or functions in Linx can reference. Settings can also be made secret for more sensitive values, such as connection strings that may contain a password.

A Linx database connection allows you to have full control over how the data is returned, what the column names should be and how you want to interact with the returned data.

  • Bind the result to a custom type
  • Alter the resulting column names outside of your SQL query
  • Iterate through the results to interact with each record independently
  • Return the entire dataset as a list, making it easier to interact with it later
  • Return only the first row of the set

Execute Stored Procedures

Many interactions can be facilitated by executing stored procedures. To do this, you can either

a) execute the stored procedure in an ExecuteSQL function as a SQL query, or
b) you can execute it with the ExecuteStoredProcedure With this function, you select what stored procedure you want to execute, along with parameters that need to be passed in. The parameters can be completely dynamic, meaning you can get them from any other function. 
Execute stored procedures with ease and pass in parameters as required using a wizard

Bulk Copy Data

Use the DB Bulk Copy function if you need to write a large quantity of data to a table. This function creates a Loader object in its execution path. Use the SetValue function to add the rows that you want to insert. 

This DB Bulk Copy function is effective when working with a time-sensitive load or when you just quickly need to get data into a specific table. It works well for ETL processes, when staging data.

This feature can be used with MS SQL Server and Oracle databases.

Use the Bulk Copy function to quickly load data into a table

Sign up to our Newsletter