Check out my new blog at

Friday, May 30, 2008

Microsoft Excel - My Favorite Fallback Application (also some information about transactions in strongly typed datasets)

It happens frequently.  A requirement comes up that must be satisfied quickly but completely.  More often than not, whatever solution I come up with needs to look nice as well.  I often turn to Microsoft Excel to create quick solutions.  With Visual Basic for Applications, a fantastic calculation engine, and a highly configurable user interface--plus its near complete ubiquity in the enterprise--it is hard to beat Microsoft Excel in certain situations, although I must admit that sometimes I get a little Excel-happy, just because it is so familiar to me.  In mid 1990's, I wrote some of the samples for Excel in the Microsoft Solutions Development Kit, a precursor to MSDN.  I worked with Peter LaForte and Tom Risso back then... smart guys.  Ken Inglis taught be Excel VBA--another smart guy.

Case in point yesterday and today.  My product, the 360° Project Management Competency Framework (PMCA™), supports a variety of project management frameworks, each with a scale, a set of domains (groups of competencies), competency statements, and competency descriptions.  New frameworks can be created through the system and served up by the assessment platform.  We are getting ready to go public with the product, so a final review of our standard competency framework was scheduled.  The challenge is the framework content exists in Microsoft SQL Server and is editable via the 360° Product Manager application, which is certainly not an application I want to deploy to a bunch of desktops.  The product is a hosted solution and the Product Manager application is exactly what its name implies: a management tool.

So, how to get the framework content into the hands of the reviewers without requiring that they install the Product Manager Application, allow the reviewers the ability to make changes, and then update the changes back to the content repository?

Microsoft Excel.

In just a matter of hours, I was able to create an Excel workbook that does the following:

  1. If connected to the network and if the person opening the workbook has sufficient rights, ask the content repository for a list of frameworks
  2. When a framework is selected, generate a worksheet for each of the major framework components in which the reviewer can make changes
  3. Lock the workbook for review
  4. Send the review workbook to the SME or copy editor
  5. When the review is complete, provide for a mechanism to update the changes back to the content repository

See the following two screen shots to get a sense for what the workbook looks like.



The reviewer can make changes to the content, save it, and send it back to me.

Once I receive the updated work book, I point the 360° Product Manager application at the workbook through the "Synchronize Office Framework Editor Content" tool, which does the work of reading the data from Excel and updating the content repository.  See the screen shot below. 


Because this tool will never be used by anybody but myself or somebody on my staff, my primary design goals were simple:

  1. Come up with a solution quickly that is pleasant to use and look at
  2. Ensure data integrity during content update
  3. Write as little code as possible

We use strong typed datasets as part of our data access layer so I already had components lying around that know how to talk to the framework content repository. In most other places of the application, there is very little writing of data and most of that is done through stored procedures.  Rather than writing a bunch of complicated data access code outside of our DAL to get the job done, I figured I would just use the strongly typed datasets directly, as is done throughout the application.

I knew that the only unknown was regarding using transactions in a strongly typed dataset.  I had read something a while back but had no reason to apply the knowledge at the time, so I was going into it a little less informed than I prefer.

Wow.  Strongly typed data sets are lacking in the area of transactions.  I tried a variety of approaches (none of which could take more than I few minutes--my first and third design goals are about speed of development!).

I finally came across Ryan Whitaker's TableAdapterHelper class, which does the job nicely, albeit through reflection--I wouldn't recommend this for server-side applications or other performance-sensitive applications.  Here is what Ryan has to say about it:

"If you've used TableAdapters, you'll know that their ability to deal with transactions leaves a little to be desired.  Little?  Did I say "little"?  I mean "a lot".

Sahil Malik has a good slew of advice for people wanting to use transactions with TableAdapters.  Most people will probably just wrap everything up in a TransactionScope and be done with it, which is fine if you're not running a high-traffic site.  In doing this, you'll run into the annoyance of the transaction being promoted to the DTC, which is an expensive bit of "bling" to tango with.

