StrataFrame Forum

Filtering Date in BO

http://forum.strataframe.net/Topic33510.aspx

By Danny Doobay - 8/4/2017

I have three properties in my customers BO. The first one cust_DateofBirth with a data type of DateTime and it is mapped to database datetime field. Other two are custom field property and they are defined in the BO as follows

<ComponentModel.Browsable(False), _
 MicroFour.StrataFrame.UI.Windows.Forms.BusinessFieldDisplayInEditor(), _
 ComponentModel.Description("DOBGMT"), _
 ComponentModel.DesignerSerializationVisibility(ComponentModel.DesignerSerializationVisibility.Hidden)> _
    Public Property DOBGMT() As Date
        Get
            Return DatePart(DateInterval.Year, Me.cust_DateofBirth).ToString & "-" & DatePart(DateInterval.Month, Me.cust_DateofBirth).ToString & "-" & DatePart(DateInterval.Day, Me.cust_DateofBirth).ToString
       End Get
        Set(value As Date)
            Me.cust_DateofBirth = value
        End Set
   End Property

<ComponentModel.Browsable(False), _
 MicroFour.StrataFrame.UI.Windows.Forms.BusinessFieldDisplayInEditor(), _
 ComponentModel.Description("DOByyyyMMdd"), _
 ComponentModel.DesignerSerializationVisibility(ComponentModel.DesignerSerializationVisibility.Hidden)> _
    Public ReadOnly Property DOByyyyMMdd() As String
        Get
          Return cust_DateofBirth.ToString("yyyy-MM-dd")
        End Get
    End Property

 Protected Overrides Function GetCustomBindablePropertyDescriptors() As MicroFour.StrataFrame.Business.FieldPropertyDescriptor()
        '-- Establish Locals
        Dim r As New List(Of FieldPropertyDescriptor)

        '-- Include all of the base descriptors
        Try
            r.AddRange(MyBase.GetCustomBindablePropertyDescriptors())
        Catch ex As Exception
        End Try

        '-- Add the custom descriptors       
        r.Add(New ReflectionPropertyDescriptor("DOBGMT", GetType(CustomersBO)))     
        r.Add(New ReflectionPropertyDescriptor("DOByyyyMMdd", GetType(CustomersBO)))
        '-- Return results
        Return r.ToArray()
 End Function

 Private Sub CustomersBO_CurrentDataTableInitialized() Handles Me.CurrentDataTableInitialized
        If Me.CurrentDataTable.Columns.Contains("DOBGMT") = False Then
            Me.CurrentDataTable.Columns.Add("DOBGMT", GetType(Date))
        End If
     
         If Me.CurrentDataTable.Columns.Contains("DOByyyyMMdd") = False Then
            Me.CurrentDataTable.Columns.Add("DOByyyyMMdd", System.Type.GetType("System.String"))
        End If
 End Sub

 Private Sub CustomersBO_CurrentDataTableRefilled() Handles Me.CurrentDataTableRefilled
        If Me.CurrentDataTable.Columns.Contains("DOBGMT") = False Then
            'Me.CurrentDataTable.Columns.Add("DOBGMT", System.Type.GetType("System.Date"))
             Me.CurrentDataTable.Columns.Add("DOBGMT", GetType(Date))
        End If
        If Me.CurrentDataTable.Columns.Contains("DOByyyyMMdd") = False Then
            Me.CurrentDataTable.Columns.Add("DOByyyyMMdd", System.Type.GetType("System.String"))
        End If
  End Sub

Now when I tried to filter the BO using all three column option I am not getting the result (I have one record in SQL database)

CustomersBO.Filter  = "cust_FirstName = 'John' AND cust_LastName = 'Smith' AND DOBGMT = '1962-09-19'"
CustomersBO.Filter  = "cust_FirstName = 'John' AND cust_LastName = 'Smith' AND DOByyyyMMdd = '1962-09-19'"
CustomersBO.Filter  = "cust_FirstName = 'John' AND cust_LastName = 'Smith' AND cust_DateofBirth = '1962-09-19'"

I tried to use CurrentDataTable with select statement and no result as well
?CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith'  AND DOBGMT = '1962-09-19'").Length
0
?CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith'  AND DOByyyyMMdd = '1962-09-19'").Length
0
?CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith'  AND cust_DateofBirth = '1962-09-19'").Length
0

