Friday, 25 November 2011

Integration between Word 2003 and SharePoint 2010 using Managed Meta-data

You cannot check out/edit/check in directly from Word 2003 if you are using managed meta-data in the list.  Word 2003 does not seem to integrate properly with managed meta-data columns, and will refuse to check in the document.

This leaves teh document in an unsaved, checked out state if a user attempts to edit it directly from Sharepoint.

My only solution to this has been to create a new content type and remove the managed meta-data from it. 

Carissima Gold 9 ct Yellow Gold Two-Row Diamond Cut Curb Bracelet of 21 cm 8.5-inch on
Carissima Gold 9 ct Yellow Gold Two-Row Diamond Cut Curb Bracelet of 21 cm/8.5-inch

Friday, 4 November 2011

C# function to get the Content Type GUID from a Sharepoint list using web services

Simple helper function to get the GUID of a list from Sharepoint web services.

Note that in the code below I have a class level object pointing to the Lists.asmx web service called m_listService already established , but I have left in the code to establish this connection in the comments as an example.

string GetListContentTypeGuid(string listName, string listID, string contentTypeName)
            string defaultGuid = "";
                //ListsService.Lists listService = new ListsService.Lists();
                //listService.Credentials = System.Net.CredentialCache.DefaultCredentials;

                //XmlNode ndList = listService.GetList(fileInfo.m_listInfo.m_listName);
                //XmlNode ndVersion = ndList.Attributes["Version"];

                XmlNode contentTypes = m_listService.GetListContentTypes(listID, "anything");

                // Loop through the fields
                foreach (XmlNode node in contentTypes)
                    if (node.Attributes["Name"].Value.ToString() == contentTypeName)
                        defaultGuid = node.Attributes["ID"].Value.ToString();
            catch (Exception ex)

                throw new Exception("ERROR: Reading content types from target site.\r\n"
                    + ex.Message + "\r\nDetails: " + ex.InnerException + "\r\n" +
                    "Check the settings file to ensure that the list settings match the target site.", ex);

            return defaultGuid;

Wednesday, 20 July 2011

Sharepoint 2010 managed meta-data taxonomy field data using web services

Sharepoint 2010 includes a very useful new concept: managed meta-data. This allows you to build taxonomic hierarchies, and provide document routing based on the meta-data.

