Anne Bougie

I use Rhino Mocks pretty much exclusively for mocking and testing. I find that there really isn't anything that I've wanted to do that I haven't been able to figure out how to accomplish with Rhino Mocks. What is everyone else using? I'd like to know so I can take a look.

Anyway, as you know, with Expect.Call we can return property values, return values from functions, throw an error, etc. Sometimes just returning a static value isn't going to work, or it's just easier to give it some simple code to run. When you have many components, all with with their mockable interfaces, sometimes when you need to write a test that is in the middle, you need to run some simple code to test certain scenarios. I think this feature can be used inappropriately if it is overused, but it definitely has it's place when there's just no other good way to set up the test scenario.

Here is a simple example of mocking out a billing component. I admit, it's a bit contrived, but demonstrates the use of delegates in Rhino Mocks. I have an interface named IBilling that I'm mocking. It's purpose is to bill several charges that fall within a billing period. The first four Expect.Call statements simply provide a date value. The .Do statement calls a delegate that passes in a function in my testing module. When your test code hits that function call, instead of just returning a predefined value, it will execute the function you passed in.

[TestMethod]
private void ChargePeriodEndsBeforeBillingPeriodDailyChargeTest()
{
   MockRepository mocks = new MockRepository();
   IBilling billing = mocks.DynamicMock<IBilling>();
   IRateCalculator rates = mocks.DynamicMock<IRateCalculator>();

   int customerId = 1;

   using (mocks.Record())
   {
      Expect.Call(billing.GetPeriodBeginDate())
         .Return(DateTime.Parse("1/1/2010");
      Expect.Call(billing.GetChargeBeginDate())
         .Return(DateTime.Parse("1/1/2010");
      Expect.Call(billing.GetPeriodEndDate())
         .Return(DateTime.Parse("2/1/2010");
      Expect.Call(billing.GetChargeEndDate())
         .Return(DateTime.Parse("1/15/2010");
      Expect.Call(billing.CalculateChargeDays(
         new DateTime(), new DateTime()
         .IgnoreArguments()
         .Do(new CalcDaysDelegate(DaysDifference));
      Expect.Call(rates.GetRate(0)).Return(0.1);
      Expect.Call(() => billing.AddCharge(0, 0, new DateTime()))
         .IgnoreArguments()
         .Constraints(Is.Equal(customerId),
                      Is.Equal(1.6),
                      Is.Anything());
   }
   using (mocks.Playback())
   {
      ChargeBilling chargeBilling = new ChargeBilling(customerId, billing);  
      chargeBilling.AddChargeToBill();
   }
}

private delegate int CalcDaysDelegate(DateTime start, DateTime end);
private int DaysDifference(DateTime start, DateTime end)
{
   TimeSpan ts = end - start;
   return ts.Days + 1;
}

In real life I would recommend just returning a "16" to the CalculateChargeDays function call, but a real-life scenario would be too long of a post ; -) I also want to point out the use of the lambda expression for calling a function call with a void return value, () => billing.AddCharge. You can also call it using the delegate keyword, but I find the lambda syntax to be cleaner and easier to read, much less easier to remember when you're coding.

Happy coding, and please let me know what you think about other mocking software you use that I might want to try.

Apple just patented MaSfPRTFfK, which stands for "Methods and Systems for Providing Real-Time Feedback for Karaoke". It detects when a singer is singing off-pitch according to some pitch data stored with the karaoke song data. The idea is to give some kind of feedback to the singer so they can learn to sing on key. How this technology will be available to programmers writing karaoke systems, I'm not sure, but I'm going to keep my eye on it. I like to sing (ahem, I mean butcher) karaoke, and I'm thinking of writing a karaoke program since everything out there basically sucks. Maybe when I do write my program with this technology, I'll learn just how bad I really do sing. A singing programmer is usually not a good thing.

Will this eradicate bad karaoke at the bars? Probably not as long as alcohol is still involved. Now, if they added a breathalyzer as well....

Recently I have been doing some work on an older system using ADODB recordsets. Every now and then we still have to support these old systems. I wanted to create an open recordset in code on the .NET side to pass to the older system, but ADODB really wants an open database connection in order to open a recordset. My first obvious attempt was to create the recordset object, then create the field objects and add them to the recordset. I could create the recordset this way, but I couldn't open it, and a recordset must be open in order to add data rows. What ADODB really wanted was a table that matched my schema so it could connect to it before adding rows. This was just not practicable.

I discovered that you can open an ADODB recordset from an XML data source, and with the ADODB Stream object, that source can be in memory. Since this was something that I needed to use a lot for this project, I wrote a helper class, named RecordSetHelper, that will create the xml with an easy-to-use interface. I used Linq XML objects to create the XML string, then some rather simple code to put the pieces together into the correct schema that ADODB was expecting. If anyone wants the complete code, I can email it to them. The high points are below.

Here is the xml for a simple ADODB recordset with a couple of data rows:

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
     xmlns:rs="urn:schemas-microsoft-com:rowset"
     xmlns:z="#RowsetSchema">
  <s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly" rs:updatable="true">
      <s:AttributeType name="FirstName" rs:number="1" rs:write="true">
        <s:datatype
            dt:type="string"
            rs:dbtype="str"
            dt:maxLength="200"
            rs:precision="0"
            rs:fixedlength="true"
            rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="LastName" rs:number="2" rs:write="true">
        <s:datatype
            dt:type="string"
            rs:dbtype="str"
            dt:maxLength="200"
            rs:precision="0"
            rs:fixedlength="true"
            rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="DOB" rs:number="3" rs:write="true">
        <s:datatype
            dt:type="dateTime"
            rs:dbtype="variantdate"
            dt:maxLength="16"
            rs:precision="0"
            rs:fixedlength="true"
            rs:maybenull="false" />
      </s:AttributeType>
      <s:extends type="rs:rowbase" />
    </s:ElementType>
  </s:Schema>
  <rs:data>
    <rs:insert>
      <z:row
            FirstName="Hugh"
            LastName="Morris"
            DOB="1975-02-05T00:00:00" />
      <z:row
            FirstName="Anne"
            LastName="Teak"
            DOB="1932-06-07T00:00:00" />
      <z:row
            FirstName="Jean"
            LastName="Poole"
            DOB="1989-05-05T00:00:00" />
    </rs:insert>
  </rs:data>
</xml>


Creating the XML in code each time seemed rather tedious, so I made an interface to create a recordset using the RecordSetHelper. Here is an NUnit test that uses the RecordSetHelper to create an open recordset that is identical to the xml listed above.

      [Test]
      public void RecordsetHelperTest()
      {
         RecordSetHelper helper = new RecordSetHelper();
         helper.AddField(new FieldItem
            {
               Name = "FirstName",
               FieldType = VariantType.String
            });
         helper.AddField(new FieldItem
            {
               Name = "LastName",
               FieldType = VariantType.String
            });
         helper.AddField(new FieldItem
            {
               Name = "DOB",
               FieldType = VariantType.Date
            });
         helper.AddDataRow(new object[]
            {
               "Hugh", "Morris", "2/5/1975"
            });
         helper.AddDataRow(new object[]
            {
               "Anne", "Teak", "6/7/1932"
            });
         helper.AddDataRow(new object[]
            {
               "Jean", "Poole", "5/5/1989"
            });
         XDocument xRS = helper.MakeXML();
         ADODB.Recordset rs = helper.GetRecordSet();
         Assert.IsInstanceOf(typeof(ADODB.Recordset), rs);
         Assert.AreEqual((int)ObjectStateEnum.adStateOpen, rs.State);
      }


Here is the code to take the XML (returned by the MakeXml function) and turn it into an open ADODB recordset that you can manipulate. The stream is the ADODB.Stream object, not a .NET Stream object.

      public Recordset GetRecordSet()
      {
         var doc = MakeXML();
         var rs = new Recordset();
         var stream = new Stream();
         stream.Open(
            Type.Missing,
            ConnectModeEnum.adModeUnknown,
            StreamOpenOptionsEnum.adOpenStreamUnspecified,
            null,
            null);
         stream.WriteText(doc.ToString(), StreamWriteEnum.adWriteChar);
         stream.Position = 0;
         rs.Open(
            stream,
            Type.Missing,
            CursorTypeEnum.adOpenUnspecified,
            LockTypeEnum.adLockUnspecified, 0);

         return rs;
      }

The MakeXML is the method that takes all the pieces needed to create the xml string and puts them all together.

      public XDocument MakeXML()
      {
         var root = MakeRoot();
         var fields = GetFieldObjects();
         var rowSetSchema = MakeFieldsXml(fields);
        
         root.Add(rowSetSchema);
         if (_rows.Count > 0)
         {
            var data = MakeDataXml();
            root.Add(data);
         }
         var xdoc = new XDocument(root);
         return xdoc;
      }


The first thing to do was to create the XML namespace objects to be used when attaching the field and data elements. I created these as fields at the class level.

      internal XNamespace xns
      {
         get { return "http://www.w3.org/2000/xmlns/"; }
      }
      internal XNamespace xnss
      {
         get { return "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"; }
      }
      internal XNamespace xnsdt
      {
         get { return "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"; }
      }
      internal XNamespace xnsrs
      {
         get { return "urn:schemas-microsoft-com:rowset"; }
      }
      internal XNamespace xnsz
      {
         get { return "#RowsetSchema"; }
      }

The "MakeRoot" function creates the root element, named "xml". The rest of the code just creates each piece and adds it to the root element. There is some funny kind of syntax needed to create the namespace attributes. You have to use the "+" operator with the XNamespace object and the prefix, then add the urn as the value. I struggled for a while with this and finally looked up the answer. It still seems like some strange idiomatic syntax, but it works.

      private XElement MakeRoot()
      {
         var root = new XElement("xml",
             new XAttribute(xns + "s", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"),
             new XAttribute(xns + "dt", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"),
             new XAttribute(xns + "rs", "urn:schemas-microsoft-com:rowset"),
             new XAttribute(xns + "z", "#RowsetSchema"));
         return root;
      }

Since each field is the same schema, but differs in its name, datatype, and a few other attributes, I created an abstract base class named RSField. The public GetFieldElement method calls the abstract method GetDataTypeElement, which is what creates the distinct xml for each data type correctly.

   public abstract class RSField
   {

      internal XNamespace xns
      {
         get { return "http://www.w3.org/2000/xmlns/"; }
      }
      internal XNamespace xnss
      {
         get { return "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"; }
      }
      internal XNamespace xnsdt
      {
         get { return "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"; }
      }
      internal XNamespace xnsrs
      {
         get { return "urn:schemas-microsoft-com:rowset"; }
      }
      protected XElement FieldElement;
      protected string FieldName { get; set; }
      protected int Ordinal { get; set; }

      protected RSField(string fieldName, int ordinal)
      {
         FieldName = fieldName;
         Ordinal = ordinal;
      }

      protected abstract XElement GetDataTypeElement();

      public XElement GetFieldElement()
      {
         var element = new XElement(xnss + "AttributeType",
             new XAttribute("name", FieldName),
             new XAttribute(xnsrs + "number", Ordinal.ToString()),
             new XAttribute(xnsrs + "write", "true"));
         element.Add(GetDataTypeElement());
         return element;
      }
   }


Here is an example of an overriding class:

   public class RSIntegerField : RSField
   {
      public RSIntegerField(string fieldName, int ordinal)
         : base(fieldName, ordinal)
      {
      }

      protected override XElement GetDataTypeElement()
      {
         var element = new XElement(xnss + "datatype",
             new XAttribute(xnsdt + "type", "i8"),
             new XAttribute(xnsdt + "maxLength", "8"),
             new XAttribute(xnsrs + "precision", "0"),
             new XAttribute(xnsrs + "fixedlength", "true"),
             new XAttribute(xnsrs + "maybenull", "false"));
         return element;
      }
   }

In the MakeXML method, after creating the root element, all the field objects are created. Each field object is created. The field objects are of type RSField, which contains the xml for each field.

      private RSField[] GetFieldObjects()
      {
         var fields = new RSField[_fields.Count];
         for (int i = 0; i < _fields.Count; i++)
         {
            FieldItem field = _fields.ElementAt(i);
            switch (field.FieldType)
            {
               case VariantType.Integer:
               case VariantType.Long:
               {
                  fields[i] = new RSIntegerField(field.Name, (i + 1));
                  break;
               }
               case VariantType.Single:
               {
                  fields[i] = new RSSingleField(field.Name, (i + 1));
                  break;
               }
               case VariantType.Double:
               {
                  fields[i] = new RSDoubleField(field.Name, (i + 1));
                  break;
               }
               case VariantType.Currency:
               {
                  fields[i] = new RSCurrencyField(field.Name, (i + 1));
                  break;
               }
               case VariantType.Date:
               {
                  fields[i] = new RSDateField(field.Name, (i + 1));
                  break;
               }
               case VariantType.String:
               {
                  fields[i] = new RSTextField(field.Name, (i + 1));
                  break;
               }
               case VariantType.Boolean:
               {
                  fields[i] = new RSBooleanField(field.Name, (i + 1));
                  break;
               }
               case VariantType.Byte:
               {
                  fields[i] = new RSByteField(field.Name, (i + 1));
                  break;
               }
               default:
               {
                  throw new ArgumentException("Field type is not valid");
               }
            }
         }
         return fields;
      }

The objects collection that this is looping through are the type of a FieldItem class used to hold the name and data type of the field. In the NUnit test above, I added several fields using FieldItem.  I decided to use the Microsoft.VisualBasic namespace for the data type enum, but I could also have used the ADODB.DataTypeEnum, but the names seemed clunkier; "adBigInt" instead of just "Long".

   public class FieldItem
   {
      public string Name { get; set; }
      public VariantType FieldType { get; set; }
   }

The MakeFieldsXML, which is called from the MakeXML method, takes all the fields of RSField type and creates the Linq XElement tree for all the fields. This is then attached to the root element.

      private XElement MakeFieldsXml(IEnumerable<RSField> fields)
      {
         var schema = new XElement(xnss + "Schema",
               new XAttribute("id", "RowsetSchema"));
         var elementType = new XElement(xnss + "ElementType",
               new XAttribute("name", "row"),
               new XAttribute("content", "eltOnly"),
               new XAttribute(xnsrs + "updatable", "true"));
         foreach (var field in fields)
            elementType.Add(field.GetFieldElement());
         elementType.Add(new XElement(xnss + "extends",
            new XAttribute("type", "rs:rowbase")));
         schema.Add(elementType);
         return schema;
      }

The data is passed to the RecordSetHelper in the form of an object array. The array must have the same number of elements as the number of fields. The MakeDataXML method creates the xml that contains all the data rows.

      private XElement MakeDataXml()
      {
         var data = new XElement(xnsrs + "data");
         var insert = new XElement(xnsrs + "insert");
         foreach (var row in _rows)
         {
            var rowElement = new XElement(xnsz + "row");
            for (int i = 0; i < _fields.Count; i++ )
            {
               var field = _fields.ElementAt(i);
               object value = row[i];
               string s = value.ToString(field.FieldType);
               var val = new XAttribute(field.Name, s);
               rowElement.Add(val);
            }
            insert.Add(rowElement);
         }
         data.Add(insert);
         return data;
      }

The only thing special about this piece of code is the ToString method on the value object-type variable. Just using the base ToString didn't work since it was of type "object", and the value may have been entered as a string, it may not have internally converted it to the correct type. Also, the date fields had to be in a certain format or it would crash when you retrieved the value from the recordset. So I created an extension method overriding the base ToString on object. It just converts the object to the correct underlying data type before converting to a string.

      public static string ToString(this object me, VariantType type)
      {
         string s;
         switch (type)
         {
            case VariantType.Integer:
            {
               s = Convert.ToInt32(me).ToString();
               break;
            }
            case VariantType.Long:
            {
               s = Convert.ToInt64(me).ToString();
               break;
            }
            case VariantType.Single:
            {
               s = Convert.ToSingle(me).ToString();
               break;
            }
            case VariantType.Double:
            {
               s = Convert.ToDouble(me).ToString();
               break;
            }
            case VariantType.Currency:
            {
               s = Convert.ToDecimal(me).ToString();
               break;
            }
            case VariantType.Byte:
            {
               s = Convert.ToByte(me).ToString();
               break;
            }
            case VariantType.Date:
            {
               DateTime dtValue = DateTime.Parse(me.ToString());
               s = dtValue.ToString("s");
               break;
            }
            case VariantType.Boolean:
            {
               s = Convert.ToBoolean(me).ToString();
               break;
            }
            default:
            {
               s = Convert.ToString(me);
               break;
            }
         }
         return s;
      }

If you find this useful, and you would like the complete source code, let me know and I'll email you the project.

I started this series to show how to copy different kinds of lists in SharePoint. Each type of list has its own challenges. In this article I will also demonstrate how to copy custom fields and keep the same internal name. It’s kind of annoying that you can’t create a field with the internal name you specify, but I found a pretty easy workaround. You create the field using AddFieldAsXml on the list Fields collection, and you use the internal name as the display name. This creates the field with the correct internal name. Then just change the Title property of the field to what you want displayed. Problem solved.

Copying a document library is similar to copying a generic list, except that you also need to copy the document file attached to the list item. You also need to determine the template that was used to create the original list, and use that template to create the new list. That way, when users add a new document, it is of the right document type. Another gotcha in copying document libraries, is that there is a subfolder structure that needs to be copied recursively to get everything in its correct place.

Here's the code. I copied it out of my project, which has everything in its own method, so I apologize if some of my capitalization or field names are not correct. Also, there is a function referenced, CopyFieldValues. If you need to know how to do this, refer to Part 1 of this series. Document libraries can have field values just like other lists, so you'll need to copy those too if you want a complete copy of the list.

// get the document template to use when creating the new list
SPDocTemplate docTemplate;
SPDocTemplateCollection templates = destinationWeb.DocTemplates;
XDocument templateSchema = XDocument.Parse(templates.SchemaXml);
IEnumerable<XElement> paths = templateSchema.Descendants("DocumentTemplateFile");

string sourceUrl = sourceDocumentTemplateUrl.Substring(sourceDocumentTemplateUrl.IndexOf("/") + 1);
XElement path = paths.Where(p => p.Attribute("TargetName").Value == sourceUrl).Single();
XElement docTemplate = path.Parent.Parent;
int type = Convert.ToInt32(docTemplate.Attribute("Type").Value);

SPDocTemplate destTemplate = null;
foreach (SPDocTemplate template in templates)
{
   if (template.Type == type)
      docTemplate = template;
}

Guid newListID = destinationWeb.Lists.Add(sourceList.Title,
                     sourceList.Description,
                     destinationWeb.ListTemplates["Document Library"], docTemplate);

SPList destinationList = destinationWeb.Lists[newListID];

// add any custom fields that were created
foreach (SPField field in SourceList.Fields)
{
   if (!DestinationList.Fields.ContainsField(field.InternalName))
   {
      // create field with correct internal name
      XDocument fieldXdoc = XDocument.Parse(field.SchemaXml);
      XElement fieldElement = fieldXdoc.Element("Field");
      XAttribute displayName = fieldElement.Attribute("DisplayName");
      displayName.Value = field.InternalName;
      string fieldName = DestinationList.Fields.AddFieldAsXml(fieldXdoc.ToString());
      SPField newfield = DestinationList.Fields.GetField(fieldName);
      newfield.Title = field.Title;
   }
}

Here is the code for CopyDocumentLibraryItems, which needs to be recursive to get all the items in all the subfolders:

      protected virtual void CopyDocumentLibraryItems(SPFolder sourceFolder, SPFolder destFolder)
      {
         // get a list of items in the source folder
         SPListItemCollection items = ((SPDocumentLibrary)SourceList).GetItemsInFolder(SourceList.DefaultView, sourceFolder);

         foreach (SPListItem item in items)
         {
            SPFileSystemObjectType myType = item.FileSystemObjectType;
            string relativeDestinationUrl = "{0}/{1}";
            if (myType == SPFileSystemObjectType.File)
            {
               SPFile fi = item.File;
               byte[] filebytes = fi.OpenBinary();
               relativeDestinationUrl = string.Format(relativeDestinationUrl, destFolder.Url, fi.Name);
               SPFile newFile = destFolder.Files.Add(relativeDestinationUrl, filebytes, true);
               SPListItem newItem = newFile.Item;
               CopyFieldValues(item, newItem);
               newItem.UpdateOverwriteVersion();
            }
            else
            {
               relativeDestinationUrl = string.Format(relativeDestinationUrl, destFolder.Url, item.Folder.Name);
               SPFolder destSubFolder = destFolder.SubFolders.Add(relativeDestinationUrl);
               SPFolder sourceSubFolder = item.Folder;
               // copy everything in sub folder
               CopyDocumentLibraryItems(sourceSubFolder, destSubFolder);
            }
         }
      }

The fantastic 40 templates from Microsoft can cause issues with content deployment. The following error prevents content deployment with the templates installed:

1. The content type name cannot contain: \ / : * ? " # % < > { } | ~ &, two consecutive periods (..), or special characters such as a tab. at Microsoft.SharePoint.SPContentType.ValidateName(String name) at Microsoft.SharePoint.SPContentType.ValidateNameWithResource(String strVal, String& strLocalized) at Microsoft.SharePoint.SPContentType.set_Name(String value) at Microsoft.SharePoint.Deployment.ContentTypeSerializer.UpdateContentTypeMetaData(SPContentType sourceContentType, SPContentType targetContentType, String contentTypeXml) at Microsoft.SharePoint.Deployment.ContentTypeSerializer.UpdateContentType(SPContentType sourceContentType, SPContentType targetContentType, String contentTypeXml, ImportObjectManager importObjectManager) at Microsoft.SharePoint.Deployment.ContentTypeSerializer.ProcessContentType(SPContentType sourceContentType, String contentTypeXml, ImportObjectManager importObjectManager, Boolean IsParentSystemObject) at Microsoft.SharePoint.Deployment.ContentTypeSerializer.SetObjectData(Object obj, SerializationInfo info, StreamingContext context, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.XmlFormatter.ParseObject(Type objectType, Boolean isChildObject) at Microsoft.SharePoint.Deployment.XmlFormatter.DeserializeObject(Type objectType, Boolean isChildObject, DeploymentObject envelope) at Microsoft.SharePoint.Deployment.XmlFormatter.Deserialize(Stream serializationStream) at Microsoft.SharePoint.Deployment.ObjectSerializer.Deserialize(Stream serializationStream) at Microsoft.SharePoint.Deployment.ImportObjectManager.ProcessObject(XmlReader xmlReader) at Microsoft.SharePoint.Deployment.SPImport.DeserializeObjects() at Microsoft.SharePoint.Deployment.SPImport.Run()

To fix this error, the Fields.xml file located at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\TSATypes\Fields.xml needs to be modified. This xml file contains many entries where the field id is set to a Guid. The problem is that this Guid contains curly braces in it: <Field ID="{38F29346-B895-4ece-9BD6-90C7D1E6DA95}". These braces need to be removed.

Make a copy of this file before making modifications in case you have problems after making this change. Then simply do a find and replace on "{ and }" and remove the braces.

The following error can occur during content deployment:

 The changeToken refers to a time before the start of the current change log.

The changeToken refers to a time before the start of the current change log. at Microsoft.SharePoint.Deployment.SPExport.ThrowInvalidChangeTokenError(DateTime minChangeTime, Int32 minChangeNumber) at Microsoft.SharePoint.Deployment.SPExport.GetIncrementalChanges() at Microsoft.SharePoint.Deployment.SPExport.CalculateObjectsToExport() at Microsoft.SharePoint.Deployment.SPExport.Run()

Content deployment job 'Entire site' failed.The exception thrown was 'Microsoft.SharePoint.SPException' : 'The changeToken refers to a time before the start of the current change log.'

This error happens when more than 15 days have passed since the last deployment, and you have the job set to use incremental deployment. The fix is really easy. You just have to change the deployment options on the deployment job to deploy all content instead of deploying only new or changed content. Run the content deployment once, then you can change back to incremental deployment.

When I needed to read an Excel spreadsheet from a SharePoint site, it seemed like a simple enough request. Previously, whenever I needed to open an Excel file, I used an OleDb connection with the following connection string:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source={0};Persist Security Info=False;
       Extended Properties=""Excel 12.0;HDR=YES""";
connectionString = string.Format(connectionString, filePath);

Of course, you can't open the file from the SharePoint site this way. So, my first hurdle was to figure out how to download the file from the web site. The System.Net namespace provides a very easy way to do this:

WebClient client = new WebClient();
client.UseDefaultCredentials = true;
Stream stream = client.OpenRead(url);

Now I had my file as a stream, so I thought I'd just extract it, save it to a temporary file, then read it in in the usual way. Problem solved. This next bit of code should probably be optimized a little so I'm not resizing the array every single loop:

BinaryReader brdr = new BinaryReader(stream);
byte[] result = new byte[0];
int bufferSize = 32768; // 32k
byte[] buffer = new byte[bufferSize];
long pos = 0;

while (true)
{
      buffer = brdr.ReadBytes(bufferSize);
      if (pos > 0)
      {
            // copy old data to bigger result
            byte[] temp = new byte[result.LongLength];
            Array.Copy(result, temp, result.LongLength);
            result = new byte[temp.LongLength + buffer.Length];
            Array.Copy(temp, result, temp.LongLength);
            // add new data
            for (int i = 0; i < buffer.Length; i++)
            {
                result[pos + i] = buffer[i];
            }
            pos += buffer.Length;
      }
      else
      {
            result = new byte[buffer.Length];
            Array.Copy(buffer, result, buffer.Length);
            pos = buffer.Length;
      }
      if (buffer.Length < bufferSize)
            break;
}
string tempFile = Path.Combine(Environment.GetEnvironmentVariable("TMP"),
      "CopyList.xlsx");
using (var fs = new FileStream(tempFile, FileMode.OpenOrCreate))
{
      var writer = new BinaryWriter(fs);
      writer.Write(result, 0, result.Length);
      writer.Close();
      fs.Close();
}


This worked beautifully on my development machine, but this is part of a larger program that is manipulating a SharePoint site via the SharePoint API. Therefore, it has to run on the server, which is 64-bit. When I first ran it, I received the error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" . So, I did a little digging around, and found out that maybe I needed to install the driver. It is available from Microsoft as a separate download, so I thought I was good to go. I installed it, and was still receiving the same error. Huh??!! Then I did a little *more* digging, and found out that there is no OleDb driver for opening Excel files on a 64-bit machine. Fine. No problem. Just compile the project as x86, and I'm good to go. Right? Well, wrong, actually. When the project is compiled in 32-bit, I can't access the SharePoint sites. Aaaaarrrrrrrhhhh! What to do? Then, I'm looking all over, trying to find a way to read an Excel file directly from a stream. How hard can it be, right?

<SARCASM>
Actually, Microsoft has this really handy little guide that tells you just how to do it. It's only 349 pages. Turns out Excel's native format is something called a BIFF (Binary Interchange File Format). So, you just have to parse all the bytes from the stream into the right format. What a treat!
</SARCASM>

I know who I'd like to BIFF about now. Anyway, I did *even more* digging around, and I found some code from someone who has done just that. Excel Data Reader  Thanks, iciobanu! However, it only works for Excel 2003 format. Now that is finally a problem I can easily solve! Save the spreadsheet to 2003 format, and read it in. Are we there yet? Almost! I was getting errors because the stream object returned from the web download didn't support seeking, which this code relies on. So, save the stream out to a temp file (back to that again), and read it back in as a FileStream object, which supports seeking, and my problems are, at long last, solved.

And that is how you open an Excel file from a SharePoint site into a spreadsheet without using a generic OleDb connection. Sucess at long last! Isn't there some famous quote somewhere about persistence paying off? 

I've been working with the SharePoint API lately. One of my tasks is to copy SharePoint lists from one site to another site. This can be done in code as long as the two sites are on the same machine. It's OK if they are in different web applications, but it can't be from one server to another. If you need to copy from one server to another, you either need to do an export/import, or find a way to save the settings and import them on the new machine. I believe you can use the SharePoint Web Services to accomplish this, though. I haven't worked with that yet.

Anyway, it took a lot of digging around to figure out all the things I needed to copy lists, so I thought I'd post the code that is working for me. This post is only going to cover copying a basic list. I am planning on several more posts to handle document libraries, wikis, and other special cases. Each has their own challenges.

The requirements for me are to copy the bulk of the lists of a large SharePoint site to a new site with a different structure. Therefore, content and deploy is not going to work. Also, they are redesiging the pages, so they really only wanted the lists. What I'm doing is creating a new list on the new site and copying all the properties, so any metadata, such as who last modified the list, is unfortunately lost.

Here's the code:

// Instantiate web instances
SPSite sourceSite = new SPSite(@"http://SharePointServer:31001");
SPWeb sourceWeb = sourceSite.RootWeb;
SPSite destSite = new SPSite(@"http://SharePointServer:31002");
SPWeb destWeb = destSite.RootWeb;

// Get a reference to the source list
SPList sourceList = sourceWeb.GetList("/Lists/Announcements");
 
// if the list exists on the destination site, delete it
try
{
   SPList temp = destWeb.Lists[sourceList.Title];
   destWeb.Lists.Delete(temp.ID);
}
catch { }

// create new list on the destination web with same properties
Guid newListID = destWeb.Lists.Add(sourceList.Title, sourceList.Description,
                                   sourceList.BaseTemplate);
SPList destList = destWeb.Lists[newListID];

// copy items
foreach (SPListItem item in announcements.Items)
{
   SPListItem newDestItem = destList.Items.Add();
   foreach (SPField field in sourceList.Fields)
   {
      if (!field.ReadOnlyField)
      newDestItem[field.Id] = item[field.Id];
   }
   newDestItem.Update();
}

// set quicklaunch settings
destList.OnQuickLaunch = sourceList.OnQuickLaunch;
destList.Update();

For testing, I often use the InternalsVisibleTo for my unit tests. It looks like this:

[assembly: InternalsVisibleTo("MyProject.Domain.Tests")]

When the assembly is strongly typed, though, this simple statement will not work, and we get the following message:

Error: "Strong-name signed assemblies must specify a public key in their InternalsVisibleTo declarations"

To fix this, you must do two things: You must sign the test assembly, and you must include the public key in the InternalsVisibleTo statement.

To get the public key value from the .snk file, use the sn.exe utility included in the .NET SDK. You must do two things, first, create a .snk file that contains only the public key:

sn -p MyKey.snk MyKey.PublicKeyOnly.snk

Then, you can extract the public key value with this command:

sn -tp MyKey.PublicKeyOnly.snk

This will produce a similar output:

Public key is
0024000004800000940000000602000000240000525341310004000001000100cfb8bc23b86a08
e70d021dd53d3b0293e716e71015870bdcc58a0231a4228618851a83e06077f5a44f42beb2baf3
56ad2d344521a96b0081ed0f25f9227523e3625eda524efe1cf2e1e5e41f3693a76ec52347684b
8129a4bb2d5fc49681adf33da0eecc4f81f011af4539d12abe1b4e760b5ce32d766db1012d4402
8381f0b4

Public key token is 2ff2b71993eeff95

Copy the public key value and update the InternalsVisibleTo:

[assembly: InternalsVisibleTo("MyProject.Domain.Tests, PublicKey=
0024000004800000940000000602000000240000525341310004000001000100cfb8bc23b86a08
e70d021dd53d3b0293e716e71015870bdcc58a0231a4228618851a83e06077f5a44f42beb2baf3
56ad2d344521a96b0081ed0f25f9227523e3625eda524efe1cf2e1e5e41f3693a76ec52347684b
8129a4bb2d5fc49681adf33da0eecc4f81f011af4539d12abe1b4e760b5ce32d766db1012d4402
8381f0b4")]

Voila! You can now access the internals from your test project for strong named assemblies.

Regular expressions are one of those things that you may not need very often, but when you do, it really solves the problem. The usage of the Regex static methods may seem difficult, but they're pretty easy. Being able to use regular expressions readily will help you quickly write some code that would take you many hours longer by parsing the strings.  I've compiled some code that uses the basic features of the Regex class. This article only covers the coding side of it. In order for it to work, you of course will need to know how to write the expressions themselves. I plan to cover that in my next post on Regex. That, too, seems very complex and difficult until you get a little experience with it.

To simply determine if a string contains a match, use IsMatch:

public bool IsValidUSPhone(string number)
{
    return Regex.IsMatch(number, @"\(\d{3}\)\s\d{3}-\d{4}");
}

The Match object contains a lot of information about the matched sub-string. As you can see in the following code, it has a Success property that will return true if a match was found. The Value property contains the matched sub-string, as in the following code:

string text = "Please call us at (808) 867-5309 to inquire about our offer.";
string expression = @"\(\d{3}\)\s\d{3}-\d{4}";
Match m = Regex.Match(text, expression);
if (m.Success)
{
    Console.WriteLine(string.Format("Phone: {0}", m.Value));
}

If the text contains several sub-strings that will match the expression, the Match object will contain all of them, and you can loop through them as in the following code. Notice that I'm also using the Index and Length properties.

string text = "Please call us at (808) 867-5309 or (808) 555-1212 at your earliest convenience.";
string expression = @"\(\d{3}\)\s\d{3}-\d{4}";
Match m = Regex.Match(text, expression);
while (m.Success)
{
    Console.WriteLine(string.Format("The phone # {0} starts at position {1}, and is {2} characters long.", m.Value, m.Index, m.Length));
    m = m.NextMatch();
}

By using parentheses around sections of the match expression, you can pull out parts of the match value. The Match object contains a Groups property. When there is a match, it always has one item in the Groups collection, which is the matched value. When you use parentheses around parts of the expression, it puts those parts into Groups items. I added parentheses around the area code match, and around the phone number match.

string text = "Please call us at (808) 867-5309 or (808) 555-1212 at your earliest convenience.";
string expression = @"\((\d{3})\)\s(\d{3}-\d{4})";
Match m = Regex.Match(text, expression);
while (m.Success)
{
    Console.WriteLine("Match: {0}  Area code: {1}  Phone #: {2}", m.Groups[0], m.Groups[1], m.Groups[2]);
    m = m.NextMatch();
}

This is good for simple tasks, but the MatchCollection is much better for more complex code.

string text = "Please call us at (808) 867-5309 or (808) 555-1212 at your earliest convenience.";
string expression = @"\(\d{3}\)\s\d{3}-\d{4}";
MatchCollection matches = Regex.Matches(text, expression);
Console.WriteLine("There are {0} phone numbers in the string.", matches.Count);
foreach (Match m in matches)
{
    Console.WriteLine(string.Format("Phone: {0}", m.Value));
}

If you wanted to perform Linq queries against a match collection, you first have to get it into a list that implements IEnumerable. I'm sure there are many ways to do this, but here is one way:

List<Match> mlist = matches.OfType<Match>().ToList();

Replacing text is so much better using the Regex.Replace instead of the standard String replace method. Here is a very simple replace, which will simple replace (808) with (800). It will replace all occurrences of (808). Notice that in the replace expression I don't need to escape the parentheses characters:

string text = "Please call us at (808) 867-5309 or (808) 555-1212 at your earliest convenience.";
string matchExpression = @"\(808\)";
string replaceExpression = @"(800)";
string newText = Regex.Replace(text, matchExpression, replaceExpression);
Console.WriteLine(newText);

Here is an example of reformatting the phone number to 800.555.1212 format. I'm using parentheses to group sections of the match. In the replace expression, you can refer to groups by their index using the "$" character. In the match expression, the $ is to match the end of the string, but in the replace expression, it refers to the matched sub-group.

string text = "Please call us at (808) 867-5309 or (808) 555-1212 at your earliest convenience.";
string matchExpression = @"\((\d{3})\)\s(\d{3})-(\d{4})";
string replaceExpression = @"$1.$2.$3";
string newText = Regex.Replace(text, matchExpression, replaceExpression);
Console.WriteLine(newText);

There are many things you cannot do, however, with the replace expression. An example is to change the case of the matched value. If you wanted to match html tags and make them all lowercase, for example, you would not be able to use the following match and replace expressions:

string text = "<P><H1>My Heading<h1><p>";
string matchExpression = @"((?:</?)\w+)(?=.*?>)";
string replaceExpression = "$1";
string newText = Regex.Replace(text, matchExpression, replaceExpression.ToLower());
Console.WriteLine(newText);

The ToLower() operates on the replace expression itself, not on the text it's replacing. In order to do this, and other complex replaces, you need to use a MatchEvaluator. This is really easy to do. Before I ever used it, I saw the word "delegate" and thought it would difficult to figure out, but it's so easy. You need to have a function that accepts a Match object as a parameter and returns a string to create a MatchEvaluator. Below is the code to replace the html tags with all lower case tags:

public string HtmlTagsToLower(string html)
{
    string matchExpression = @"((?:</?)\w+)(?=.*?>)";
    MatchEvaluator tolower = MatchEvaluatorToLower;
    return Regex.Replace(html, matchExpression, tolower);
}

private string MatchEvaluatorToLower(Match m)
{
    return m.Value.ToLower();
}

I hope this helps to understand how to use the Regex methods.