Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Creating and Using SmartConnect Script Templates


This week’s Tech Tuesday article is from our Director of Professional Services, Lorren Zemke, who has provided a starting point on using the Script Templates in SmartConnect.
 
One of the lesser used features of SmartConnect that has some great potential are the Script, SQL and Mail templates. Templates allow users of SmartConnect to create .NET Scripts or SQL Code that can be pulled into any map through calculated columns or map tasks as a starting point or a complete set of code to be executed. The script templates are available for any SmartConnect map and not limited to a specific connector. 

This article will help you get started on using the Script templates to create a quick method for code reuse.  

Start by opening SmartConnect and clicking on the Maintenance Tab. You will see the three templates available with SmartConnect.
 

Click on the Script button to open the Task Script window. Don’t worry, these scripts can be used in calculated columns as well. If you have not created your own scripts, there will be some defaults created as part of your SmartConnect installation.


Click the Add button to open the Script Template window.
 

 

Give the Template a name. We’ll call this one EXECUTESQLCOMMAND_VB.
Choose the language type to use for this script. You can use either C# or VB.NET.  If you want to have both available, you will want to create two templates.
Enter the Template Description. We’ll call it “VB Script Template for Executing SQL Code.”
————————————————————————————–
Now, enter your script code. Remember, all .NET Scripts in SmartConnect must return a value, so keep that in mind when entering your template code.
This example is used to allow for quickly executing a SQL procedure or script and using a Data Reader to grab the values from the results. The code is pretty generic so you may need to change or add code as needed to fit your specific need. A great resource for learning .NET coding is the Microsoft Developer Network. http://msdn.microsoft.com/en-us/library/k1s94fta(v=vs.71).aspx
‘————————————————————————————–
‘ Script Name:  Execute SQL Command
‘ Created On:   2013-Nov-18
‘ Author:       Lorren Zemke
‘  Executing a stored procedure within a calculated field to return a single data element
‘  This script uses a Data Reader to loop through the rows to get the value of the last row.
‘ History:     
‘—————————————————————————————
‘———————————– CONFIGURATION ————————————-
Dim mapInterface As Integer = {TV_SCHEDULED}
‘—————————————————————————————
Try
    ‘******************************************************************
    ‘ This section is used to set the connection string to execute the stored procedure
    ‘
    ‘ Requires setting up MSSQL Default servers from the Maintenance tab
    ‘
    ‘Declare the connection string based on the configuration variables
   
Dim conString As New String({TV_SQLConnection})
      
    ‘ Set connection string based on the company defined at the destination
    ‘ to match the company connection string
    ‘
    ‘If _COMPANY = ” TWO” Then
    ‘           conString = _SQLDEF_TWO
    ‘Else
    ‘           conString = _SQLDEF_OTHERCOMPANY
    ‘End If
    ‘*******************************************************************
  
    ‘Create the SQL connection and open it
    Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
    myConn.Open()
    ‘Define the query command to be run
    Dim sqlCode As String = “{TV_STOREDPROCEDURE}”
    ‘Declare the SQL command as the query string
    Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlCode, myConn)
    myCmd.CommandType = System.Data.CommandType.StoredProcedure
    ‘——————————————————————————-
    ‘ Add SQL Stored Procedure parameters here
    ‘ myCmd.Parameters.AddWithValue(“@ParameterName”, _ParameterValue)
    ‘——————————————————————————-
    ‘Define the SQL Reader and execute it
    Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
    Dim result as string
    Try
       
        ‘Read the line
        if sqlReader.HasRows then
              while sqlReader.Read()
                           result = sqlReader.GetValue(0)
                           ‘ Add Logic as needed
                     end while
        end if
        Return result
    Catch ex As Exception
              If (mapInterface = 1) Then
                     System.Windows.Forms.MessageBox.Show(ex.ToString())
              End If
              Return String.Empty
      
    Finally
              sqlReader.Close()
              myConn.Close()
    End Try
Catch ex As Exception
       If (mapInterface = 1) Then
              System.Windows.Forms.MessageBox.Show(ex.ToString())
       End If
Return String.Empty
End Try
You will notice some of the text does not follow standard .NET coding conventions or syntax. Those are the strings inside of the brackets “{ }”, usually starting with TV, which stands for Template Variable. These are replacement variables that are replaced when the template is created in a script to speed up the coding process. 

To create the Template Variables, click on the Manage Variables button.

 

For this script, we need to create two new Template Variables. 

In the Variable Name field, enter TV_SQLCONNECTION and click Add. This will insert the variable into the list. 

Change the Data Type to Constant. 

Enter a Prompt Description.

Enter the next variable name, TV_STOREDPROCEDURE in the Variable Name field and click add. 

Change the Data Type to Constant 

Enter the Prompt Description.

 

Click OK to save the new Variables. 

Click OK on the Script Template window to save the script.

