Continuing the effort to provide more free Citrix Workflow Activities here is another
one that is targeted at bringing easy SQL Server database access into the workflow studio
designer.

Background:

So let’s provide a little bit of context. If you have been following the workflow studio developer network here on community.citrix.com, you probably have noticed that there is already an existing SQL Server Activity library available for download coded up by Ed York.
Those SQL Libraries are available here and are excellent if you have some TSQL skill. They are generic activities and can be used with pretty much any SQL statement you can think of. What I wanted to achieve with this updated set of activities was to take Ed York’s original idea and extend it into multiple activities, each performing a
specific function. In addition to getting more specific activities I thought it would be interesting
to explore the idea of providing a custom editor for some properties, making for a richer user experience when using the activities.

Download Information


If you want to download the activity installer, you can do that here (This will install the activity library into the GAC on the device it is run on)
Download Activity Installer

If you would like to download the source code for the libraries, you can do that as well here.
Download Source Code for SQL Activity Library

Lets break this post up into two areas, first an overview of the activities and some screen shots showing what
can be done with them, second lets review the custom property editor that enables a ritcher UI experience.

Activity Overview:

The SQL Server activity library currently consists of 12 activities. This library was designed with the goal of providing multiple activities, each with a very specific function and to be easily used without knowing much of TSQL.

Below is a listing of the activities available in this library.

Activity Name Activity Description
SQL Database Connection Establishes a connection to a SQL Server
SQL Create Database Creates a SQL Server database
SQL Create Table Creates a table with a specified database. Also allows you to specify columns for the table
SQL Database Table Returns a collection of rows based on a given SQL statement.
SQL Create User Create a SQL user within the database server. (Same as “Create Login”)
SQL Delete Database Delete’s a specified database.
SQL Delete User Delete’s a SQL user.
SQL Add User to DB Add an existing SQL user right to a given SQL database.
SQL Remove User From DB Remove’s a user’s access from a given database
SQL Insert Command Generic insert SQL command
SQL Update Command Generic update SQL Command
SQL Delete Command Generic delete SQL Command


Note: In a future version I am looking to add support for OLEDB based database, but I am interested in your feedback around this. You can email me at john dot mcbride at citrix dot com or catch me on twitter at @johnmcbride .
Current this library only supports SQL Server 2005,2008.

Custom Property Editors:

Now lets jump into custom property editors and how they are used within this library, specifically to make adding columns to a table easier.

So what is a custom property editor? At a high level, a custom property editor is a way for you to add your own UI for editing a specific property. This enables you to control what/how the users enters the information into your custom property even more than the standard property validators within workflow studio. These also come in helpful when you have a complex property of an activity that you will almost certainly want to edit that property with your own editor.

Below you can see the custom property called “Columns”. That property has a custom editor on it which when the “…” property button is pressed will display the custom editor form as shown below.

Property Grid
Custom Property Editor

So how exactly is this done? If you want to write your own custom property editor you will have to implement a couple of things.

  • Create a class that inherits from System.Drawing.Design.UITypeEditor (this will allow you to spin up whatever UI you want to to edit your property.
  • Create a windows form UI that will allow you to edit your property.
  • Specify on the property that you want to use a custom editor. You can do this by adding and attribute like the following to your property. This lets the property know what editor to spin up when the user hits the button.
    [Editor(typeof(SQLColumnTypeEditor), typeof(UITypeEditor))]
  • Potentially, depending on how complex your property might be, you might have to implement your own property serializer. I didnt have to do that in this case, but you might if your property is of a complex type. There is an example of this is the source code attached to this post if your interested in how to accomplish it.

There are a few more tidbits in this library that i thought were fun to develop, one being the use of “[OutputAttribute]” on the connection property of the sql connection activity. What this means, is that you can put one connection activity on the workflow and set its connection string. Once that is done, every other property, can then use the sql connection activity’s “Connection” property to perform their function.

As always, let me know what you think. You can find me at john dot mcbride at citrix dot com or you can catch my on twitter at @johnmcbride