Versioning Workflow Services

A couple of years ago (that sounds like an eternity in computer years), I had written up an article on the versioning of workflows https://blogs.msdn.com/ncdevguy/archive/2007/06/14/versioning-techniques-for-workflows.aspx.

Since then, a lot of things have happened, I've been bouncing back and forth between WCF, SharePoint Workflows, WCF exposed workflows and so on. One thing I've come to discover though is that when you are talking with customers who are using the Workflow Foundation, most of them still don't have a concrete handle on how to version their workflows and especially workflows that are exposed as WCF services.

So with the help of Xingang Liao of Ensemble Studios (China), we came up with a similar way of doing the versioning that we thought may help others who are still trying to figure this out. We of course are of the opinion that this may not be the best way to do this and if you see anything to make the code more efficient or an even better way to do this, let me know.

We’ll start with this context:

Framework Version: .Net Framework 3.5 SP1

Host: Console based application that uses WorkflowServiceHost as its host. This console based approach can of course be applied to a Windows Service (NT Service) based approach.

 

Creating the Solution

1. Create a new empty workflow Visual Studio 2008 Solution named WFVersioningApp by selecting File | New | Project | Visual C# | Workflow | Empty Workflow Project and giving it the before mentioned name.

Creating the Workflow Project

1.  Here, I created a new Sequential Workflow Library named WFProject and with a workflow (VersionWF.cs) that looks like this.  The objective here is to have a workflow that has two Receive activities, the first one of which will be used to start the workflow, the workflow will then be unloaded and another WCF operation will be called ( RestartWF - into the second Receive activity) to reload and start the workflow back up.  I also added a custom activity I wrote that would log to the Application Event log but you really could put any type of activity here that would allow you to see that indeed the correct version of the workflow has been awakened.

image

I placed a code activity inside of the first Receive activity just to hard-code a value for the event log.  So basically, I don’t have any real code inside of my workflow code-behind that is really important for this demonstration.

2.  Next, I created a new file for my WCF service.

    1:  using System;
    2:  using System.Collections.Generic;
    3:  using System.Linq;
    4:  using System.Text;
    5:  using System.ServiceModel;
    6:   
    7:  namespace WFProject
    8:  {
    9:      [ServiceContract]
   10:      public interface IVersionWorkflow
   11:      {
   12:          [OperationContract]
   13:          void KickoffWF();
   14:   
   15:          [OperationContract]
   16:          void RestartWF();
   17:      }
   18:      
   19:  }

Notice here that both these operations return void and are Request Response type of operations.  You will need to add a reference to System.ServiceModel to your workflow project.

3.  Add an app.config file to the project (which will later be used in your console based host ~ I just like to have this in two places in the beginning).  In this app.config file you need to add a services section.  You’ll notice in this example that I have two services listed, I’ll explain that later in the article.

    1:  <system.serviceModel>
    2:      
    3:          <services>
    4:              <service name="WFProject.VersionWF" behaviorConfiguration="ServiceBehavior" >
    5:                  <endpoint address="net.tcp://localhost:8800/WFProject/VersionWF" binding="netTcpContextBinding" contract="WFProject.IVersionWorkflow" />
    6:              </service>
    7:        <service name="WFProject.VersionWF_V2" behaviorConfiguration="ServiceBehaviorV2" >
    8:          <endpoint address="net.tcp://localhost:8800/WFProject/VersionWFV2" binding="netTcpContextBinding" contract="WFProject.IVersionWorkflow" />
    9:        </service>
   10:          </services>
   11:          <behaviors>
   12:              <serviceBehaviors>
   13:                  <behavior name="ServiceBehavior"  >
   14:                      <serviceMetadata httpGetEnabled="true" httpGetUrl="https://localhost:8888/WFProject/VersionWF"/>
   15:                      <serviceDebug includeExceptionDetailInFaults="true" />
   16:                      <serviceCredentials>
   17:                          <windowsAuthentication  allowAnonymousLogons="false" includeWindowsGroups="true" />
   18:                      </serviceCredentials>
   19:                  </behavior>
   20:          <behavior name="ServiceBehaviorV2"  >
   21:            <serviceMetadata httpGetEnabled="true" httpGetUrl="https://localhost:8888/WFProject/VersionWFV2"/>
   22:            <serviceDebug includeExceptionDetailInFaults="true" />
   23:            <serviceCredentials>
   24:              <windowsAuthentication  allowAnonymousLogons="false" includeWindowsGroups="true" />
   25:            </serviceCredentials>
   26:          </behavior>
   27:          </serviceBehaviors>
   28:          </behaviors>
   29:      </system.serviceModel>

