none
Linq to datatable in VB.NET - need help with enumeration and copytodatatable RRS feed

  • Question

  • I am trying to access an XML web service using Linq (VB.NET) and load the returned records to a datatable. Ultimately, I want to load the records into SQL Server (using SqlBulkCopy), but I figure the records must first first step is required and I am stumped. I am having trouble with the enumerable type concept (newbie to LINQ here). I know I need to get that working before the copytodatatable feature will work, though. My code is below, thanks in advance for any assistance you can provide. 

    Private Sub loadOmniDatamod()
    
            Dim omniURL As String = "http://www2.mvr.usace.army.mil/OMNI/ws/OMNIdata.cfm?river=TN&rptname=vessel_current_location"
    
            Try
                Dim doc = XDocument.Load(omniURL)
    
                Dim query = (From p In doc...<SERVER>.<POOL> _
                       From v In p.<VESSEL> _
                       Select New With _
                       { _
                         .River = p.@RIVER_CODE, _
                         .Name = v.@VESSEL_NAME, _
                         .VesselNumber = v.@VESSEL_NUMBER, _
                         .Date = v.<DATETIME_FORMATTED>.Value, _
                         .Status = v.<STATUS_RECORD>.Value, _
                         .TotalBarges = v.<TOTAL_BARGES>.Value, _
                         .PoolNumber = p.@POOL_NUMBER, _
                         .PoolName = p.@POOL_NAME, _
                         .VesselDirection = p.@VESSEL_DIRECTION _
                       }).AsEnumerable
    
                ' Bind the vessel data to the DataGridView
    
                Dim ds As New DataSet
                Dim omniDT As New DataTable("omnirecords")
                ds.Tables.Add(omniDT)
    
                Dim dc1 As New DataColumn("River")
                Dim dc2 As New DataColumn("Name")
                Dim dc3 As New DataColumn("VesselNumber")
                Dim dc4 As New DataColumn("Date")
                Dim dc5 As New DataColumn("Status")
                Dim dc6 As New DataColumn("TotalBarges")
                Dim dc7 As New DataColumn("PoolNumber")
                Dim dc8 As New DataColumn("PoolName")
                Dim dc9 As New DataColumn("VesselDirection")
    
                omniDT.Columns.Add(dc1)
                omniDT.Columns.Add(dc2)
                omniDT.Columns.Add(dc3)
                omniDT.Columns.Add(dc4)
                omniDT.Columns.Add(dc5)
                omniDT.Columns.Add(dc6)
                omniDT.Columns.Add(dc7)
                omniDT.Columns.Add(dc8)
                omniDT.Columns.Add(dc9)
    
                omniDT = query.copytodatatable()
    
                DataGridView1.DataSource = omniDT
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    
        End Sub
    


    Friday, January 13, 2012 11:02 PM

Answers

