none
Datatable skill level: Jedi RRS feed

  • Question

  • I am working on a solution that will update a sharepoint list from columns/values in a datatable. What I would like to do is add a row to an 'output' datatable for each column that is update and then have more or less a batch update procedure.

    So I have the SP list data in a DataTable "PhoneTable", and a BindingSource. The method in which I got the data there is not relevant.

    There is a DGV and some bound textboxs to the Bindingsource on a form. Changing any of these values is where Jedi skills is necessary to get what I want.

    First I create a New DataTable to hold the output. It includes 3 columns:

            With OutputTable
                .Columns.Add("ItemID", GetType(System.String)) ''Sharepoint ID needed for updating the column
                .Columns.Add("ColName", GetType(System.String)) ''Sharepoint column name to update
                .Columns.Add("ColVal", GetType(System.String)) ''Value of SharePoint Column
            End With


    Currently I am working on the textbox control TextChanged event, once work out a solution for this event I will move on to the DGV events.

            For Each ctrl As Control In panel_form.Controls
                If TypeOf ctrl Is TextBox Then
                    Dim tbctrl As TextBox = DirectCast(ctrl, TextBox)
                    AddHandler tbctrl.TextChanged, AddressOf tbchange
                End If
            Next

    So I am figuring in the 'tbchange' sub I need some kind of sorcery to to either update an existing row/column value if the id/column exists, or insert a new row if the id/column name does not exist.

        Private Sub tbchange(sender As Object, e As EventArgs)
            Dim ItemID As String = tb_id.Text ''Sharepoint ID from current PhoneTable Binding.position
            Dim ItemCol As String = DirectCast(sender, TextBox).Tag.ToString 'Sharepoint column name from TAG'
            Dim ItemVal As String = DirectCast(sender, TextBox).Text ''Value of column
            ''ninjutsu needed
        End Sub

    Hopefully I have explained this adequately.

    Any tips appreciated =)


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Friday, June 30, 2017 3:52 PM

Answers

  • Thanks Karen. Again a remarkable solution. I actually did come up with something, and just came back to delete this question. I'll instead put what I came up with. Kind of looks like a hackjob next to your procedure!

        Private Sub tbchange(sender As Object, e As EventArgs)
            Dim ItemID As String = tb_id.Text ''Sharepoint ID from current PhoneTable Binding.position
            Dim ItemCol As String = DirectCast(sender, TextBox).Tag.ToString 'Sharepoint column name from TAG'
            Dim ItemVal As String = DirectCast(sender, TextBox).Text ''Value of column
    
            ''Dataview declared in class declarations
            ''Build a filterstring for dataview
            Dim FilterStr As String = "ItemID ='" & ItemID & "' and ColName = '" & ItemCol & "'"
            OutputDV.RowFilter = FilterStr
            If OutputDV.Count = 0 Then
                ''If no rows found match, add row with colum and col value
                With OutputBS
                    .AddNew()
                    DirectCast(.Current, DataRowView)("ItemID") = ItemID
                    DirectCast(.Current, DataRowView)("ColName") = ItemCol
                    DirectCast(.Current, DataRowView)("ColVal") = ItemVal
                    .EndEdit()
                    OutputTable.AcceptChanges()
                End With
            Else
                ''found a row matching id and colname, update the colval
                With OutputBS
                    DirectCast(.Current, DataRowView)("ColVal") = ItemVal
                End With
                ''remove filter
                OutputDV.RowFilter = Nothing
            End If
        End Sub
    now I just need to iterate the table and execute the queries to update the Sharepoint list

    Thank for having a look, Karen.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Friday, June 30, 2017 5:41 PM
    • Marked as answer by Gtripodi Friday, June 30, 2017 5:42 PM
    Friday, June 30, 2017 5:40 PM

