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.
="1.0"="utf-8"="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())
{
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);
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");
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"];
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
="1.0"="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)
{
Console.WriteLine("Creating list...");
list = CreateList(web, project.Destination.List, project.Columns);
CreateView(list, project.Columns);
CreateNavigationMenuItem(web, project.Destination.List);
}
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)
{
string url = "Lists/" + listName + "/AllItems.aspx";
SPNavigationNode navNode = new SPNavigationNode(listName, url);
foreach(SPNavigationNode node in web.Navigation.QuickLaunch)
{
if(node.Title == "Lists")
{
bool menuFound = false;
foreach(SPNavigationNode item in node.Children)
{
if(item.Url == navNode.Url)
{
menuFound = true;
break;
}
}
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.