I may be dating myself, but one of the first programming rules I learned after hacking around in BASIC on the Commodore 64 was to never, ever use the GOTO statement available in many programming languages. I’m not going to go into detail as to why that is bad, but it is a general rule by which I’ve lived throughout my programming career. K2 blackpearl, however, provides some rather powerful functionality through its APIs and Workspace to allow administrative users to GOTO different points in a process instance (active workflow). Just because it bears the evil name of “GOTO” doesn’t mean we need to avoid it, does it? In testing and development, use of GOTO can actually be a very useful feature. It allows you to skip through steps of the workflow to get straight to the part you are working on, speeding up the development and testing process. So, I recommend using it in that manner, though you still need to be cautious about skipping to a part of the workflow that relies on data determined by workflow steps preceding it. In a production environment, though, use of GOTO should be utilized only as a last resort, particularly if the workflow process contains parallel activities.

Using GOTO essentially picks up the process instance, cuts any ties to parallel activities, and moves it to wherever the administrator dictates. Thus, if the parallel activity that was NOT moved via GOTO completes, the active process instance of which it is a part does not react to it completing, and any lines coming out of the activity will never be followed. This can quickly put you into a worse state than when you initially opted to use the GOTO command, and it will be much more complicated to determine what happened and how to fix it.

Thoughtful workflow design can mitigate the risk of using GOTO. For example, before branching out into any parallel activities, create a system activity (no client tasks) as the decision point for branching out. If something goes wrong in the workflow and you need to get back to a clean state, this allows you to stop the existing parallel activities and GOTO this decision point, ensuring that parallel activities are recreated appropriately and are still connected to their parent process. See the following example; after Default Activity 1 is submitted, the workflow decides whether Activities 2 and 3 are required. While this decision could be made in the “Submit” line rule, the only way to correctly regenerate Activities 2 and 3 would be to GOTO Default Activity 1 (forcing someone to complete the task again). Again, this is useful if the workflow goes awry.

So, while the GOTO feature should be generally avoided, there are times when it is extremely useful to get a workflow process out of trouble without having to restart the entire process. If your workflow is designed upfront with this in mind, then GOTO can be utilized in a safe and effective manner. Providing documentation and training to the workflow administrators is recommended so that they understand which points in the workflow are appropriate to GOTO. Assigning names to these points using a convention that clearly indicates that they are decision points also contributes to their effective and safe usage.

0 comments , permalink

Ever since I started building workflow solutions on the K2 blackpearl platform, I have consistently had to reject one common customer request due to technical limitations- the ability to act on tasks via e-mail. Specifically, when users receive an e-mail from the system telling them they have a task, users would like to respond to this e-mail with a word or letter indicating the action they want to take. For example, suppose a user receives an e-mail indicating that someone needs approval for time off. Instead of clicking a link or viewing the task list, entering the system, and clicking an action button, the user would like to be able to just reply to the e-mail with the text “Approve” or “Reject” to cause the workflow to react accordingly. It is a very reasonable and logical request that would not only save time for end users but would allow them to act on tasks using any device capable of sending and receiving e-mails.

Every time this feature is requested, however, my mind starts going down the path of overriding the event bus, intercepting e-mails, determining the task related to the e-mail, parsing the action taken, etc. It becomes at least a week of effort to even figure out whether such a solution would be possible, so it is a feature consistently pushed to the backburner in favor of other system enhancements.

As of K2 blackpearl 4.5 Update KB001420, allowing the workflow to react to e-mails from end users has become much simpler with the introduction of SmartActions. SmartActions is a new feature built directly into the K2 blackpearl platform that takes all of the plumbing effort out of having the workflow system react to e-mails from users. Still, there are a few caveats to heed.

First, configuring SmartActions is a trivial process that takes place during the K2 Configuration step after the K2 blackpearl product is installed (and can be re-run at any time). This process simply asks for an exchange server for processing e-mails to the system, a service account with access to a mailbox on the specified exchange server, and an e-mail address for handling e-mails to the system. The e-mail address must be associated with the specified service account and will auto-fill as information is selected. That is the extent of the configuration for simple SmartActions features; more complicated features are covered thoroughly in the K2 manual.

The following is a quick list of “gotchas” to keep in mind when implementing SmartActions:

Requires Microsoft Exchange Server version 2007 SP1 / 2010 with Autodiscover and Exchange Web Services (EWS) features enabled

If the version of Microsoft Exchange Server in your environment does not meet these requirements, then SmartActions cannot be utilized in your workflow solutions. SmartActions relies on features in this specific version to work as designed.

Unable to respond to multiple word actions that may contain other actions

If your workflow solution uses actions with multiple words or actions that contain within them the text of other actions, then it may be easy to confuse the workflow on which action the user is attempting to take. For example, if a solution has an action named “Approve” and another action named “Approve Conditionally,” replying to a task with “Approve Conditionally” may only cause the system to parse “Approve” and consequently fire the wrong action. To avoid this, workflows should be designed to keep actions short and unambiguous. In this case, the actions could be renamed to “Approve” and “Conditional Approval.”

Unable to block respond via e-mail for certain tasks

Once SmartActions are enabled, it is difficult to prevent them from affecting even tasks that should not be actionable via e-mail. Specifically, if a task requires more information (such as user comments), then it should not be possible to action the task with a simple “Approve” e-mail. One way to work around this is to have some post-action validation in the workflow itself. A task can be reassigned to the user if the workflow determines that the required information was not provided.

Must configure action synonyms separately

This one is not a caveat of the system, but something to make note of as another SmartActions implementation option. SmartActions may be configured with synonyms so users can type “A” instead of “Approve” in their e-mails to trigger the “Approve” action. The K2 manual covers how to do this, but much care needs to be taken in defining each action and its synonyms to prevent action ambiguity.

Must have serial number somewhere in the e-mail

K2 uses “Serial Number” to uniquely identify tasks for specific users. The serial number is a string made up of the process instance ID and activity destination instance ID separated by an underscore. It is not a very user-friendly bit of text, but without it included somewhere in the e-mail, K2 is not able to properly react to user responses. One approach to addressing this is to “hide” the serial number within an HTML link which standard task notification e-mails from K2 do by default. Alternatively, the serial number can be included at the bottom of all e-mails in small, white, or otherwise relatively inconspicuous text. This approach opens the door to potential abuse, but the risk is minimal. A clever user can identify the serial number of a task and put it into any e-mail to the system; the system reacts to the e-mail, but it is smart enough to prevent users that do not have access to the task from acting on it successfully.

Keeping these caveats in mind, SmartActions work remarkably well for the problem they were designed to solve. They respond quite intelligently to my simple attempts to trick the system – for example if I paste the serial number of someone else’s task into an e-mail to the system or indicate an invalid action, I get an e-mail back that clearly explains that my attempt to action the task failed and the reasons why. With SmartActions, no longer will I have to reject user’s requests to have the ability to act on their tasks via e-mail. So long as the K2 platform can be upgraded to 4.5 KB001420 to include this feature, implementing SmartActions on existing workflows can be a simple if not trivial process.

