Programmatically Exporting Data from SharePoint into SQL Server using SQLBulkCopy

 

Those who have read my previous posts will know I was planning on using the SSIS SharePoint Connectors for extracting data from SharePoint to SQL Server.  All was going swimmingly well until I deployed them onto the server, then for some reason (and I spent a day scratching my head) they did not work.  Unfortunately on this server I have limited debugging resources, and had to make a risk based decision.  Do I put in debugging, find out why it does not work then fix it or try another solution.  I have never struggled a whole day on a problem like that before, and at 5 in the morning (whilst asleep) I had a blinding idea!

The basic idea is to use the ADO.Net SqlBulkCopy  to insert the data, but my main challenge is to make this configurable so that any SharePoint list can be inserted into any SQL Server table.  To do so I have created a number of objects which store the required data.   These are described below, but on a very basic level it mimics the SharePoint hierarchy.

Class	Properties
SharepointSite	string SiteURL
	string AuditList
	List<SharepointList> Lists
SharepointList	string ListName
	string SQLTableName
	List<SharepointField> Fields
SharepointField	string FieldName
	string SQLFieldName

Then using  the method below we can insert the data into database,SharepointList is the object model above, the DataTable is the SharePoint list data in form of a DataSet table and the connectionstring is self explanatory.

Code Snippet

1. private static void InsertIntoDatabase(SharepointList sharepointList, DataTable data, string connectionString)
2.         {
3.             //connectionstring
4.             using (var connection = new SqlConnection(connectionString))
5.             {
6.                 var bulkCopy = new SqlBulkCopy(connection)
7.                                    {
8.                                        DestinationTableName = sharepointList.SQLTableName
9.                                    };
10.                 foreach (var field in sharepointList.Fields)
11.                 {
12.                     var mapping = new SqlBulkCopyColumnMapping
13.                                       {
14.                                           DestinationColumn = field.SQLFieldName,
15.                                           SourceColumn = field.FieldName
16.                                       };
17.                     bulkCopy.ColumnMappings.Add(mapping);
18.  
19.                 }
20.                 connection.Open();
21.  
22.                 bulkCopy.WriteToServer(data);
23.             }
24.         }

The clever bit is using Serialization for the configuration.  By Serializing and De-serializing the data into and out of XML we can now use any combination of Sharepoint Lists and SQL Tables.  The code below shows how to do this.  You will need to mark classes as Serializable see here.

Code Snippet

1. private static SharepointSite GetSharepointSitefromSettings()
2.         {
3.             var mappingFile = ConfigurationSettings.AppSettings["MappingFile"];
4.  
5.             var ser = new XmlSerializer(typeof(SharepointSite));
6.             var xmlReader = new XmlTextReader(mappingFile);
7.  
8.             var sharepointSite = (SharepointSite)ser.Deserialize(xmlReader);
9.             xmlReader.Close();
10.             return sharepointSite;
11.         }

And this is a snippet of the XML

<SharepointSite xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SiteURL>http://srvJupiter</SiteURL>
    <Lists>
    <SharepointList>
      <ListName>Projects</ListName>
            <SQLTableName>ProjectsStaging</SQLTableName>
      <Fields>
        <SharepointField>
          <FieldName>Title</FieldName>
          <SQLFieldName>Title</SQLFieldName>
        </SharepointField>
        <SharepointField>
          <FieldName>ID</FieldName>
          <SQLFieldName>ID</SQLFieldName>
        </SharepointField>
         </Fields>
    </SharepointList>   
  </Lists>
</SharepointSite>

Conclusion

I hope this gives an insight on how I solved my problem. 

Obviously this is not the complete solution, as that is too large to go into detail;

•   I made this as a SharePoint Timer Job, which requires a OWSTimer.exe.config file for the settings.  I stored the mappings in a separate file, which allows you to keep the xml format.
• The timer job also runs stored procedures before and after the import, which allows data transformations to happen.
• Audit data is written to a SharePoint list as well.

But there is enough code here to create a POC.

Creating SharePoint Views

As usual a little background, as I do not want you to take it out of context.

I am using SSIS with the SharePoint connectors to extract data out of a SharePoint list, to do so I wanted to create a custom view, this allows me to get all of the columns and to select the rows I need.  Also I like to do these things programmatically as I personally will not be doing the release.

To create a View we need to use SPList.Views.Add, which is slightly unusual because we cannot add an object. see http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spviewcollection.add.aspx

So this is the code I have used.

string viewName = "ExportView";

var strCollViewFields = new StringCollection();

foreach (SPField field in site.Lists[projectsListName].Fields)
{
    if (field.Hidden == false)
    {
        strCollViewFields.Add(field.InternalName);
    }
}
var strQuery = "<Where><Eq><FieldRef Name=\"Status\" /><Value Type=\"Choice\">Complete</Value></Eq></Where>";


site.Lists[projectsListName].Views.Add(viewName, strCollViewFields, strQuery, 100,true,false, SPViewCollection.SPViewType.Html, false);

The StringCollection is of type System.Collections.Specialized.StringCollection and is a list of the internal field name, this is usually the text on the field. With "_x0020_" instead of spaces.

StrQuery is a CAML query I use CAML Builder (by U2U) but remember to remove the <query> tag, or it will fail and all items will be returned.


100 is the number of rows, when imported this is ignored


true is whether to show pagination.


false is whether this is the default view


The next shows how to display the screen take a look at http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spviewcollection.spviewtype.aspx


And the last is whether this is a personal view or not.