StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Filtering Date in BO

Filtering Date in BO

Danny Doobay — Sat, 05 Aug 2017 05:44:11 GMT

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

?CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith' AND DOByyyyMMdd = '1962-09-19'").Length

?CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith' AND cust_DateofBirth = '1962-09-19'").Length

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

Any help is appreciated.

RE: Filtering Date in BO

Danny Doobay — Sat, 05 Aug 2017 05:44:11 GMT

by the way this is related to SF version 1.7.7.1

RE: Filtering Date in BO

Danny Doobay — Sat, 05 Aug 2017 05:13:08 GMT

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'"

Dim foundRows As DataRow() = CustomersBO.CurrentDataTable.[Select]("cust_FirstName = 'John' AND cust_LastName = 'Smith' AND cust_DateofBirth = '1962-09-19'

is not working ?

RE: Filtering Date in BO

Trent L. Taylor — Fri, 04 Aug 2017 09:03:38 GMT

Here is the project. Thought this would be easier.

RE: Filtering Date in BO

Trent L. Taylor — Fri, 04 Aug 2017 08:57:22 GMT

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.

[codesnippet]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);
} [/codesnippet]