CODEWITHSUNDEEP
vb.netlinqdatatable
Fred Kerber
Fred Kerber

Reputation: 187

How to update a single column in single row in a datatable using LinQ

I'm working on a project that uses a Master database (a datatable) to drive downloading of files from an FTP server. During the process, I create a local database (another datatable) for each client in the master database. Here is the code that I use to create a client database:

Console.WriteLine(" Building Client Database")
Clientdatabase = New DataTable
Clientdatabase = DataBaseTable.Clone
Dim RowList = (From row In DataBaseTable.AsEnumerable() Where (row.Field(Of String)("co") = CompanyID))
For Each RowItem In RowList
    Clientdatabase.ImportRow(RowItem)
Next

This code is working as expected; the Clientdatabase is accurate in what it contains. My problem is updating a field in the Clientdatabase. From within a loop, I'm getting each record that contains the full path and file name of the target file on the FTP server, then I attempt to update the clientdatabase with the local path and I can't get it to work. From within the loop this code is performing the logic and downloading the file:

Dim FileList = (From row In DataBaseTable.AsEnumerable().Select(Function(x) New With {
     Key .co = x.Field(Of String)("co"),
     Key .path = x.Field(Of String)("path"),
     Key .OriginalFileName = x.Field(Of String)("OriginalFileName"),
     Key .DocumentID = x.Field(Of String)("DocumentID")
               }).Where(Function(s) s.co = CompanyID).ToList)
Console.Write(" Downloading Files : ")
Dim CursorArray() As String = Split("\,|,/,-", ",")
Dim FileCounter As Integer = 1
For Each CompanyFile In FileList
    Dim ThisFile As String = CompanyFile.path.Replace("\", "/")
    Dim Results As TransferOperationResult = DownloadFromPath(ThisFile, DestinPath)
    Dim TmpParts() As String = Split(ThisFile, "/")
    Dim LocalName As String = TmpParts(UBound(TmpParts))
    If Results.IsSuccess Then
        UpdateCDBPath(CompanyID, CompanyFile.DocumentID, LocalName)
        ReportData &= CompanyID & ",Success," & CompanyFile.OriginalFileName & "," & ThisFile & vbCrLf
    Else
        ReportData &= CompanyID & ",Failed," & CompanyFile.OriginalFileName & "," & ThisFile & vbCrLf
    End If
    FileCounter += 1
    Console.Write(CursorArray(FileCounter Mod 4) & Chr(8))
Next

The call to update (UpdateCDBPath) contains the following code:

Sub UpdateCDBPath(ByVal CompanyID As String, ByVal DocumentID As String, TargetValue As String)
    Dim result = (From row In Clientdatabase.AsEnumerable().Select(Function(x) New With {
                    Key .DocumentID = x.Field(Of String)("DocumentID"),
                    Key .Co = x.Field(Of String)("co")
                }).Where(Function(s) s.DocumentID = DocumentID And s.Co = CompanyID ) Select Clientdatabase)
    For Each ItemRow In result
        ItemRow.Rows(0).Item("Path") = TargetValue
    Next
End Sub

The problem is within this code block. DocumentID is a GUID that is unique within the client that uniquely identifies the document, TargetValue is the replacement path (local path to the file). If I'm understanding the Linq correctly (and I'm not), it should only return the rows I'm interested in (1), thus how I'm setting the new path. I've tried countless examples from various examples and can't make it work. When I check the Clientdatabase, none of the path fields are updated. I've also confirmed that after saving the Clientdatabase locally, the fields are still the same. Can some one please point me in the right direction, tell me where I went wrong or something so I can solve this. I eventually will need to update other fields in the clientdatabase; this is the first one. Thanks in advance for any and all help that might come my way!

Upvotes: 1

Views: 882

Answers (1)

ItsPete
ItsPete

Reputation: 2368

The linq for result is returning a EnumerableRowCollection(Of System.Data.DataTable), meaning that it contains a list of rows that are the entire Clientdatabase.

As each row contains the entire DataRowCollection, ItemRow.Rows(0).Item("Path") = TargetValue will update only the first row in Clientdatabase as many times as there are records in results.

Changing the query to return an EnumerableRowCollection(Of System.Data.DataRow) allows each row to be accessed directly in a loop:

Sub UpdateCDBPath(ByVal CompanyID As String, ByVal DocumentID As String, TargetValue As String)
    Dim result = From row In Clientdatabase.AsEnumerable()
                 Where row.Field(Of String)("DocumentID") = DocumentID AndAlso
                       row.Field(Of String)("co") = CompanyID
                 Select row

    For Each ItemRow In result
        ItemRow.Item("Path") = TargetValue
    Next
End Sub

Upvotes: 1

Related Questions