SharePoint List To DataTable

[kpmc]

If you're like me then you work for a large corp that pushes SharePoint like its delivered from God. You also have no love for SharePoint and you do everything in your power to prevent navigating to it altogether. You are also having nightmares of off the wall requests coming from several departments, and you have a bucket near your desk that you can go to quickly when you have to vomit.

Anyway...what am I getting at here...

Oh yes...

I have created code that will
A) Load up only SubSite Titles from the site root they have access to (preventing access violations and ensuing exceptions)
B) Load up all lists from selected SubSite
C) Load up all fields from selected List
D) Load up all data from the selected list
E) Add columns to a DataTable from the list field collection
F) Add data as rows to the datatable

This is much different than many examples out there where it uses the least amount of resources, easier to read, faster, and in VB.NET
It requires 2 comboboxs (Set DropDownStyle to DropDownList so people cant type anything manually) and a DataGridView

If you're interested in learning how to push any changes you make to the data from this DGV to the actual site, I have method madness for that too...

Imports Microsoft.SharePoint.Client
Public Class FormSPList
    Private Sub FormSPList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim path As String = "http://intranet"
        Dim clientContext = New ClientContext(path)
        Dim oWebsite = clientContext.Web
        Dim result As WebCollection = oWebsite.GetSubwebsForCurrentUser(New SubwebQuery())
        clientContext.Load(result, Function(n) n.Include(Function(o) o.Title, Function(o) o.ServerRelativeUrl))
        clientContext.ExecuteQuery()

        For Each orWebsite In result
            ComboBoxSites.Items.Add(path & orWebsite.ServerRelativeUrl)
        Next
    End Sub

    Private Sub ComboBoxSites_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxSites.SelectedIndexChanged
        ComboBoxLists.Items.Clear()

        Dim context As ClientContext = New ClientContext(ComboBoxSites.Text)
        Dim web As Web = context.Web
        context.Load(web.Lists, Function(lists) lists.Include(Function(list) list.Title, Function(list) list.Id))
        context.ExecuteQuery()

        For Each list As List In web.Lists
            ComboBoxLists.Items.Add(list.Title)
        Next

    End Sub

    Private Sub ComboBoxLists_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxLists.SelectedIndexChanged
        Dim Dtable As New DataTable

        Dim context As ClientContext = New ClientContext(ComboBoxSites.Text)
        Dim web As Web = context.Web
        Dim list = web.Lists.GetByTitle(ComboBoxLists.Text)
        context.Load(list.Fields)
        Dim query As CamlQuery = CamlQuery.CreateAllItemsQuery()
        Dim AllItems As ListItemCollection = list.GetItems(query)
        context.Load(AllItems)
        context.ExecuteQuery()

        For Each f As Field In list.Fields
            Dtable.Columns.Add(f.InternalName, GetType(System.String))
        Next

        For Each RowItem As ListItem In AllItems
            Dim dr As DataRow = Dtable.NewRow
            For i As Integer = 0 To list.Fields.Count - 1
                dr(i) = RowItem.FieldValues.Values(i)
            Next
            Dtable.Rows.Add(dr)
        Next

        DGVList.DataSource = Dtable
    End Sub
End Class

[kpmc]

Ok, so the above code will get you every column, and you maybe asking yourself "What the heck do I care about content types, and meta data, that's all sharepoint garbage!" And you'd be right, there are lots of columns that you will not want, in fact, the majority is just nuts and bolts for the list. So I set to work to narrow this down a bit and found a "ReadOnlyField" property in the Field Class. By checking this property you can effectively and drastically reduce the column count down to only the fields you can change! Obviously this will include any columns created by the user.

Imports Microsoft.SharePoint.Client
Public Class FormSPList
    Private Sub FormSPList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Width = Screen.PrimaryScreen.Bounds.Width - 50
        Height = Screen.PrimaryScreen.Bounds.Height - 100
        CenterToScreen()

        Dim path As String = "http://intranet"
        Dim clientContext = New ClientContext(path)
        Dim oWebsite = clientContext.Web
        Dim result As WebCollection = oWebsite.GetSubwebsForCurrentUser(New SubwebQuery())
        clientContext.Load(result, Function(n) n.Include(Function(o) o.Title, Function(o) o.ServerRelativeUrl))
        clientContext.ExecuteQuery()

        For Each orWebsite In result
            ComboBoxSites.Items.Add(path & orWebsite.ServerRelativeUrl)
        Next
    End Sub

    Private Sub ComboBoxSites_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxSites.SelectedIndexChanged
        ComboBoxLists.Items.Clear()

        Dim context As ClientContext = New ClientContext(ComboBoxSites.Text)
        Dim web As Web = context.Web
        context.Load(web.Lists, Function(lists) lists.Include(Function(list) list.Title, Function(list) list.Id))
        context.ExecuteQuery()

        For Each list As List In web.Lists
            ComboBoxLists.Items.Add(list.Title)
        Next

    End Sub

    Private Sub ComboBoxLists_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxLists.SelectedIndexChanged
        Dim Dtable As New DataTable

        Dim context As ClientContext = New ClientContext(ComboBoxSites.Text)
        Dim web As Web = context.Web
        Dim list = web.Lists.GetByTitle(ComboBoxLists.Text)
        context.Load(list.Fields)
        Dim query As CamlQuery = CamlQuery.CreateAllItemsQuery()
        Dim AllItems As ListItemCollection = list.GetItems(query)
        context.Load(AllItems)
        context.ExecuteQuery()

        For Each f As Field In list.Fields
            If Not f.ReadOnlyField Then
                Dtable.Columns.Add(f.InternalName, GetType(System.String))
            End If
        Next

        For Each RowItem As ListItem In AllItems
            If RowItem IsNot Nothing Then
                Dim dr As DataRow = Dtable.NewRow
                For Each ColName As DataColumn In Dtable.Columns
                    If RowItem.FieldValues.ContainsKey(ColName.ColumnName) Then
                        If RowItem.FieldValues(ColName.ColumnName) IsNot Nothing Then
                            dr(ColName.ColumnName) = RowItem.FieldValues(ColName.ColumnName).ToString

                        End If
                    End If
                Next
                Dtable.Rows.Add(dr)
            End If

        Next

        DGVList.DataSource = Dtable
        For Each DgvCol As DataGridViewColumn In DGVList.Columns
            DgvCol.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
        Next
    End Sub
End Class

[kpmc]

AndAlso if you intend to push any updates back to the site you're going to need the ID column! Just add a column to the DTable "ID" as string and the latter item loop will fill it's values

        Dtable.Columns.Add("ID", GetType(System.String)) 'Will need this to push updates to list item

        For Each f As Field In list.Fields
            If Not f.ReadOnlyField Then
                Dtable.Columns.Add(f.InternalName, GetType(System.String))
            End If
        Next