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
- Lets you efficiently bulk load a SQL Server table with data from another source.
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.