Sunday, 30 December 2012

Binding Gridview using LINQ to XML vb.net


Binding Gridview using LINQ to XML vb.net

This example queries XMl file using LINQ to XML and binds data to gridview using VB.NET

 

Step 1) Create a Web Page 

                Add gridview 
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Binding Gridview using LINQ to XML</h1>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>


 Step2)   Add Xml file to Web Project, name it as Salesorder.xml
<?xml version="1.0" encoding="utf-8" ?>

<HeaderLines>
  <SalesOrder>
    <Header Date="17-02-04" Type="Quote">
      <SellTo CountryRegion="GB">
       <Name>The Cannon Group PLC</Name>
       <Address>192 Market Square</Address>
       <City>Birmingham</City>
       <Zip>B27 4KT</Zip>
     </SellTo>
     <BillTo CountryRegion="GB">
       <Name>The Cannon Group PLC</Name>
       <Address>192 Market Square</Address>
       <City>Birmingham</City>
       <Zip>B27 4KT</Zip>
     </BillTo>
     <Lines>
       <Item PartNum="LS-150">
         <ProductName>Loudspeaker, Cherry, 150W</ProductName>
         <Quantity>8</Quantity>
         <UnitPrice>12900</UnitPrice>
         <ShipmentDate />
         <Comment>Confirm the voltage is 75W</Comment>
       </Item>
          <Item PartNum="LS-MAN-10">
          <ProductName>Manual for Loudspeakers</ProductName>
          <Quantity>20</Quantity>
            <UnitPrice>100</UnitPrice>
          <ShipmentDate />
          <Comment />
       </Item>
          <Item PartNum="LS-2">
            <ProductName>Cables for Loudspeakers</ProductName>
            <Quantity>10</Quantity>
            <UnitPrice>2100</UnitPrice>
            <ShipmentDate />
            <Comment />
            </Item>
     </Lines>
     <Contact>Mr. Andy Toal</Contact>
     <Terms>14 days</Terms>
    </Header>
  </SalesOrder>
</HeaderLines>

Step 3)  Query XML file using LINQ to XML and Bind it to GridView


Private  Sub LoadXml()
            Try
                Dim doc As XDocument =  XDocument.Load(MapPath("SalesOrder.xml"))

                var query = from ele Function doc.Descendants(ByVal"Item") As in
                                ProductName = ele.Element("ProductName").Value,
                                Quanity = ele.Element("Quantity").Value,
                                UnitPrice = ele.Element("UnitPrice").Value,
                                Total = Convert.ToInt32(ele.Element("Quantity").Value) *
                                        Convert.ToDouble(ele.Element("UnitPrice").Value)
                End Function

                GridView1.DataSource = query.ToList() 'Bind it to GridView
                GridView1.DataBind()
            Catch ex As Exception

            End Try
End Sub


Step 4) Call LoadXml in on Page_Load() Method.

Step 5) Run the Page.