A couple of things to point out in the above config file settings:

a.  I am actually using a net.tcp address for the service but I’m using an http address for the clients who will be getting the service information. 
b.  The authentication information in this example is not really relevant to our discussion here.

c.  I am not using a single base address, I am using relative addresses for each service configuration.

4.  I created a WFProject.snk file so this assembly could be strong named and placed in the GAC, hence allowing execution side-by-side with other versions.

5.  I confirmed that in my workflow projects Assembly.cs file that the AssemblyVersion is 1.0.0.0.

Creating the Console Host

1. Create a new Console based Windows application named WorkflowHost in the same solution.

2.  Copy the app.config file from the WFProject project into the WorkflowHost project.

3.  Add references to the following assemblies:

image

4.  Create a new class (in a new file) named WFVersionServiceHost.  This class will be derived from WorkflowServiceHost and will be a our base class for our service host.

    1:  using System;
    2:  using System.Collections.Generic;
    3:  using System.Linq;
    4:  using System.Text;
    5:  using System.ServiceModel;
    6:  using System.ServiceModel.Description;
    7:  using System.Workflow.Runtime;
    8:  using System.Workflow.Runtime.Hosting;
    9:   
   10:  namespace WorkflowHost
   11:  {
   12:      public class WFVersionServiceHost : WorkflowServiceHost
   13:      {
   14:          public static string ConfigurationName;
   15:          public static ServiceDescription _serviceDesc;
   16:   
   17:          static string connectionString = "Initial Catalog=TrackingStore;" + "Data Source=.\\SQLEXPRESS; Integrated Security=SSPI;";
   18:   
   19:          public WFVersionServiceHost(Type workflowType, params Uri[] baseAddress)
   20:              : base(workflowType, baseAddress)
   21:          {
   22:              
   23:              WorkflowRuntime wfRuntime = _serviceDesc.Behaviors.Find<WorkflowRuntimeBehavior>().WorkflowRuntime;
   24:   
   25:              wfRuntime.WorkflowTerminated
   26:                             += delegate(object sender, WorkflowTerminatedEventArgs e)
   27:                             {
   28:                                 Console.WriteLine("WorkflowTerminated: " + e.Exception.Message);
   29:                             };
   30:              wfRuntime.WorkflowCompleted
   31:                           += delegate(object sender, WorkflowCompletedEventArgs e)
   32:                           {
   33:                               Console.WriteLine("WorkflowCompleted: " + e.WorkflowInstance.InstanceId.ToString());
   34:                           };
   35:              wfRuntime.WorkflowUnloaded
   36:                           += delegate(object sender, WorkflowEventArgs e)
   37:                           {
   38:                               Console.WriteLine("WorkflowUnloaded: " + e.WorkflowInstance.InstanceId.ToString());
   39:                           };
   40:              
   41:                         
   42:              wfRuntime.AddService(new SqlWorkflowPersistenceService(connectionString, true, new TimeSpan(0, 0, 30), new TimeSpan(0, 5, 0)));
   43:   
   44:          }
   45:          
   46:          protected override ServiceDescription CreateDescription(out IDictionary<string, ContractDescription> implementedContracts)
   47:          {
   48:              ServiceDescription sd = base.CreateDescription(out implementedContracts);
   49:              
   50:              sd.ConfigurationName = WFVersionServiceHost.ConfigurationName;
   51:   
   52:              _serviceDesc = sd;
   53:   
   54:              return sd;
   55:          }
   56:   
   57:      }
   58:  }

 

A couple of things to note about the base class:

a.  I wanted to create a custom host to make it a bit easier to capture the workflow terminated/completed etc events. 

