February 7, 2009

Surround SCM MS Project Add-In Toolbar

Surround SCM

Works with Surround SCM 2009

The obvious benefit of having the Surround SCM database in an RDBMS format is reporting. Another benefit that may not be so obvious is the ability to access the database programatically. In previous versions, our only option was to run a Command Line Interface (CLI) command, pipe the output into a text file, and then parse the text file to get the data we were after. This approach, while doable, wasn't an exact science and performance was not the best. Note: Accessing the database directly is only for gathering information, but not for retrieving the actual files stored in Surround SCM. This also means that the database shall not be modified in any way. Doing so is likely to result in a corrupt database, and your only course of action will be to restore a full backup. [toc]

MS Project Add-in Toolbar

The purpose of this project is to show an example of how the data accessed from the database could be used. By no means is this project to be considered an official Seapine release. While I have made every attempt to make it as error free as possible, it has not been tested by anyone other than me. I may have not trapped every single possible error either. If you find this idea as something you would like to use with your copy of Microsoft Project, I strongly recommend that you review every line of code and only use it once you fully understand it and are confident of it.

What does the toolbar do?

The toolbar allows you to browse your Surround SCM branches and repositories in the given SQL database and get a list of files for a given branch and repository. You can then select a file and have the add-in toolbar insert an sscm:// link in the hyperlink field of a task in the current MS Project project. If you have a task selected, it adds the link to that task, otherwise, it creates a new task. Clicking on the hyperlink opens the Surround SCM client and selects the file in the specified branch and repository. The idea is that you may have tasks in MS Project related to files you are working on that are controlled in Surround SCM. The link allows you to easily access those files.

How it works

The toolbar consists of two buttons. One pops up a window where you enter the connection informatino to the SQL database. The other one will open a window with three panes, a branch pane, a repository pane, and a files pane. It also contains a button to insert the link.

The Database Connection window

SQL Database Connection window
  • MS SQL Server Address: The address of the SQL Server. Should be the same address/name you use when connecting via a SQL Client componenet.
  • UserName: SQL Username that will allow you to access and read the database. The user should not need any data modification permissions. In fact, you should use a user that does not have any permissions to modify the data to reduce risk.
  • Password: The password of the above user.
  • Database: The database where the mainline branch is stored.
  • Test Connection: This button tests the connection based on the information provided. It actually queries the MAINLINE table and if it has any issues doing this, it displays a message box with the exception message.
If it is able to successfully query the MAINLINE table, then the connection information should be correct.
  • SSCM Hyperlink: Enter the Surround SCM server and port in the following format:
<server address>:<server port> This will be used when the sscm:// link is built. Hint: Open your Surround SCM Client and enable the address toolbar. Note the server address and port used there. This is the server address and port you need. Click on OK to save the information and close the window, Cancel to discard any changes and close the window.

The Repository Browser window

The Surround SCM Repository/Files Browser window will show you at first the mainline(s) found in the SQL database. If you select the mainline branch, then the repository pane will be populated with the repository tree for that mainline. If you expand the mainline, then you will see the branch tree for that mainline branch. Upon selecting a repository, the file pane will be populated with the files for that specific branch and repository. Select a file a click on Add Reference to add an sscm:// link to a task in Project.

Behind the scenes

While I do not want to spend to much time explaining the code I wrote for this example, I would like to explain the general approach

About the Project

I used the Visual Studio "shared Add-in" project type for this, specifying Microsoft Project as the application for my add-in. Theoretically, you can create your own project and select a different Microsoft Office product. Maybe you like the idea of inserting sscm:// links, but to an Excel spreadsheet instead. When you create this type of project, you follow a wizard and it places some minimal code for you. There is plenty of documentation and tutorials out on the web that can help you get started.

Requirements

The project has the following references:
  • Microsoft.Office.Interop.MSProject - 11.0.0.0
  • Office - 2.3.0.0
Also, the Add-in toolbar may not work unless you first install the Shared Add-in Support Update for Microsoft .NET framework 2.0 (KB908002). This is set as a pre-requisite in the installer program.

Handling the data

I created classes for each type of Surround SCM object, mainly a class for the branches (CBranch) and a class for the repository (CRepository). I then created fields for each class to store information about each branch or repository, like the name, for example. When reading the database, I used arrays of these classes to store the information. The following is an excerpt of the code used to build a CBranch array. The array is then used to build the branch tree. Based on the mainline name passed, it builds the array: Note: Some of the fields of the CBranch class were added exclusively to aid in the process of building the tree.
 public CBranch[] GetBranchTreeforMainline(string mainline)
 {
     CBranch[] scmBranches = null;
     SqlConnection myConnection = new SqlConnection(connString);

     SqlDataAdapter ad = new SqlDataAdapter("SELECT BRANCH.MainlineID, BranchID, BranchName, ParentBranchID from BRANCH, MAINLINE WHERE MAINLINE.MainlineName = '"+ mainline +"' AND BRANCH.MainlineID = MAINLINE.MainlineID", myConnection);
     DataSet ds = new DataSet();

     ad.Fill(ds, "Branches");

     int rowCount = ds.Tables[0].Rows.Count;
     scmBranches = new CBranch[rowCount];
     //Build the CBranch array
     for (int i = 0; i < rowCount; i++)
     {
         scmBranches[i] = new CBranch();
         scmBranches[i].SetMainlineID(ds.Tables[0].Rows[i][0].ToString());
         scmBranches[i].SetMainline(mainline);
         scmBranches[i].SetID(ds.Tables[0].Rows[i][1].ToString());
         scmBranches[i].SetName(ds.Tables[0].Rows[i][2].ToString());
         scmBranches[i].SetParentID(ds.Tables[0].Rows[i][3].ToString());

     }
     //Build more info on the CBranch array - the info built below is used for creating the branch and repo trees
     for (int i = 0; i < scmBranches.Length; i++)
     {
         String branchPath = "";
         String fullBranchPath = "";
         String parentPath = "";
         int level = 1;
         String ParentID = scmBranches[i].GetParentID();
         while (ParentID != "")
         {
             for (int j = 0; j < scmBranches.Length; j++)
             {
                 if (scmBranches[j].GetID().Equals(ParentID))
                 {
                     parentPath = scmBranches[j].GetName();
                     if (level == 1)
                     {
                         scmBranches[i].SetParent(parentPath);
                     }
                     branchPath = parentPath + "/" + branchPath;
                     level++;
                     ParentID = scmBranches[j].GetParentID();
                 }

             }
          }
          fullBranchPath = branchPath + scmBranches[i].GetName();
          scmBranches[i].SetPath(fullBranchPath);
          scmBranches[i].SetLevel(level.ToString());

      }
        return scmBranches;

 }

Download the Source Code

You can download the Visual Studio 2005 project written in C# from here