All replies

  • One idea is to use DataTable events as in my MSDN code sample. In the code sample you can get original and what is called proposed values. I have two DataGridView controls, top for changes, bottom for showing what was learned in events of the DataTable.

    If you want changes at anytime up until if AcceptChanges is called which is not wise you can look at using DataTable.GetChanges where depending on how it's setup and used properly can give you added, removed and changed rows. I have language extension methods that feed off a class and a DataTable.

    The class

    Public Class TableChanges
        ''' <summary>
        ''' Indicates the DataTable has one or more deleted rows
        ''' </summary>
        ''' <returns></returns>
        Public Property HasDeleted() As Boolean
        ''' <summary>
        ''' DataTable contains all deleted rows
        ''' </summary>
        ''' <returns></returns>
        Public Property Deleted() As DataTable
        Private _DeletedPrimaryKeys As New List(Of Integer)()
        Public Property DeletedPrimaryKeys() As List(Of Integer)
            Get
                Return _DeletedPrimaryKeys
            End Get
            Set(ByVal value As List(Of Integer))
                _DeletedPrimaryKeys = value
            End Set
        End Property
        ''' <summary>
        ''' Indicates the DataTable has one ore more modified rows
        ''' </summary>
        ''' <returns></returns>
        Public Property HasModified() As Boolean
        ''' <summary>
        ''' DataTable contains all modified rows
        ''' </summary>
        ''' <returns></returns>
        Public Property Modified() As DataTable
        ''' <summary>
        ''' Indicates the DataTable has one or more new rows
        ''' </summary>
        ''' <returns></returns>
        Public Property HasNew() As Boolean
        ''' <summary>
        ''' DataTable containing new rows
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks>Will not have the new primary key</remarks>
        Public Property Added() As DataTable
        ''' <summary>
        ''' Indicates if there are any unchanged rows
        ''' </summary>
        ''' <returns></returns>
        Public Property HasUnchanged() As Boolean
        ''' <summary>
        ''' DataTable containing unchanged rows
        ''' </summary>
        ''' <returns></returns>
        Public Property UnChanged() As DataTable
        ''' <summary>
        ''' Indicates there were one or more changes to the DataTable excluding unchanged
        ''' </summary>
        ''' <returns></returns>
        Public Property Any() As Boolean
    End Class
    
    

    The extension methods were at the top is a simple example of usage.

    Imports System.Text
    ' this is here for the code module Example otherwise not needed
    Imports System.Windows.Forms
    
    Module Example
        Private bsData As New BindingSource
        Private Sub Demo()
            Dim dt As New DataTable
            bsData.DataSource = dt
            '
            '
            '
            Dim dtLater As DataTable = CType(bsData.DataSource, DataTable)
            Dim allResults As TableChanges = dtLater.AllChanges("id")
            If allResults.HasNew Then
                Dim dtModified As DataTable = allResults.Modified
            End If
        End Sub
    End Module
    
    Public Module ExtensionMethods
    
        ''' <summary>
        ''' Get changes for a DataTable
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="PrimaryKeyIndex">Primary auto-incrementing key column index</param>
        ''' <returns></returns>
        ''' <remarks>
        ''' DO NOT Access Deleted table unless you first invoke AcceptChanges on the DataTable.
        ''' Generally we only need to know) are there any deleted rows and/or the primary key
        ''' of the deleted record(s)
        ''' </remarks>
        <Runtime.CompilerServices.Extension>
        Public Function AllChanges(ByVal sender As DataTable, ByVal PrimaryKeyIndex As Integer) As TableChanges
            Dim results = New TableChanges()
    
            results.Deleted = sender.GetChanges(DataRowState.Deleted)
            results.HasDeleted = results.Deleted IsNot Nothing
    
            For index = 0 To sender.Rows.Count - 1
                If sender.Rows(index).RowState = DataRowState.Deleted Then
                    results.DeletedPrimaryKeys.Add(Convert.ToInt32(sender.Rows(index)(PrimaryKeyIndex, DataRowVersion.Original)))
                End If
            Next
    
            results.Modified = sender.GetChanges(DataRowState.Modified)
            results.HasModified = results.Modified IsNot Nothing
    
            results.Added = sender.GetChanges(DataRowState.Added)
            results.HasNew = results.Added IsNot Nothing
    
            results.UnChanged = sender.GetChanges(DataRowState.Unchanged)
            results.HasUnchanged = results.UnChanged IsNot Nothing
    
            results.Any = results.HasDeleted OrElse results.HasModified OrElse results.HasNew
    
            Return results
    
        End Function
        ''' <summary>
        ''' Get changes by primary name
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="PrimaryKeyColumnName"></param>
        ''' <returns></returns>
        <Runtime.CompilerServices.Extension>
        Public Function AllChanges(ByVal sender As DataTable, ByVal PrimaryKeyColumnName As String) As TableChanges
    
            Dim PrimaryKeyIndex As Integer = sender.Columns(PrimaryKeyColumnName).Ordinal
            Dim results = sender.AllChanges(PrimaryKeyIndex)
    
            Return results
    
        End Function
        ''' <summary>
        ''' Returns a comma delimited string representing all 
        ''' data rows in the table.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <returns></returns>
        <Runtime.CompilerServices.Extension>
        Public Function Flatten(ByVal sender As DataTable) As String
    
            Dim sb = New StringBuilder()
    
            For Each row As DataRow In sender.Rows
                sb.AppendLine(String.Join(",", row.ItemArray))
            Next
    
            Return sb.ToString()
    
        End Function
    End Module
    
    
    The above code is from a C# code sample I did where I converted the above from C# to vb.net.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 30, 2017 5:17 PM
    Moderator
  • Thanks Karen. Again a remarkable solution. I actually did come up with something, and just came back to delete this question. I'll instead put what I came up with. Kind of looks like a hackjob next to your procedure!

        Private Sub tbchange(sender As Object, e As EventArgs)
            Dim ItemID As String = tb_id.Text ''Sharepoint ID from current PhoneTable Binding.position
            Dim ItemCol As String = DirectCast(sender, TextBox).Tag.ToString 'Sharepoint column name from TAG'
            Dim ItemVal As String = DirectCast(sender, TextBox).Text ''Value of column
    
            ''Dataview declared in class declarations
            ''Build a filterstring for dataview
            Dim FilterStr As String = "ItemID ='" & ItemID & "' and ColName = '" & ItemCol & "'"
            OutputDV.RowFilter = FilterStr
            If OutputDV.Count = 0 Then
                ''If no rows found match, add row with colum and col value
                With OutputBS
                    .AddNew()
                    DirectCast(.Current, DataRowView)("ItemID") = ItemID
                    DirectCast(.Current, DataRowView)("ColName") = ItemCol
                    DirectCast(.Current, DataRowView)("ColVal") = ItemVal
                    .EndEdit()
                    OutputTable.AcceptChanges()
                End With
            Else
                ''found a row matching id and colname, update the colval
                With OutputBS
                    DirectCast(.Current, DataRowView)("ColVal") = ItemVal
                End With
                ''remove filter
                OutputDV.RowFilter = Nothing
            End If
        End Sub
    now I just need to iterate the table and execute the queries to update the Sharepoint list

    Thank for having a look, Karen.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Friday, June 30, 2017 5:41 PM
    • Marked as answer by Gtripodi Friday, June 30, 2017 5:42 PM
    Friday, June 30, 2017 5:40 PM