0 comments , permalink

Many solutions we’ve developed in the past (and probably will develop in the future) have a business requirement around sending scheduled e-mail notifications containing line of business data or even an embedded report.  The typical solution for such a requirement is to utilize SQL Server’s built-in capability to provide this sort of functionality, but that ends up with yet another service to configure and maintain, and it is not the type of service that is easy for a business user to administer.  While K2 is a workflow automation platform, it does have the ability to send e-mails AND the ability to schedule tasks AND the ability to get to line of business data, so perhaps it is possible to utilize K2 to schedule regular notifications.  Let’s take a look at how this can be set up:

First, we will need to set up a process to contain or scheduled activities.  Create a new, empty K2 project and then add a new Process to it is named ‘Scheduled Activities’:

Drag out a new activity onto the process canvas (or hold down the right mouse button and draw an upside down ‘V’ – which kind of looks like an ‘A’ to symbolize a new activity).  Name this activity ‘Scheduled Notification 1’ and hook it up to the start activity:

Drag a new ‘E-mail Event’ into this activity.  These e-mail events, when combined with SmartObjects, can be very dynamic such as having the ability to generate recipients based on code, include line of business data in the subject or body, and even embed an HTML version of a SQL Reporting Services report.  How to do that is a subject for another day, for now,we will keep it simple and just fill in some default recipient,subject and body for this e-mail using the e-mail event wizard:

So if we were to leave things like this, starting an instance of this process would send the notification once, right away, and then end.  That is not exactly what we are going for.  Let’s add another activity directly after the Scheduled Notification activity that we can use to decide when the process should loop back and send the notification again.  Additionally, we want this activity to assign a task to an administrator that would allow them to override the schedule and send the notification on demand.  To do this, either drag out a client event from the toolbox or hold down the right mouse button and draw a ‘C’ on the canvas.  This will bring up the Client Event wizard, which we will configure to take the user to www.claritycon.com because we don’t really need an interface for this event (we just want an administrator to be able to execute an ‘override’ action from the K2 worklist):

Note that we are using the process originator as the user to assign the override task to – this could be any user.  Connect the new activities so things appear like so:

So what would happen now if we were to start this process instance is that the notification would be immediately sent, then a task would be created for the process originator with an action of ‘Override’.  The notification would not be sent again unless the administrator took that action, and then it would create the override task again right after the notification was sent.  This is still not desirable, but we are almost there!  How are we going to get the notification to be sent on a schedule, without the administrator taking the override action?  The answer lies in ‘Activity Escalations’.  In blackpearl, we can configure an event to automatically expire after a certain amount of time or at a certain time of day.  The task can expire, and then the workflow can be redirected afterwards.  That sounds pretty ideal for what we are going for, let’s bring up the event escalation wizard on the Scheduled Notification 1 Override activity and configure it to escalate every five minutes (simulating a notification scheduled to be sent every five minutes – not realistic but much faster to test!):

So that should about do it – that last part was telling the activity escalation to go and send the scheduled notification when the escalation fires (every 5 minutes) and then afterwards it will assign the scheduled notification 1 override task to the administrator again – keeping the process going indefinitely.

To start out this process and test it, we will need to visit the K2 Workspace and drill down to this process definition and start a new instance:

S

After this, our e-mail should immediately arrive and then a task will appear on the worklist to override and send it again.  To do so, navigate to the default worklist in the workspace and select the ‘Override’ action off of the task’s context menu:

Doing so will send the e-mail again immediately, then create the override task once again.  If you wait for five minutes, the notification will be sent again automatically.

That should do it for simple scheduled notfications using K2 blackpearl – this concept can be expanded upon to include multiple scheduled notifications all within the same process with different e-mails and escalation rules, giving one place to go for all scheduled notification definitions, maintenance and overrides.

0 comments , permalink

There are plenty of great blog posts out there on how to get started with Visual Web Parts on SharePoint 2010 using Visual Studio 2010, but when I started to go beyond the basics I found that I had to piece together information from various sources to get the complete picture.  As such, my goal in this post is to bring all of that information together into one place.

To begin with, I have a user control built for SharePoint 2007 that takes advantage of the ‘SmartPart’ project to turn it into a web part.  This user control has custom images, references other project assemblies, uses JQuery libraries and has configurable web part properties.  These are all things I wanted to replicate in my SharePoint 2010 Visual Web Part with as little re-coding as possible, so we’ll stumble through whatever hurdles present themselves in this process.

I am not going to rehash the basics of creating a Visual Web Part project, other blogs have covered that far better than I could, but there was one interesting stumbling block I hit during the create visual web part wizard.  When I told it the SharePoint site I wanted to use for debugging, I received the following error:

Well, what the heck?  That was the only site that showed up in the dropdown and I can navigate to it just fine so I know it is correct.  What is the deal?  Searching on this error uncovered a ton of different information on the various security rights that the user opening Visual Studio must have, but not all of these recommendations seemed to be necessary.  In fact,I was able to get away with only making the user db_owner of the SharePoint_AdminContent,SharePoint_Config and application content databases.  Still seems like a lot of rights to grant a developer, but it is necessary to allow the feature to be deployed to and retracted from the farm (and it should be a development server any way, so no big deal). 

Despite making this change, I was still receiving the error above.  The final piece of this puzzle was that even though the local site name is in fact appsrv2, when the web application was created in Central Administration it was given a different URL.  Apparently, both URLs still work when browsing via IE, but Visual Studio required the URL given in Central Administration in order to connect correctly.  This pain could’ve been avoided if Visual Studio showed the URL from Central Admin in the dropdown instead of the IIS site name but oh well, we figured it out.

Now that our SharePoint debugging server is set up, every time we run our project it will deploy the web part and open up our SharePoint page where we can add the web part and see its behavior.  Then, when we are done debugging, it retracts the web part.  This is all well and good if you are in pure development mode and don’t have any pages that have that web part used in them for any other reason – for example, I had the web part on a page that I was using for a proof of concept to show end users, and every time I ended a debugging session, that page would blow up because the web part was no longer available!  Thankfully, there is an easy way around this auto-retract feature.  Right click your visual web part project and open its properties.  There should be a SharePoint tab within there, and tucked way at the bottom of that panel is an option to Auto-retract after debugging.  This is checked by default, uncheck it and it will no longer retract your web part for you (leaving any proof of concept pages functional after a debugging session):