—————————————————————————————————- 

To use the script as a task or calculated field, go to a task, such as a task that runs before a document.  Click on Run Script.



With the empty script window open, click on the Load Template button.
 

 

Select the desired template:
 

When we select the template we will be asked to replace the Template Variables with the intended values we want to use for this script. 

You will notice there are three variables to replace.  You can choose to enter a value or just continue and replace them later.

TV_SCHEDULED – This variable is used to determine if we should display an error dialog should one occur.  If we select GBL_MANUAL (or 1) as our value, we can display the dialog.  If we select GBL_SCHEDULED (or 0) as our value, we don’t display the dialog since no use is monitoring the processing. 

TV_SQLCONNECTION – This will be used to set the SQL Connection string.  If an MSSQL Default connection was created in SmartConnect (a topic for another Tech Tuesday), you can use that value as well. 

TV_STOREDPROCEDURE – The Stored Procedure name that will be executed in this script.  This could also be T-SQL code that isn’t specifically a stored procedure.

 

Once the replacement values have been entered, click OK.  We will get the same script from our template but the Template Variables will be updated.
‘————————————————————————————–
‘ Script Name:  Execute SQL Command
‘ Created On:   2013-Nov-18
‘ Author:       Lorren Zemke
‘  Executing a stored procedure within a calculated field to return a single data element
‘  This script uses a Data Reader to loop through the rows to get the value of the last row
‘ History:     
‘—————————————————————————————
‘———————————– CONFIGURATION ————————————-
Dim mapInterface As Integer = GBL_MANUAL
‘—————————————————————————————
Try
    ‘******************************************************************
    ‘ This section is used to set the connection string to execute the stored procedure
   
    ‘ Requires setting up MSSQL Default servers from the Maintenance tab
   
    ‘Declare the connection string based on the configuration variables
   
Dim conString As New String(_MYSQL)
      
    ‘ Set connection string based on the company defined at the destination
    ‘ to match the company connection string
   
    ‘If _COMPANY = ” TWO” Then
    ‘           conString = _SQLDEF_TWO
    ‘Else
    ‘           conString = _SQLDEF_OTHERCOMPANY
    ‘End If
    ‘*******************************************************************
  
    ‘Create the SQL connection and open it
    Dim myConn As System.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection(conString)
    myConn.Open()
    ‘Define the query command to be run
    Dim sqlCode As String = paCreateMissingBudgetPeriodics
    ‘Declare the SQL command as the query string
    Dim myCmd AsSystem.Data.SqlClient.SqlCommand = NewSystem.Data.SqlClient.SqlCommand(sqlCode, myConn)
    myCmd.CommandType = System.Data.CommandType.StoredProcedure
    ‘——————————————————————————-
    ‘ Add SQL Stored Procedure parameters here
    ‘ myCmd.Parameters.AddWithValue(“@ParameterName”, _ParameterValue)
    ‘——————————————————————————-
    ‘Define the SQL Reader and execute it
    Dim sqlReader AsSystem.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
    Dim result as string
    Try
       
        ‘Read the line
        ifsqlReader.HasRows then
              while sqlReader.Read()
                     result = sqlReader.GetValue(0)
                     ‘ Add Logic as needed
              end while
        end if
        Return result
    Catch ex As Exception
              If (mapInterface = 1) Then
                     System.Windows.Forms.MessageBox.Show(ex.ToString())
              End If
              Return String.Empty
      
    Finally
              sqlReader.Close()
              myConn.Close()
    End Try
Catch ex As Exception
       If (mapInterface = 1) Then
              System.Windows.Forms.MessageBox.Show(ex.ToString())
       End If
       Return String.Empty
End Try
Now, you can modify the script as needed but remember, I can use this template as my starting point for any .NET process I need to create.

Hopefully this has provided a starting point for how to use the Script Templates in SmartConnect.
Happy Integrating,
Lorren

Want more in depth knowledge of SmartConnect? Feel free to reach out to Lorren or our sales team at lorren.zemke@eonesolutions.com or sales@eonesolutions.com for more training information!

2 Comments

  1. My Homepage on December 20, 2015 at 3:56 am

    … [Trackback]

    […] Read More: eonesolutions.com/blog-post/tech-tuesday-creating-and-using-smartconnect-script-templates/ […]

  2. Michael Taylor on August 24, 2017 at 5:38 pm

    Can you share any other tasks? Run SQL Command or Run Map? Even by copying and pasting it out of the XML file…?

Leave a Comment





RECENT POSTS


Happy Thanksgiving from eOne! - US Office Closed November 25 & 26
Popdock: What is your Locale?
Q4 2021 Partner All Hands Call - Recording Now Available
An Announcement regarding SmartList Builder and SmartView from eOne Solutions
Join us for the Q4 2021 Partner All Hands Call!

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.