All replies

  • You are posting to the wrong forum concerning your issue. The below
    forum is where you should post.
     
    Msdn.en-US.linqprojectgeneral
     
    Friday, January 13, 2012 11:20 PM
  • I am trying to access an XML web service using Linq (VB.NET) and load the returned records to a datatable. Ultimately, I want to load the records into SQL Server (using SqlBulkCopy), but I figure the records must first first step is required and I am stumped. I am having trouble with the enumerable type concept (newbie to LINQ here). I know I need to get that working before the copytodatatable feature will work, though. My code is below, thanks in advance for any assistance you can provide. 

     

    Private Sub loadOmniDatamod()
    
            Dim omniURL As String = "http://www2.mvr.usace.army.mil/OMNI/ws/OMNIdata.cfm?river=TN&rptname=vessel_current_location"
    
            Try
                Dim doc = XDocument.Load(omniURL)
    
                Dim query = (From p In doc...<SERVER>.<POOL> _
                       From v In p.<VESSEL> _
                       Select New With _
                       { _
                         .River = p.@RIVER_CODE, _
                         .Name = v.@VESSEL_NAME, _
                         .VesselNumber = v.@VESSEL_NUMBER, _
                         .Date = v.<DATETIME_FORMATTED>.Value, _
                         .Status = v.<STATUS_RECORD>.Value, _
                         .TotalBarges = v.<TOTAL_BARGES>.Value, _
                         .PoolNumber = p.@POOL_NUMBER, _
                         .PoolName = p.@POOL_NAME, _
                         .VesselDirection = p.@VESSEL_DIRECTION _
                       }).AsEnumerable
    
                ' Bind the vessel data to the DataGridView
    
                Dim ds As New DataSet
                Dim omniDT As New DataTable("omnirecords")
                ds.Tables.Add(omniDT)
    
                Dim dc1 As New DataColumn("River")
                Dim dc2 As New DataColumn("Name")
                Dim dc3 As New DataColumn("VesselNumber")
                Dim dc4 As New DataColumn("Date")
                Dim dc5 As New DataColumn("Status")
                Dim dc6 As New DataColumn("TotalBarges")
                Dim dc7 As New DataColumn("PoolNumber")
                Dim dc8 As New DataColumn("PoolName")
                Dim dc9 As New DataColumn("VesselDirection")
    
                omniDT.Columns.Add(dc1)
                omniDT.Columns.Add(dc2)
                omniDT.Columns.Add(dc3)
                omniDT.Columns.Add(dc4)
                omniDT.Columns.Add(dc5)
                omniDT.Columns.Add(dc6)
                omniDT.Columns.Add(dc7)
                omniDT.Columns.Add(dc8)
                omniDT.Columns.Add(dc9)
    
                omniDT = query.copytodatatable()
    
                DataGridView1.DataSource = omniDT
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    
        End Sub
    


    Saturday, January 14, 2012 12:29 AM
  • Thanks, I posted and the link is at the following location for those who might have the same issue:

    http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/28b2585b-7da4-4198-9f5e-2987a2ba019a

    Saturday, January 14, 2012 12:32 AM
    • Marked as answer by dobbinjp Saturday, January 14, 2012 2:02 PM
    • Marked as answer by dobbinjp Monday, January 16, 2012 2:53 AM
    Saturday, January 14, 2012 5:17 AM
  • Federico,

    Thanks, that worked great!

    -Jimmy

    Saturday, January 14, 2012 2:05 PM
  • Hi dobbinjp;

    Please see comment below for solution.

    Private Sub loadOmniDatamod()
    
        Dim omniURL As String = "http://www2.mvr.usace.army.mil/OMNI/ws/OMNIdata.cfm?river=TN&rptname=vessel_current_location"
    
        Try
            Dim doc = XDocument.Load(omniURL)
    
            Dim query = (From p In doc...<SERVER>.<POOL> _
                   From v In p.<VESSEL> _
                   Select New With _
                   { _
                     .River = p.@RIVER_CODE, _
                     .Name = v.@VESSEL_NAME, _
                     .VesselNumber = v.@VESSEL_NUMBER, _
                     .Date = v.<DATETIME_FORMATTED>.Value, _
                     .Status = v.<STATUS_RECORD>.Value, _
                     .TotalBarges = v.<TOTAL_BARGES>.Value, _
                     .PoolNumber = p.@POOL_NUMBER, _
                     .PoolName = p.@POOL_NAME, _
                     .VesselDirection = p.@VESSEL_DIRECTION _
                   }).AsEnumerable
    
            ' Bind the vessel data to the DataGridView
            Dim ds As New DataSet
            Dim omniDT As New DataTable("omnirecords")
            ds.Tables.Add(omniDT)
    
            Dim dc1 As New DataColumn("River")
            Dim dc2 As New DataColumn("Name")
            Dim dc3 As New DataColumn("VesselNumber")
            Dim dc4 As New DataColumn("Date")
            Dim dc5 As New DataColumn("Status")
            Dim dc6 As New DataColumn("TotalBarges")
            Dim dc7 As New DataColumn("PoolNumber")
            Dim dc8 As New DataColumn("PoolName")
            Dim dc9 As New DataColumn("VesselDirection")
    
            omniDT.Columns.Add(dc1)
            omniDT.Columns.Add(dc2)
            omniDT.Columns.Add(dc3)
            omniDT.Columns.Add(dc4)
            omniDT.Columns.Add(dc5)
            omniDT.Columns.Add(dc6)
            omniDT.Columns.Add(dc7)
            omniDT.Columns.Add(dc8)
            omniDT.Columns.Add(dc9)
    
            ' Because CopyToDataTable() only works with IEnumerable(Of DataRow) and the above
            ' Linq query "query" returns an IEnumerable(Of Anonymous) type CopyToDataTable()
            ' can not be used and in fact there is no function currently available to do this.
            ' Therefore you need to iterate over the query and fill each row as shown below.
            
            'omniDT = query.copytodatatable()
    
            For Each row in query
                Dim dr As DataRow
                dr = omniDT.NewRow
                dr("River") = row.River
                dr("Name") = row.Name
                dr("VesselNumber") = row.VesselNumber
                dr("Date") = row.Date
                dr("Status") = row.Status
                dr("TotalBarges") = row.TotalBarges
                dr("PoolNumber") = row.PoolNumber
                dr("PoolName") = row.PoolName
                dr("VesselDirection") = row.VesselDirection
                omniDT.Rows.Add(dr)
            Next
    
            DataGridView1.DataSource = omniDT
    
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    
    End Sub
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, January 14, 2012 3:35 PM
  • You're welcome !
    Federico Daniel Colombo
    Sunday, January 15, 2012 12:30 AM