Ok, the basics are out of the way – we have a Visual Web Part project that we can debug and deploy to SharePoint so let’s get into the guts of the functionality of this web part.  I already have the user control from the SharePoint 2007 ‘SmartPart’ approach, so I’m going to copy the entire layout and code behind into the user control created as a part of our Visual Web Part project and see what it doesn’t like.  Aside adding my references to the new project that were in the old project, everything seemed to copy over just fine.  Building the project, however, revealed that a utility project I was referencing within the web part solution was not getting picked up.  To include project references in a Visual Web Part project, they need to be added to the package.  Each Visual Web Part project has a ‘Package’ node underneath it in the Solution Explorer that has its own set of properties.  Right click it to bring up the properties window and you’ll see by default that the only item in the package is the web part itself.  Clicking the ‘Advanced’ button will allow you to add other references to the package, including project references.  Additionally, it will allow you to specify how this reference will be deployed with the web part, either into the GAC or into the web project.  In my case, I selected ‘Add’ and ‘Add Assembly from Project Output’.  This presented me with a screen to select which project I would like to include out of all the projects in my solution:

I chose to add this assembly into the GAC as that is the simplest way to make it available, though this process does seem to streamline adding the control to safe controls for you (kind of a pain to do by hand in the past).

Ok, great, with the addition of the ‘K2 Utility’ project into the web part package (something I happened to be referencing in the original web part), we can now build and deploy the visual web part.  This makes it available to add to any page just like any other web part.  Doing so, however, revealed that there are two other things that failed to come across in the deployment:  my images and scripts!  I had them included in the web part in the typical manner of an ‘Images’ and ‘Scripts’ folder right in the root of the project, but that didn’t seem to do the trick in the SharePoint 2010 world.  What we actually need to do is add a reference to SharePoint’s ‘Layouts’ folder, which automatically will include a folder for this web part to put referenced files into.  To do this, right click the Visual Web Parts project and select ‘Add’ –> ‘SharePoint “Layouts” Mapped Folder’.  Within this folder, you can then move your Images and Scripts folder from the original web part.  Unfortunately, any reference to said scripts and images need to be changed to look like this:  /_layouts/TaskList2010/Images/ or /_layouts/TaskList2010/Scripts/.  After these quick changes, images and scripts were being referenced correctly in the web part, making it look like its old self again.

Happy once again with the look of my web part, I went into the web part settings screen in SharePoint to set up the configuration values that make it actually function, and was saddened to see that there were no custom properties available in the properties page.  In the SmartPart world, all I had to do was decorate a public property of the user control with ‘System.ComponentModel.Browsable(true)’ to make it appear in the properties page, not so in SharePoint 2010.  To accomplish this, we need to add the properties to the code behind of the Visual Web Part (not the user control) and decorate the properties with this: 

System.Web.UI.WebControls.WebParts.WebBrowsable(true),
System.Web.UI.WebControls.WebParts.WebDisplayName(“Property Name”),
System.Web.UI.WebControls.WebParts.WebDescription(“”),
System.Web.UI.WebControls.WebParts.Personalizable(System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared),
System.ComponentModel.Category(“Custom Settings”),
System.ComponentModel.DefaultValue(“”)

This will make the property appear under a ‘Custom Settings’ section, but then you have to tie it into your actual user control property – kind of a tedious duplication of work but it is a one-time thing.  This tie-in will occur in the ‘CreateChildControls’ event within the Visual Web Part code behind, setting each property value of the control to its corresponding value in the Visual Web Part.

Well there you have it – it wasn’t totally a copy+paste+celebrate affair to get a SmartPart from SharePoint 2007 working as a Visual Web Part in SharePoint 2010, but there weren’t too many changes necessary.  Hopefully this collection of hurdles I encountered during the conversion will be useful to you as you move into SharePoint 2010.

0 comments , permalink

So finally we get to the fun part – the fruits of all of our middle-tier/back end labors of generating classes to interface with an XML data source that the previous posts were about can now be presented quickly and easily to an end user.  I think.  We’ll see.  We’ll be using a WPF window to display all of our various MFL information that we’ve collected in the two XML files, and we’ll provide a means of adding, updating and deleting each of these entities using as little code as possible.  Additionally, I would like to dig into the performance of this solution as well as the flexibility of it if were were to modify the underlying XML schema.  So first things first, let’s create a WPF project and include our xml data in a ‘data’ folder within.  On the main window, we’ll drag out the following controls:

• A combo box to contain all of the teams
• A list box to show the players of the selected team, along with add/delete player buttons
• A text box tied to the selected player’s name, with a save button to save any changes made to the player name
• A combo box of all the available positions, tied to the currently selected player’s position
• A data grid tied to the statistics of the currently selected player, with add/delete statistic buttons

This monstrosity of a form and its associated project will look like this (don’t forget to reference the ‘DataFoundation’ project from the ‘Presentation’ project):

To get to the visual data binding, as we learned in a previous post, you have to first make sure the project containing your bindable classes is compiled.  Do so,and then open the Data Sources pane to add a reference to the Teams and Positions classes in the DataFoundation project:

Why only Team and Position?  Well,we will get to Players from Teams, and Statistics from Players so no need to make an interface for them as we’ll see in a second.  As for Positions, we’ll need a way to bind the dropdown to ALL positions – they don’t appear underneath any of the other classes so we need to reference it directly.  After adding these guys, expand every node in your Data Sources pane and see how the Team node allows you to drill into Players and then Statistics.  This is why there was no need to bring in a reference to those classes for the UI we are designing:

Now for the seriously hard work of binding all of our controls to the correct data sources.  Drag the following items from the Data Sources pane to the specified control on the window design canvas:

• Team.Name –> Teams combo box
• Team.Players.Name –> Players list box
• Team.Players.Name –> Player name text box
• Team.Players.Statistics –> Statistics data grid
• Position.Name –> Positions combo box

That is it!  Really?  Well, no, not really… there is one caveat here in that the Positions combo box is not bound the selected player’s position.  To do so, we will apply a binding to the position combo box’s ‘SelectedValue’ to point to the current player’s PositionId value:

That should do the trick… now, all we need to worry about is loading the actual data.  Sadly, it appears as if we will need to drop to code in order to invoke our IO methods to load all teams and positions.  At least Visual Studio kindly created the stubs for us to do so, ultimately the code should look like this:

Note the weirdness with the ‘InitializeDataFiles’ call – that is my current means of telling an IO where to load the data for each of the entities.  I haven’t thought of a more intuitive way than that yet, but do note that all data is loaded from Teams.xml besides for positions, which is loaded from Lookups.xml.   I think that may be all we need to do to at least load all of the data, let’s run it and see:

Yay!  All of our glorious data is being displayed!  Er, wait, what’s up with the position dropdown?  Why is it red?  Let’s select the RB and see if everything updates:

Crap, the position didn’t update to reflect the selected player, but everything else did.  Where did we go wrong in binding the position to the selected player?  Thinking about it a bit and comparing it to how traditional data binding works, I realize that we never set the ‘value member’ (or some similar property) to tell the control to join the Id of the source (positions) to the position Id of the player.  I don’t see a similar property to that on the combo box control, but I do see a property named ‘SelectedValuePath’… that might be it, so I set it to Id and run the app again:

Hey, all right!  No red box around the positions combo box.  Unfortunately, selecting the RB does not update the dropdown to point to Runningback.  Hmmm.  Now what could it be?  Maybe the problem is that we are loading teams before we are loading positions, so when it binds position Id, all of the positions aren’t loaded yet.  I went to the code behind and switched things so position loads first and… no dice.  Same result when I run.  Why?  WHY?  Ok, ok, calm down, take a deep breath.  Get something with caffeine or sugar (preferably both) and think rationally.

Ok, gigantic chocolate chip cookie and a mountain dew chaser have never let me down in the past, so don’t fail me now!  Ah ha!  of course!  I didn’t even have to finish the mountain dew and I think I’ve got it:  Data Context.  By default, when setting on the selected value binding for the dropdown, the data context was list_team.  I don’t even know what the heck list_team is, we want it to be bound to our team players view source resource instead, like this:

Running it now and selecting the various players:

Done and done.  Everything read and bound, thank you caffeine and sugar!  Oh, and thank you Visual Studio 2010.  Let’s wire up some of those buttons now… There has got to be a better way to do this, but it works for now.  What the add player button does is add a new player object to the currently selected team.  Unfortunately, I couldn’t get the new object to automatically show up in the players list (something about not using an ‘observable collection’ – gotta look into this) so I just save the change immediately and reload the screen.  Terrible, but it works:

Let’s go after something easier:  The save button.  By default, as we type in new text for the players name, it is showing up in the list box as updated.  Cool!  Why couldn’t my add new player logic do that?  Anyway, the save button should be as simple as invoking MFL.IO.Save for the selected player, like this:

MFL.IO.Save((MFL.Player)lbTeamPlayers.SelectedItem, true);

Surprisingly, that worked on the first try.  Let’s see if we get as lucky with the Delete player button:

MFL.IO.Delete((MFL.Player)lbTeamPlayers.SelectedItem);
Refresh();

Note the use of the ‘Refresh’ method again – I can’t seem to figure out why updates to the underlying data source are immediately reflected, but adds and deletes are not.  That is a problem for another day, and again my hunch is that I should be binding to something more complex than IEnumerable (like observable collection).

Now that an example of the basic CRUD methods are wired up, I want to quickly investigate the performance of this beast.  I’m going to make a special button to add 30 teams, each with 50 players and 10 seasons worth of stats.  If my math is right, that will end up with 15000 rows of data, a pretty hefty amount for an XML file.  The save of all this new data took a little over a minute, but that is acceptable because we wouldn’t typically be saving batches of 15k records, and the resulting XML file size is a little over a megabyte.  Not huge, but big enough to see some read performance numbers… or so I thought.  It reads this file and renders the first team in under a second.  That is unbelievable, but we are lazy loading and the file really wasn’t that big.  I will increase it to 50 teams with 100 players and 20 seasons each – 100,000 rows.  It took a year and a half to save all of that data, and resulted in an 8 megabyte file.  Seriously, if you are loading XML files this large, get a freaking database!  Despite this, it STILL takes under a second to load and render the first team, which is interesting mostly because I thought that it was loading that entire 8 MB XML file behind the scenes. 

I have to say that I am quite impressed with the performance of the LINQ to XML approach, particularly since I took no efforts to optimize any of this code and was fairly new to the concept from the start.  There might be some merit to this little project after all… Look out SQL Server and Oracle, use XML files instead!  Next up, I am going to completely pull the rug out from under the UI and change a number of entities in our model.  How well will the code be regenerated?  How much effort will be required to tie things back together in the UI?

0 comments , permalink

The last post ended with us just getting started on stumbling into text template file customization, a task that required a Visual Studio extension (Tangible T4 Editor) to even have a chance at completing.  Despite the benefits of the Tangible T4 Editor, I still had a hard time putting together a solid text template that would be easy to explain.  This is mostly due to the way the files allow you to mix code (encapsulated in ) with straight-up text to generate.  It is effective to be sure, but not very readable.  Nevertheless, I will try and explain what was accomplished in my custom tt file, though the details of which are not really the point of this article (my way of saying ‘don’t criticize my crappy code, and certainly don’t use it in any somewhat real application.  You may become dumber just by looking at this code.  You have been warned’ – really the footnote I should put at the end of all of my blog posts).

To begin with, there were two basic requirements that I needed the code generator to satisfy:  Reading one to many entity framework files, and using the entities that were found to write one to many class files.  Thankfully, using the Entity Object Generator as a starting point gave us an example on how to do exactly that by using the ‘MetadataLoader’ and ‘EntityFrameworkTemplateFileManager’ – you include references to these items and use them like so:

// Instantiate an entity framework file reader and file writer
MetadataLoader loader = new MetadataLoader(this);
EntityFrameworkTemplateFileManager fileManager = EntityFrameworkTemplateFileManager.Create(this);

// Load the entity model metadata workspace
MetadataWorkspace metadataWorkspace = null;
bool allMetadataLoaded =loader.TryLoadAllMetadata(“MFL.tt”, out metadataWorkspace);
EdmItemCollection ItemCollection = (EdmItemCollection)metadataWorkspace.GetItemCollection(DataSpace.CSpace);

// Create an IO class to contain the ‘get’ methods for all entities in the model
fileManager.StartNewFile(“MFL.IO.gen.cs”);

Next, we want to be able to loop through all of the entities found in the model,and then each property for each entity so we can generate classes and methods for each.  The code for that is blissfully simple:

// Iterate through each entity in the model
foreach (EntityType entity in ItemCollection.GetItems().OrderBy(e => e.Name))
{
    // Iterate through each primitive property of the entity
    foreach (EdmProperty edmProperty in entity.Properties.Where(p => p.TypeUsage.EdmType is PrimitiveType && p.DeclaringType == entity))
    {
        // TODO:  Create properties
    }
    // Iterate through each relationship of the entity
    foreach (NavigationProperty navProperty in entity.NavigationProperties.Where(np => np.DeclaringType == entity))
    {
        // TODO:  Create associations
    }
}

There really isn’t anything more advanced than that going on in the text template – the only thing I had to blunder through was realizing that if you want the generator to interpret a line of code (such as our iterations above),you need to enclose the code in while if you want the generator to interpret the VALUE of code, such as putting the entity name into the class name, you need to enclose the code in like so:

public partial class

To make a long story short, I did a lot of repetition of the above to come up with a text template that generates a class for each entity based on its properties, and a set of IO methods for each entity based on its relationships.  The two work together to provide lazy-loading for hierarchical data (such getting Team.Players) so it should be pretty intuitive to use on a front-end.  This text template is available here – you can tweak the ‘inputFiles’ array to load one or many different edmx models and generate the basic xml IO and class files, though it will probably only work correctly in the simplest of cases, like our ‘MFL’ model described in the previous post.  Additionally, there is no validation, logging or error handling which is something I want to handle later by stumbling through the enterprise library 5.0.

