views:

105

answers:

1

I sort the records of the datatable datewise with the column TradingDate which is type of datetime.

TableWithOnlyFixedColumns.DefaultView.Sort = "TradingDate asc";

Now I want to store these sorted records into csv file but stored records are not sorted by date.

 TableWithOnlyFixedColumns.DefaultView.Sort = "TradingDate asc";
  DataTable newTable = TableWithOnlyFixedColumns.Clone();
  newTable.DefaultView.Sort = TableWithOnlyFixedColumns.DefaultView.Sort;
  foreach (DataRow oldRow in TableWithOnlyFixedColumns.Rows)
  {
     newTable.ImportRow(oldRow);
  }
  // we'll use these to check for rows with nulls
  var columns = newTable.DefaultView.Table.Columns.Cast<DataColumn>();
  using (var writer = new StreamWriter(@"C:\Documents and Settings\Administrator\Desktop\New.csv"))
  {
     for (int i = 0; i < newTable.DefaultView.Table.Rows.Count; i++)
     {
        DataRow row = newTable.DefaultView.Table.Rows[i];
        // check for any null cells
        if (columns.Any(column => row.IsNull(column)))
        continue;
       string[] textCells = row.ItemArray
      .Select(cell => cell.ToString()) // may need to pick a text qualifier here
      .ToArray();
      // check for non-null but EMPTY cells
      if (textCells.Any(text => string.IsNullOrEmpty(text)))
      continue;
      writer.WriteLine(string.Join(",", textCells));
    }
 }

So how to store sorted records in csv file ?

+1  A: 

This line of code;

DataRow row = newTable.DefaultView.Table.Rows[i]; 

is referencing the unsorted DataTable behind the DataView. You need to use a DataRowView instead of a DataRow and access the sorted rows from the DataView;

DataRowView row = newTable.DefaultView[i]; 
Matt
@Matt,then I can not get the method isNull() for the row which is below that line of code.
Harikrishna
you can access the isNull() method through the Row property, row.Row.IsNull, might want to change your variable name :-)
Matt
@Matt,Thank You it works.But I don't understand the concept of that when we sort the records of the datatable it does not sorted physically and according to we have sorted the records the datatable is not changed ?
Harikrishna
A DataView is a similar concept to a view in SQL, it just provides another way of viewing the raw data. Changing the physical order of the data, in either a database or in memory datatable is a fairly expensive process.
Matt
@Matt,Then when we sort the datatable records using defaultView,it just virtually sorted records ? How can we sort the datatable records physically ?
Harikrishna
the quickest way to sort the records physically would be to change the underlying stored procedure/sql so that the data is loaded into the datatable in the correct order. Alternatively you could create a new empty DataTable and loop through the DataView adding the rows in the sorted order, but this isn't very efficient.
Matt
@Matt,Thanks friend,But if I want to more about this have you any good link of articles or any else thing ? +1 for the great answer.
Harikrishna
No problem, Microsoft is always a good starting point, http://msdn.microsoft.com/en-us/library/ss7fbaez.aspx, and the following link contains some decent examples; http://www.knowdotnet.com/articles/dataviewspart2.html
Matt
@Matt, Thanks..
Harikrishna