Unfortunately, it seems that using this field type using a web service call is extremely complicated, and hardly seems to be documented at all (this is so typical of Sharepoint stuff...).  Microsoft have given us a new web service class ("taxonomyclientservice") but there hardly any documentation on it all (

Here is an example that I have managed to get working (after MUCH trial and error) that will hopefully help you to do the following:
  1. opens a list using the Lists web service, 
  2. iterates through the fields in the default content type for the list (you can remove this bit and just iterate through the fields in the list if you like),
  3. if it finds a "TaxonomyFieldType" field, it then gets the "Shared Service ID" (GUID of managed meta-data service) and Term Set ID (GUID of term set defined on this field) required to run the Taxonomy web service call
  4. sets up the CAML to run the Taxonomy web service call,
  5. retrieves the taxonomy data for that field in XML.  
 I do not try to analyse what the taxonomy XML means - that is for another day!

Note that all my web service proxies are called "ListService" and "Taxonomys".

//get list data from webservice
ListService.Lists listService = new ListService.Lists();
// Use your own list name
XmlNode list = listService.GetList("My List Name");

// Let's have a look at it

// Get the content types for this list
//Get our defined content type (see previous post on how to do this - you need your own values here!)
string contentTypeGuid = GetListContentTypeGuid("My List Name", "{GUID}", "My Content Type");

// Now get content type definition 
XmlNode contentType = m_listService.GetListContentType(mylistinfo.m_listName, contentTypeGuid);

//Get the fields from the content type
XmlNodeList nodes = RunXPathQuery(contentType, "//sp:Field");

// Loop through the fields in the content type
foreach (XmlNode node in nodes)
 // This stuff just sets up some variables based on the content type and skips over hidden fields
 string Name = node.Attributes["Name"].Value;
 if (Name == "ContentType") continue;
 string ID = node.Attributes["ID"].Value;

 if (string.IsNullOrEmpty(ID) || string.IsNullOrEmpty(Name))

 bool hidden = false;
 bool ReadOnly = false;
 try { hidden = Convert.ToBoolean(node.Attributes["Hidden"].Value); }
 catch { }
 try { ReadOnly = Convert.ToBoolean(node.Attributes["ReadOnly"].Value); }
 catch { }

 if (hidden || ReadOnly)

 string ShowInFileDlg = "";
 try { ShowInFileDlg = node.Attributes["ShowInFileDlg"].Value; }
 catch { }
 if (ShowInFileDlg == "FALSE") continue;

 string StaticName = node.Attributes["StaticName"].Value;
 string DisplayName = node.Attributes["DisplayName"].Value;

 // Now we can check the "Type" attribute
 string FieldType = node.Attributes["Type"].Value;

 if (FieldType == "TaxonomyFieldType")

     // Lets get the shared service ID and the termset ID from teh List schema
     string sharedServiceIds = "";
     string termSetId = ""; 
     // jump a few nodes to get the correct bit of the schema (included for clarity)
     XmlNode childNode =  node.ChildNodes[1];
     XmlNode termNodes = childNode.ChildNodes[0];

     //Loop through these nodes until we find the information we need
     foreach (XmlNode term in termNodes.ChildNodes)
         System.Diagnostics.Debug.Print("term = " + term.ChildNodes[0].InnerText.ToString());
         if (term.ChildNodes.Count > 1)
             System.Diagnostics.Debug.Print("value = " + term.ChildNodes[1].InnerText.ToString());
             if (term.ChildNodes[0].InnerText.ToString() == "SspId")
                 // Get shared services ID from list
                 sharedServiceIds = term.ChildNodes[1].InnerText.ToString();
             if (term.ChildNodes[0].InnerText.ToString() == "TermSetId")
                 // Get Term Set ID from list
                 termSetId = term.ChildNodes[1].InnerText.ToString();
     int lcid = System.Globalization.CultureInfo.CurrentUICulture.LCID;
     string serverTermSetTimeStampXml = "";

     string termStoreIds = "<termStoreIds><termStoreId>" + sharedServiceIds + "</termStoreId></termStoreIds>";
     string termSetIds = "<termSetIds><termSetId>" + termSetId + "</termSetId></termSetIds>";
     //Always set timestamp to this
     string oldtimestamp = "<timeStamps><timeStamp>633992461437070000</timeStamp></timeStamps>";
     //Always set version to 1
     string clientVersion = "<versions><version>1</version></versions>";
     string timeStamp = "";

     // Taxonomy web service!!
     Taxonomys.Taxonomywebservice taxonomy = new Taxonomys.Taxonomywebservice();
     taxonomy.Credentials = System.Net.CredentialCache.DefaultCredentials;
     string resultXML = taxonomy.GetTermSets(termStoreIds, termSetIds, lcid, oldtimestamp, clientVersion, out timeStamp);

     //Loop through the XML
     string termValue = "MHA";
     string termGUID = "";
     string parentID = "";
     XmlDocument termSetXML = new XmlDocument();
     XmlNodeList terms = termSetXML.GetElementsByTagName("T");
     foreach (XmlNode term in terms)
         string termName = term.FirstChild.FirstChild.Attributes["a32"].Value;
         termGUID = term.Attributes["a9"].Value;
             parentID = term.Attributes["a25"].Value;
         catch (Exception)
             parentID = "";
         System.Diagnostics.Debug.Print("termName = " + termName + ", termGUID = " + termGUID + ", parentID = " + parentID);   

If you would like to know how to analyse the attribute codes returned in the XML, then take a look at this blog entry:

Thursday, 2 June 2011

Mark ContentType as default in Sharepoint Designer 2010

This is pretty simple stuff, but I just spent ten minutes looking at Sharepoint Designer trying to figure out where I clicked to change the default content type on a list!  I guess I'm just not used to the ribbon :)

Google didn't help (probably too easy a problem for it) so I thought I would right this up in case anyone else was suffering.

I've created a content type, here it is associated with a list in Sharepoint Designer:
But how do I make it default?  I finally spotted the option after clicking through endless options in SP designer - the trick is to highlight the content type (and not click on its name to see the content type editor!) and the look at the ribbon:

Hurray!  There it is.
Click on this and we have a new default:
I hope this helps some other Sharepoint 2010 newbie.

Thursday, 10 March 2011

VBA code to check in a document to Sharepoint and set meta data

Carissima Gold 9 ct Yellow Gold Two-Row Diamond Cut Curb Bracelet of 21 cm 8.5-inch on
Carissima Gold 9 ct Yellow Gold Two-Row Diamond Cut Curb Bracelet of 21 cm/8.5-inch

The following VBA code will upload a file to Sharepoint 2007 using a web service, and then sest some meta data (the title) for that file in the document library.
Remember, you need to have installed the VBA Microsoft Office 2003 Web Services Toolkit and created a reference to the web service in your project before this will work. See these previous posts for more information on calling Sharepoint web services from VBA:

' Change these values to your own
sSourceFile = "C:\mtest.doc"  ' File to upload
sTargetFile = "HTTP://my sharepoint site/Shared%20Documents/mytest.doc" ' Target site, document library and file name

' First set up DOM document containing fields
Dim xmlDoc As New MSXML2.DOMDocument30
xmlDoc.async = False

xmlText = "<root>" & _
"<Batch OnError='Continue' ListVersion='" & iVersionNumber & "' PreCalc='TRUE' xmlns=''>" & _
"<Method ID='1' Cmd='Update'>" & _
"<Field Name='ID' />" & _
"<Field Name='FileRef'>" & sTargetFile & "</Field>" & _
"<Field Name='Title'>Uploaded from VBA</Field>" & _
"</Method>" & _
"</Batch>" & _

xmlDoc.LoadXml (xmlText)
Debug.Print xmlText

' This bit is just for testing
If xmlDoc.parseError.errorCode <> 0 Then

Set myErr = xmlDoc.parseError
MsgBox (myErr.reason)
GoTo fnUpload_Error
MsgBox xmlDoc.XML
End If

' Set up IXMLDOMNodeList
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement

Set root = xmlDoc.documentElement
Set myXMLNodeList = root.ChildNodes

' Create an array of IXMLDOMNodeList
Dim ar_Fields(1) As IXMLDOMNodeList
Set ar_Fields(0) = myXMLNodeList
'Debug.Print "ar_Fields(0) = " & ar_Fields(0)

' Now set up an array of strings to hold the URL
Dim ar_URL(1) As String
ar_URL(0) = sTargetFile

Debug.Print "ar_URL(0) = " & ar_URL(0)
' Set up the results object
Dim myresults() As struct_CopyResult

' Set up the byte array and read the source file into it
Dim ar_Stream() As Byte
ar_Stream = ReadFile(sSourceFile)

' The follwoing comes from the "Microsoft Office 2003 Web Services Toolkit":
'"ar_DestinationUrls" is an array with elements defined as String
'"ar_Fields" is an array with elements defined as IXMLDOMNodeList
'"ar_Stream" is an array with elements defined as Byte
'"ar_Results" is an array with elements defined as struct_CopyResult
'See Complex Types: Arrays in Microsoft Office 2003 Web Services Toolkit Help
'for details on implementing arrays.
documentId = copyws.wsm_CopyIntoItems(sDocumentPath, ar_URL, ar_Fields, ar_Stream, myresults)
Debug.Print "DocumentID = " & documentId

Dim updateReturn As IXMLDOMNodeList

Set updateReturn = listws.wsm_UpdateListItems(sListID, myXMLNodeList)

Dim xmlReturnDoc As New MSXML2.DOMDocument30
If (updateReturn.Length > 0) Then    
xmlReturnDoc.LoadXml (updateReturn.Item(0).XML)

Dim errorText As String
errorText = xmlReturnDoc.Text
If (errorText <> "0x00000000") Then
MsgBox ("Error: Cannot upload load file to Sharepoint." & vbCrLf & _
"     : " & errorText & vbCrLf & Err.Description & vbCrLf )

End If
End If

' Uncomment for debug information.
'MsgBox ("Return XML = " & xmlReturnDoc.XML)

I use the following VBA to read the target file in as a byte array:

Private Function ReadFile(ByVal strFileName As String, Optional ByVal lngStartPos As Long = 1, Optional ByVal lngFileSize As Long = -1) As Byte()
    Dim FilNum As Integer
    FilNum = FreeFile
    Open strFileName For Binary As #FilNum
    If lngFileSize = -1 Then
        ReDim ReadFile(LOF(FilNum) - lngStartPos)  
        ReDim ReadFile(lngFileSize - 1)
    End If
    Get #FilNum, lngStartPos, ReadFile
    Close #FilNum
End Function

Sunday, 27 February 2011

Deploy Office 2003 VSTO add in to All Users using Visual Studio 2008

Deploying VSTO add-ins can be a real pain, at least if you are not using 2010.  Having just successfully deployed my first add-in, I thought it would be useful to explain how I did and the resources I used.

Firstly, it is important that you follow the instructions for the appropriate version of Office and Visual Studio that you are using.  My example is for a legacy solution using Office 2003, created with VSTO Second Edition (Visual Studio 2008).  The general  principles are the same for other editions, but the specifics may be subtly different.

To get started, look at the walkthrough here:
This is another useful collection of resources:

This explains how to configure a deployment project in Visual Studio 2008 and set up the additional security package required to grant the add-in full trust.  I would recommend you follow the steps in here to create a brand new simple package and get that working first.  I created a simple Word 2003 add-in package to test, rather than the suggested Outlook.

The security package can be downloaded from MSDN here:
Unpackage the download and you should find a .SLN file that has everything pre-configured.  Copy the "SetSecurity" package from here and add it to your solution as explained in the walkthrough.  Configure the Setup project's outputs as described, and you should end up with a package that looks something like this:

Once you have built your setup package, you can find the .MSI and .EXE files in solution bin/debug (or bin/release) directory.   Run the .exe and you should have installed your add-in onto your development machine.  Run the appropriate Office application to check that the pop up message appears to confirm this.  Your development computer should have all the pre-requisites installed, so if this doesn't work you have probably missed something in the walkthrough, or have perhaps used an incorrect version of the setup project, so go back and double check everything.   It is vital that you get a simple version working at this stage, or it will cause you all sorts of problems down the line.

Once you have a simple install project working you can go about making it available to ALL USERS on the machine.  This is not  covered in the walkthrough,  although there is mention of it for newer versions of Office in this excellent blog here:

In Office 2003, deploying to all users is actually really simple:  all we have to do is copy the registry keys that have automatically been generated for us by Visual Studio in the Setup project from HKCU to HKLM.  In practice, this mean highlighting your setup project (called Word2003AddInSetup in my example), right click and choose "View" and "Registry":
This will open up the registry editor.   Now, expand all, and renambe the "Software" key in HKLM to some temporary name (say "SoftwareTESTTESTTEST").

You can now drag the "Software" key from the HKCU leaf to the HKLM leaf.  Once done, drag your renamed "SoftwareTESTTESTTEST" key from HKLM to HKCU, and rename this back to "Software".  You should now have something that looks like this:
One thing that is not really very well documented at all, and kept me scratching my head for many days, is that you must set the "InstallAllUsers" property on the setup package to "True".  This is obvious, but not mentioned anywhere! 

That's it!  Build your setup solution and re-install it (you will have to un-install your original version first).  You should now find that any user that logs into the machine will have the add in enabled.

Please note that if you are using Office 2007, this technique does not work - you will have to refer to the blog post linked above to learn how to set up the appropriate registry keys.

Wednesday, 26 January 2011

Sharepoint 2007 Batch Update example using web services and CAML

Here is an example of running a batch update process on Sharepoint 2007 using web services. It was written in C# in Visual Studio 2008.

In this example, I take the value of a text field "Date_x0020_of_x0020_Birth", turn this into a DateTime value, and then use this to update the the value of a DateTime field "Date_x0020_of_x0020_Birth0". Obviously this section of the code will not be particularly generalisable, however I have left in all the code in order to show you the principles involved, and hopefully you will find it useful in building your own batch routines.

In order to get it running, you must create a reference to the Sharepoint Lists web service and call it "ListsService". The SDK shows you how to do this, and is available here:

The GetListItems() method of the Lists web service is used to query the data, and the UpdateListItems() method is used to update items.

I used an XPathNavigator object to filter only rows that contain data. You could just loop through the nodes if you prefer.

CAML is used to query the Sharepoint list and look for items where the "Date_x0020_of_x0020_Birth" is null. Tailor this to your own needs.
For CAML reference, look at the MSDN article here:

The CAML update command uses owshiddenversion, and so is useful in situations where versioning is enabled. Lists without versioning do not need this field.

If the UpdateListItems command returns an error, in my experience this is usually caused by using an incorrect internal name for a field.  You may need to add the URI prefix "urn:schemas-microsoft-com:office:office#" to the field name (e.g. change line 42 to something like:  "<Field Name=\"urn:schemas-microsoft-com:office:office#{4}\">{5}</Field>").

Full code:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Net;

namespace BatchUpdateDOB
    class Program
        static string LogFile = "D:\\BatchUpdateLog.txt";  // Log file location

        static void Main(string[] args)
                StatusMessage("Writing log file to: " + LogFile);
                // Set up the variables to be used.
                StringBuilder methodBuilder = new StringBuilder();
                string batch = string.Empty;
                DateTime currentDate = DateTime.Now;
                string formattedDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentDate);
                string fieldToReference = "Date_x0020_of_x0020_Birth";
                string fieldToUpdate = "Date_x0020_of_x0020_Birth0";

                string batchFormat = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                  "<ows:Batch OnError=\"Return\">{0}</ows:Batch>";

                // CAML update command for web service
                string methodFormat = "<Method ID=\"1\" Cmd=\"Update\">" +
                 "<Field Name=\"ID\">{1}</Field>" +
                 "<Field Name=\"FileRef\">{2}</Field>" +
                 "<Field Name=\"owshiddenversion\">{3}</Field>" +
                 "<Field Name=\"{4}\">{5}</Field>" +

                string siteName = "http://<your site root uri here>";
                string listName = "<your list name here>";

                //string viewName = "";  // No need for viewname
                int rowLimit = 10;       //The number of items returned per batch (not used)

                StatusMessage("Processing " + siteName + ", List: " + listName + "...");

                //Web service setup
                //ListService should be the name of your web service reference
                ListService.Lists listService = new ListService.Lists();
                listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
                listService.Url = siteName + "/_vti_bin/Lists.asmx";

                //Build CAML query to search for records
                XmlDocument camlDocument = new XmlDocument();
                XmlNode queryNode = camlDocument.CreateElement("Query");
                //queryNode.InnerXml =  "<Where><IsNull><FieldRef Name='Date_x0020_of_x0020_Birth'/></IsNull></Where>";
                //queryNode.InnerXml = "<OrderBy><FieldRef Name='Country' /></OrderBy>";

                XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");
                viewFieldsNode.InnerXml = "<FieldRef Name='Title' />" +
                  "<FieldRef Name='owshiddenversion' />" +
                  "<FieldRef Name='" + fieldToReference + "' />" +
                  "<FieldRef Name='" + fieldToUpdate + "' />" +
                  "<FieldRef Name='FileRef' />";

                XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
                queryOptionsNode.InnerXml = "<ViewAttributes Scope=\"Recursive\" /><IncludeMandatoryColumns>True</IncludeMandatoryColumns>";

                // Get the GUID of the list
                XmlNode ndListView = listService.GetListAndView(listName, "");
                string listGuid = ndListView.ChildNodes[0].Attributes["Name"].Value;
                string listVersion = ndListView.ChildNodes[0].Attributes["Version"].Value;

                // Run the CAML query
                XmlNode resultNode = listService.GetListItems(listGuid, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);
                // If you want to put a rowlimit on, then use this command:
                //XmlNode resultNode = listService.GetListItems(listGUID, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);

                long totalRecords = 0;
                //string itemDOB;
                string dob;
                DateTime dtDOB;
                string owsHiddenVersion;  
                string fullFileRef;
                string owsFileRef;

                // Create xpath navigator   
                XPathNavigator navigator = resultNode.CreateNavigator();
                // Set up namespace manager for XPath   
                XmlNamespaceManager ns = new XmlNamespaceManager(navigator.NameTable);
                ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
                ns.AddNamespace("z", "#RowSetSchema");

                // Select only the z:row nodes
                XmlNodeList nodes = resultNode.SelectNodes("//*[name()='z:row'] ", ns);

                // Iterate through the returned nodes
                foreach (XmlNode node in nodes)
                    string itemID = node.Attributes["ows_ID"].InnerText;
                        dob = node.Attributes["ows_" + fieldToReference].InnerText;
                        owsHiddenVersion = node.Attributes["ows_" + "owshiddenversion"].InnerText;
                        owsFileRef = node.Attributes["ows_" + "FileRef"].InnerText;
                        // For some reason my data is returned in the format: 2#/listname/path, 
                        // where 2 seems to be an id or version number, and listname/path points to the file in relation to the root site.
                        // The following strips that out and gets the full path (required for the CAML update)
                        fullFileRef = siteName + "/" + owsFileRef.Remove(0, owsFileRef.IndexOf("#") + 1);
                    catch(Exception ex)
                        // Ignore this row
                        //StatusMessage("ERROR: Cannot process: " + node.InnerXml.ToString() + ": " + ex.Message); // If you want to see why, uncomment

                    if (String.IsNullOrEmpty(dob)) continue;  // Ignore list items with no value

                    dtDOB = Convert.ToDateTime("01/01/1900"); // Set default value for dates before 1900

                    // Convert date text to DateTime
                        dtDOB = Convert.ToDateTime(dob);
                    catch (Exception ex)
                        StatusMessage("Cannot process: " + siteName + "/" + fullFileRef + " DOB: " + dob + ".  Setting to 01/01/1900. " + ex.Message);

                    MessageToFile("Processing: " + fullFileRef + ", DOB = " + dob);

                    methodBuilder.AppendFormat(methodFormat, listGuid, itemID, fullFileRef, owsHiddenVersion, fieldToUpdate, SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDOB));

                    // Put the pieces together.
                    batch = string.Format(batchFormat, methodBuilder.ToString());

                    //Build CAML command to update using Web service
                    XmlDocument xmlDoc = new XmlDocument();
                    XmlElement elBatch = xmlDoc.CreateElement("Batch");
                    elBatch.SetAttribute("OnError", "Continue");
                    elBatch.SetAttribute("ListVersion", listVersion.ToString());
                    elBatch.SetAttribute("ViewName", "");
                    elBatch.InnerXml = methodBuilder.ToString();

                    // This line runs the CAML update command
                    XmlNode updatesResponse = listService.UpdateListItems(listGuid, elBatch);
                    if (updatesResponse.FirstChild.FirstChild.InnerText != "0x00000000") throw new Exception("Could not update properties.\n\n" + updatesResponse.InnerText + "\n" + updatesResponse.FirstChild.FirstChild.InnerText);
                    StatusMessage(fullFileRef + " processed...");

                StatusMessage("Completed - " + totalRecords + " processed.");
            catch (Exception exp)
                StatusMessage("ERROR: " + exp.Message);

        /// <summary>
        /// Display a  message
        /// </summary>
        /// <param name="myMessage"></param>
        static void StatusMessage(string myMessage)

        /// <summary>
        /// Write message to the log file
        /// </summary>
        /// <param name="myMessage"></param>
        static void MessageToFile(string myMessage)
            using (StreamWriter sw = new StreamWriter(LogFile, true))
                DateTime now = DateTime.Now;
                sw.WriteLine(now.ToString() + ": " + myMessage.ToString());

