SQL Tutorial

Introduction

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

Prerequisites

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.

Modeling Entities

1. Start by accessing the Modeler.

   1.1. Open the user menu (click in the user name, placed in the top-right corner).

   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. Add an attribute (option Add Attribute in the Attributes section) to this interaction, with the Code and Name "Description", of Text type.

8. Confirm the changes to the attribute using the Accept button.

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

10. Confirm the changes to the attribute using the Accept button.

11. 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. Confirm the changes to the attribute using the Accept button.

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

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

9. Confirm the changes to the attribute using the Accept button.

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

11. Confirm the changes to the attribute using the Accept button.

12. 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 and Name to "myCompany"

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 myMIS 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 list is necessary, otherwise the application will not reflect all the data in the SELECT. To create this view, access the Configure Views option on the external entity, and add the Code, Name and BeginDate fields to the list you create. 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. Access Scripting in the menu and create an ExtensibilityScript called IntegrateTask.

2. Set the Entity Kind to Interaction, the Process Type to TaskManagement, and the Entity Type to Task.

3. Set the Action to Create.

4. Set the Moment to "After".

5. Add the SQLIntegrateTask_V2.cs file as its only script file, with the Execution type Remote.

   

6. Save the script.

7. Go back to the application.

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

9. Fill the form and Save.

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

11. 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.

12. Set the Query field:

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

13. Identitfy the Query Key Parameter with: ID.

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

15. Save the External Entity.

16. 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.