b.  The override to CreateDescription is used so that from our program.cs file, we can specify a new configuration name for each version of our workflow.  This configuration name represents the <service name=””> setting in our app.config file.

c.  Subscribing to the workflow runtime events need to take place in the constructor because if you put this code in the CreateDescription method, the workflow runtime has not yet been created.

d.  On line 42, notice that for each version of the workflow, I add the SQLWorkflowPersistenceService.  As far as I know, there is no way to add this once for all hosts.  If you know a way, please comment!

5.  Here is the program.cs file:

    1:  using System;
    2:  using System.Collections.Generic;
    3:  using System.Linq;
    4:  using System.Text;
    5:  using System.Threading;
    6:  using System.Workflow.Runtime;
    7:  using System.Workflow.Runtime.Hosting;
    8:  using System.ServiceModel;
    9:  using System.ServiceModel.Description;
   10:  using System.Reflection;
   11:   
   12:  namespace WorkflowHost
   13:  {
   14:      class Program
   15:      {
   16:         static void Main(string[] args)
   17:          {
   18:              //get the 'types' related to the version of the assemblies
   19:              Type serviceTypeV1 = Type.GetType("WFProject.VersionWF, WFProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=88a8d45b55e52cbf", true, true);
   20:              Type serviceTypeV2 = Type.GetType("WFProject.VersionWF, WFProject, Version=2.0.0.0, Culture=neutral, PublicKeyToken=88a8d45b55e52cbf", true, true);
   21:              
   22:              //the configuration name will match the <service name=""> value in the 
   23:              //app.config file
   24:              WFVersionServiceHost.ConfigurationName = "WFProject.VersionWF";
   25:              //create an instance of the workflow service host
   26:              WFVersionServiceHost wshV1 = new WFVersionServiceHost(serviceTypeV1);
   27:              //open the host
   28:              wshV1.Open();
   29:   
   30:              //notice we have to listen for both versions of the component at different
   31:              //addresses
   32:              WFVersionServiceHost.ConfigurationName = "WFProject.VersionWF_V2";
   33:              WFVersionServiceHost wshV2 = new WFVersionServiceHost(serviceTypeV2);
   34:              wshV2.Open();
   35:   
   36:        
   37:              
   38:              Console.Title = "WF Version Service";
   39:              Console.BackgroundColor = ConsoleColor.DarkGreen;
   40:              Console.ForegroundColor = ConsoleColor.Yellow;
   41:              Console.Clear();
   42:              Console.WriteLine();
   43:              Console.WriteLine("WF Version Service is ready.");
   44:              Console.WriteLine("WF Version V2 Service is ready.");
   45:              Console.WriteLine("Press <enter> to exit.");
   46:              Console.ReadLine();
   47:              wshV1.Close();
   48:              wshV2.Close();
   49:              
   50:              
   51:   
   52:          }
   53:      }
   54:  }

Comments:

a.  Note that for each version of our workflow project, I get the type.

b.  Next, set the ConfigurationName.  This will match the section in the app.config file for the service name.

c.  Create an instance of the WFVersionServiceHost, passing in the service type.

d.  Open the service host.

Once again, let’s look at the corresponding section in the app.config file:

    1:  <services>
    2:      <service name="WFProject.VersionWF" behaviorConfiguration="ServiceBehavior" >
    3:          <endpoint address="net.tcp://localhost:8800/WFProject/VersionWF" binding="netTcpContextBinding" contract="WFProject.IVersionWorkflow" />
    4:      </service>
    5:                <service name="WFProject.VersionWF_V2" behaviorConfiguration="ServiceBehaviorV2" >
    6:          <endpoint address="net.tcp://localhost:8800/WFProject/VersionWFV2" binding="netTcpContextBinding" contract="WFProject.IVersionWorkflow" />
    7:        </service>
    8:  </services>

Notice how the service names match up with the ConfigurationName on lines 24 and 32 in the program.cs file.  They are both still using the same WCF service contract and the same workflow class name.

 

Create the Client Application

1.  For this, I created a new WinForm application named WFVersioningApp and added it to the solution.  The form looks like this:

image