But when I just use first and last names it is fine in both occasion
CustomersBO.Filter  = "cust_FirstName = 'John' AND cust_LastName = 'Smith'"
?CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith'").Length
1

Any help is appreciated.
By Trent L. Taylor - 8/4/2017

You can go about this a number of different ways.  First, you could use a view and filter out the record.  Since there is a data row within the collection, you can use a filter to filter out the view.  But it looks like you are wanting to select the contents.  So if I were you, use LINQ in this case.  Here is an example I wrote and tested for you.

Just dump the below code into a Console Application and it should work for you.

Now keep in mind, in SF V2 all entities (business objects) are typed entities and don't use ADO.NET, but that doesn't mean that you can't use this type of logic on ADO.NET data tables.  The different is that you first have to get the ADO.NET into a typed format (reference the query below).  Once in that state, you can Select from it, filter it out with a Where, or whatever using lambda expressions and LINQ.  But this is a faster and better method that the weak typed logic of the ADO.NET select.  Let me know if this gets you going.

private static DataTable _Data;

      static void Main(string[] args)
      {
         CreateData();

         //-- Create a query straight from the data table and implement your WHERE logic
         var query = from _Data in _Data.AsEnumerable()
                  where _Data.Field<string>("Last").Equals("smith", StringComparison.OrdinalIgnoreCase)
                  select new
                  {
                     First = _Data.Field<string>("First"),
                     Last = _Data.Field<string>("Last"),
                     Dob = _Data.Field<DateTime>("Dob")
                  };

         //-- Now you can enumerate the query
         foreach(var i in query)
         {
            Console.WriteLine($"First: {i.First} Last: {i.Last} Date of Birth: {i.Dob.ToString("MM/dd/yyyy")}");
         }

         Console.WriteLine("");
         Console.WriteLine("Now filter out using inline LINQ instead of the query");
         Console.WriteLine("");

         //-- You can also leave the WHERE out of the query itself and then use LINQ to filter out the list.
         //   This is my preferred approach in a situation like this.
         foreach(var i in query.Where(i => i.Dob == new DateTime(1952,01,01)))
         {
            Console.WriteLine($"First: {i.First} Last: {i.Last} Date of Birth: {i.Dob.ToString("MM/dd/yyyy")}");
         }



         Console.ReadKey();
      }

      private static void CreateData()
      {
         //-- Create a data table for testing
         _Data = new DataTable("Testing");
         _Data.Columns.Add("First", typeof(string));
         _Data.Columns.Add("Last", typeof(string));
         _Data.Columns.Add("Dob", typeof(DateTime));

         //-- Create some records
         var row = _Data.NewRow();
         row["First"] = "John";
         row["Last"] = "Smith";
         row["Dob"] = new DateTime(1952,01,01);
         _Data.Rows.Add(row);

         row = _Data.NewRow();
         row["First"] = "Neta";
         row["Last"] = "Aberdeen";
         row["Dob"] = new DateTime(1948, 10, 12);
         _Data.Rows.Add(row);

         row = _Data.NewRow();
         row["First"] = "Able";
         row["Last"] = "Smith";
         row["Dob"] = new DateTime(1968, 06, 05);
         _Data.Rows.Add(row);

         row = _Data.NewRow();
         row["First"] = "Jed";
         row["Last"] = "Lazbuddie";
         row["Dob"] = new DateTime(1970, 12, 09);
         _Data.Rows.Add(row);
      } 
By Trent L. Taylor - 8/4/2017

Here is the project.  Thought this would be easier.
By Danny Doobay - 8/4/2017

Thanks for the reply.

I have tested out something similar and it worked fine. But the issue arise when using CustomersBO.

My question is why CustomersBO.Filter  = "cust_FirstName = 'John' AND cust_LastName = 'Smith' AND cust_DateofBirth = '1962-09-19'" 
or
Dim foundRows As DataRow() = CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith'  AND cust_DateofBirth = '1962-09-19'
is not working ?
By Danny Doobay - 8/4/2017

by the way this is related to SF version 1.7.7.1