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.

 

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