LINQ

Read an XML documents with a Parent/Child relationship into a class

steve@4penny.net — Wed, 16 Mar 2011 20:47:47 GMT

The task here is to take an XML document in this form:

       2011-03-14 11:24:47.077


               6A639774-5EA9-440C-978B-FDC7DBF71949
               1
               001
               002


               D8FD406F-8CF2-47E6-B09D-E44106153C11
               1
               001
               002




       2011-03-14 11:24:47.077


               6A639774-5EA9-440C-978B-FDC7DBF71949
               2
               001
               002


               D8FD406F-8CF2-47E6-B09D-E44106153C11
               2
               001
               002

And parse it and move it into a class that we can manipulate in VB.

The class structure mirrors the XML document:

Code Snippet
 Public Class Transfer
    Public transferNumber
    Public timestamp As DateTime
    Public transferDetail As List(Of TransferDetail)
    Public test As List(Of String)
 
End Class
 
Public Class TransferDetail
    Public itemguid As Guid
    Public quantity As Double
    Public fromsite As String
    Public tosite As String
End Class
 

Our code loads the XML into an XDocument, and then uses LINQ queries to read it into an instance of the class.

Code Snippet
     Sub example()
        Dim xDoc As XDocument
        Dim eDoc As XElement
        Dim oTransfer As Transfer
        Dim oTransferDetail As TransferDetail
 
        'load an XML document into an xElement type
        eDoc = <transfers>
                   <transfer transferNumber="TSFR001">
                       <timestamp>2011-03-14 11:24:47.077timestamp>
                       <items>
                           <item>
                               <itemguid>6A639774-5EA9-440C-978B-FDC7DBF71949itemguid>
                               <quantity>1quantity>
                               <fromsite>001fromsite>
                               <tosite>002tosite>
                           item>
                           <item>
                               <itemguid>D8FD406F-8CF2-47E6-B09D-E44106153C11itemguid>
                               <quantity>1quantity>
                               <fromsite>001fromsite>
                               <tosite>002tosite>
                           item>
                       items>
                   transfer>
                   <transfer transferNumber="TSFR002">
                       <timestamp>2011-03-14 11:24:47.077timestamp>
                       <items>
                           <item>
                               <itemguid>6A639774-5EA9-440C-978B-FDC7DBF71949itemguid>
                               <quantity>2quantity>
                               <fromsite>001fromsite>
                               <tosite>002tosite>
                           item>
                           <item>
                               <itemguid>D8FD406F-8CF2-47E6-B09D-E44106153C11itemguid>
                               <quantity>2quantity>
                               <fromsite>001fromsite>
                               <tosite>002tosite>
                           item>
                       items>
                   transfer>
               transfers>
 
        'read the xElement into an xDocument. This is the easiest and clearest way to code the example
        xDoc = XDocument.Parse(eDoc.ToString)
 
        'create a collection of xElements
        Dim transferNodes As IEnumerable(Of XElement)
 
        'LINQ query to read the TRANSFERS into our collection
        transferNodes = _
            From el In xDoc.<transfers>.Elements() _
            Select el
 
        'loop through the collection of TRANSFERS
        For Each el As XElement In transferNodes
            'delare a new transfer class
            oTransfer = New Transfer
 
            'initialize the transferDetail Generic.List
            oTransfer.transferDetail = New List(Of TransferDetail)
 
            'assign some of the transfer class properties. Note how we get the transferNumber attribute here
            oTransfer.transferNumber = el.@transferNumber
            oTransfer.timestamp = el.<timestamp>.Value
 
            'the transfer document has childern - items
            'declare a collection of items
            Dim itemNodes As IEnumerable(Of XElement)
 
            'LINQ query to loop through the collection of item elements
            itemNodes = From ie In el.<items>.Elements _
                           Select ie
 
            'loop through the colleciton of ITEMS
            For Each ie As XElement In itemNodes
                'delcare a new transfer detail class
                oTransferDetail = New TransferDetail
 
                'assign some of the class properties
                oTransferDetail.itemguid = New Guid(ie.<itemguid>.Value)
                oTransferDetail.quantity = ie.<quantity>.Value
                oTransferDetail.tosite = ie.<tosite>.Value
                oTransferDetail.fromsite = ie.<fromsite>.Value
 
                'add the new transferdetail class to the parent transfer class
                oTransfer.transferDetail.Add(oTransferDetail)
            Next
        Next
 
 
 
 
    End Sub
End Class
 

More ...

Linq to XML: Reading an XML doc into a Class

steve@4penny.net — Tue, 15 Mar 2011 20:27:55 GMT

This code sample will show you how to take an XML document and parse it into VB elements (in this example I use a class)

First create the class:

Code Snippet
 Public Class Item
    Public Property itemnmbr As String
    Public Property itemguid As Guid
    Public Property active As String
    Public Property description As String
    Public Property category As String
    Public Property commissionamt As Double
    Public Property listprice As Double
    Public Property reorderpt As Int32
 
