WSS List and Database Synchronization

• Download source code - 11.4 KB

Introduction

Windows SharePoint Services (WSS) and Microsoft Office SharePoint Server (MOSS) are great tools for providing useful information and analysis of that information. One of the basic components for making this information visible to end users is the List. These lists can then be used to form different views of the data, filtered by specified criteria to focus on essential information. MOSS can also use these lists as the basis for creating Key Performance Indicators (KPI).

What’s wrong with SharePoint?

The biggest problem with using SharePoint to display information and data is that SharePoint can only display information in a list if it knows about it, such as a listing of documents in a Document Library. Most businesses, however, don't use SharePoint to store all of their data; they have external systems, or Line Of Business (LOB) systems, that may have been in use prior to SharePoint, or use others systems with more focused objectives, such as an order processing system or a warehouse management system.

Getting external data into SharePoint

This article will briefly discuss various methods for exposing external data to SharePoint so it can be used in SharePoint Lists, but the main focus will be on creating a reusable and extensible method that uses the best of all the methods.

Since MOSS is an extension to WSS, this article will use the term SharePoint to represent both. Where features are specific to MOSS, it will be noted.

Business Data Catalog

The Business Data Catalog (BDC) is a feature of MOSS that uses an XML file to define and expose external data sources.

There are many articles that cover the details of BDC, so we will just take a brief look at it for familiarization.

Although the BDC definition file can be created by hand, it is a laborious task as seen below, so it is better to use one of the many tools available. Microsoft provides the Business Data Catalog Definition Editor in the SharePoint Server 2007 SDK.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
- <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/
                    BusinessDataCatalog BDCMetadata.xsd" 
Type="Database" Version="1.0.0.0" Name="Northwind" 
xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
- <LobSystemInstances>
- <LobSystemInstance Name="Northwind_Instance">
- <Properties>
  <Property Name="rdbconnection Data Source" Type="System.String">dbserver</Property> 
  <Property Name="rdbconnection Initial Catalog" Type="System.String">Northwind</Property> 
  <Property Name="rdbconnection User ID" Type="System.String">sa</Property> 
  <Property Name="DatabaseAccessProvider" 
    Type="Microsoft.Office.Server.ApplicationRegistry.
          SystemSpecific.Db.DbAccessProvider">SqlServer</Property> 
  <Property Name="AuthenticationMode" 
    Type="Microsoft.Office.Server.ApplicationRegistry.
          SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property> 
  </Properties>
  </LobSystemInstance>
  </LobSystemInstances>
- <Entities>
- <Entity EstimatedInstanceCount="10000" Name="Employees">
- <Identifiers>
  <Identifier TypeName="System.Int32" Name="EmployeeID" /> 
  </Identifiers>
- <Methods>
- <Method Name="Find_Employees">
+ <Properties>
  <Property Name="RdbCommandType" 
    Type="System.Data.CommandType, System.Data, Version=2.0.0.0, 
          Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property> 
  <Property Name="RdbCommandText" 
    Type="System.String">Select "EmployeeID","LastName",
         "FirstName","Title", "TitleOfCourtesy","BirthDate", 
         "HireDate","Address","City","Region", 
         "PostalCode", "Country","HomePhone","Extension", 
         "Photo","Notes","ReportsTo","PhotoPath" 
         from Employees where EmployeeID=@EmployeeID</Property> 
  </Properties>
+ <Parameters>
- <Parameter Direction="In" Name="@EmployeeID">
  <TypeDescriptor 
    TypeName="System.Int32, mscorlib, Version=2.0.0.0, 
              Culture=neutral, PublicKeyToken=b77a5c561934e089" 
    IdentifierName="EmployeeID" Name="EmployeeID" /> 
  </Parameter>
- <Parameter Direction="Return" Name="@Employees">
- <TypeDescriptor 
    TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, 
              Culture=neutral, PublicKeyToken=b77a5c561934e089" 
