Introduction to Sandboxed Solutions - Sort data in repeating tables using managed code

Hi. My name is Phil Newman and I'm a program manager on the InfoPath team. In this post, I'd like to introduce you to one of my favourite new features in InfoPath 2010 - Sandboxed Solutions. In InfoPath 2010, forms with code can now be published directly to SharePoint without requiring the farm administrator to approve and upload them! These forms run in a sandboxed environment which protects other resources on the SharePoint server from malicious code.

In this short video demo, I show how you can add the ability to sort repeating tables in your forms using code.  I use picture buttons in the heading of the repeating table to trigger the code which sorts the table based on the values in the columns. 

Get Microsoft Silverlight

About the Code

In a nutshell, the code puts the XML from the repeating table into an array, sorts the array using built-in .Net functionality, and then writes back into the XML to update the table.

//Sorts a repeating table. Takes the parent group node and the index of the column to sort by.

private void SortTable(string xpathToSort, int columnToSortBy)

{

//get the values in an array

string[][] ValuesToSort = GetRepeatingTableValues(xpathToSort);

//sort the array

Array.Sort(ValuesToSort, new StringArrayComparer(columnToSortBy));

//write the values back to the xml

SetRepeatingTableValues(xpathToSort, ValuesToSort);

}

//Takes an array of arrays and inserts it into a repeating table

//Assumes that the array and table are the same size

private void SetRepeatingTableValues(string xpathToSet, string[][] tableValues)

{

//Create a navigator on main node supplied in the parameters

XPathNavigator mainGroup = this.CreateNavigator().SelectSingleNode(xpathToSet, this.NamespaceManager);

//Clone the first child node of the main navigator and populate it with data

XPathNodeIterator tableRows = mainGroup.SelectChildren(XPathNodeType.Element);

           

//iterate through the existing XML and update the values from the sorted array

for (int i = 0; i < tableValues.Length; i++)

{

tableRows.MoveNext();

XPathNodeIterator thisRow = tableRows.Current.SelectChildren(XPathNodeType.Element);

for (int j = 0; j < tableValues[0].Length; j++)

{

thisRow.MoveNext();

thisRow.Current.InnerXml = tableValues[i][j];

}

}

}

//Returns an array of arrays of strings representing the values in a repeating table

private string[][] GetRepeatingTableValues(string xpathToGet)

{

XPathNavigator myNav = this.CreateNavigator();

int rows, cols;

XPathNodeIterator tableNodes;

//figure out the dimensions of the table

tableNodes = myNav.SelectSingleNode(xpathToGet, this.NamespaceManager).SelectChildren(XPathNodeType.Element);

            rows = tableNodes.Count;

           

//move to the first row to count the columns

tableNodes.MoveNext();

cols = tableNodes.Current.SelectChildren(XPathNodeType.Element).Count;

//create an array to store the values

string[][] tableValues = new string[rows][];

//get all the rows in the table

tableNodes = myNav.SelectSingleNode(xpathToGet, this.NamespaceManager).SelectChildren(XPathNodeType.Element);

//iterate through the rows and write the inner xml of each element in each row to the array

for (int i = 0; i < rows; i++)

{

tableNodes.MoveNext();

XPathNodeIterator childNodes = tableNodes.Current.SelectChildren(XPathNodeType.Element);

string[] rowValues = new string[cols];

for (int j = 0; j < cols; j++)

{

childNodes.MoveNext();

rowValues[j] = childNodes.Current.InnerXml;

}

tableValues[i] = rowValues;

}

return tableValues;

}

//Comparison implementation for array or arrays

class StringArrayComparer : IComparer

{

private int iColumn;

public StringArrayComparer(int iColumn)

{

this.iColumn = iColumn;

}

int IComparer.Compare(Object x, Object y)

{

string[] xAsString = (string[])x;

string[] yAsString = (string[])y;

return xAsString[iColumn].CompareTo(yAsString[iColumn]);

}

}

Requirements for publishing your forms with code as Sandboxed solutions:

  • The Sandboxed code service need to be enabled on the farm
  • You must be a site collection administrator on the site collection you’re publishing to
  • Your form must be domain trust
  • Your form template must be an InfoPath 2010 template
  • InfoPath 2007 form code needs to be upgraded to InfoPath 2010 form code

Complete sandboxed solution documentation is available here.  Note that InfoPath takes care of packaging and activating the solution, all you need to do is publish your form to a document library or as a site content type.

I look forward to hearing your comments about this new feature. Let me know what you think!

Phil