Tuesday, 11 January 2011

Binding a CheckBoxList object to an ordered Sharepoint list of reference values in a web part (c#)

It's common when creating database applications to use reference tables to provide lookup values.  These tables ofter contain a flag that is used to mark a record as no longer available, and an order column that provides a method of putting the values in an order of importantance, perhaps when using them in a drop down list or list of checkboxes.  Sharepoint is NOT a database, however there is no reason why good practice such as this cannot be applied to Sharepoint's list model.

In the following example I will show you how to set up a reference list and then use the Sharepoint object model to programmatically access this list and bind it to an control (in this case a Sharepoint CheckBoxList control).  Once you understand this, you can use the same method to connect to any reference list and bind it to other objects and controls, such as drop down lists. This example has been developed for Sharepoint 2007, but should also work for Sharepoint 2010.

Step 1:  Set up a reference list
Create a new list on your Sharepoint site with teh following structure: 
  • ID - integer (this is a default field) - this will be used as the unique key (lookup value) used to link this reference filed to other lists
  • Title - string (this is a default field) - this is used to hold the description of the reference field, and will be the value shown in lookups
  • SortOrder - integer, default 1000 - this can be used to sort the list values in lookups and programmatically using CAML
  • Enabled - Yes/No field, default Yes - setting to NO will enable you to filter out these values in views and in programmatic lookups in CAML
You can now enter you reference values into the list, for example here is a list of types of medical record:

Step 2:  Add method to webpart that use CAML to read and order the list
Now for the code that reads the Sharepoint list in a structured manner so we can use it in web parts.  First, add the following method to your web part:
/// <summary>
/// Returns a list collection, ordered using a field in the list called "SortOrder"
/// </summary>
/// <param name="listName">The literal string name of the list to query</param>
/// <returns>SPListItemCollection</returns>
private SPListItemCollection ReadOrderedList(string listName)
        using (SPWeb configWeb = SPContext.Current.Site.OpenWeb())
            //Get the list
            SPList lookupList = configWeb.Lists[listName];
            // Use CAML to order the list
            SPQuery query = new SPQuery();
            // This CAML where clause searches for only items where the Enabled field is set to "Yes"
            query.Query = "<Where><Eq><FieldRef Name=\"Enabled\" /><Value Type=\"Integer\">1</Value></Eq></Where> ";
            // This CAML order by claus uses an integer field of "SortOrder"
            query.Query += "<OrderBy><FieldRef Name=\"SortOrder\" /></OrderBy>";
            SPListItemCollection lookupItems = lookupList.GetItems(query);
            return lookupItems;
    catch (Exception ex)
        ErrorTrap("ReadOrderedList: " + ex.Message + Environment.NewLine + ex.StackTrace.ToString());

This method will return the content of the list in a SPListItemCollection object. CAML is use to order the list by the "SortOrder" column, and to ignore values that do not have the boolean field "Enabled" set to true.

Step 3:  Bind the list to a control
At the class level of your webpart, create the control that you wish to bind to (in this case a CheckBoxList):

CheckBoxList myCheckList = new CheckBoxList();

To bind this control to your Sharpoint list, put this code in the CreateChildControls() method. Simply replace "My List" with the real name of your list:
// Set up the Record Section checkboxes list
    SPListItemCollection dt = ReadOrderedList("My List");
    myCheckList.DataSource = dt;
    myCheckList.DataTextField = "Title";
    myCheckList.DataValueField = "ID";

That's it! You now have a Sharepoint object bound to your Sharepoint list of reference values.  When rendered, the output will look something like this:

Yyou can use the following method to render the CheckBoxList to the web part.  This example puts the CheckBoxList in a DIV that fixes the maximum size of the list with a scrollbar.
/// <summary>
/// render a list of checkboxes  to the page
/// </summary>
void RenderCheckBoxes()
        // Set up list of checkboxes
        myCheckList.EnableViewState = true;
        myCheckList.CellSpacing = 0;
        myCheckList.CellPadding = 0;

        for (int i = 0; i < myCheckList.Items.Count; i++)
            ListItem li = new ListItem(myCheckList.Items[i].Text, myCheckList.Items[i].Value);
            // Set all boxes to selected if not a postback
            if (!Page.IsPostBack)
                myCheckList.Items[i].Selected = true;

        // Give the checklist an ID (can use this in javascript etc)
        myCheckList.ID = "RecordSections";

        //Add controls to web part
        Controls.Add(new LiteralControl(@"<TABLE border='0' bordercolor='red' cellpadding='0' cellspacing='0' valign='top'>"));
        Controls.Add(new LiteralControl("<TR>"));
        Controls.Add(new LiteralControl("<TD>"));
        Controls.Add(new LiteralControl(@"<div class=""ms-frombody""><b>Record Sections</b></div>"));
        Controls.Add(new LiteralControl("</TD>"));
        Controls.Add(new LiteralControl("</TR>"));
        Controls.Add(new LiteralControl("<TR>"));

        Controls.Add(new LiteralControl("<TD>\r\n"));
        Controls.Add(new LiteralControl(@"<input type=""hidden"" id=""ScrollPos"" runat=""server""/>"));

        Controls.Add(new LiteralControl(@"<div id='RecordSectionScroll' style=""OVERFLOW: auto; WIDTH: 247px;  TOP: 0px; HEIGHT: 300px"" onscroll=""javascript:document.getElementById('ScrollPos').value = this.scrollTop""> "));

        Controls.Add(new LiteralControl("</div></TD>"));
        Controls.Add(new LiteralControl("</TR>"));     
        Controls.Add(new LiteralControl("</TABLE>"));
    catch (Exception ex)
        ErrorTrap("RenderCheckBoxes" + ex.Message + Environment.NewLine + ex.StackTrace.ToString());

Note that I am using literal controls to render the check boxes in a HTML table. This is pretty horrible and I don't recommend it, but it does work.

Here is the ErrorTrap method. Note that this is just a simple error handling function and is not required to render a checklist bound to a Sharepoint list, but since it appears in the examples I though I would show you what I do.
/// <summary>
/// Give an error message
/// </summary>
/// <param name="myMessage"></param>
void ErrorTrap(string myMessage)
    Controls.Add(new LiteralControl("<strong>ERROR: " + myMessage.ToString() + "<BR/></strong>"));

/// <summary>
/// Write errors to the log file
/// </summary>
/// <param name="myMessage"></param>
void ErrorToFile(string myMessage)
    using (StreamWriter sw = new StreamWriter("D:\\SearchErrorLog.txt"))
        DateTime now = new DateTime();
        sw.Write(now.ToString() + ": " + myMessage.ToString());