locked
read xml file using LINQ and retun data table at last RRS feed

  • Question

  • User264732274 posted
    public DataTable SupplyPageOfData(int lowerPageBoundary, int rowsPerPage)
            {
                // Store the name of the ID column. This column must contain unique  
                // values so the SQL below will work properly. 
                if (columnToSortBy == null)
                {
                    columnToSortBy = this.Columns[0].ColumnName;
                }
    
                if (!this.Columns[columnToSortBy].Unique)
                {
                    throw new InvalidOperationException(String.Format(
                        "Column {0} must contain unique values.", columnToSortBy));
                }
    
                // Retrieve the specified number of rows from the database, starting 
                // with the row specified by the lowerPageBoundary parameter.
                command.CommandText = "Select Top " + rowsPerPage + " " +
                    CommaSeparatedListOfColumnNames + " From " + tableName +
                    " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
                    lowerPageBoundary + " " + columnToSortBy + " From " +
                    tableName + " Order By " + sortColumn + 
                    ") Order By " + sortColumn ;
                adapter.SelectCommand = command;
    
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                adapter.Fill(table);
                return table;
            }

    where i am doing paging on order table and return data table.

    1) when i will query the same data by linq from xml file then how could i write the same query by linq. where order by clause will be there and pagination will be there.

    so please show me how to customize the below linq query for order and pagination

    var items = xmldoc.Descendants("whatevernodename")
               .Select(node => node.Value.ToString())
               .Skip(PageTimes100)
               .Take(100)
               .ToList();

    2) i have another routine which will read xml file and return total no of records from xml file with linq ?

    please answer point wise with code sample. thanks

    Sunday, July 19, 2015 7:22 AM

Answers

  • User-271186128 posted

    Hi sudip_inn,

    As for the first question, you could use OrderBy method to sort the result and use the Take<TSource> operator and the Skip<TSource> operator to achieve paging.

    Please refer to the following sample:

    XML files:

    <?xml version="1.0" encoding="utf-8"?>
    <Setting>
      <Details>
        <id>10004</id>
        <posted-date>2015-07-02</posted-date>
        <source>CCC.com</source>
        <summary>Hello CCC</summary>
      </Details>
      <Details>
        <id>10005</id>
        <posted-date>2015-07-03</posted-date>
        <source>DDD.com</source>
        <summary>Hello DDD</summary>
      </Details>
    
      <Details>
        <id>10003</id>
        <posted-date>2015-07-01</posted-date>
        <source>BBB.com</source>
        <summary>Hello BBB</summary>
      </Details>
      <Details>
        <id>10001</id>
        <posted-date>2015-04-20</posted-date>
        <source>aaaa.com</source>
        <summary>Hello world</summary>
      </Details>
      <Details>
        <id>10002</id>
        <posted-date>2015-06-20</posted-date>
        <source>booking.com</source>
        <summary>I feel like going on home.</summary>
      </Details>
      <Details>
        <id>10006</id>
        <posted-date>2015-07-04</posted-date>
        <source>EEE.com</source>
        <summary>Hello EEE</summary>
      </Details>
    </Setting>

    Code in page (.aspx.cs)

           protected void Page_Load(object sender, EventArgs e)
            {
                XElement xmldoc = XElement.Load(Server.MapPath("XMLFile1.xml"));
    
                var query2 = xmldoc.Descendants("Details")
                    .OrderBy(cc => cc.Element("posted-date").Value)
                    .Select(cc => new { 
                        ID = cc.Element("id").Value, 
                        PostedDate = cc.Element("posted-date").Value, 
                        Source = cc.Element("source").Value, 
                        Summary = cc.Element("summary").Value })
                        .Skip(2)
                        .Take(3); 
    
                GridView1.DataSource = query2.ToList();
                GridView1.DataBind();
            }

    As for the second question, if you want to get the total number of the records. You could use the Count method. You could refer to the following code:

        var count = xmldoc.Descendants("Details").Select(cc => cc).Count();
                Response.Write("Total count: " + count);
    

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 19, 2015 10:59 PM