I just recently posted a SQL Server activity library to the Citrix Developer Network (CDN).  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.  The activity library, setup instructions, and full source code can be downloaded here.

I actually needed this type of library for a Workflow Studio project I’m currently on.  I coded the activity library to be general enough for anyone to use within their own environments.  The environment I’ve been using with this activity consists of Workflow Studio 1.1 and SQL Server 2005.  I haven’t tested with other versions of SQL Server, but I am providing the full source code to this library in case you need to tweak anything.

There are two activities within the SQL Server activity library:

  • 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.

There are five properties that need to be set on the SQLSelect/SQLExecute activities:

  • 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 SQL Server activity library was developed using the Workflow Studio SDK:
If you need to review the code and make changes, I highly recommend reviewing my previous blog series on using the Workflow Studio SDK.  This blog series will help you get your Visual Studio development environment up and running so you can make changes to the code. 

For the programmers out there, the activities themselves leverage the standard ADO.NET objects for connecting to a SQL Server database and executing a command.  If you are familiar with ADO.NET, then the code should be fairly straightforward to walk through.

Finally:

When you check out the CDN you’ll see the full setup and usage instructions so I didn’t restate them here.  The SQL queries you execute can be static (using hard-coded values) or dynamic (based on the bindable properties of other activities within the workflow).  This makes this activity pretty flexible to use in any kind of situation or query you may encounter.  Have fun and I hope this helps you out as well!