IsCollection="true" Name="Reader">
- <TypeDescriptors>
- <TypeDescriptor 
    TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, 
              Culture=neutral, PublicKeyToken=b77a5c561934e089" 
Name="Record">
- <TypeDescriptors>
  <TypeDescriptor 
    TypeName="System.Int32, mscorlib, Version=2.0.0.0, 
              Culture=neutral, PublicKeyToken=b77a5c561934e089" 
    IdentifierName="EmployeeID" Name="EmployeeID" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="LastName" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="FirstName" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, 
              Culture=neutral, PublicKeyToken=b77a5c561934e089" 
    Name="Title" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="TitleOfCourtesy" /> 
  <TypeDescriptor 
    TypeName="System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="BirthDate" /> 
  <TypeDescriptor 
    TypeName="System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="HireDate" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="Address" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="City" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="Region" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="PostalCode" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="Country" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="HomePhone" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="Extension" /> 
  <TypeDescriptor 
    TypeName="System.Byte[], mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="Photo" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="Notes" /> 
  <TypeDescriptor 
    TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="ReportsTo" /> 
  <TypeDescriptor 
    TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    Name="PhotoPath" /> 
  </TypeDescriptors>
  </TypeDescriptor>
  </TypeDescriptors>
  </TypeDescriptor>
  </Parameter>
  </Parameters>
+ <MethodInstances>
  <MethodInstance 
    Type="SpecificFinder" 
    ReturnParameterName="@Employees" 
    ReturnTypeDescriptorName="Reader" 
    ReturnTypeDescriptorLevel="0" 
    Name="Find_Employees_Instance" /> 
  </MethodInstances>
  </Method>
+ <Method Name="FindAll_Employees">
- <Properties>
  <Property Name="RdbCommandType"
    Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, 
          PublicKeyToken=b77a5c561934e089">Text</Property> 
  <Property Name="RdbCommandText" 
    Type="System.String">Select "EmployeeID" from Employees</Property> 
  </Properties>
- <Parameters>
- <Parameter Direction="Return" Name="@Employees">
- <TypeDescriptor 
   TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, 
             PublicKeyToken=b77a5c561934e089" 
   IsCollection="true" Name="Reader">
- <TypeDescriptors>
- <TypeDescriptor 
     TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, 
               PublicKeyToken=b77a5c561934e089" 
     Name="Record">
- <TypeDescriptors>
  <TypeDescriptor 
    TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, 
              PublicKeyToken=b77a5c561934e089" 
    IdentifierName="EmployeeID" 
    Name="EmployeeID" /> 
  </TypeDescriptors>
  </TypeDescriptor>
  </TypeDescriptors>
  </TypeDescriptor>
  </Parameter>
  </Parameters>
- <MethodInstances>
  <MethodInstance 
    Type="IdEnumerator" 
    ReturnParameterName="@Employees" 
    ReturnTypeDescriptorName="Reader" 
    ReturnTypeDescriptorLevel="0" 
    Name="FindAll_Employees_Instance" /> 
  </MethodInstances>
  </Method>
  </Methods>
  </Entity>
  </Entities>
  </LobSystem>

This example is for the Northwind database Employees table, and we can see that it defines the necessary access methods and connection string information to access and expose the data. We’ll leave it here since, again, there are many other articles that cover the depths and nuances of BDC.

Exposing BDC to SharePoint

The BDC XML file is imported to MOSS through the Central Administration website which then makes it available for use in any site. That is one of the drawbacks of using the BDC; there is no way to limit the exposure to certain sites, although permissions can be applied.

Once the BDC has been imported, items can be added to individual lists through specifying custom columns.

There are other Web Parts available in MOSS, such as the Business Data List, that can also be used.

Custom Web Part

One way to overcome the limitations of BDC, and not be reliant on MOSS, is to create a custom web part that accesses the necessary data and displays it in a list type manner. The SPGridView control is perfect for this function; however, it too has limitations. The SPGridView is derived from the ASP.NET GridView control, so it provides familiar functionality for ASP.NET developers.

