Monday, 7 January 2013

Read Xml Data into dataSet using VB.NET Bind to GridView


This example loads/imports XML file into DataSet, and queries dataset and binds it to GridView in asp.net

Read Xml Data into dataSet using C# Bind to GridView

1)    Add Project Books.xml file

<?xml version="1.0" encoding="utf-8" ?>

<bookslist>
  <book id="ISBN-220-244">
    <title>LINQ Unleased</title>
    <price>$40.99</price>
    <authors>
      <author>
        <firstname>Kimmel </firstname>
        <lastname>Paul</lastname>
      </author>
    </authors>
    <publisher>Sams</publisher>
  </book>
  <book id="ISBN-456-344">
    <title>C# Design Patterns</title>
    <price>$30.99</price>
    <authors>
      <author>
        <firstname>john</firstname>
        <lastname>peter</lastname>
      </author>
      <author>
        <firstname>mary</firstname>
        <lastname>kurl</lastname>
      </author>
    </authors>
    <publisher>Tata Mcgraw Hill</publisher>
  </book>
  <book id="ISBN-434-233">
    <title>Pro Entity Framework 4.0</title>
    <price>29.99</price>
    <authors>
      <author>
        <firstname>Scott</firstname>
        <lastname>Klein</lastname>
      </author>
    </authors>
    <publisher>APress</publisher>
  </book>
  <book id="ISBN-433-234">
    <title>Beginning PHP and MySQL E-Commerce</title>
    <price>$35.99</price>
    <authors>
      <author>
        <firstname>Cristian</firstname>
        <lastname>Darie</lastname>
      </author>
      <author>
        <firstname>Emilian</firstname>
        <lastname>Balanescu</lastname>
      </author>
    </authors>
    <publisher>APress</publisher>
  </book>
</bookslist>

2) Read books.xml into dataSet

                XmlReadMode xrm = ds.ReadXml(Server.MapPath("/data/Books.xml"),
XmlReadMode.InferSchema);

                GridView1.DataSource = ds.Tables[0].DefaultView;
                GridView1.DataBind();

3)   Book has 1 or more authors

         So  one - Many  relationship will be created.

DataSet has  3 tables
  •  Table1  is book
  • Table2  authors
  • Table3 author
 book  table has  all xml elements and attributes of xml node book

authors table has book_id and authors_id columns(created by ReadXml)

author table has authors_id and firestname and lastname.(Sub elements of authors)

So for getting complete details about book,  we need to fetch authors_id from second table(authors)

use this authors_id ,to fetch book's author's firstname and last name

     Public Function GetAuthors(ByVal bookid As Object) As String
            Dim strAuthors As String =  String.Empty
            Dim filterExp As String =  "book_id=" Int32.Parse(bookid.ToString())
            DataRow()SelRows
                =ds.Tables(1).Select(filterExp)

            Dim AuthorID As Integer =  -1
            Dim row As DataRow
            For Each row In SelRows
                AuthorID = Int32.Parse(row("authors_id").ToString())
            Next
            filterExp = "authors_id=" AuthorID
            SelRows
                     Dim  As =
            Dim row As DataRow
            For Each row In SelRows
                strAuthors = "<b>firstName:</b>" row("firstname") "<br/>"
                    "<b>lastName:</b>" row("lastname") "<br/>"
            Next
            Return strAuthors
      End Function
 
4) Bind it GridView in asp.net

<h1>Read Xml Data into dataSet using C# and Bind it to GridView</h1>
                <asp:GridView ID="GridView1"
                    runat="server"
                    EmptyDataText="No Data Found"
                    AutoGenerateColumns="false">
                    <Columns>
                        <asp:BoundField NullDisplayText="no values" DataField="title" HeaderText="Title" />
                        <asp:BoundField NullDisplayText="no values" DataField="price" HeaderText="Price" />
                        <asp:BoundField NullDisplayText="no values" DataField="id" HeaderText="Book Id" />
                        <asp:BoundField NullDisplayText="no values" DataField="publisher" HeaderText="Publisher" />
                        <asp:BoundField NullDisplayText="no values" DataField="book_id" HeaderText="ID" />
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:Label
                                    Text='<%# GetAuthors(Eval("book_id")) %>'
                                    runat="server"></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <%--<asp:BoundField NullDisplayText="no values" DataField=  HeaderText="Authors" />--%>
                    </Columns>
                </asp:GridView>


Here is the Output

Read Xml Data into dataSet using C# Bind to GridView
Tags:Read Xml Data into dataSet using VB.NET Bind to GridView, Load Xml into Dataset, Read xml into Dataset,Import Xml into dataset, How to load xml into a datatable,How to convert xml file to datatable,Open and read xml file into dataset,Read XML File Into DataTable In ASP.NET VB.NET