What happens with this form is that I will associate a customers name with my unique workflow InstanceId.  The WF Version field is just for me to use inside of my winform code to determine which proxy I want to use to reload the workflows.   The Endpoint Addr field is used at the endpoint address for the proxy I am using.  We will have a different net.tcp endpoint address for each version of our workflow.

2.  The Winform code looks like this:

    1:  using System;
    2:  using System.Collections.Generic;
    3:  using System.ComponentModel;
    4:  using System.Data;
    5:  using System.Drawing;
    6:  using System.Linq;
    7:  using System.Text;
    8:  using System.Windows.Forms;
    9:  using System.ServiceModel;
   10:  using System.ServiceModel.Channels;
   11:  using System.Workflow.Runtime;
   12:  using System.Data.SqlClient;
   13:  using WFVersioningApp.WFReference;
   14:  using WFVersioningApp.WFReferenceV2;
   15:  using System.Xml;
   16:   
   17:  namespace WFVersioningApp
   18:  {
   19:      public partial class WFForm : Form
   20:      {
   21:   
   22:          //Customer myCust;
   23:          //proxy type located in reference.cs
   24:          WFReference.VersionWorkflowClient proxy_v1;
   25:          WFReferenceV2.VersionWorkflowClient proxy_v2;
   26:          WFReference.VersionWorkflowClient proxy2_v1;
   27:          WFReferenceV2.VersionWorkflowClient proxy2_v2;
   28:   
   29:          IDictionary<string, string> basicContext;
   30:          private string _wfInstId = default(string);
   31:          private string _epAddress = default(string);
   32:   
   33:          public WFForm()
   34:          {
   35:              InitializeComponent();
   36:          }
   37:   
   38:          private void WFForm_Load(object sender, EventArgs e)
   39:          {
   40:              // TODO: This line of code loads data into the 'trackingStoreDataSet1.tblVersion' table. You can move, or remove it, as needed.
   41:              
   42:              this.tblVersionTableAdapter.Fill(this.trackingStoreDataSet1.tblVersion);
   43:   
   44:          }
   45:   
   46:          private void btnStartNewWF_Click(object sender, EventArgs e)
   47:          {
   48:              IContextManager contextManager = null;
   49:   
   50:              _epAddress = this.txtEndpoint.Text;
   51:   
   52:              if (this.txtVersion.Text == "1")
   53:              {
   54:                  proxy_v1 = new WFReference.VersionWorkflowClient();
   55:                  contextManager = proxy_v1.InnerChannel.GetProperty<IContextManager>();
   56:                  proxy_v1.KickoffWF();
   57:                  proxy_v1.Close();
   58:              }
   59:              else
   60:              {
   61:                  proxy_v2 = new WFReferenceV2.VersionWorkflowClient();
   62:                  contextManager = proxy_v2.InnerChannel.GetProperty<IContextManager>();
   63:                  proxy_v2.KickoffWF();
   64:                  proxy_v2.Close();
   65:              }
   66:   
   67:   
   68:   
   69:              // This comes back as an instantiated object, but has 0 elements in it
   70:              basicContext = contextManager.GetContext();
   71:              _wfInstId = contextManager.GetContext()["instanceId"];
   72:   
   73:              AddCustomerToDatabase();
   74:              
   75:          }
   76:   
   77:          private void btnRestartWF_Click(object sender, EventArgs e)
   78:          {
   79:              string _wfId = default(string);
   80:              string _wfVersion = default(string);
   81:              IContextManager contextManager = null;
   82:   
   83:              Dictionary<string, string> _myDict = new Dictionary<string, string>();
   84:   
   85:              #region DataGrid Logic
   86:              DataGridViewSelectedRowCollection rows = dataGridView1.SelectedRows;
   87:   
   88:              if (rows.Count > 1)
   89:              {
   90:                  MessageBox.Show("You can only select one item");
   91:                  return;
   92:              }
   93:              else if(rows.Count < 1)
   94:              {
   95:                  MessageBox.Show("You must select at least one row in the grid");
   96:                  return;
   97:              }
   98:   
   99:              //get the value from the workflow id column
  100:              _wfId = rows[0].Cells[1].Value.ToString();
  101:   
  102:              //get the version number
  103:              _wfVersion = rows[0].Cells[2].Value.ToString();
  104:   
  105:              if (_wfId.Length <= 0)
  106:              {
  107:                  MessageBox.Show("Invalid workflow instance ID");
  108:                  return;
  109:              }
  110:   
  111:              #endregion
  112:   
  113:              _myDict.Add("instanceId", _wfId);
  114:              
  115:   
  116:              switch (_wfVersion)
  117:              {
  118:                  case "1":
  119:                      proxy2_v1 = new WFReference.VersionWorkflowClient();
  120:                      contextManager = proxy2_v1.InnerChannel.GetProperty<IContextManager>();
  121:                      contextManager.SetContext(_myDict);
  122:                      proxy2_v1.RestartWF();
  123:                      break;
  124:                  case "2":
  125:   
  126:                      proxy2_v2 = new WFReferenceV2.VersionWorkflowClient();
  127:                      contextManager = proxy2_v2.InnerChannel.GetProperty<IContextManager>();
  128:                      contextManager.SetContext(_myDict);
  129:                      proxy2_v2.RestartWF();
  130:                      break;
  131:                  default:
  132:                      break;
  133:   
  134:              }
  135:   
  136:              try
  137:              {
  138:                  //the workflow has already been deleted but we need to delete our own records
  139:                  DeleteCustomerFromDatabase(_wfId);
  140:              }
  141:              catch (System.Exception ex)
  142:              {
  143:                  MessageBox.Show("Exception during workflow restart: " + ex.Message);
  144:              }
  145:              finally
  146:              {
  147:   
  148:                  if (proxy2_v1 != null)
  149:                  {
  150:                      if (proxy2_v1.State == CommunicationState.Opened)
  151:                      {
  152:                          proxy2_v1.Close();
  153:                      }
  154:                  }
  155:   
  156:                  if (proxy2_v2 != null)
  157:                  {
  158:                      if (proxy2_v2.State == CommunicationState.Opened)
  159:                      {
  160:                          proxy2_v2.Close();
  161:                      }
  162:                  }
  163:              }
  164:   
  165:          }
  166:   
  167:          private void btnClose_Click(object sender, EventArgs e)
  168:          {
  169:              this.Close();
  170:          }
  171:          #region Database methods
  172:          private void AddCustomerToDatabase()
  173:          {
  174:              SqlCommand cmd = new SqlCommand();
  175:              cmd.CommandType = CommandType.StoredProcedure;
  176:              cmd.CommandText = "dbo.InsertCustomer";
  177:              cmd.Connection = this.tblVersionTableAdapter.Connection;
  178:   
  179:              try
  180:              {
  181:                  if (cmd.Connection.State != ConnectionState.Open)
  182:                  {
  183:                      cmd.Connection.Open();
  184:                  }
  185:                  
  186:                  cmd.Parameters.AddWithValue("@CustomerName", this.txtCustomerName.Text);
  187:                  cmd.Parameters.AddWithValue("@WFId", _wfInstId);
  188:                  cmd.Parameters.AddWithValue("@Version", this.txtVersion.Text);
  189:                  cmd.Parameters.AddWithValue("@EndpointAddr", _epAddress);
  190:                  
  191:   
  192:                  cmd.CommandType = CommandType.StoredProcedure;
  193:                  cmd.ExecuteNonQuery();
  194:   
  195:              }
  196:              catch (Exception ex)
  197:              {
  198:                  Console.WriteLine("InsertCustomerName error :{0}", ex.Message);
  199:              }
  200:              finally
  201:              {
  202:                  cmd.Connection.Close();
  203:              }
  204:   
  205:              this.dataGridView1.DataSource = this.tblVersionTableAdapter.GetData();
  206:   
  207:   
  208:          }
  209:          private void DeleteCustomerFromDatabase(string wfInstId)
  210:          {
  211:              //when the workflow has completed, remove it from the database
  212:              SqlCommand cmd = new SqlCommand();
  213:              cmd.CommandType = CommandType.StoredProcedure;
  214:              cmd.CommandText = "dbo.DeleteCustomerWorkflow";
  215:              cmd.Connection = this.tblVersionTableAdapter.Connection;
  216:   
  217:              try
  218:              {
  219:                  if (cmd.Connection.State != ConnectionState.Open)
  220:                  {
  221:                      cmd.Connection.Open();
  222:                  }
  223:   
  224:                  cmd.Parameters.AddWithValue("@WFId", wfInstId);
  225:                  cmd.CommandType = CommandType.StoredProcedure;
  226:                  cmd.ExecuteNonQuery();
  227:   
  228:              }
  229:              catch (Exception ex)
  230:              {
  231:                  Console.WriteLine("DeleteCustomerWorkflow error :{0}", ex.Message);
  232:              }
  233:              finally
  234:              {
  235:                  cmd.Connection.Close();
  236:              }
  237:   
  238:              this.dataGridView1.DataSource = this.tblVersionTableAdapter.GetData();
  239:   
  240:          }
  241:          #endregion
  242:   
  243:   
  244:      }
  245:  }

 

