SQL Server Activity Library

The SQL Server activity library allows you to execute SQL statements (SELECT, INSERT, UPDATE, DELETE, etc.) on a Microsoft SQL Server database as part of a workflow within Citrix Workflow Studio. 

There are two activities within this activity library as noted below.  The activity library plus full source code is provided in the download section below.

• SQLSelect activity - used to run SELECT queries on a database.  The output of this activity is a collection containing the query results.  Since the output is a "collection" type object, you can loop through this collection within Workflow Studio using the For Each Object activity and dump the collection contents to an XML file via the ExportToXML activity.
• SQLExecute activity - used to run INSERT, UPDATE, and DELETE queries on a database.  The output of this activity is a string containing the number of records affected by the SQL statement.

Downloads 

Download the SQL Server Activity Library (DLL)
Download the SQL Server Activity Library source code

Setup Instructions 

To set up and test the SQL Server activity library, follow the steps below on your Workflow Studio machine. 

(1) Copy SQLActivityLibrary.dll from the Activity ZIP file to C:\Program Files\Citrix\Workflow Studio.

(2) Open the Workflow Studio Console and create a new workflow.

(3) The Workflow Studio Designer is now open.  To add the SQL Server activity library to the activities pane, navigate to Tools->Choose Activities from the file menu.  Browse to the SQLActivityLibrary.dll (C:\Program Files\Citrix\Workflow Studio\SQLActivityLibrary.dll) and ensure the two SQL activities are selected before closing the Choose Activities dialog.

(4) Once the SQL Server activity library is listed within the activities pane, you can drag the SQLSelect and/or SQLExecute activity to the designer surface.  Both of these activities require five properties to be set as noted in the screen shot below:

• Database Server - SQL Server machine hosting the database
• Database Name - Name of the database to connect to
• SQL Command - SQL statement to execute on the database
• SQL Username - SQL Server username for connecting to the database
• SQL Password - SQL Server password for connecting to the database

The SQL Command property will be set differently based on which SQL activity you are using.  The SQLSelect activity will expect a SELECT statement here.  The SQLExecute activity will expect an INSERT, UPDATE, or DELETE statement here (essentially any non-SELECT query).

The screen shot below provides a sample SELECT statement you can use within the SQLSelect activity.

The SQL statements you construct do not have to be static statements as shown above.  You can create dynamic statements that are based on the bindable properties of the other activities within the workflow.  An example is shown below.  In this example, we are referencing a global property of the workflow called "gInstanceID".  

As noted above, the SQLSelect activity returns a collection containing the query results.  You can use the For Each Object activity to loop through the query results.  Within the For Each Object activity, just set the Input Collection property equal to the output of the SQLSelect activity as noted below.  
 

In the sample workflow above, we are looping through the SQL query results with the For Each Object activity and displaying the data within each row using the MessageBox activity.  The configuration of the MessageBox activity is shown below.  To get the value of the first field of the current row, reference the Field00 attribute.  To get the value of the second field of the current row, reference the Field01 attribute.  Etc.
 

Disclaimer

These software applications are provided to you as is with no representations, warranties or conditions of any kind. You may use and distribute it at your own risk. CITRIX DISCLAIMS ALL WARRANTIES WHATSOEVER, EXPRESS, IMPLIED, WRITTEN, ORAL OR STATUTORY, INCLUDING WITHOUT LIMITATION WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NONINFRINGEMENT. Without limiting the generality of the foregoing, you acknowledge and agree that (a) the software application may exhibit errors, design flaws or other problems, possibly resulting in loss of data or damage to property; (b) it may not be possible to make the software application fully functional; and (c) Citrix may, without notice or liability to you, cease to make available the current version and/or any future versions of the software application. In no event should the code be used to support of ultra-hazardous activities, including but not limited to life support or blasting activities. NEITHER CITRIX NOR ITS AFFILIATES OR AGENTS WILL BE LIABLE, UNDER BREACH OF CONTRACT OR ANY OTHER THEORY OF LIABILITY, FOR ANY DAMAGES WHATSOEVER ARISING FROM USE OF THE SOFTWARE APPLICATION, INCLUDING WITHOUT LIMITATION DIRECT, SPECIAL, INCIDENTAL, PUNITIVE, CONSEQUENTIAL OR OTHER DAMAGES, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. You agree to indemnify and defend Citrix against any and all claims arising from your use, modification or distribution of the code.