Tip SharePoint 2010 - List to DataTable Code.

[Lotok]

Hey Fellas

Here is some code I have written for extracting a SharePoint list to a DataTable. As it stands it works fien but does take 3 or 4 seconds to complete. As well as this just being me sharing some code, I am also happy to hear ideas for making it more efficient. The Client Object Model doesn't have an output to DataTable option natively.

I think possibly the way I extract the keyvaluepair data from the ListItems could be done better, I havent used dictionaries much in the past.

For the code to work, you need to reference the Client OM Assemblies which are Microsoft.SharePoint.Client & Microsoft.SharePoint.Client.Runtime.

Example Usage

Imports Microsoft.SharePoint.Client
Imports Microsoft.SharePoint
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim sp As New SPQuick("[URL]http://SharePoint/[/URL]", "SPListName")
        Dim dt As DataTable = sp.GetList
        DataGridView.DataSource = dt
        DataGridView.Update()
    End Sub
End Class

Code for SPQuick Class

Imports Microsoft.SharePoint.Client
Imports Microsoft.SharePoint
Public Class SPQuick
    Protected Friend Property SiteURL As String
    Protected Friend Property ListName As String
    Public Sub New(spURL As String, List As String)
        'set class properties
        SiteURL = spURL
        ListName = List
    End Sub

    Public Function GetList() As DataTable
        'Create a datatable for holding the list data
        Dim dt As New DataTable
        'Get List Item collection (Set as 100 Row Limit)
        Dim ColListItem As ListItemCollection = GetData("<View><RowLimit>100</RowLimit></View>")
        'Create the dataTable Columns
        For Each x As KeyValuePair(Of String, Object) In ColListItem.Item(0).FieldValues
            'loop through all the keys in dictionary creating the columns using the key as column name
            Try
                dt.Columns.Add(x.Key)
            Catch ex As Exception
                Throw New Exception("Problem Creating Table Columns", ex)
            End Try
        Next
        For Each Li As Client.ListItem In ColListItem
            Try
                'load item to a datarow
                'using dt.newrow to keep columns info
                Dim dr As DataRow = dt.NewRow
                For Each x As KeyValuePair(Of String, Object) In Li.FieldValues
                    Try
                        'If Look Up Value from joined tables, extract data
                        'make sure there is data to check
                        If x.Value IsNot DBNull.Value = True And x.Value IsNot Nothing = True Then
                            Dim sType As String = x.Value.GetType.FullName
                            'check if its a lookup value
                            If sType = "Microsoft.SharePoint.Client.FieldLookupValue" Then
                                'get the lookup value
                                dr(x.Key) = CType(x.Value, FieldLookupValue).LookupValue
                                Exit Try
                            ElseIf sType = "Microsoft.SharePoint.Client.FieldUserValue" Then
                                'get the user value
                                dr(x.Key) = CType(x.Value, FieldUserValue).LookupValue
                                Exit Try
                            End If
                        End If
                        dr(x.Key) = x.Value
                    Catch ex As Exception
                        Throw New Exception("Inserting Data into DataRow", ex)
                    End Try
                Next
                'add datarow to the datatable
                dt.Rows.Add(dr)
            Catch ex As Exception
                Throw New Exception("Problem loading DataRow to DataTable", ex)
            End Try
        Next
        'Return the datatable
        Return dt
    End Function

    Private Function GetData(Query As String) As ListItemCollection
        'Create a connection to SharePoint. Using USING as this is a wrapped com object and needs to be disposed to avoid memory leaks. 
        Using ctx As New ClientContext(SiteURL)
            Dim MyWeb As Microsoft.SharePoint.Client.Web = ctx.Web
            Dim lst As List = MyWeb.Lists.GetByTitle(ListName)
            'Create a CAML Query
            Dim CQuery As New CamlQuery
            CQuery.ViewXml = Query
            'Create a Collection
            Dim ColListItems As ListItemCollection = lst.GetItems(CQuery)
            Try
                'Load and execute the request to SharePoint server
                ctx.Load(ColListItems)
                ctx.ExecuteQuery()
            Catch ex As Exception
                Throw New Exception("Problem Accessing List", ex)
            End Try
            'Return Collection
            Return ColListItems
        End Using
    End Function
End Class