There is certainly a lot of code here, none of which is terribly complex (or production ready!) but I’ll explain what I consider to be the important pieces:

a.  Lines 24 – 27.  I wanted to make sure here that I was truly using different proxy instances whenever I call back into the workflow.  You will have to add two different service references to the project, one for each endpoint (remember to use the http addresses from the WorkflowHost projects app.config file to find the service metadata).

b.  Lines 52 through 58.  Whenever I want to create a version 1 workflow, I need to create an proxy to the first endpoints client and then from that, I get the ContextManager (which will be empty at this point).  When I call KickoffWF, the context will be filled with the instance id of the workflow returned by the first Receive activity.  Note that in real life, you are not typically going to have code that allows people to keep creating versions of workflow version 1.0.0.0.  Imagine that this is the code that your client app started with and then as you added new versions of your workflow, you would have to add such code as you see here to at minimum be able to restart version 1.0.0.0 of the workflow.

c.  Lines 70 and 71, this is where we get the workflow instance ID out of the context.

d.  Line 73, call the AddCustomerToDatabase method.  I have added a tblVersion table to the TrackingStore database along with a few stored procs to handle adding and deleting the customer name and corresponding workflow instance id information. (I’ll show the SQL script down below)

e.  Line 113.  In the btnRestartWF_Click method, I grab the workflow instance ID out of the grid and fill in a string dictionary to be used to set my context for the next Receive activity call.

