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,
