CODEWITHSUNDEEP
c#asp.netlinqdatatable
Talha Malik
Talha Malik

Reputation: 1559

C#: Sort DATATABLE with column date

I know it is a repeated question but i have tried much but i am getting exception

  • $exception {"String was not recognized as a valid DateTime."} System.Exception {System.FormatException}

Following is my code please check and guide

SQL QUERY

SELECT gangId as gang, respectPoints as respectPoints,DATE_FORMAT( purchasedDate, '%d-%m-%Y') as date_purchase FROM tbl_gang t where gangId=" + gangId

Data Access Layer Code

DataTable dt = new DataTable();
MySqlCommand cmd = conn.CreateCommand();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
cmd.CommandText = inAppPurchaseQuery;
da.Fill(dt)

Sorting Code

dt = gangRPObj.getGangRPLogsBL(gangId, fromDate, toDate);
var sortedTable = dt.AsEnumerable()
             .OrderBy(r => DateTime.ParseExact(("date_purchase"),
                                                "dd-mm-yyyy", null))
             .CopyToDataTable();

Thanks

Upvotes: 1

Views: 1900

Answers (2)

husnain_sys
husnain_sys

Reputation: 571

You are passing string to datetime conversion that's why exception is coming. Try 

var orderedRows = from row in dt.AsEnumerable()
                  let date = DateTime.ParseExact(row.Field<string>("date_purchase"),"dd-mm-yyyy", null)
                  orderby date 
                  select row;

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460138

Sorting a date-column as string also doesn't sort correctly. You should also sort in the database instead of in memory and use parameters instead of string concatenation to prevent sql-injection and date-conversion-issues like this.

string sql = @"SELECT gangId as gang, 
                      respectPoints as respectPoints, 
                      DATE_FORMAT(purchasedDate, '%d-%m-%Y') as date_purchase,
               FROM tbl_gang t 
               WHERE gangId=@gangId
               ORDER BY purchasedDate ASC";
using (var cmd = new MySqlCommand(sql, conn))
using (var da = new MySqlDataAdapter(cmd))
{
    da.SelectCommand.Parameters.Add("@gangId", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = gangID;
    da.Fill(dt);  // no need to order this on client side
}

Upvotes: 1

Related Questions