f.  Line 119 – 122.  Create a new proxy instance.  The only difference in this code and the code that initially kicked the workflow off is that here (line 121) I ‘set’ the context value to be the instance id of the workflow to reload.

 

Testing the Workflow Versions

1.  In order to test this, what I did was I wrote out strings in my logging activity that specifically said ‘v1’.  I then built the component and deployed it to the GAC.   I ran through my client and created a v1 workflow.

2.  I then went and changed the strings for the logging activity to ‘v2’ and updated the Assembly.cs file to 2.0.0.0. and redeployed to the GAC.

3.  I ran the client and created a few workflows that were version 2.0. 

4.  I then, selected a version 1.0 workflow from the grid and restarted that workflow.  I was able to look in my Application event viewer and see that ‘v1’ had been restarted.

 

The SQL Script – I put this code in with my TrackingStore database (persistence and tracking database)

    1:  USE [TrackingStore]
    2:  GO
    3:  /****** Object:  Table [dbo].[tblVersion]    Script Date: 09/29/2009 19:35:36 ******/
    4:  SET ANSI_NULLS ON
    5:  GO
    6:  SET QUOTED_IDENTIFIER ON
    7:  GO
    8:  CREATE TABLE [dbo].[tblVersion](
    9:      [CustomerName] [nvarchar](50) NOT NULL,
   10:      [WorkflowID] [uniqueidentifier] NOT NULL,
   11:      [WorkflowVersion] [smallint] NULL,
   12:      [EndpointAddress] [nvarchar](50) NULL,
   13:   CONSTRAINT [PK_tblVersion] PRIMARY KEY CLUSTERED 
   14:  (
   15:      [WorkflowID] ASC
   16:  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   17:  ) ON [PRIMARY]
   18:   
   19:  GO
   20:  /****** Object:  StoredProcedure [dbo].[DeleteCustomerWorkflow]    Script Date: 09/29/2009 19:36:26 ******/
   21:  SET ANSI_NULLS ON
   22:  GO
   23:  SET QUOTED_IDENTIFIER ON
   24:  GO
   25:  -- =============================================
   26:  -- Author:        <Author,,Name>
   27:  -- Create date: <Create Date,,>
   28:  -- Description:    <Description,,>
   29:  -- =============================================
   30:  CREATE PROCEDURE [dbo].[DeleteCustomerWorkflow] @WFId uniqueidentifier 
   31:  AS
   32:  BEGIN
   33:      DELETE FROM [dbo].[tblVersion] WHERE WorkflowID=@WFId
   34:  END
   35:   
   36:   
   37:  GO
   38:  /****** Object:  StoredProcedure [dbo].[InsertCustomer]    Script Date: 09/29/2009 19:37:06 ******/
   39:  SET ANSI_NULLS ON
   40:  GO
   41:  SET QUOTED_IDENTIFIER ON
   42:  GO
   43:  -- =============================================
   44:  -- Author:        <Author,,Name>
   45:  -- Create date: <Create Date,,>
   46:  -- Description:    <Description,,>
   47:  -- =============================================
   48:  CREATE PROCEDURE [dbo].[InsertCustomer] @CustomerName nvarchar(50),
   49:  @WFId uniqueidentifier,
   50:  @Version smallint,
   51:  @EndpointAddr nvarchar(50)
   52:   
   53:  AS
   54:  BEGIN
   55:      -- SET NOCOUNT ON added to prevent extra result sets from
   56:      -- interfering with SELECT statements.
   57:      SET NOCOUNT ON;
   58:   
   59:      SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   60:          
   61:      declare @localized_string_InsertCustomer_Failed_GetType nvarchar(256)
   62:      set @localized_string_InsertCustomer_Failed_GetType = N'GetTypeId failed'
   63:   
   64:      declare @localized_string_InsertCustomer_InsertFailed nvarchar(256)
   65:      set @localized_string_InsertCustomer_InsertFailed = N'Failed inserting Customer into TrackingStore'
   66:   
   67:   
   68:      DECLARE @local_tran        bit
   69:              ,@error            int
   70:              ,@error_desc    nvarchar(256)
   71:              ,@ret            smallint
   72:   
   73:  IF @@TRANCOUNT > 0
   74:          SET @local_tran = 0
   75:      ELSE
   76:       BEGIN
   77:          BEGIN TRANSACTION
   78:          SET @local_tran = 1        
   79:       END
   80:   
   81:      INSERT        [dbo].[tblVersion] (
   82:                      [CustomerName]
   83:                      ,[WorkflowID]
   84:                      ,[WorkflowVersion]
   85:                      ,[EndpointAddress]
   86:      )
   87:  VALUES
   88:  (
   89:  @CustomerName,
   90:  @WFId,
   91:  @Version,
   92:  @EndpointAddr
   93:  )
   94:   
   95:  IF @@ERROR NOT IN ( 3604 /* ignore dup key */, 0 )
   96:       BEGIN
   97:          SELECT @error_desc = @localized_string_InsertCustomer_InsertFailed
   98:          GOTO FAILED
   99:       END
  100:   
  101:      IF @local_tran = 1
  102:          COMMIT TRANSACTION
  103:   
  104:      SET @ret = 0
  105:      GOTO DONE
  106:   
  107:  FAILED:
  108:      IF @local_tran = 1
  109:          ROLLBACK TRANSACTION
  110:   
  111:      RAISERROR( @error_desc, 16, -1 )
  112:   
  113:      SET @ret = -1
  114:      GOTO DONE
  115:   
  116:  DONE:
  117:      RETURN @ret
  118:  END

In Conclusion

I’m sure that there are those of you out there that can find flaws in this method, suggest improvements, or have other ways you’ve done this but I’ve found few concrete methods distributed in blogs.  I (and Xingang) were just hoping this could help someone along the way and maybe with suggestions from others, even improve what we have.  Comments welcome!