SQL Tutorial

Introduction

This tutorial is designed to model a very simple interaction in the OMNIA platform, and use the OMNIA Connector to register the documents created in that interaction into an external system (an SQL database).

In this tutorial we will see how to Read and Write data from any on-premise system (the SQL Database is just an example).

Prerequisites

The system requirements are:

1. Operating System: Microsoft Windows (any version)
2. SQL Server Express 2014

Prepare your database by executing both .sql files found in this tutorial's folder:

1. TutorialDatabase.sql
2. TutorialDatabase - Sample Data.sql

If you are performing this tutorial on top of a tenant that was already used to execute a previous tutorial, when there is an instruction to create an Entity and then perform further operations on that entity, if you already created one with the given name, you can reuse the one that already exists.

Download the C# Scripts files that can be found in this tutorial's folder:

1. SQL Remote Query that will be used to Read data from a SQL Database.
2. SQL Integrate Task that will be used to Write data in a SQL Database.

We would like to invite you to open the C# Scripts and see that this is simple C#, that you can adjust to meet your needs. If you want to know more about OMNIA Scripting Extensibility and how you can use Visual Studio to script development, please take a look at the Scripting chapter from the Modeling Manual.

This tutorial is undergoing updates for version 2.0 of the OMNIA Platform. The screenshots may not match what you see on the screen.

Modeling Entities

1. Start by accessing the Modeler.

   1.1. Open the left-side context menu (click in arrow next to the name of the tenant you are currently on).

   1.2. Choose the option Modeler.

2. Through the top navigation menu, access Entities > Agents. Create an Agent Type (Add new button), and set its Code and Name to "Employee". Click the Save button.

   

3. Access Entities > Resources. Create a Resource (Add new button), and set its Code and Name to "Hours". Click the Save button.

4. Access Processes and Interactions, click the Add new Process button and create a process with the code "TaskManagement" and the name "Task Management".

5. When the process is created, create an interaction under the process you just created using the option Add Interaction.

   

6. Set the Code and Name of the Interaction to "Task".

7. In the General tab, add an attribute (option Add Attribute in the Attributes section) to this interaction, with the Code and Name "Description", of Text type.

8. Add another attribute, with code and name equal to "Completed", of True/False (boolean) type.

9. Save the interaction.

Model the SQL Server system

1. Access External > Systems in the top menu.

2. Create an external system (option Add New) with the Code "SqlDatabase" and the name "Sql Database". This entity will represent the server containing our external database.

3. Edit the "SystemVersion" attribute.

4. In the Behaviour Tab, set the Default Value to 1.

5. Hide the "SystemVersion" attribute unchecking the Visible flag in the Interface tab.

   

6. Add an attribute to this system, with the Code and Name "SqlDb", of Text type.

7. You can set, in the Behaviour tab, SqlDb's Default Value to TutorialDatabase, if you didn't change the database name in the provided prerequisite scripts.

8. Add an attribute to this system, with code and name equal to "SqlServer", of Text type.

9. Save the External System and it will be created.

Connect to the SQL Server

1. Access to the Application clicking at your username at the navigation bar and then option Application.

2. Create a Company, in Configurations > myCompany > Create.

3. Set the Code to "myCompany" and Name to "My Tutorial Company"

4. Click the Save button.

5. Create a connector, in Administration > Connectors > Create.

6. Set a name to your connector (for example: SqlTutorialConnector)

7. Configure your OMNIA Connector to use its endpoint and license file.

   You can find more information about configuring the connector at this tutorial.

8. Ensure your SQL Database has a user with read and write privileges, for the platform to use.

9. Create a SqlDatabase external system mapping, in Configurations > Sql Database > Create, connecting the company you created to your SQL Database.

The default Username and Password fields identify the user you want to login with, and the SqlDb and SqlServer represent the C# SQLConnection InitialCatalog and DataSource attributes, respectively.

Reading data from the SQL Server

Now that we have a connection to the SQL Database ready, we can test the connector.

1. Go back to the Modeler using the side bar accessible when you click in your username, and select the option Modeler.

2. In the navigation bar click the External > Entities options

3. With the Add new option create an external entity with code "ExternalProject" and name "External Project".

4. Set the Query field:

    SELECT [Code]
    ,[Name]
    ,[BeginDate]
    FROM [dbo].[Project]
    WHERE [EndDate] IS NULL
   

5. Identify the Query Key Parameter with: Code

6. In the Script file, upload the SQLRemoteQuery_V2.cs file found in this tutorial's folder.

   

7. Save the External Entity.

   If the connection to the database fails (for example, you did not connect the connector yet), you will see a Warning (yellow) notification (at the navigation bar), informing you that the default view for this external entity was not created.

   Even after correcting the problem that caused the connection failure, creation of the correct list is necessary, otherwise the application will not reflect all the data in the SELECT. To create this view, access the Configure Lists option on the external entity, and add the Name and BeginDate fields to the list the platform automatically created. It should end up looking like the (default) list below.

   

8. In order to test the Sql Query and the Connection, go back to the Application and access the list of the ExternalProject, through Configurations > External Project > List.

9. Verify the projects that show up are those you created in the initial setup.

   

10. Now, we will add a Project field to the interaction, that references the external entity we created in the last step. Go back to the modeler, and click Processes and Interactions in the menu. Edit the Task interaction.

11. Add an attribute (option Add Attribute in the Attributes section) to this interaction, with the Code and Name "Project", of Other Entity type. It should refer the kind External Entity and type ExternalProject.

12. Save the Task interaction.

13. Go back to the application and access Task Management > Task > Create. Verify the new Project field is available, and that you can select all the projects that show up in step 9.

Writing data to the SQL Server

Now, we are going to add the script that integrates the Task in the SQL database, when it is created.

1. Go back to the Modeler using the left-side context menu.

2. Access Processes and Interactions in the menu.

3. Select the Task interaction, and use the context menu to access Configure Scripts.

4. Press Add New, and create an ExtensibilityScript called IntegrateTask.

5. Set its Execution Moment to Create -> After.

6. Add the SQLIntegrateTask_V2.cs file as its only script file, with the Execution type On-Premise System.

   

7. Save the script.

8. Go back to the application.

9. Create a new Task by accessing Task Management > Task > Create.

10. Fill the form and Save.

11. In your SQL database, verify if it is integrated successfully.

12. So that it is not necessary to manually access the SQL Server to see what tasks were integrated, access the modeler again, and, in External > Entities, create a new external entity named ExternalTask.

13. Set the Query field:

      SELECT [ID]
            ,[ProjectID]
            ,[Description]
            ,[Completed]
      FROM [dbo].[Task]
    

14. Identify the Query Key Parameter with: ID.

15. In the Script file, upload the SQLRemoteQuery_V2.cs file found in this tutorial's folder.

    

16. Save the External Entity.

17. Go back to the application, and access the new list, through Configurations > ExternalTask > List, and see the task you just integrated.

Next Steps

You can continue the tutorials by performing the HTML/XML tutorial.