For the people that don't like the whole promotion to DTC thing, they'll probably extend their TableAdapter's partial class and add a BeginTransaction method similar to what Sahil proposes.

For me, who's looking at a bajillion TableAdapters, with quite a few of those needing to operate within the scope of a transaction, I instead choose to play the Hacky card and just set the transaction on the TableAdapter's commands through a little bit of reflection.  Some may throw your hands up in the air on this.  I, however, love it, as it fits my purposes like a latex glove."

I agree with Ryan on all points.  For a quick hit like I was looking for, this approach suited me just fine.

Here is a snippet contain two of the update methods: UpdateScale and UpdateDomains:

   1: private void UpdateScale(Worksheet scaleWorkSheet)
   2: {
   3:     Range scaleAreaRange = scaleWorkSheet.get_Range("ScaleArea", Type.Missing);
   5:     using (FrameworkScaleItemTableAdapter frameworkScaleItemTableAdapter = new FrameworkScaleItemTableAdapter())
   6:     {
   7:         _Transaction = StronglyTypedDataSetTransaction.BeginTransaction(frameworkScaleItemTableAdapter);
   9:         using (FrameworkDataSet.FrameworkScaleItemDataTable frameworkScaleItemDataTable = frameworkScaleItemTableAdapter.GetDataByFrameworkID(_FrameworkID))
  10:         {
  11:             foreach (Range rowRange in scaleAreaRange.Rows)
  12:             {
  13:                 int id = Convert.ToInt32(GetCellValue(rowRange, 1, 1));
  15:                 FrameworkDataSet.FrameworkScaleItemRow scaleItem = frameworkScaleItemDataTable.FindByFrameworkScaleItemID(id);
  17:                 if (scaleItem != null)
  18:                 {
  19:                     scaleItem.Value = Convert.ToInt32(GetCellValue(rowRange, 1, 2));
  20:                     scaleItem.Name = GetCellValue(rowRange, 1, 3).ToString();
  21:                     scaleItem.Description = GetCellValue(rowRange, 1, 4).ToString();
  22:                 }
  24:                 ExcelWrapper.FreeCOMObject(rowRange);
  25:             }
  26:             frameworkScaleItemTableAdapter.Update(frameworkScaleItemDataTable);
  27:         }
  28:     }
  30:     ExcelWrapper.FreeCOMObject(scaleAreaRange);
  31:     RaiseOnSynchronizationStepCompleteEvent(SynchronizationStep.SynchronizeScale, true, string.Empty);
  32: }
  34: private void UpdateDomains(Worksheet domainsWorksheet)
  35: {
  36:     Range domainAreaRange = domainsWorksheet.get_Range("DomainArea", Type.Missing);
  38:     using (FrameworkDomainTableAdapter frameworkDomainTableAdapter = new FrameworkDomainTableAdapter())
  39:     {
  40:         StronglyTypedDataSetTransaction.SetTransaction(frameworkDomainTableAdapter, _Transaction);
  42:         using (FrameworkDataSet.FrameworkDomainDataTable frameworkDomainDataTable = frameworkDomainTableAdapter.GetDataByFrameworkID(_FrameworkID))
  43:         {
  44:             foreach (Range rowRange in domainAreaRange.Rows)
  45:             {
  46:                 int id = Convert.ToInt32(GetCellValue(rowRange, 1, 1));
  48:                 FrameworkDataSet.FrameworkDomainRow domain = frameworkDomainDataTable.FindByFrameworkDomainID(id);
  50:                 if (domain != null)
  51:                 {
  52:                     domain.Name = GetCellValue(rowRange, 1, 2).ToString();
  53:                     domain.Description = GetCellValue(rowRange, 1, 3).ToString();
  54:                 }
  55:                 ExcelWrapper.FreeCOMObject(rowRange);
  56:             }
  58:             frameworkDomainTableAdapter.Update(frameworkDomainDataTable);
  59:         }
  60:     }
  62:     ExcelWrapper.FreeCOMObject(domainAreaRange);
  63:     RaiseOnSynchronizationStepCompleteEvent(SynchronizationStep.SynchronizeDomains, true, string.Empty);
  64: }


   1: using System;
   2: using System.Data;
   3: using System.Data.SqlClient;
   4: using System.Reflection;
   6: public class StronglyTypedDataSetTransaction
   7: {
   8:     public static SqlTransaction BeginTransaction(object tableAdapter)
   9:     {
  10:         return BeginTransaction(tableAdapter, IsolationLevel.ReadUncommitted);
  11:     }
  13:     public static SqlTransaction BeginTransaction(object tableAdapter,
  14:                                                   IsolationLevel isolationLevel)
  15:     {
  16:         // get the table adapter's type
  17:         Type type = tableAdapter.GetType();
  19:         // get the connection on the adapter
  20:         SqlConnection connection = GetConnection(tableAdapter);
  22:         // make sure connection is open to start the transaction
  23:         if (connection.State == ConnectionState.Closed)
  24:             connection.Open();
  26:         // start a transaction on the connection
  27:         SqlTransaction transaction = connection.BeginTransaction(isolationLevel);
  29:         // set the transaction on the table adapter
  30:         SetTransaction(tableAdapter, transaction);
  32:         return transaction;
  33:     }
  35:     /// <summary>
  36:     /// Gets the connection from the specified table adapter.
  37:     /// </summary>
  38:     private static SqlConnection GetConnection(object tableAdapter)
  39:     {
  40:         Type type = tableAdapter.GetType();
  41:         PropertyInfo connectionProperty = type.GetProperty("Connection");
  42:         SqlConnection connection = (SqlConnection) connectionProperty.GetValue(tableAdapter, null);
  43:         return connection;
  44:     }
  46:     /// <summary>
  47:     /// Sets the connection on the specified table adapter.
  48:     /// </summary>
  49:     private static void SetConnection(object tableAdapter,
  50:                                       SqlConnection connection)
  51:     {
  52:         Type type = tableAdapter.GetType();
  53:         PropertyInfo connectionProperty = type.GetProperty("Connection");
  54:         if (connectionProperty != null)
  55:             connectionProperty.SetValue(tableAdapter, connection, null);
  56:     }
  58:     /// <summary>
  59:     /// Enlists the table adapter in a transaction.
  60:     /// </summary>
  61:     public static void SetTransaction(object tableAdapter,
  62:                                       SqlTransaction transaction)
  63:     {
  64:         // get the table adapter's type
  66:         Type type = tableAdapter.GetType();
  67:         PropertyInfo adapterProperty = type.GetProperty("Adapter", BindingFlags.NonPublic | BindingFlags.Instance);
  69:         SqlDataAdapter adapter = (SqlDataAdapter) adapterProperty.GetValue(tableAdapter, null);
  71:         adapter.InsertCommand.Transaction = transaction;
  73:         adapter.UpdateCommand.Transaction = transaction;
  75:         adapter.DeleteCommand.Transaction = transaction;
  77:         // set the transaction on each command in the adapter
  79:         PropertyInfo commandsProperty = type.GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.Instance);
  80:         SqlCommand[] commands = (SqlCommand[]) commandsProperty.GetValue(tableAdapter, null);
  82:         foreach (SqlCommand command in commands)
  83:             command.Transaction = transaction;
  85:         // set the connection on the table adapter
  87:         SetConnection(tableAdapter, transaction.Connection);
  88:     }
  89: }

