none
Data Table Compute method RRS feed

  • Question

  •  

    Hi,

    i have 2 questions

    1)

    The datatable has a compute method.I am using it to evaluate an expression

    Dim dtbX as New DataTable

    dtbX.Compute("1+1","") the result returned will be 2.

    Can i use the same to give an absolute value of the expression returned

    eg dtbX.Compute("Abs(-2+1)","")

    .I know that i can get the result and do an abolute value.The thing is the expression that is formed is configurable

    something like a formula builder where i construct and expression

    "(A-B) -1" i then split this expression get the values for A,B if A = 5 ,B=10 then expression becomes

    (5-10) -1 ,if i give this to the dtbX.Compute it will give me -6. I was thinking of giving an option in the formula builder to specify absolute value say "Abs(A-B)-1" then trying to do a split on this and get values of A and B

    and form an expression "Abs(5-10)-1" which i can send to the dtbX.Compute method.

     

     

    2) if i have a datatable dtbX with columns SalesId  of type int  ,SalesDate of type datetime

     

    i know i can do a dtbX.Select("SalesDate>'1/1/2008'")

     

    but it does not allow me do the following select where mont of the sales date is september and year is  2008

    we can do this in sql server

    dtbX.Select("month(SalesDate)=9 And Year(SalesDate)=2008") dfoes not work

    Any ideas.

     

     

     

     

    Thanks,

    Anand.

     

     

    Saturday, October 11, 2008 7:17 AM

Answers

  • 1) Don't use "Abs" in DataTable.Select, DataTable.Compute, DataColumn.Expression, DataView.RowFilter.  The functionality never worked correctly in V1.1 or V2.0, the Abs of anything is always null.

     

    2) using dtbX.Select("SalesDate > Convert('1/1/2008', System.DateTime");  or dtbX.Select("SalesDate > #1/1/2008#") may work better so you compare.

     

    I'd recommend looking at Linq to DataSet functionality.  Then your select can look something like:

    Code Snippet

    DataTable table = new DataTable();

    table.Columns.Add("ID", typeof(int));

    table.Columns.Add("SalesDate", typeof(DateTime));

     

    table.Rows.Add(new object[] { 1, new DateTime(2008, 9, 12) });

    table.Rows.Add(new object[] { 2, new DateTime(2008, 10, 16) });

     

    IEnumerable<DataRow> rows =

        from a in table.AsEnumerable()

        where (a.Field<DateTime>("SalesDate").Month == 9) &&

              (a.Field<DateTime>("SalesDate").Year == 2008)

        select a;

     

    foreach (DataRow row in rows)

        Console.WriteLine("ID={0}, SalesDate={1:d}", row.ItemArray);

     

    Friday, October 17, 2008 1:58 AM
    Moderator
  • These two could work by bounding the region

     

    Code Snippet
    DataView view = new DataView(table, "SalesDate >= #2008/9/1# and SalesDate < #2008/10/1#", "", DataViewRowState.CurrentRows);

     

    or

    Code Snippet

    DataRow[] rows = table.Select("SalesDate >= #2008/9/1# and SalesDate < #2008/10/1#", "", DataViewRowState.CurrentRows);

     

     

    Friday, October 24, 2008 3:56 PM
    Moderator
  • At that point, just scanning the table may be the easiest.

     

    List<DataRow> rows = new List<DataRow>();

    foreach(DataRow row in table) {

      DateTime salesdate = (DateTime)row[salesDateColumn];

      if (saledate.Month == 9) {

         rows.Add(row);

      }

    }

    Wednesday, October 29, 2008 3:57 PM
    Moderator

All replies

  • 1) Don't use "Abs" in DataTable.Select, DataTable.Compute, DataColumn.Expression, DataView.RowFilter.  The functionality never worked correctly in V1.1 or V2.0, the Abs of anything is always null.

     

    2) using dtbX.Select("SalesDate > Convert('1/1/2008', System.DateTime");  or dtbX.Select("SalesDate > #1/1/2008#") may work better so you compare.

     

    I'd recommend looking at Linq to DataSet functionality.  Then your select can look something like:

    Code Snippet

    DataTable table = new DataTable();

    table.Columns.Add("ID", typeof(int));

    table.Columns.Add("SalesDate", typeof(DateTime));

     

    table.Rows.Add(new object[] { 1, new DateTime(2008, 9, 12) });

    table.Rows.Add(new object[] { 2, new DateTime(2008, 10, 16) });

     

    IEnumerable<DataRow> rows =

        from a in table.AsEnumerable()

        where (a.Field<DateTime>("SalesDate").Month == 9) &&

              (a.Field<DateTime>("SalesDate").Year == 2008)

        select a;

     

    foreach (DataRow row in rows)

        Console.WriteLine("ID={0}, SalesDate={1:d}", row.ItemArray);

     

    Friday, October 17, 2008 1:58 AM
    Moderator
  • i Am using .net V2.0 we , i tried to add System.Linq namespace which requires System.Core reference but when trying to add .NET reference's System.Core is not listed.

     

    Friday, October 24, 2008 5:02 AM
  •  

    is there any way to achieve the below  

    using ado.net

    IEnumerable<DataRow> rows =

        from a in table.AsEnumerable()

        where (a.Field<DateTime>("SalesDate").Month == 9) &&

              (a.Field<DateTime>("SalesDate").Year == 2008)

        select a;

     

    Thanks,

    Anand.

     

    Friday, October 24, 2008 5:03 AM
  • These two could work by bounding the region

     

    Code Snippet
    DataView view = new DataView(table, "SalesDate >= #2008/9/1# and SalesDate < #2008/10/1#", "", DataViewRowState.CurrentRows);

     

    or

    Code Snippet

    DataRow[] rows = table.Select("SalesDate >= #2008/9/1# and SalesDate < #2008/10/1#", "", DataViewRowState.CurrentRows);

     

     

    Friday, October 24, 2008 3:56 PM
    Moderator
  • thanks  nice approach ,dint think of it from that angle.

     

    Monday, October 27, 2008 6:46 AM
  •  

    DataRow[] rows = table.Select("SalesDate >= #2008/9/1# and SalesDate < #2008/10/1#", "", DataViewRowState.CurrentRows);

     

    in the above we can get only for the september in 2008 is there any way to do this irrespective of the year

    like if my data has many september months spanning across different years,also is there a way to say get rows only for the year 2008 or 2009 also is there any way to get the day type like select where the day of the date is 0 assume that 0 is a sunday ,1 is a monday etc

     

    Tuesday, October 28, 2008 1:27 PM
  • At that point, just scanning the table may be the easiest.

     

    List<DataRow> rows = new List<DataRow>();

    foreach(DataRow row in table) {

      DateTime salesdate = (DateTime)row[salesDateColumn];

      if (saledate.Month == 9) {

         rows.Add(row);

      }

    }

    Wednesday, October 29, 2008 3:57 PM
    Moderator