MsSqlLogic

Note

The support for the Microsoft SQL Server package is available starting in FactoryTalk Optix 1.6.0.43 on all supported platforms.

This C# code is designed to interact with a Microsoft SQL Server database from an FT Optix project. It includes methods to create a database if it does not exist, create a table, insert data into the table, create a stored procedure, call the stored procedure, and query the data from the table. The code uses Microsoft.Data.SqlClient for database operations.

This application does not only demonstrate how to execute stored procedure but also how to query data from a table and update the FactoryTalk Optix project with the results.

Note

For the Microsoft.Data.SqlClient package to work, the project must contain an ODBC Database, even if not configured or unused.

Prerequisites

• FactoryTalk Optix 1.6.0.43 or later
• .NET 8
• C# 12.0
• SQL Server instance (see below)

SQL Server instance

If you don't already have a SQL Server instance, you can use the following Docker Compose to create a SQL Server container:

services:
    sql-server:
        container_name: sql-server
        restart: unless-stopped
        environment:
            - "ACCEPT_EULA=1"
            - "MSSQL_SA_PASSWORD=Pass@word"
        ports:
            - 1433:1433
        image: mcr.microsoft.com/azure-sql-edge

NetLogic description

MsSqlLogic

This class inherits from BaseNetLogic and contains methods to interact with the SQL Server database.

Methods

• Start(): Initializes the dbConfiguration object and queries the motors.
• Stop(): Placeholder for code to be executed when the user-defined logic is stopped.
• CreateDatabaseIfNotExists(NodeId resultLabel): Creates the database if it does not exist.
• CreateDatabaseTable(NodeId resultLabel): Creates the table in the database.
• InsertDataIntoTable(int numberOfMotors, NodeId resultLabel): Inserts a specified number of motors into the table.
• CreateIncreaseSpeedStoredProcedure(NodeId resultLabel): Creates a stored procedure to increase the speed of motors with odd numbers in their names.
• CallIncreaseSpeedStoredProcedure(NodeId resultLabel): Calls the stored procedure to increase the speed of motors with odd numbers in their names.
• QueryMotors(NodeId resultLabel): Queries the motors from the table and updates the FT Optix project.

Private Methods

• CreateDatabaseIfNotExists(string connectionString, string databaseName): Checks if the database exists and creates it if it does not.
• CreateDatabaseTable(string connectionString, string tableName): Creates the table in the database.
• InsertDataIntoTable(string connectionString, string tableName, List motors): Deletes existing content and inserts new motors into the table.
• CreateIncreaseSpeedStoredProcedure(string connectionString, string tableName): Creates a stored procedure to increase the speed of motors with odd numbers in their names.
• CallIncreaseSpeedStoredProcedure(string connectionString): Calls the stored procedure to increase the speed of motors with odd numbers in their names.
• QueryMotors(string connectionString, string tableName): Queries the motors from the table and returns a list of SqlMotor objects.
• GenerateMasterConnectionString(): Generates a connection string to the master database.
• GenerateConnectionString(): Generates a connection string to the specified database.
• WriteResultLabel(NodeId resultLabel, string message): Writes a message to a label in the FT Optix project.

SqlMotor Class

A simple class representing a motor with the following properties:

• Name: The name of the motor.
• Speed: The speed of the motor.
• Acceleration: The acceleration of the motor.

DatabaseConfiguration Class

This class is auto-generated and represents the database configuration in the FT Optix project. It includes the following properties:

• Hostname: The hostname of the SQL Server.
• Port: The port of the SQL Server.
• DatabaseName: The name of the database.
• Username: The username for the SQL Server.
• Password: The password for the SQL Server.
• TableName: The name of the table.

Usage

1. Create Database: Call the CreateDatabaseIfNotExists method to create the database if it does not exist.
2. Create Table: Call the CreateDatabaseTable method to create the table in the database.
3. Insert Data: Call the InsertDataIntoTable method to insert a specified number of motors into the table.
4. Create Stored Procedure: Call the CreateIncreaseSpeedStoredProcedure method to create the stored procedure.
5. Call Stored Procedure: Call the CallIncreaseSpeedStoredProcedure method to execute the stored procedure.
6. Query Motors: Call the QueryMotors method to query the motors from the table and update the FT Optix project.

Output of the queries is shown in the DataGrid and int the result label at the bottom of the FactoryTalk Optix MainWindow.

Notes

• The TrustServerCertificate=True option is used in the connection string to bypass certificate validation. This is useful for development and testing purposes but should be used with caution in a production environment.
• The GO command is used to separate the DROP PROCEDURE and CREATE PROCEDURE statements into separate batches.

Disclaimer

Rockwell Automation maintains these repositories for your convenience and that of other users. Although Rockwell Automation has the right at any time and for any reason to not access, edit, or remove content from this Repository, you acknowledge and agree to accept sole responsibility and liability for any Repository content posted, transmitted, downloaded, or used by you. Rockwell Automation has no obligation to monitor or update Repository content

The examples provided are to be used as a reference for building your own application and should not be used in production as-is. It is recommended to adapt the example for the purpose and observe the highest safety standards.