Two problems are revealed with this method though. One, everything must be done manually; all sorting, filtering, and displaying must be coded. Two, it isn’t a SharePoint list, and can’t be used to create different views or have the columns used in other lists.

Custom list method

Obviously, a method that could make use of the best features of the above methods and use the built-in SharePoint list functionality would be advantageous.

Creating SharePoint lists programmatically

As can be seen from the code below, creating a SharePoint list is very easy.

private void CreateList()
{
    using(SPSite site = new SPSite("http://sharepoint.com/MySite"))
    {
        using(SPWeb web = site.OpenWeb())
        {
            // Use the Custom List template
            SPListTemplate template = web.ListTemplates["Custom List"];
            Guid listGuid = web.Lists.Add("ListName", "Description", template);
            SPList list = web.Lists[listGuid];
            list.Fields.Add("Column1", SPFieldType.Text, true);
            // Add other fields to list
            list.Update();
            web.Update();
        }
    }
}

After the list has been created, a view can be added for the list.

private void CreateView()
{
    using(SPSite site = new SPSite("http://sharepoint.com/MySite"))
    {
        using(SPWeb web = site.OpenWeb())
        {
            SPList list = web.Lists["List Name"];

            SPView view = list.Views["All Items"];
            view.ViewFields.Add("View Column1");
            // Add other view fields
            view.Update();                    
        }
    }
}

Adding data to the list is also very straightforward:

private void AddDataToList()
{
    DataTable dt = new DataTable();
    using(SqlConnection conn = new SqlConnection("Connection String"))
    {
        using(SqlCommand cmd = new SqlCommand("cmd", conn))
        {
            conn.Open();
            dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
        }
    }

    using(SPSite site = new SPSite("http://sharepoint.com/MySite"))
    {
        using(SPWeb web = site.OpenWeb())
        {
            SPList list = web.Lists["List Name"];

            foreach(DataRow row in dt.Rows)
            {
                SPListItem item = list.Items.Add();
                item["Column1"] = row["Column1"];
                // Repeat for additional columns
                item.Update();
            }
        }
    }
}

The above code is meant to run on a system that has SharePoint installed, since it uses classes from the SharePoint API. To use these techniques outside of a SharePoint environment, you would need to use the SharePoint Web Services.

WssDatabaseSync

Although the above method can be used effectively, it’s not very flexible; the columns, site, and list are hard coded, and will, of course, only work for the specified list. The site and list can be abstracted out, but you’re still left with the columns to deal with.

The WssDatabaseSync project uses a definition file, similar to how the BDC functions, to define information necessary to create a SharePoint List and add data to it from a database like the custom list method. However, since the methods are abstracted any database source can be exposed to any list simply and flexibly by editing the definition file.

Definition file

<?xml version="1.0" encoding="utf-8" ?>
<syncProjects>
<syncProject name="SyncProject1">
<syncSource>
<connectionString>Connection string</connectionString>
<source isStoredProc="false">View or Stored proc name</source>
</syncSource>
<syncDestination append="true" 
   sourceKeyField="EmployeeID" destinationKeyField="EmployeeID">
<site>http://sharepoint.com/MySite</site>
<list>Employees</list>
</syncDestination>
<columns>
<column source="EmployeeID" destination="ID" dataType="Integer"/>
<column source="FirstName" destination="First Name" dataType="Text"/>
<column source="LastName" destination="Last Name" dataType="Text"/>
<column source="Address" destination="Address" dataType="Text"/>
<column source="City" destination="City" dataType="Text"/>
<column source="Today" destination="Date" 
  dataType="DateTime" timeZone="Central Standard Time"/>
</columns>
</syncProject>
</syncProjects>

The syncProject element encapsulates the settings and definition for each project, with the name attribute only being used for readability.

The syncSource element defines the source for the data the list will be populated with. To reduce the exposure to malicious SQL statements, i.e., SQL Injection, this project only uses Stored Procedures or views. The isStoredProc simply states whether the given string is a Stored Procedure or not.