Kinda hacky but it got the job done in just a few hours total development time.

Thanks Ryan.

FAMFAMFAM & Converticon - Great *Free* Icons and Conversion Tool

My favorite free icons come as PNGs.  There is a handy little tool online called ConvertIcon that will quickly convert the PNG image files to ICO format.

Thanks PunkLabs & Mark James

Wednesday, May 28, 2008

Blogging Tips

I have been blocking for a few months now.  Given that I expected nothing in return for my scribbling, I have been rewarded with a small readership and a growing number of repeat readers.  My bounce rate is improving and I am getting traffic from referring sites.  This is great, but I blog because writing relaxes me, I love what I do, and I believe in giving back to my community. 

In learning about blogging and in reading blogs for a number of years, I have developed sort of an internal checklist of concepts and practices that I think help me in blogging.  What follows is a summary of the key points.

Find Your Niche

Determine what your talent, specialization, or particular passion is and write about it.  You may find yourself out on a tangent or you may feel the need to rant, but try to return to just a few core subjects.   The world is highly specialized.  Most people will arrive at your blog by way of search engines.  That means they are looking for something specific.  Use your talents and passions to full a demand and people will read you. 

Know Your Audience

First, expect the world as your audience--not because you are important or a brilliant writer (maybe you are), but because that is just how the web works.  Since May, my blog has seen visitors from 76 countries.  Many of these readers come to the site with English as a second language.  I try to refrain from using American slang, academic prose, or any language construct that might obscure my intentions.  Think of Strunk and White's Elements of Style.

