Monday, 4 June 2012

C# Programming : Join in XML Data files Using LINQ Query in LINQ to XML

Using LINQ to XML you can also join multiple files and get result based on LINQ query.
LINQ to XML uses System.Xml.Linq namespace.

Here are example for this.

In this examples we have to XML files first "Books.xml" and second "Publishers.xml",
we are joining this two files with one common field "PublisherId". We are fetching "PublisherName" from "Publishers.xml" file to display in grid.

Here are examples :

XML File : (Books.xml)
<?xml version="1.0" encoding="utf-8" ?>
<Books>
   <Book>
    <Title>ASP.NET</Title>
    <ISBN>asp1</ISBN>
    <ReleaseDate>11/11/2010</ReleaseDate>
    <Pages>200</Pages>
    <PublisherId>1</PublisherId>
  </Book>
  <Book>
    <Title>C#.NET</Title>
    <ISBN>c#2</ISBN>
    <ReleaseDate>10/11/2010</ReleaseDate>
    <Pages>500</Pages>
    <PublisherId>1</PublisherId>
  </Book>
  <Book>
    <Title>VB.NET</Title>
    <ISBN>vb3</ISBN>
    <ReleaseDate>5/5/2009</ReleaseDate>
    <Pages>400</Pages>
    <PublisherId>1</PublisherId>
  </Book>
  <Book>
    <Title>SQL Server</Title>
    <ISBN>sql4</ISBN>
    <ReleaseDate>6/9/2010</ReleaseDate>
    <Pages>300</Pages>
    <PublisherId>2</PublisherId>
  </Book>
  <Book>
    <Title>JAVA</Title>
    <ISBN>java5</ISBN>
    <ReleaseDate>8/5/2011</ReleaseDate>
    <Pages>400</Pages>
    <PublisherId>3</PublisherId>
  </Book>
</Books>

XML File : (Publishers.xml)
<?xml version="1.0" encoding="utf-8" ?>
<Publishers>
  <Publisher>
    <PublisherId>1</PublisherId>
    <PublisherName>Microsoft</PublisherName>
  </Publisher>
  <Publisher>
    <PublisherId>2</PublisherId>
    <PublisherName>Wrox</PublisherName>
  </Publisher>
  <Publisher>
    <PublisherId>3</PublisherId>
    <PublisherName>Sun Publications</PublisherName>
  </Publisher>
</Publishers>

ASPX Code :
<form id="form1" runat="server">
    <div>
        <asp:GridView ID="gvBooks" runat="server">
        </asp:GridView>
    </div>
</form>

C# Example :
    protected void Page_Load(object sender, EventArgs e)
    {
        var query = from bl in
                    XElement.Load(MapPath("Books.xml")).Elements("Book")
                    join g in XElement.Load(MapPath("Publishers.xml")).Elements("Publisher")
                    on (int)bl.Element("PublisherId") equals (int)g.Element("PublisherId")
                    select new 
                    {
                        Title = (string)bl.Element("Title"),
                        ISBN = (string)bl.Element("ISBN"),
                        ReleaseDate = (DateTime)bl.Element("ReleaseDate"),
                        Pages = (int)bl.Element("Pages"),
                        PublisherName = (string)g.Element("PublisherName")
                    };

        gvBooks.DataSource = query;
        gvBooks.DataBind();
    }

VB.net Example :
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim query = From bl In XElement.Load(MapPath("Books.xml")).Elements("Book") _
                    Join g In XElement.Load(MapPath("Publishers.xml")).Elements("Publisher") _
                    On CInt(bl.Element("PublisherId")) Equals CInt(g.Element("PublisherId")) _
                    Select New With { _
                        .Title = CStr(bl.Element("Title")), _
                        .ISBN = CStr(bl.Element("ISBN")), _
                        .ReleaseDate = CDate(bl.Element("ReleaseDate")), _
                        .Pages = CInt(bl.Element("Pages")), _
                        .PublisherName = CStr(g.Element("PublisherName")) _
                    }
        gvBooks.DataSource = query
        gvBooks.DataBind()
    End Sub

Output :

1 comment: