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#