End Class
 

This code will take the XML document shown are read it into the class. You’d want a Generic Collection of this class to get all the items, I’m going for simplicity here as much as possible

Code Snippet
 Dim xDoc As XDocument
Dim xElem As XElement
 
xElem = <items>
            <item guid="6A639774-5EA9-440C-978B-FDC7DBF71949">
                <SKUid>AAA001SKUid>
                <active>Yactive>
                <description>SD128 RAMdescription>
                <category>Electronicscategory>
                <commissionamt>0.00commissionamt>
                <listprice>9.99listprice>
                <reorderpt>1reorderpt>
            item>
            <item guid="D8FD406F-8CF2-47E6-B09D-E44106153C11">
                <SKUid>AAA02SKUid>
                <active>Yactive>
                <description>SD256 RAMdescription>
                <category>Electronicscategory>
                <commissionamt>0.00commissionamt>
                <listprice>19.99listprice>
                <reorderpt>5reorderpt>
            item>
        items>
 
xDoc = XDocument.Parse(xElem.ToString)
 
Dim childElements As IEnumerable(Of XElement)
 
childElements = _
    From el In xDoc.<items>.Elements() _
    Select el
 
For Each el As XElement In childElements
    oItem = New Item
    oItem.itemguid = New Guid(el.@guid)
    oItem.itemnmbr = el.<SKUid>.Value
    oItem.active = el.<active>.Value
    oItem.description = el.<description>.Value
    oItem.category = el.<category>.Value
    oItem.commissionamt = el.<commissionamt>.Value
    oItem.listprice = el.<listprice>.Value
    oItem.reorderpt = el.<reorderpt>.Value
Next
 

More ...

LINQ to DataTable – summarize a table and then iterate through the groups

steve@4penny.net — Mon, 21 Feb 2011 20:24:42 GMT

I’m… just not a big fan of LINQ. It’s hard to work with, the syntax is obscure and it’s hard to find documentation for. I’m not sure if it’s something that will be around in the future.

Anyway, I have this task that reoccurs every so often – given a DataTable, I need to group the table by one (or more) of the fields and do something with the group. Often I need to iterate through the lines in the table that match a group.

Note that the first query returns one field, so when you reference that field in ‘salesQueryItem’, you only need strTo = salesQueryItem. In the second loop there is more than one field in the select, so they’re itemized.

Code Snippet
 Dim oDT As New DataTable
 
'create columns
oDT.Columns.Add(New DataColumn("salesperson", System.Type.GetType("System.String")))
oDT.Columns.Add(New DataColumn("order", System.Type.GetType("System.String")))
oDT.Columns.Add(New DataColumn("amount", System.Type.GetType("System.Int16")))
 
'create the rows
oDT.Rows.Add("Bob@test.com", "ORD00100", 1.23)
oDT.Rows.Add("Bob@test.com", "ORD00102", 3.23)
oDT.Rows.Add("Sam@test.com", "ORD00104", 2.23)
oDT.Rows.Add("Sam@test.com", "ORD00105", 4.23)
 
 
Dim strBody As String = ""
Dim strTo As String = ""
Dim strSubject As String = "Product Hold Email Notification"
 
Try
    Dim salesQuery = From sales In oDT _
               Group sales By salesperson1 = sales("salesperson") Into Group _
               Select salesperson1 = salesperson1
 
 
    For Each salesQueryitem In salesQuery
        strTo = salesQueryitem
        strBody = "Orders for " & strTo & "
"
 
        Dim Query2 = From emailNotifications In oDT _
                      Where emailNotifications("salesperson") = strTo _
                      Select order1 = emailNotifications("order"), amount1 = emailNotifications("amount").ToString
 
        For Each Query2item In Query2
            strBody += Query2item.order1 + "
"
            strBody += "Amount: " & Query2item.amount1 + "
"
        Next
 
        'code to send an email here
    Next
 
Catch ex As Exception
    MsgBox(ex.Message)
End Try
 

More ...

Linq: How to read one node in an XML file

steve@4penny.net — Thu, 17 Jun 2010 17:02:17 GMT

I frequently have to read from XML files, the general format of them is something like this:

<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <POPReceivingsType>
    <taUpdateCreateVendorRcd>
    taUpdateCreateVendorRcd>
    <taCreateVendorAddress>
    taCreateVendorAddress>
    <taPopRcptLineInsert_Items>
      <taPopRcptLineInsert>
        <PONUMBER>PO08/00012PONUMBER>
        <LOCNCODE>VA-PIEDMONTBLOCNCODE>
      taPopRcptLineInsert>
      <taPopRcptLineInsert>
        <PONUMBER>PO08/00012PONUMBER>
        <LOCNCODE>VA-PIEDMONTALOCNCODE>
      taPopRcptLineInsert>
    taPopRcptLineInsert_Items>
    <taPopRcptHdrInsert>
      <POPRCTNM>RC08/00028POPRCTNM>
      <POPTYPE>1POPTYPE>
    taPopRcptHdrInsert>
  POPReceivingsType>