The syncDestination element defines the site and the list to be used. The append attributes determine whether the list will be appended to or updated. The sourceKeyField and destinationKeyField attributes are optional, unless append is true, in which case, they identify the fields to be used to compare in order to update a given list item.

The column element defines the columns to be added to the SharePoint list. The source and destination attributes should be self-explanatory. The dataType attribute is a string representation of any valid SPFieldType enumeration value.

One special column type that has been added is represented by specifying Today as the source attribute value. This also makes use of the optional timeZone attribute. This was added to support dates from different time zones. Since the SharePoint server may be in a different time zone than the users, this allows for adjustments to give an expected value.

Support classes

A couple of internal classes are used to simplify the access to the definition file using LINQ to SQL.

#region Internal classes

internal class SyncProject
{
    …
}

internal class SyncSource
{
    public SyncSource(XElement sourceElement)
    {
        ConnectionString = sourceElement.Element("connectionString").Value;
        DataSource = sourceElement.Element("source").Value;
        IsStoredProc = Convert.ToBoolean(sourceElement.Element("source").
                                         Attribute("isStoredProc").Value);
    }

    public string ConnectionString { get; private set; }
    public string DataSource { get; private set; }
    public bool IsStoredProc { get; private set; }
}

internal class SyncDestination
{
    …
}

internal class SyncColumn
{
    …
}

#endregion

Main processing

The code simply iterates through each syncProject element in the definition file as represented by the SyncProject class.

SyncProjects projects = new SyncProjects();
foreach(SyncProject project in projects.Projects)
{
    SyncProject(project);
}   

private static void SyncProject(SyncProject project)
{
    try
    {
        SPList list = null;
        Console.WriteLine("Attempting to open: " + project.Destination.Site);
        using(SPSite site = new SPSite(project.Destination.Site))
        {
            using(SPWeb web = site.OpenWeb())
            {
                try
                {
                    Console.WriteLine("Varify list exists...");
                    list = web.Lists[project.Destination.List];
                }
                catch(ArgumentException)
                {
                    // Can't find list so just eat exception and create the list
                    Console.WriteLine("Creating list...");
                    list = CreateList(web, project.Destination.List, project.Columns);
                    CreateView(list, project.Columns);
                    CreateNavigationMenuItem(web, project.Destination.List);
                }

                // Should be valid by now but check anyway
                if(list != null)
                {
                    SyncList(project, list);
                }
            }
        }
    }
    catch(Exception ex)
    {
        if(ExceptionPolicy.HandleException(ex, "Default Policy"))
            throw;
    }
}

As we can see here, the SPSite specified as the destination is attempted to be opened along with the destination web. The interesting point here is when we look for the specified list. Because the SPListsCollection exposed by the SPWeb.Lists property doesn’t contain a method like Contains, we need to handle the ArguementException that is thrown when trying to access a list that doesn’t exist.

If the list doesn’t exist, it, of course, needs to be created.

private static SPList CreateList(SPWeb web, string listName, List<SyncColumn> columns)
{
    SPList list = null;
    try
    {
        Guid listGuid = web.Lists.Add(listName, listName, 
                        web.ListTemplates["Custom List"]);
        list = web.Lists[listGuid];
        SPFieldCollection fields = list.Fields;

        foreach(SyncColumn col in columns)
        {
            SPField field = new SPField(fields, col.DataType, col.Destination);
            list.Fields.Add(field);
        }
        
        list.Update();
        web.Update();
    }
    catch(ArgumentException ex)
    {
        Console.WriteLine("Create list failed");
        if(ExceptionPolicy.HandleException(ex, "Default Policy"))
            throw;
    }
    return list;
}

There is nothing outstanding about this method, just iterate through the columns defined for the SyncProject and add a SPField to the SPFieldCollection for the SPList.

Sync the list with the database

