dcsimg
Results 1 to 3 of 3

Thread: SharePoint List To DataTable

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    649

    SharePoint List To DataTable

    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...


    Code:
    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

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    649

    Re: SharePoint List To DataTable

    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.

    Code:
    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

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    649

    Re: SharePoint List To DataTable

    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

    Code:
            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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width