Filtering Date in BO

Author Message
 Posted 8/4/2017 12:42:26 AM
StrataFrame Beginner

StrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame Beginner

Group: StrataFrame Users
Last Login: 6/11/2019 6:04:45 AM
Posts: 41, Visits: 342
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.

Tags:

Edited: 8/4/2017 12:50:11 AM by Danny Doobay
Post #33510
Add to Twitter Add to Facebook
 Posted 8/4/2017 2:57:22 AM
StrataFrame Developer

StrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame Developer

Group: StrataFrame Developers
Last Login: 11/20/2019 8:57:25 AM
Posts: 6,610, Visits: 6,776
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);
} 


Edited: 8/4/2017 2:58:00 AM by Trent L. Taylor
Post #33511
Add to Twitter Add to Facebook
 Posted 8/4/2017 3:03:38 AM
StrataFrame Developer

StrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame Developer

Group: StrataFrame Developers
Last Login: 11/20/2019 8:57:25 AM
Posts: 6,610, Visits: 6,776
Here is the project.  Thought this would be easier.

 SampleDataTable.zip (1 view, 20.88 KB)
Post #33512
Add to Twitter Add to Facebook
 Posted 8/4/2017 11:13:08 PM
StrataFrame Beginner

StrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame Beginner

Group: StrataFrame Users
Last Login: 6/11/2019 6:04:45 AM
Posts: 41, Visits: 342
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 ?
Post #33513
Add to Twitter Add to Facebook
 Posted 8/4/2017 11:44:11 PM
StrataFrame Beginner

StrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame BeginnerStrataFrame Beginner

Group: StrataFrame Users
Last Login: 6/11/2019 6:04:45 AM
Posts: 41, Visits: 342
by the way this is related to SF version 1.7.7.1
Post #33514
Add to Twitter Add to Facebook


Similar Topics

Expand / Collapse

Reading This Topic

Expand / Collapse

Site Map - Home - My Account - Forum - About Us - Contact Us - Try It - Buy It

Microsoft, Visual Studio, and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.