The code that gets generated isn’t anything special, though using the LINQ to XML feature was something very new and exciting for me – I had only worked with XML in the past using the DOM or XML Reader objects along with XPath, and the LINQ to XML model is just so much more elegant and supposedly efficient (something to test later).  For example, the following code was generated to create a ‘Player’ object for each ‘Player’ node in the XML:

        return from element in GetXmlData(_PlayerDataFile).Descendants(“Player”)
            select new Player
            {
                Id = int.Parse(element.Attribute(“Id”).Value)
                ,ParentName = element.Parent.Name.LocalName
                ,ParentId = long.Parse(element.Parent.Attribute(“Id”).Value)
                ,Name = element.Attribute(“Name”).Value
                ,PositionId = int.Parse(element.Attribute(“PositionId”).Value)
            };

It is all done in one line of code, no looping needed.  Even though ‘GetXmlData’ loads the entire xml file just like the old XML DOM approach would have, it is supposed to be much less resource intensive.  I will definitely put that to the test after we develop a user interface for getting at this data.  Speaking of the data… where IS the data?  We’ve put together a pretty model and a bunch of code around it, but we don’t have any data to speak of.  We can certainly drop to our favorite XML editor and crank out some data, but if it doesn’t totally match our model, it will not load correctly.  To help with this, I’ve built in a method to generate xml at any given layer in the hierarchy.  So for us to get the closest possible thing to real data, we’d need to invoke MFL.IO.GenerateTeamXML and save the results to file.  Doing so should get us something that looks like this:

Sadly, it is missing the Positions node (haven’t thought of a way to generate lookup xml yet) and the data itself isn’t quite realistic (well, as realistic as MFL data can be anyway…).  Let’s manually remedy that for now to give us a decent starter set of data.  Note that this is TWO xml files – Lookups.xml and Teams.xml:

Ok, so we have some data, we have a way to read/write that data and we have a friendly way of representing that data.  Now, what remains is the part that I have been looking forward to the most: present the data to the user and give them the ability to add/update/delete, and doing so in a way that is very intuitive (easy) from a development standpoint.

0 comments , permalink

I would now like to expand a little on what I stumbled through in part I of my Visual Studio 2010 post and touch on a few other features of VS 2010.  Specifically, I want to generate some code based off of an Entity Framework model and tie it up to an actual data source.  I’m not going to take the easy way and tie to a SQL Server data source, though, I will tie it to an XML data file instead.  Why?  Well, why not?  This is purely for learning, there are probably much better ways to get strongly-typed classes around XML but it will force us to go down a path less travelled and maybe learn a few things along the way.  Once we get this XML data and the means to interact with it, I will revisit data binding to this data in a WPF form and see if I can’t get reading, adding, deleting, and updating working smoothly with minimal code. 

To begin, I will use what was learned in the first part of this blog topic and draw out a data model for the ‘MFL’ (My Football League) – I don’t want the NFL to come down and sue me for using their name in this totally football-related article.  The data model looks as follows, with Teams having Players,and Players having a position and statistics for each season they played:

Note that when making the associations between these entities,I was given the option to create the foreign key but I only chose to select this option for the association between Player and Position.  The reason for this is that I am picturing the XML that will contain this data to look somewhat like this:

Statistic will be under its associated Player node, and Player will be under its associated Team node – no need to have an Id to reference it if we know it will always fall under its parent.  Position, however, is more of a lookup value that will not have any hierarchical relationship to the player.  In fact, the Position data itself may be in a completely different xml file (something I’d like to play around with), so in any case, a player will need to reference the position by its Id.

So now that we have a simple data model laid out, I would like to generate two things based on it: 

• A class for each entity with properties corresponding to each entity property
• An IO class with methods to get data for each entity, either all instances, by Id or by parent.

Now my experience with code generation in the past has consisted of writing up little apps that use the code dom directly to regenerate code on demand (or using tools like CodeSmith).  Surely, there has got to be a more fun way to do this given that we are using the Entity Framework which already has built-in code generation for SQL Server support.  Let’s start with that built-in stuff to give us a base to work off of.  Right click anywhere in the canvas of our model and select ‘Add Code Generation Item’:

So just adding that code item seemed to do quite a bit towards what I was intending:

It apparently generated a class for each entity, but also a whole ton more.  I mean a TON more.  Way too much complicated code was generated – now that code is likely to be a black box anyway so it shouldn’t matter, but we need to understand how to make this work the way we want it to work, so let’s get ready to do some stumbling through that text template (tt) file.

When I open the .tt file that was generated, right off the bat I realize there is going to be trouble… there is no color coding, no intellisense no nothing!  That is going to make ‘stumbling through’ more like ‘groping blindly in the dark while handcuffed and hopping on one foot’, which was one of the alternate titles I was considering for this blog.  Thankfully, the community comes to my rescue and I won’t have to cast my mind back to the glory days of coding in VI (look it up, kids).  Using the ‘Extension Manager’ (Available under the ‘Tools’ menu), I did a quick search for ‘tt editor’ in the Online Gallery and quickly found the ‘Tangible T4 Editor’:

Downloading and installing this was a breeze, and after doing so I got some color coding and intellisense while editing the tt files.  If you will be doing any customizing of tt files, I highly recommend installing this extension.  Next, we’ll see if that is enough help for us to tweak that tt file to do the kind of code generation that we want

0 comments , permalink

I have recently attended a three-day training session on K2′s Case Management Framework (CMF), a free framework built on top of K2′s blackpearl workflow product, and I have come away with several different impressions for some of the different aspects of the framework. 

Before we get into the details, what is the Case Management Framework?  It is essentially a suite of tools that, when used together, solve many common workflow scenarios.  The tool has been developed over time by K2 consultants that have realized they tend to solve the same problems over and over for various clients, so they attempted to package all of those common solutions into one framework.  Most of these common problems involve workflow process that aren’t necessarily direct and would tend to be difficult to model.  Such solutions could be achieved in blackpearl alone, but the workflows would be complex and difficult to follow and maintain over time.  CMF attempts to simplify such scenarios not so much by black-boxing the workflow processes, but by providing different points of entry to the processes allowing them to be simpler, moving the complexity to a middle layer.  It is not a solution in and of itself, development is still required to tie the pieces together.

CMF is under continuous development, both a plus and a minus in that bugs are fixed quickly and features added regularly,but it may be difficult to know which versions are the most stable.  CMF is not an officially supported K2 product,which means you will not get technical support but you will get access to the source code.

The example given of a business process that would fit well into CMF is that of a file cabinet, where each folder in said file cabinet is a ‘case’ that contains all of the data associated with one complaint/customer/incident/etc. and various users can access that case at any time and take one of a set of pre-determined actions on it.  When I was given that example, my first thought was that any workflow I have ever developed in the past could be made to fit this model – there must be more than just this model to help decide if CMF is the right solution.  As the training went on, we learned that one of the key features of CMF is SharePoint integration as each ‘case’ gets a SharePoint site created for it, and there are a number of excellent web parts that can be used to design a portal for users to get at all the information on their cases.  While CMF does not require SharePoint, without it you will be missing out on a huge portion of functionality that CMF offers.  My opinion is that without SharePoint integration, you may as well write your workflows and other components the old fashioned way.

