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.

No comments :


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.