SqlBulkCopy with SharePoint 2010

In this post, I will illustrate an example on how you can use SqlBulkCopy Class for pulling out data or information from any list residing in SharePoint 2010 to an external SQL table of your choice. As a SharePoint user you would ponder and ask

I’m sorry why do we need to do that? We got everything here in SharePoint, that’s what it is for?

Well, there is sometimes a need for some end users to have data on a SharePoint list to be available to them in SQL table for reporting purposes. Without further babbling, I would like to first share this MSDN reference

Using the Method WriteToServer(DataTable) it comes as no surprise that SharePoint ListItemCollections can be returned back as DataTable based on any CAML Query. Needless to say, this DataTable can then be passed as parameter to the method.

Here is how I pulled back everything on a SharePoint list to an external SQL table for a business requirement. This was done in the context of a Console Application but don’t hesitate to run such operations as a Windows Service or something else to suit your requirement.

First create a desired SQL table with desired columns and associated DataTypes on SQL Server.

Add the following references


Imports System.Data.SqlClient
Imports Microsoft.SharePoint

Add the following anywhere in a Sub, Function, a Method in a different class somewhere.


Dim ConStr As String = GetConnectionString()
Using SQLConn As SqlConnection = New SqlConnection(ConStr)
     Dim dt As DataTable = GetDataTableFromSPList()
     Try
         SQLConn.Open()
         Dim BlkCpy As SqlBulkCopy = New SqlBulkCopy(SQLConn)
         BlkCpy.DestinationTableName = "dbo.YOURTABLENAME"
         ' Here we map the Source Column (<em>SharePoint Internal Name</em>) to SQL Columns
         BlkCpy.ColumnMappings.Add("Product_x0020_Number", "ID")
         BlkCpy.ColumnMappings.Add("Title", "Title")
         BlkCpy.ColumnMappings.Add("Description", "Description")
         BlkCpy.ColumnMappings.Add("Any_x0020_Other_x0020_Column", "Notes")
         BlkCpy.WriteToServer(dt)
     Catch ex As Exception
         Throw ex
Finally
SQLConn.Close()
     End Try
End Using

Private Function GetConnectionString() As String
         Return "Data Source=SQLSERVERINSTANCENAME; Initial Catalog=DBNAME; Trusted_Connection=True; User Id=username; Password=password"
End Function

Private Function GetDataTableFromSPList () As DataTable
         Dim dt As DataTable = Nothing
         Dim oSite As SPSite = New SPSite("urlofsharepointsite")
         Dim oWeb As SPWeb = oSite.OpenWeb
         Dim _List As SPList =oWeb.Lists("Desired List Name")
         Dim _Qry As SPQuery = New SPQuery
         ' I built this query to get all records but you can do something more specific
         _Qry.Query = "<Where>" & _
                           "<IsNotNull>" & _
                                    "<FieldRef Name='ID' />" & _
                           "</IsNotNull>" & _
                      "</Where>"
         ' Use the Internal Names of the Columns you wish to have send back to SQL table
         _Qry.ViewFields = String.Concat("<FieldRef Name='Product_x0020_Number' />",
                                             "<FieldRef Name='Title' />",
                                             "<FieldRef Name='Description' />",
                                             "<FieldRef Name='Some_x0020_Other_x0020_Column' />")

          Dim items As SPListItemCollection = _List.GetItems(_Qry)
         dt = items.GetDataTable
         oWeb.Dispose()
         oSite.Dispose()
         Return dt
End Function

I hope this post will encourage you to utilize native ASP.Net classes in conjunction with SharePoint 2010, afterall SharePoint 2010 is built on ASP.Net. A word of advice; make sure you set the .Net Framework to 3.5 to make sure you are able to execute the code above.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s