When I heard that each case gets its own SharePoint site created for it, warning bells immediately went off in my head as I felt that depending on the data load, a CMF enabled solution could quickly overwhelm SharePoint with thousands of sites – so we have yet another deciding factor for CMF:  Just how many cases will your solution be creating?  While it is not necessary to use the site-per-case model, it is one of the more useful parts of the framework.  Without it, you are losing a big chunk of what CMF has to offer.

When it comes to developing on top of the Case Management Framework, it becomes a matter of configuring what makes up a case, what can be done to a case, where each action on a case should take the user, and then typing up actions to case statuses.  This last step is one that I immediately warmed up to, as just about every workflow I’ve designed in the past needed some sort of mapping table to set the status of a work item based on the action being taken – definitely one of those common solutions that it is good to see rolled up into a re-useable entity (and it gets a nice configuration UI to boot!).  This concept is a little different than traditional workflow design, in that you don’t have to think of an end-to-end process around passing a case along a path, rather, you must envision the case as central object with workflow threads branching off of it and doing their own thing with the case data.  Certainly there can be certain workflow threads that get rather complex, but the idea is that they RELATE to the case, they don’t BECOME the case (though it is still possible with action->status mappings to prevent certain actions in certain cases, so it isn’t always a wide-open free for all of actions on a case).

I realize that this description of the Case Management Framework merely scratches the surface on what the product actually can do, and I don’t think I’ve conclusively defined for what sort of business scenario you can make a case for Case Management Framework.  What I do hope to have accomplished with this post is to raise awareness of CMF – there is a (free!) product out there that could potentially simplify a tangled workflow process and give (for free!) a very useful set of SharePoint web parts and a nice set of (free!) reports.  The best way to see if it will truly fit your needs is to give it a try – did I mention it is FREE?  Er, ok, so it is free, but only obtainable at this time for K2 partners…

1 comment , permalink

I’ve spent the better part of the last two years doing nothing but K2 workflow development, which until very recently could only be done in Visual Studio 2005 so I am a bit behind the times. I seem to have skipped over using Visual Studio 2008 entirely, and I am now ready to stumble through all that I’ve missed. Not that I will abandon my K2 ramblings, but I need to get back to some of the other technologies I am passionate about but haven’t had the option of working with them on a day-to-day basis as I have with K2 blackpearl. Specifically, I am going to be focusing my efforts on what is new in the Entity Framework and WPF in Visual Studio 2010, though you have to keep in mind that since I have skipped VS 2008, I may be giving VS 2010 credit for things that really have been around for a while (hey, if I haven’t seen it, it is new to me!).

I have the following simple goals in mind for this exercise:

· Entity Framework – Model an inherited class

· Entity Framework – Model a lookup entity

· WPF – Bind a list of entities

· WPF – on selection of an entity in the bound list, display values of the selected entity

· WPF – For the lookup field, provide a dropdown of potential values to lookup

All of these goals must be accomplished using as little code as possible, relying on the features we get out of the box in Visual Studio 2010. This isn’t going to be rocket science here,I’m not even looking to get or save this data from/to a data source,but I gotta start somewhere and hopefully it will grow into something more interesting.

For this exercise, I am going to try to model some fictional data about football players and personnel (maybe turning this into some sort of NFL simulation game if I lose my job and can play with this all day), so I’ll start with a ‘Person’ class that has a name property, and extend that with a ‘Player’ class to include a Position lookup property. The idea is that a Person can be a Player, Coach or whatever other personnel type may be associated with a football team but we’ll only flesh out the ‘Player’ aspect of a person for this.

So to get started, I fired up Visual Studio 2010 and created a new WPF Application:

To this project, I added a new ADO.NET Entity Data Model named ‘PlayerModel’ (for now, not sure what will be an appropriate name so this may be revisited):

I chose for it to be an empty model, as I don’t have a database designed for this yet:

Using the toolbox, I dragged out an entity for each of the items we identified earlier: Person, Player and Position, and gave them some simple properties (note that I kept the default ‘Id’ property for each of them):

Now to figure out how to link these things together the way I want to – first, let’s try to tell it that ‘Player’ extends ‘Person’. I see that ‘Inheritance’ is one of the items in the toolbox, but I can’t seem to drag it out anywhere onto the canvas. However, when I right-click an element, I get the option to Add Inheritance to it, which gives us exactly what we want:

Ok, now that we have that, how do we tell it that each player has a position? Well, despite ‘association’ being in the toolbox, I have learned that you can’t just drag and drop those elements so I right click ‘Player’ and select ‘Add -> Association’ to get the following dialog:

I see the option here to ‘Add foreign key properties’ to my entities – I’ve read somewhere this this is a new and highly-sought after feature so I’ll see what it does. Selecting it includes a ‘PositionId’ on the Player element for me, which seems pretty database-centric and I would like to see if I can live without it for now given that we also got the ‘Position’ property out of this association. I’ll bring it back into the fold if it ends up being useful later. Here is what we end up with now:

Trying to compile this resulted in an error stating that the ‘Player’ entity cannot have an Id, because the ‘Person’ element it extends already has a property named Id. Makes sense, so I remove it and compile again. Success, but with a warning… but success is a good thing so I’ll pretend I didn’t see that warning for now. It probably has to do with the fact that my Player entity is now pretty useless as it doesn’t have any non-navigation properties.

So things seem to match what we are going for, great… now what the heck do we do with this? Let’s switch gears and see what we get for free dealing with this model from the UI. Let’s open up the MainWindow.xaml and see if we can connect to our entities as a data source. Hey, what’s this? Have you read my mind, Visual Studio? Our entities are already listed in the Data Sources panel:

I do notice, however, that our ‘Player’ entity is missing. Is this due to that compilation warning? I’ll add a bogus property to our player entity just to see if that is the case… no, still no love. The warning reads: “Error 2062: No mapping specified for instances of the EntitySet and AssociationSet in the EntityContainer PlayerModelContainer. ” Well if everything worked without any issues, then I wouldn’t be stumbling through at all, so let’s get to the bottom of this. My good friend google indicates that the warning is due to the model not being tied up to a database. Hmmm, so why don’t ‘Players’ show up in my data sources? A little bit of drill-down shows that they are, in fact, exposed under ‘Positions’:

Well now that isn’t quite what I want. While you could get to players through a position, it shouldn’t be that way exclusively. Oh well, I can ignore that for now – let’s drag ‘Players’ out onto the canvas after selecting ‘List’ from the dropdown:

Hey, what the heck? I wanted a list not a listview. Get rid of that list view that was just dropped, drop in a listbox and then drop the ‘Players’ entity into it. That will bind it for us. Of course, there isn’t any data to show, which brings us to the really hacky part of all this and that is to stuff some test data into our view source without actually getting it from any data source. To do this through code, we need to grab a reference to the ‘positionsPlayersViewSource’ resource that was created for us when we dragged out our Players entity. We then set the source of that reference equal to a populated list of Players.  We’ll add a couple of players that way as well as a few positions via the positionsViewSource resource, and I’ll ensure that each player has a position specified.  Ultimately, the code looks like this:

System.Windows.Data.CollectionViewSource positionViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource(“positionsViewSource”)));

            List positions = new List();

            Position newPosition = new Position();
            newPosition.Id = 0;
            newPosition.Name = “WR”;
            positions.Add(newPosition);
            newPosition = new Position();
            newPosition.Id = 1;
            newPosition.Name = “RB”;
            positions.Add(newPosition);
            newPosition = new Position();
            newPosition.Id = 2;
            newPosition.Name = “QB”;
            positions.Add(newPosition);

            positionViewSource.Source = positions;

            System.Windows.Data.CollectionViewSource playerViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource(“positionsPlayersViewSource”)));

            List players = new List();

            Player newPlayer = new Player();
            newPlayer.Id = 0;
            newPlayer.Name = “Test Dude”;
            newPlayer.Position = positions[0];
            players.Add(newPlayer);
            newPlayer = new Player();
            newPlayer.Id = 1;
            newPlayer.Name = “Test Dude II”;
            newPlayer.Position = positions[1];
            players.Add(newPlayer);
            newPlayer = new Player();
            newPlayer.Id = 2;
            newPlayer.Name = “Test Dude III”;
            newPlayer.Position = positions[2];
            players.Add(newPlayer);

            playerViewSource.Source = players;

Now that our views are being loaded with data, we can go about tying things together visually. Drop a text box (to show the selected player’s name) and a combo box (to show the selected player’s position). Drag the ‘Positions’ entity from the data sources panel to the combo box to wire it up to the positions view source. Click the text box that was dragged, and find its ‘Text’ property in the properties pane. There is a little glyph next to it that displays ‘Advanced Properties’ when hovered over – click this and then select ‘Apply Data Binding’. In the dialog that appears, we can select the current player’s name as the value to bind to:

Similarly, we can wire up the combo box’s ‘SelectedItem’ value to the current player’s position:

When the application is executed and we navigate through the various players, we automatically get their name and position bound to the appropriate fields:

All of this was accomplished with no code save for loading the test data, and I might add, it was pretty intuitive to do so via the drag and drop of entities straight from the data sources panel. So maybe all of this was old hat to you, but I was very impressed with this experience and I look forward to stumbling through the caveats of doing more complex data modeling and binding in this fashion. Next up, I suppose, will be figuring out how to get the entities to get real data from a data source instead of stuffing it with test data as well as trying to figure out why Players ended up being under Positions in the data sources panel.

0 comments , permalink

In many K2 projects I’ve worked on, we end up having to tie existing business data into the various workflow processes.  While k2 provides a means to do this via process/activity data fields, they tend to be difficult to query against and can be inefficient compared to an existing schema that has been optimized specifically for the business data involved.  That, and why store the same data twice if it already exists in a database?  That discussion is not the point of this blog entry, though it is an interesting topic that I may revisit later. For now, lets roll with the assumption that we have data in a custom database that is related to K2 process instances. 

During development and testing cycles, one of the tedious activities for me has been trying to clean up old test data, particularly when there are thousands of process instances involved.  The reason for this is because cleaning data in the scenario described above is a two-step process:  First delete the custom database data and then delete the K2 process instances.  Deleting the custom database data is no problem – write a quick SQL script and run it as needed but cleaning the K2 process instances is a different story…

There are two main ways to clear K2 process instances:  via API or via Workspace.  While we could write a program that would execute our database clean up scripts and then iterate through and delete our K2 process instances via API, but that is too much work for this lazy busy developer.  The workspace approach can be a real hassle when there are thousands of process instances because let’s face it – deleting more than 100 process instances at a time is not a snappy experience through today’s workspace. 

After wrestling with clearing test data repeatedly through the workspace, I finally posed myself the question:  Why is it that we can’t we delete process instances through the same SQL script we use to clear the custom data?  Surely, the workspace uses some sort of stored procedure(s) when it deletes a process instance, right?  I started digging around through the K2 databases to see if any stored procedures jumped out at me as obvious choices and while there were a few likely candidates,none of them fit the bill.  Frustrated,I posed the question on the K2Underground and was quickly directed to an article that came close to what I was trying to accomplish.  Using the code in this article as a base, I tweaked it until it was capable of clearing all process instances for a given folder (could easily work for a given process name as well) and it has been so useful to me that I figured I’d share it here.  Be warned, this is slapped together for development assistance, and should by no means be used on a production box!  Happy data clearing…

*** UPDATE 3/15/2010 – I’ve gone through and verified that all tables were being cleared, and updated the script accordingly ***

/*
    Delete all workflow process instances
*/
Use K2Server
DECLARE @FOLDERNAME NVARCHAR(1024)
SET @FOLDERNAME = ‘[Folder name]‘

PRINT ‘Starting to remove Process Instances for ‘ + @FOLDERNAME

PRINT ‘Getting List of Process Instances’
SELECT inst.ID
INTO #TMP
FROM dbo.[_ProcInst] AS inst
INNER JOIN dbo.[_Proc] AS prc
       ON (inst.ProcID = prc.ID)
INNER JOIN dbo.[_ProcSet] AS pset
       ON (prc.ProcSetID = pset.ID)
WHERE pset.[Folder] = @FOLDERNAME;

PRINT ‘Removing Process Instances from _IPC’
DELETE _IPC
FROM _IPC
INNER JOIN #TMP
ON SrcProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _IPC’

PRINT ‘Removing Process Instances from _Async’
DELETE _Async
FROM _Async
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _Async’

PRINT ‘Removing Process Instances from _ErrorLog’
DELETE _ErrorLog
FROM _ErrorLog
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ErrorLog’

PRINT ‘Removing Process Instances from _FieldProcInst’
DELETE _FieldProcInst
FROM _FieldProcInst
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _FieldProcInst’

PRINT ‘Removing Process Instances from _FieldActInst’
DELETE _FieldActInst
FROM _FieldActInst
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _FieldActInst’

PRINT ‘Removing Process Instances from _FieldActInstDest’
DELETE _FieldActInstDest
FROM _FieldActInstDest
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _FieldActInstDest’

PRINT ‘Removing Process Instances from _FieldSlot’
DELETE _FieldSlot
FROM _FieldSlot
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _FieldSlot’

PRINT ‘Removing Process Instances from _FieldOnDemand’
DELETE _FieldOnDemand
FROM _FieldOnDemand
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _FieldOnDemand’

