This article will show the basics of using a SmartConnect scripting to query additional data during an integration with the focus on the MSSQL ADO.NET Provider. With the capabilities of .NET in a SmartConnect calculated field or script task, almost any piece of data can be read or updated, if one of the Microsoft ADO.NET Data Providers can connect to the data. There are a lot of resources on the internet about how to read and write data using .NET Connections and Commands, some of which have been listed at the end of this article. If you are not familiar with using ADO.NET, I would suggest looking through the links at the end of this document to get an understanding of basic ADO.NET development.
Default Connections
The first step to querying data is to create your connection to the data using the appropriate .NET Data provider. In SmartConnect, default connections types have been made available for ODBC, OLEDB and Microsoft SQL Server providers.
Open SmartConnect, click on the Maintenance menu and then click on either ODBC Data Source, OLEDB Data Source or MSSQL Defaults to create your connection string that will be used in the script. The connection type you pick will determine which ADO.NET library will be used.
There are two reasons to create default connections. One, create a secure connection to our SQL Data Source that hides connection information in the scripts and two, there is now only one place to change connection information versus changing each script.
The type of .NET Library that will be used for the script will determine which connector to create. My typical practice is to create an MSSQL Default connection that will be used for any calculated fields or tasks and to create the ODBC Connection that may be used as a data source for a map. Having the extra connectors at this point does not hurt and can speed up implementation by not having to stop halfway through the mapping to create another connector.
Library |
Connector |
System.Data.Odbc |
ODBC Data Source |
System.Data.OleDb |
OLEDB Data Source |
System.Data.SqlClient |
MSSQL Defaults |
ODBC
To create an ODBC connection, click on the maintenance menu and then click on ODBC Data Source.
Then Click Add
Provide a name and description for the connection.
Click on the Ellipsis to the right of the Connection String to open the Connections window. The connection string is not editable, so if changes are required to an ODBC Connection, follow the same steps to make those changes.
Select the Provider or DSN.
Enter Credentials
Click Ok
OleDB
To create an OLEDB connection, click on the maintenance menu and then click on OLEDB Data Source.
Then Click Add
Provide a name and description for the connection.
Click on the Ellipsis to the right of the Connection String to open the Connections window. The connection string is not editable, so if changes are required to an OLEDB Connection, follow the same steps to make those changes. Be sure to test the Connection before saving.
Choose the OLE DB Provider
Enter credentials
Test the Connection
Click OK
MSSQL
To create an MS SQL connection, click on the maintenance menu and then click on MSSQL Defaults.
Then Click Add
Provide a name and description for the connection.
Click on the Ellipsis to the right of the Connection String to open the Connections window. The connection string is not editable, so if changes are required to an MSSQL Default Connection, follow the same steps to make those changes.
Calculated Field
To create a Calculated Field in any map, open the destination node, click on Additional Columns and then Columns. Click on Calculated to open the Create new calculation window.
The list of Default connections created above will all appear in the left hand pane of any calculated field, restriction, run script task or destination definition script.
Reading Data
There are several ways to read data using ADO.NET by calling a stored procedure that returns data or writing a T-SQL command with several differences. One difference is the query text, another is the Command.CommandType value, either Text or StoredProcedure, and finally how the data is returned.
The full scripts can be downloaded here. There are scripts for VB.NET and C# for each default connection type that exists within SmartConnect (MSSQL, ODBC, OLEDB). The example maps and scripts, simply take the Vendor ID and query SQL to get the Vendor Name, returning it in a calculated field.
Connection
The first thing that needs to be done is to create the Connection object, using the default connectors created above. In this example, the _SQLDEF_SQLCLIENT_DATA_CONNECTION is the connection made above for the MSSQL Defaults.
Expand the
C#
//Create the SQL connection and open it
string conString = _SQLDEF_SQLCLIENT_DATA_CONNECTION;
System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(conString);
myConn.Open();
myConn.ChangeDatabase(GlobalCompanyID); // to ensure it’s using the runtime company or use a specific database name if needed
VB.NET
‘Create the SQL connection and open it
Dim conString As New String(_SQLDEF_SQLCLIENT_DATA_CONNECTION)
Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
myConn.Open()
myConn.ChangeDatabase( GlobalCompanyID) ‘ to ensure it’s using the runtime company or use a specific database name if needed
Command
Create a command object that will be used to execute the query by connecting it to the SQL connection, defining the command type and the SQL that will be executed.
To execute a SQL stored procedure, provide the stored procedure name and the parameters to the stored procedure, if any.
To execute SQL text, provide the SQL text that will be used.
Stored Procedure
NOTE: The syntax for the StoredProcedure Command Type may be different for each ADO.NET provider.
C#
//Declare the SQL command using the procedure name in the first parameter
string procedureName = “E1_Get_Vendor_Name”;
System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(procedureName, conString);
myCmd.CommandType = System.Data.CommandType.StoredProcedure;
VB.NET
‘Declare the SQL command using the procedure name in the first parameter
Dim procedureName as string = “E1_Get_Vendor_Name”
Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(procedureName, myConn)
myCmd.CommandType = System.Data.CommandType.StoredProcedure
To execute SQL Text instead of a stored procedure, define the SQL text when creating the SQL command versus the stored procedure name.
SQL Text
C#
//Declare the SQL text to our variable
string SQLCode = “SELECT VENDNAME FROM PM00200 WHERE VENDORID = ‘” + _VENDORID + “‘ “;
System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(SQLCode, conString);
myCmd.CommandType = System.Data.CommandType.Text;
VB.NET
‘Declare the SQL command using the procedure name in the first parameter
Dim SQLCode as string = “SELECT VENDNAME FROM PM00200 WHERE VENDORID = ‘” & _VENDORID & “‘ ”
Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SQLCode, myConn)
myCmd.CommandType = System.Data.CommandType.Text
Reading Data
To get the data from the command object, there are different methods of the command object that can be used. Store the returned value into a variable and then process that variable as needed depending on where you are using this process within Smartconnect. If it is a calculated field, you will always want to return some value that will be put into the mapped field. If it is a Task or Restriction you will want to return true or false.
ExecuteReader
One method is to use the Data Reader object, each ADO.NET provider has their own data reader. This allows use to read the value from each field and place it into a variable which can then be returned as the value of the calculated field or stored into a Global Variable. If there are multiple rows of data returned, the code below goes through each row, so we do need to be aware of that and only do a single read if we only want the first row of data.
C#
//Define the SQL Reader and execute it
System.Data.SqlClient.SqlDataReader sqlReader = myCmd.ExecuteReader();
string result = null; //Be sure to use the correct data type
try
{
//Read the line
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
result = sqlReader.GetValue(0);
// Add Logic as needed
}
}
return result;
}
catch (Exception ex)
{
//If the mapInterface variable is GBL_MANUAL, display the exception here
if (mapInterface == int.Parse(GBL_MANUAL))
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
return string.Empty;
}
finally
{
sqlReader.Close();
myConn.Close();
}
VB.NET
‘Define the SQL Reader and execute it.
Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
Dim result as string = string.empty ‘Be sure to use the correct data type
Try
‘Read the line
if sqlReader.HasRows then
while sqlReader.Read()
result = sqlReader.GetValue(0)
‘ Add any additional logic as needed
end while
end if
Return result ‘Return the result if we are using a calculated field or place into Global Variable if desired
Catch ex As Exception
‘If the mapInterface variable is GBL_MANUAL, display the exception here
If (mapInterface = 1) Then
System.Windows.Forms.MessageBox.Show(ex.ToString())
End If
Return String.Empty
Finally
sqlReader.Close()
myConn.Close()
End Try
ExecuteScalar
If the only value needed is the first column of the first row of the data returned, we can simply run the ExecuteScalar method regardless if we are using the SQL Text or SQL Stored Procedure Command Type.
C#
//execute the sql query and set it to a variable
string result = null; //Be sure to use the correct data type
try
{
result = myCmd.ExecuteScalar();
return result;
}
catch (Exception ex)
{
//If the mapInterface variable is GBL_MANUAL, display the exception here
if ((mapInterface == 1))
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
return string.Empty;
}
finally
{
myConn.Close();
}
VB.NET
‘Execute the SQL Query and set it to a variable.
Dim result as string = string.empty ‘Be sure to use the correct data type
Try
result = myCmd.ExecuteScalar()
Return result ‘Return the result if we are using a calculated field or place into Global Variable if desired
Catch ex As Exception
‘If the mapInterface variable is GBL_MANUAL, display the exception here
If (mapInterface = 1) Then
System.Windows.Forms.MessageBox.Show(ex.ToString())
End If
Return String.Empty
Finally
myConn.Close()
End Try
ExecuteNonQuery
If we do not care what the results of the SQL command execution are, we can use the ExcecuteNonQuery method to just run the SQL code. This will typically be useful in a Script Task that may process data before or after a map runs.
C#
try
{
//Execute the SQL code defined in the SQL Command object
myCmd.ExecuteNonQuery();
return true; //Successful execution
}
catch (Exception ex)
{
//If the mapInterface variable is GBL_MANUAL, display the exception here
if ((mapInterface == 1))
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
return false;
}
finally
{
myConn.Close();
}
VB.NET
Try
‘Execute the SQL code defined in the SQL Command object
myCmd.ExecuteNonQuery()
return true ‘Successful execution
Catch ex As Exception
‘If the mapInterface variable is GBL_MANUAL, display the exception here
If (mapInterface = 1) Then
System.Windows.Forms.MessageBox.Show(ex.ToString())
End If
Return false
Finally
myConn.Close()
End Try
Script Task
As with calculated fields, pick the Task Section, right click and select Run Script Task. The big difference with the Script Tasks is they must return a true or false versus the results of the query like you may do with the Calculated Field. If the result of the query is needed in another part of the map, set the result of the query to a global variable.
Additional Scripts
The scripts that are created for the Destination Definition (Companies/Organizations) and Map Restrictions can use the same process. Remember, however, these scripts run for every record in the data source and may have an adverse effect on performance depending on how long it takes to connect to the source and execute the query
Samples
Follow this link to get to the full sample scripts. The Calculated Field on each map, VENDORNAME, contains the same script for the different versions of connector and VB.NET or C#. There is one SQL script in the zip file for creating the stored procedure that will need to be run before using the Stored Procedure examples.
Script Templates
If the same code is being used multiple times, I would recommend creating a Script Template and picking that as the starting point and adjust from there. Here is an article on how to create and use Script Templates.
Additional Resources
Here are a few links to learning about using ADO.NET.
Using Commands: https://msdn.microsoft.com/en-us/library/ms971481.aspx#adonetbest_topic4
Using Connections: https://msdn.microsoft.com/en-us/library/ms971481.aspx#adonetbest_topic5
ADO.NET 101 SqlCommand: https://technet.microsoft.com/en-us/library/aa175652(v=sql.80).aspx
ADO.NET Code Samples: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples