Monday 7 January 2013

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  String GetAuthors(object bookid)
        {
            String strAuthors = String.Empty;
            String filterExp = "book_id=" + Int32.Parse(bookid.ToString()) ;
            DataRow[]SelRows
                =ds.Tables[1].Select(filterExp);

            int AuthorID = -1;
            foreach (DataRow row in SelRows)
            {
                AuthorID = Int32.Parse(row["authors_id"].ToString());
            }
            filterExp = "authors_id=" + AuthorID;
            SelRows
                     = ds.Tables[2].Select(filterExp);
            foreach (DataRow row in SelRows)
            {
                strAuthors += "<b>firstName:</b>" + row["firstname"] + "<br/>" +
                    "<b>lastName:</b>" + row["lastname"] + "<br/>";
            }
            return strAuthors;
        }
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 C# 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 C#

2 comments:

  1. i need to fetch a node with condition.

    for example:
    In my xml file i have status node with true or false value.I need to populate only the node which has True status.

    Thanks in advance...

    ReplyDelete
  2. hey that is coool and awsome it helped me alottt, but i am styck in 3rd level
    like 1 author have address node which have Country,State,Zip so how can i get those address? see below
    Author
    |->FirstName
    |->LastName
    Address
    |->Country
    |->State
    |->Zip
    Address is inside Author tag but it also contains 3 nodes

    ReplyDelete