Once the list has been created and added to the site, the next step is to get the data into it.

The first step is to get the data. A SQLConnection is created as specified in the definition file, and a SQLCommand is created using either a Stored Procedure or view as specified in the definition file. The resulting DataTable is returned to the calling method.

private static DataTable GetDataSource(SyncProject project)
{
    Console.WriteLine("Connecting to database...");
    DataTable dt = new DataTable();
    using(SqlConnection conn = new SqlConnection(project.Source.ConnectionString))
    {
        string cmdString = string.Empty;

        if(project.Source.IsStoredProc)
            cmdString = project.Source.DataSource;
        else
            cmdString = "SELECT * FROM " + project.Source.DataSource;

        using(SqlCommand cmd = new SqlCommand(cmdString, conn))
        {
            if(project.Source.IsStoredProc)
                cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();
            dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
        }
    }
    return dt;
}

After getting the data, we simply iterate through each DataRow in the DataTable and match the SPListItem with the DataColumn.

private static void SyncList(SyncProject project, SPList list)
{
    DataTable dt = GetDataSource(project);

    Console.Write("Creating items...");
    foreach(DataRow row in dt.Rows)
    {
        Console.Write(".");
        SPListItem item = null;

        if(project.Destination.ShouldAppend)
            item = FindItem(list, project.Destination.DestinationKeyField, 
                   row[project.Destination.SourceKeyField]);
        else
            item = list.Items.Add();

        foreach(SyncColumn col in project.Columns)
        {
            if(col.Source.ToUpper().CompareTo("TODAY") == 0)
                item[col.Destination] = GetToday(col.TimeZone);
            else
                item[col.Destination] = row[col.Source] == 
                            DBNull.Value ? null : row[col.Source];
        }

        item.Update();
    }
    Console.WriteLine("{0} items added", dt.Rows.Count);
}

Two interesting points here are finding the SPItem to update, if required, and handling the special Today column.

For finding SPItem, we just iterate through the items in the list and compare the value to what we’re looking for. If the item is found, it is returned; otherwise, a new SPListItem is added to the list.

private static SPListItem FindItem(SPList list, string destinationField, object value)
{
    foreach(SPListItem item in list.Items)
    {
        if(item[destinationField].ToString() == value.ToString())
            return item;
    }

    return list.Items.Add();
}

To handle the Today field, we use the TimeZone value property to convert the current DateTime to the proper time zone.

private static string GetToday(string timeZone)
{
    DateTime dt = TimeZoneInfo.ConvertTime(DateTime.Now, 
                  TimeZoneInfo.FindSystemTimeZoneById(timeZone));
    return dt.ToString();
}

Creating a navigation link

A nice convenience step is add a link to the newly created list to the Quick Links.

private static void CreateNavigationMenuItem(SPWeb web, string listName)
{
    // Create a navigation item for this list
    string url = "Lists/" + listName + "/AllItems.aspx";
    SPNavigationNode navNode = new SPNavigationNode(listName, url);
    foreach(SPNavigationNode node in web.Navigation.QuickLaunch)
    {
        // Find the Lists node
        if(node.Title == "Lists")
        {
            bool menuFound = false;
            // Check if menu item already exists
            foreach(SPNavigationNode item in node.Children)
            {
                if(item.Url == navNode.Url)
                {
                    menuFound = true;
                    break;
                }
            }
            // If the menu wasn't found then add it
            if(!menuFound)
                node.Children.AddAsLast(navNode);
        }
    }
}

Conclusion

There is nothing very difficult or outstanding about this solution, it’s just understanding the problem and the possible solutions to it, and putting the pieces together to solve it. After researching the problem, I wasn’t able to find any solution, or at least a free one, that provided the required degree of flexibility and ease of use for users. I thought it might be useful to others needing a similar solution and an insight into SharePoint coding.

Additional Features

An additional feature that could be added to this solution would be an editor to create and edit the definition file to further isolate the internals from end users.

History

• 11/30/08 – Initial release.