eConnect>

Here’s a simple piece of code that will read the first  node and

give me the element contained in it.

In earlier .NET Framework versions I had to set references and include ‘IMPORTS’

directives, 4.0 is not requiring that.


Public Class Form2
 
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles MyBase.Load
        test()
    End Sub
 
    Private Sub test()
        Dim strPOnumber As String
        Dim strLOCNCODE As String
 
        Dim doc As System.Xml.Linq.XDocument = _

XDocument.Load(Application.StartupPath & "\xmlfile1.xml")
 
        Dim query = _
            From c In doc.<eConnect>.<POPReceivingsType>.<taPopRcptLineInsert_Items> _
            Select c.Nodes.First
 
        For Each g As XElement In query
            strPOnumber = g.<PONUMBER>(0).Value
            strLOCNCODE = g.<LOCNCODE>(0).Value
        Next
 
    End Sub
End Class

More ...

LINQ: How to read one record from an XML file

steve@4penny.net — Thu, 03 Jun 2010 11:46:30 GMT

Recently I had a project where I needed to have a small configuration file/table that a user could maintain, possibly add records to if needed. The overhead to put that in SQL was a little too much – I’d need to write forms for the maintenance, handle security… it would take a bit to do all that.

Instead, it seemed easier to write an XML file to handle the task and to allow the user FTP access to the web site. So, given an XML file, how do I get access to all the fields in one ‘record’? LinqToXML to the rescue.

First, create a class to represent the record

Public Class eCard
    Public cardID As Int16
    Public image As String
    Public imageThumb As String
    Public subject As String
    Public label As String
End Class

Then, here is code to read the XML file and populate the class. This is a ASP DataList ItemCommand method, the DataList as the ‘cardID’ in the CommandArgument.

Imports System.Data
Imports System.Xml.Linq
Imports System.Linq
Imports System.Xml

    Protected Sub lstCards_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles lstCards.ItemCommand
        Dim doc = XDocument.Load(Server.MapPath("/eCards/eCard.xml"))
        Dim cardID As Int16 = e.CommandArgument
        Dim query = _
            From c In doc.. _
            Where c..Value = cardID _
            Select c
        Dim eCard As New eCard
        For Each g In query
            eCard.cardID = cardID
            eCard.image = g.(0).Value
            eCard.imageThumb = g.(0).Value
            eCard.subject = g.(0).Value
            eCard.label = g.(0).Value
        Next
        Session("eCard") = eCard
        Response.Redirect("eCards2.aspx")
    End Sub

More ...

Sum a data table using LINQ

steve@4penny.net — Mon, 24 Aug 2009 14:16:10 GMT

In a recent project, I needed to sum data in a web page grid and process it on postback.

I could get it into at datatable easily enough, but getting it grouped and summed was stumping me.

Our columns are ITEMNMBR, QUANTITY, LOTNUMBER, DEX_ROW_ID. We’ll group on ITEMNMBR and DEX_ROW_ID. We’ll sum on QUANTITY

So, the data might be:
ITEM1, 5, LOT A, 1001
ITEM1, 6, LOT B, 1001
ITEM2, 1, , 1002
ITEM3, 2, ,1003

Lines 1 and 2 above have the same item number and row id, but 2 different lot numbers.

The final result needs to be:
ITEM1, 11
ITEM2, 1
ITEM3 2

The ‘groupRow’ item in the code below holds a set of rows of the underlying group. In the second section of code we loop through that object. Note that there are two quantity results, the group quantity (11) and the line quantity (5,6)

Here’s the solution. It uses LINQ, so add a reference to SYSTEM.LINQ at the top of the page. Note the use of a system function to do the summing; it’s declared in the second line

     Dim oDT As DataTable = gridToDataTable(Me.Grid1)

    Dim aggregateSum As System.Func(Of DataRow, Integer) = AddressOf GetSum

    Dim data = From c In oDT.AsEnumerable() _

               Group c By itemGroup = c.Field(Of String)("itemnmbr"), _

                    dexGroup = c.Field(Of Int32)("dex_row_id") Into Group _

               Select groupRow = Group, _

                    itemGroup = itemGroup, _

                    quantity = Group.Sum(aggregateSum), _

                    dexGroup = dexGroup

    For Each g In data

        strItemnmbr = g.itemGroup

        dblItemSellingQtyReceived = g.quantity

        intDex_row_id = g.dexGroup

        For Each n In g.groupRow

            strSerltnum = n("serltnum")

            dblLotSellingQtyReceived = n("quantity")

        Next

    Next

The function:

    Public Function GetSum(ByVal datarow As DataRow) As Integer

        Return Integer.Parse(datarow("quantity").ToString())

    End Function

More ...