PRINT ‘Removing Process Instances from _IPCReturn’
DELETE _IPCReturn
FROM _IPCReturn
INNER JOIN #TMP
ON SrcProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _IPCReturn’

PRINT ‘Removing Process Instances from _Log’
DELETE _Log
FROM _Log
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _Log’

PRINT ‘Removing Process Instances from _LogProcInst’
DELETE _LogProcInst
FROM _LogProcInst
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _LogProcInst’

PRINT ‘Removing Process Instances from _ProcInstDestQueue’
DELETE _ProcInstDestQueue
FROM _ProcInstDestQueue
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstDestQueue’

PRINT ‘Removing Process Instances from _ProcInstDestQueue’
DELETE _ProcInstDestQueue
FROM _ProcInstDestQueue
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstDestQueue’

PRINT ‘Removing Process Instances from _ServerList’
DELETE _ServerList
FROM _ServerList
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ServerList’

PRINT ‘Removing Process Instances from _WorklistSlot’
DELETE _WorklistSlot
FROM _WorklistSlot
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _WorklistSlot’

PRINT ‘Removing Process Instances from _WorklistHeader’
DELETE _WorklistHeader
FROM _WorklistHeader
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _WorklistHeader’

PRINT ‘Removing Process Instances from _ActionActInstShared’
DELETE _ActionActInstShared
FROM _ActionActInstShared
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActionActInstShared’

PRINT ‘Removing Process Instances from _ActionActInstRights’
DELETE _ActionActInstRights
FROM _ActionActInstRights
INNER JOIN #TMP
ON ProcInstID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActionActInstRights’

PRINT ‘Removing Process Instances from _ProcInst’
DELETE _ProcInst
FROM _ProcInst
INNER JOIN #TMP
ON _ProcInst.ID = #TMP.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInst’

PRINT ‘Completed removing completed process instances from K2ServerLog for folder ‘ + @FOLDERNAME

DROP TABLE #TMP

Use K2ServerLog

PRINT ‘Starting to remove Process Instances for ‘ + @FOLDERNAME

PRINT ‘Getting List of Process Instances’
SELECT ID INTO #TMP2 FROM _ProcInst
WHERE PROCID IN
(
    SELECT ID
    FROM _PROC
    WHERE PROCSETID IN
    (
        SELECT ID
        FROM _PROCSET
        WHERE FOLDER =  @FOLDERNAME
    )
)

PRINT ‘Removing Process Instances from _ActInst’
DELETE _ActInst
FROM _ActInst INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInst’

PRINT ‘Removing Process Instances from _ActInstAudit’
DELETE _ActInstAudit
FROM _ActInstAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstAudit’

PRINT ‘Removing Process Instances from _ActInstData’
DELETE _ActInstData
FROM _ActInstData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstData’

PRINT ‘Removing Process Instances from _ActInstDataAudit’
DELETE _ActInstDataAudit
FROM _ActInstDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstDataAudit’

PRINT ‘Removing Process Instances from _ActInstDest’
DELETE _ActInstDest
FROM _ActInstDest
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstDest’

PRINT ‘Removing Process Instances from _ActInstDestData’
DELETE _ActInstDestData
FROM _ActInstDestData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstDestData’

PRINT ‘Removing Process Instances from _ActInstDestDataAudit’
DELETE _ActInstDestDataAudit
FROM _ActInstDestDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstDestDataAudit’

PRINT ‘Removing Process Instances from _ActInstDestXml’
DELETE _ActInstDestXml
FROM _ActInstDestXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstDestXml’

PRINT ‘Removing Process Instances from _ActInstDestXmlAudit’
DELETE _ActInstDestXmlAudit
FROM _ActInstDestXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstDestXmlAudit’

PRINT ‘Removing Process Instances from _ActInstSlotData’
DELETE _ActInstSlotData
FROM _ActInstSlotData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstSlotData’

PRINT ‘Removing Process Instances from _ActInstSlotDataAudit’
DELETE _ActInstSlotDataAudit
FROM _ActInstSlotDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstSlotDataAudit’

PRINT ‘Removing Process Instances from _ActInstSlotXml’
DELETE _ActInstSlotXml
FROM _ActInstSlotXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstSlotXml’

PRINT ‘Removing Process Instances from _ActInstSlotXmlAudit’
DELETE _ActInstSlotXmlAudit
FROM _ActInstSlotXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstSlotXmlAudit’

PRINT ‘Removing Process Instances from _ActInstXml’
DELETE _ActInstXml
FROM _ActInstXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstXml’

PRINT ‘Removing Process Instances from _ActInstXmlAudit’
DELETE _ActInstXmlAudit
FROM _ActInstXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInstXmlAudit’

PRINT ‘Removing Process Instances from _EscInst’
DELETE _EscInst
FROM _EscInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _EscInst’

PRINT ‘Removing Process Instances from _EventInst’
DELETE _EventInst
FROM _EventInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _EventInst’

PRINT ‘Removing Process Instances from _IPC’
DELETE _IPC
FROM _IPC
INNER JOIN #TMP2
ON SrcProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _IPC’

PRINT ‘Removing Process Instances from _LogBatch’
DELETE _LogBatch
FROM _LogBatch
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _LogBatch’

PRINT ‘Removing Process Instances from _LineInst’
DELETE _LineInst
FROM _LineInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _LineInst’

PRINT ‘Removing Process Instances from _ProcEscInst’
DELETE _ProcEscInst
FROM _ProcEscInst
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcEscInst’

PRINT ‘Removing Process Instances from _ProcInstAudit’
DELETE _ProcInstAudit
FROM _ProcInstAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstAudit’

PRINT ‘Removing Process Instances from _ProcInstData’
DELETE _ProcInstData
FROM _ProcInstData
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstData’

PRINT ‘Removing Process Instances from _ProcInstDataAudit’
DELETE _ProcInstDataAudit
FROM _ProcInstDataAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstDataAudit’

PRINT ‘Removing Process Instances from _ProcInstRevision’
DELETE _ProcInstRevision
FROM _ProcInstRevision
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstRevision’

PRINT ‘Removing Process Instances from _ProcInstXml’
DELETE _ProcInstXml
FROM _ProcInstXml
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ActInst’

PRINT ‘Removing Process Instances from _ProcInstXmlAudit’
DELETE _ProcInstXmlAudit
FROM _ProcInstXmlAudit
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInstXmlAudit’

PRINT ‘Removing Process Instances from _Worklist’
DELETE _Worklist
FROM _Worklist
INNER JOIN #TMP2
ON ProcInstID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _Worklist’

PRINT ‘Removing Process Instances from _ProcInst’
DELETE _ProcInst
FROM _ProcInst
INNER JOIN #TMP2
ON _ProcInst.ID = #TMP2.ID
PRINT CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘ records removed from _ProcInst’

PRINT ‘Completed removing completed process instances from K2ServerLog for folder ‘ + @FOLDERNAME

DROP TABLE #TMP2

2 comments , permalink