Second, based on the first point about finding your niche, figure out who you are talking to.  In my case, I am speaking to software developers who are using Microsoft technologies to build solutions around Microsoft Project.  I also write about more general C# and .NET topics.  The blog, therefore, is not likely to be interesting to a great many people.  I am okay with that. 

Don't Try to Be Right

I read blogs where the writer is trying to convince me of of something.  This is okay for political blogs, but less appealing in a technology blog.  If you want to convince people that something is more correct than something else, demonstrate it rather than proselytize. 

Be Selfless

This tip may not be for everybody, but I make an effort to remain in the background.  I think the problems I am trying to solve and the process I go through to solve them are more interesting than what movie I saw last weekend or what my dog did in the backyard last night.  I am attracted to blogs that state clearly in the blog title what the subject is, followed by a deep dive into the meat of the topic.  Guys like Chris Brumme, Eric Lippert, and Joe Duffy follow this pattern nicely.  If you are writing about your work but frequently subject your readers to accounts of your weekends out on the town, consider having a separate personal blog.

Give Credit

It is a fact that in software development we borrow from each other all the time.  I don't mean the way Microsoft and Apply borrowed from PARC, I am referring to the daily borrowing of interesting code and concepts from those that went before us.  When I am working, I spend just as much time writing code as I do reading about how others have solved similar problems.  When I borrow something directly, I try to give credit to those responsible--this goes as far as adding a link to a blog post or article in comments.  It certainly extends to blogging.  If I use somebody else's work, I try to give them credit for it.

Check Your Stats

There are a number of analytics packages available which give you interesting data about how readers arrive at your site, what they do when they get there, as well as technical demographics, regional demographics, and other analytic features.  I like Google Analytics.  I check my statistics daily to see what the hot posts are, where people are coming from, and how long they are staying on the site.  In the early days, I was seeing a high bounce rate--readers would sweep in for 30-40 seconds and then exist the site.  I did some research and compared my writing style to other, more successful blogs, and made some changes.  Use these tools to reach more people.

Moderate Your Comments

I see blogs with have open comments policies with links to stuff I expect is illegal and certainly offensive, or I see blogs where readers have posed questions to the author who seems to never return to answer.  You have a responsibility to your readers to tend to their comments and questions.

Don't Ask For Donations

Unless you are unemployed, writing for a non-profit, or want to solicit funds for a worthy cause (this does not include your personal income or coffee fund), don't ask for donations.  If you want to make money off your blog, do it through ads (and  

Be Persistent

In the beginning, blogging can feel like yelling into a vacuum.  Be persistent.  Eventually, if you write about something people need or if you are interesting enough, you will start to be rewarded.   The number of bloggers out there is astounding.  If you hook up Google Analytics and see just a few people a day spending ten minutes reading one of your posts, be thankful.  Don't expect this to happen overnight.  In fact, the best bet is to expect it will never happen and be pleasantly surprised when it does.

Write Because You Like...No, Love It!

I close with the most important tip:  write because you like it.  I come across blogs that read like the author was straining to write or straining for something to say just to get in their daily post.  Other blogs are written like press releases or academic treatments on a subject.  These styles come across as though the author is trying to impress somebody or trying to meet a quota.  Write because your passion for your work drives you to share it with others.  Write for all of those times you have read other blogs and got that little piece of information that set you free from a hard problem.  Write because you love it.

Tuesday, May 27, 2008

Simple Property Editor using Reflection in C#

I am working on an internal project where I need to be able to pass an object that has both read and write properties to a simple editor.  The editor must understand when a property can be changed and how to do conversions correctly back into the original object.  I use reflection to accomplish this.  See the scree below:


Notice the Property Description column.  A "friendly" description of each property is achieved through the DescriptionAttribute.  Here is an example of applying this attribute to a property:

[Description("Specifies whether scheduling constraints take precedence over dependencies.")]
public bool HonorConstraints
    get { return _HonorConstraints; }
    set { _HonorConstraints = value; }
Here is the object creation and the instantiation of the property editor:
ProjectCreationOptions options = new ProjectCreationOptions 
                                     {   AutoCalculateActualAndRemainingWorkAndCosts = true, 
                                         CalculateActualCosts = false, 
                                         CalculateMultipleCriticalPaths = true, 
                                         CalendarGuid = Guid.NewGuid(), 
                                         /*Guid = Guid.NewGuid(),*/ 
                                         HonorConstraints = true, Name = "test"

PropertyEditorForm propertyEditorForm = new PropertyEditorForm(options);

if (propertyEditorForm.ShowDialog() == DialogResult.OK)
    Debug.Print((propertyEditorForm.Object as ProjectCreationOptions).Name);

The form's constructor looks like this:

public PropertyEditorForm(object objectWithProperties)

      _ObjectWithProperties = objectWithProperties;

      Type type = _ObjectWithProperties.GetType();

      _PropertyInfo = type.GetProperties();

      foreach (PropertyInfo property in _PropertyInfo)
          object[] attributes = property.GetCustomAttributes(false);

          string description = string.Empty;

          foreach (object attribute in attributes)
              var descriptionAttribute = attribute as DescriptionAttribute;

              if (descriptionAttribute != null)
                  description = descriptionAttribute.Description;

          object returnValue = property.GetValue(_ObjectWithProperties, null);

          if (returnValue == null)
              returnValue = string.Empty;

          propertiesGridView.Rows.Add(new object[] {property.Name, description, returnValue.ToString()});

          if (!property.CanWrite)
              propertiesGridView.Rows[propertiesGridView.Rows.Count - 1].ReadOnly = true;
              propertiesGridView.Rows[propertiesGridView.Rows.Count - 1].DefaultCellStyle.BackColor = Color.SlateGray;

The essence of what is going on above is this: 

  • Get the properties from the incoming type (type.GetProperties())
  • For each property, get the custom attributes (in order to get at the DescriptionAttribute)
  • If the description is defined, store it
  • Get the value of the property for the particular instance
  • If the value of the property is null, convert it to string.empty
  • Add a new row to the property editor data grid view
  • If the property is read-only, set the row to read-only and make its background gray

In the editor, if the row is not read-only, changes can be made to the values in the furthest right column.  Clicking "OK" persists those values back into the object, which in turn is available as a public property of the editor dialog.  The code looks like this (error handling removed):

string propertyName = (string) row.Cells[PROPERTY_NAME_COLUMN_INDEX].Value;

foreach (PropertyInfo propertyInfo in _PropertyInfo)
    if (propertyInfo.Name == propertyName && propertyInfo.CanWrite)
        object value = 

        propertyInfo.SetValue(_ObjectWithProperties, value, null);


The interesting part here is the call to TypeDescriptor.GetConverter handles the conversion for simple types.

Hope this helps someone.

Monday, May 26, 2008

Microsoft Project Fx (mpFx) - Entity Factory

A common pattern across the PSI is illustrated by the following pseudo-code (which doesn't account for disposal):

MainDataSet mainDataSet = new MainDataSet()

MainDataSet.Row row = mainDataSet.NewRow()

row.Attribute_1 = "some value"

row.Attribute_2 = "some other value"



I wrote a factory wrapper that makes this a bit easier.  Here is the top of the call chain, where use my EntityFactory to create a new ProjectDataSet with a single Project row populated with basic information:

EntityFactory.AutoCreateGuids = true;

using (ProjectDataSet project = EntityFactory.NewProject("Test", true, DateTime.Now))

    _ProjectServer.Projects.Create(project, false, true);                

The EntityFactory current has several overloaded methods for creating a ProjectDataSet.  Eventually it will house methods for creating all primary entities in Project Server (project, resource, task, assignment, and custom field).    Here is the project creation methods:

public class EntityFactory
    #region Static Fields

    private static bool _AutoCreateGuid = true;


    #region Public Properties

    public static bool AutoCreateGuids
        get { return _AutoCreateGuid; }
        set { _AutoCreateGuid = value; }


    #region Project Creation Methods

    /// <summary>
    /// Create empty project row
    /// </summary>
    /// <returns></returns>
    public static ProjectDataSet NewProject()
        ProjectDataSet projectDataSet = new ProjectDataSet();
        ProjectDataSet.ProjectRow project  = projectDataSet.Project.NewProjectRow();

        if (AutoCreateGuids)
            project.PROJ_UID = Guid.NewGuid();                    


        return projectDataSet;                        

    /// <summary>
    /// Create project row with name 
    /// </summary>
    /// <param name="name">Project Name</param>
    /// <returns></returns>
    public static ProjectDataSet NewProject(string name)
        ProjectDataSet project = NewProject();

        project.Project[0].PROJ_NAME = name;

        return project;

    /// <summary>
    /// Create project with name and guid
    /// </summary>
    /// <param name="name">Project Name</param>
    /// <param name="guid">Project GUI</param>
    /// <returns></returns>
    public static ProjectDataSet NewProject(string name, Guid guid)
        ProjectDataSet project = NewProject(name);

        project.Project[0].PROJ_UID = guid;

        return project;

    /// <summary>
    /// Create project with name, set schedule from (SFS or SFF) and relevant date (start for SFS, finish for SFF)
    /// </summary>
    /// <param name="name">Project Name</param>
    /// <param name="scheduleFromStart">True to schedule from Start Date, false to schedule from Finish Date</param>
    /// <param name="date">if 'schedleFromStart' = true, Start Date; false, Finish Data</param>
    /// <returns></returns>
    public static ProjectDataSet NewProject(string name, bool scheduleFromStart, DateTime date)
        ProjectDataSet project = NewProject(name);            

        if (scheduleFromStart)
            project.Project[0].PROJ_INFO_SCHED_FROM = true;
            project.Project[0].PROJ_INFO_START_DATE = date;

            project.Project[0].PROJ_INFO_SCHED_FROM = false;
            project.Project[0].PROJ_INFO_FINISH_DATE = date;

        return project;


CreateProject looks like this:

public Guid Create(ProjectDataSet project, bool validateOnly, bool wait)
    Guid jobGuid = Guid.NewGuid();

    _Parent.ProjectsWebService.QueueCreateProject(jobGuid, project, validateOnly);

    if (wait)
        string errorMessage;

                                      out errorMessage);


    return jobGuid;                

Notice the "wait" parameter, which causes mpFx to wait on the queued job to complete.  The wait is controlled by a repeat count and a sleep duration.  It looks like this:

public void WaitOnJobStatus(Guid jobGuid, JobState jobState, int count, int duration, out string errorString)
    JobState currentState;

    errorString = string.Empty;

    for (int i = 0; i < count - 1; i++)
        currentState = _Parent.QueueWebService.GetJobCompletionState(jobGuid, out errorString);

        if (currentState == jobState)


    throw new Exception(errorString);

Note that exception handling and other fundamentals are left out for clarity.  Making progress, slowly but surely.  Thanks to all for the ideas!


Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.