3-tier design; sorting and filtering object-based data

[Sjaakie]

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

 

[Sean Chambers]

If you wish to do this on the data end, you could at an ORDER BY clause
onto your sql statemtent:

select * from someTable ORDER BY field

If you wish to do it in your data layer you have a number of different
options, probably the easiest would be to have your class implement the
IComparable system interface:

http://msdn2.microsoft.com/en-us/library/4d7sx9hd.aspx

hope that helps!

 

[sloan]

If you're using tsql as your backend database, then check out
http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp

For sorting strong typed objects, check
6/19/2006
Advanced IComparer // Sorting on Multiple Values
at
http://sholliday.spaces.live.com/


I also have a Tiered Example (and 1.1 and 2.0)

You need to look at the SerializeOrder (or something like that) code that
accepts and IDataReader

If you look closely, you 'll see that you can reuse (and reuse) the
serializer ..........
no matter what/how you created the IDataReader


...

If you're not using tsql, then you could write a sql generator code .... to
dynamically create sql statements
(like, if you're using access).
But I'm not a big fan of this.

 

[Sjaakie]

I know about the sql-way of dealing with this, but I was looking for
something like implementing the IComparable interface. I'll dig into it.
Thanks!

If you wish to do this on the data end, you could at an ORDER BY clause
onto your sql statemtent:

select * from someTable ORDER BY field

If you wish to do it in your data layer you have a number of different
options, probably the easiest would be to have your class implement the
IComparable system interface:

http://msdn2.microsoft.com/en-us/library/4d7sx9hd.aspx

hope that helps!

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

 

[Sjaakie]

Thanks sloan,
I'll have a look at your samples!

If you're using tsql as your backend database, then check out
http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp

For sorting strong typed objects, check
6/19/2006
Advanced IComparer // Sorting on Multiple Values
at
http://sholliday.spaces.live.com/


I also have a Tiered Example (and 1.1 and 2.0)

You need to look at the SerializeOrder (or something like that) code that
accepts and IDataReader

If you look closely, you 'll see that you can reuse (and reuse) the
serializer ..........
no matter what/how you created the IDataReader


..

If you're not using tsql, then you could write a sql generator code .... to
dynamically create sql statements
(like, if you're using access).
But I'm not a big fan of this.

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

 

[Damien]

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

Hi Sjaakie,

In the data layer that we use (we auto generate it from the database),
any data retrieval function we have can accept an enumeration value
which determines the sort order desired. In that case, we pass the
order through to the stored procedure and let it do the sorting in the
database (normally the best place to do the sorting).

However, if we've already got a collection of objects loaded, there's
no point in going to the database again just to get it to do some
sorting, so we also have a collection of classes associated with each
collection. Each of these classes implements IComparer. These classes
are private but the collection class exposes a readonly shared property
which returns an instance of the appropriate class.

Each of these sorting classes is dedicated to sorting the appropriate
object types based on the value of a particular attribute (one class
per attribute). The classes also have a function called Desc, which
returns a nearly identical class which sorts in the opposite direction.
Finally, we also support a ThenBy function which we can pass a second
comparer to, so that we can have any combination of sorting we want
(this functionality is more fleshed out than the database sorting). So
if we have a collection, called Collection, which inherits
CollectionBase, and an instance of the collection called coll we can do
something like:

coll.Sort(coll.SimpleSortComparers.ByColumnA.ThenBy(coll.SimpleSortComparers.ByColumnB)

(The second and third references to coll could be replaced by a
reference to Collection)

I dread to think how difficult this would all be if you were doing it
by hand though. Like I say, ours is auto generated. Hope this has given
you some ideas.

Damien

 

[Sjaakie]

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

Hi Sjaakie,

In the data layer that we use (we auto generate it from the database),
any data retrieval function we have can accept an enumeration value
which determines the sort order desired. In that case, we pass the
order through to the stored procedure and let it do the sorting in the
database (normally the best place to do the sorting).

However, if we've already got a collection of objects loaded, there's
no point in going to the database again just to get it to do some
sorting, so we also have a collection of classes associated with each
collection. Each of these classes implements IComparer. These classes
are private but the collection class exposes a readonly shared property
which returns an instance of the appropriate class.

Each of these sorting classes is dedicated to sorting the appropriate
object types based on the value of a particular attribute (one class
per attribute). The classes also have a function called Desc, which
returns a nearly identical class which sorts in the opposite direction.
Finally, we also support a ThenBy function which we can pass a second
comparer to, so that we can have any combination of sorting we want
(this functionality is more fleshed out than the database sorting). So
if we have a collection, called Collection, which inherits
CollectionBase, and an instance of the collection called coll we can do
something like:

coll.Sort(coll.SimpleSortComparers.ByColumnA.ThenBy(coll.SimpleSortComparers.ByColumnB)

(The second and third references to coll could be replaced by a
reference to Collection)

I dread to think how difficult this would all be if you were doing it
by hand though. Like I say, ours is auto generated. Hope this has given
you some ideas.

Damien

Damien,
What you got is exactly what I'm trying to achieve. But I have to little
knowledge of programming OOP with .net to write objects like this. I
think I'll attend to some courses and will try again later...
Anyone can recommend a course?

 

[Damien]

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

Hi Sjaakie,

In the data layer that we use (we auto generate it from the database),
any data retrieval function we have can accept an enumeration value
which determines the sort order desired. In that case, we pass the
order through to the stored procedure and let it do the sorting in the
database (normally the best place to do the sorting).

However, if we've already got a collection of objects loaded, there's
no point in going to the database again just to get it to do some
sorting, so we also have a collection of classes associated with each
collection. Each of these classes implements IComparer. These classes
are private but the collection class exposes a readonly shared property
which returns an instance of the appropriate class.

Each of these sorting classes is dedicated to sorting the appropriate
object types based on the value of a particular attribute (one class
per attribute). The classes also have a function called Desc, which
returns a nearly identical class which sorts in the opposite direction.
Finally, we also support a ThenBy function which we can pass a second
comparer to, so that we can have any combination of sorting we want
(this functionality is more fleshed out than the database sorting). So
if we have a collection, called Collection, which inherits
CollectionBase, and an instance of the collection called coll we can do
something like:

coll.Sort(coll.SimpleSortComparers.ByColumnA.ThenBy(coll.SimpleSortComparers.ByColumnB)

(The second and third references to coll could be replaced by a
reference to Collection)

I dread to think how difficult this would all be if you were doing it
by hand though. Like I say, ours is auto generated. Hope this has given
you some ideas.

Damien

Damien,
What you got is exactly what I'm trying to achieve. But I have to little
knowledge of programming OOP with .net to write objects like this. I
think I'll attend to some courses and will try again later...
Anyone can recommend a course?

I'm afraid mine is mostly self-taught over a number of years (been
doing .Net solidly for about 3 years). The data layer system we have
(called the DOG, short for Database Objects Generator) has taken a long
time (and a lot of false alleys during that time) to get where it is.
I've been writing it (off and on) for at least three years, and it was
based on a previous tool which had been written for VB (by others),
which had evolved over about two years.

On the other hand, writing the DOG has also contributed to my
developing skills, and will do so more in the future (at present, it
writes the various class files to disk, and then adds them to a DLL
project. One upgrade is to at least switch to CodeDom/EnvDTE to get
better integration. Final approach is probably authoring a new project
type, so that you'd just add a DOG project to your solution, set the
appropriate options and namespace, and then it'll build the data layer
automagically. Probably be ready to do that in another 3 years, because
I certainly don't have the skills at present)

Damien

 

[Sean Chambers]

If your're looking for a Object Relational Mapper that is already
pretty solid, take a look at NHibernate:

http://www.hibernate.org

They have a version for java (Hibernate) and for .NET (NHibernate)

It has a large set of options/sorting options etc...

check it out, might be what your're looking for.

Sjaakie wrote:
Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA

Hi Sjaakie,

In the data layer that we use (we auto generate it from the database),
any data retrieval function we have can accept an enumeration value
which determines the sort order desired. In that case, we pass the
order through to the stored procedure and let it do the sorting in the
database (normally the best place to do the sorting).

However, if we've already got a collection of objects loaded, there's
no point in going to the database again just to get it to do some
sorting, so we also have a collection of classes associated with each
collection. Each of these classes implements IComparer. These classes
are private but the collection class exposes a readonly shared property
which returns an instance of the appropriate class.

Each of these sorting classes is dedicated to sorting the appropriate
object types based on the value of a particular attribute (one class
per attribute). The classes also have a function called Desc, which
returns a nearly identical class which sorts in the opposite direction.
Finally, we also support a ThenBy function which we can pass a second
comparer to, so that we can have any combination of sorting we want
(this functionality is more fleshed out than the database sorting). So
if we have a collection, called Collection, which inherits
CollectionBase, and an instance of the collection called coll we can do
something like:

coll.Sort(coll.SimpleSortComparers.ByColumnA.ThenBy(coll.SimpleSortComparers.ByColumnB)

(The second and third references to coll could be replaced by a
reference to Collection)

I dread to think how difficult this would all be if you were doing it
by hand though. Like I say, ours is auto generated. Hope this has given
you some ideas.

Damien

Damien,
What you got is exactly what I'm trying to achieve. But I have to little
knowledge of programming OOP with .net to write objects like this. I
think I'll attend to some courses and will try again later...
Anyone can recommend a course?

I'm afraid mine is mostly self-taught over a number of years (been
doing .Net solidly for about 3 years). The data layer system we have
(called the DOG, short for Database Objects Generator) has taken a long
time (and a lot of false alleys during that time) to get where it is.
I've been writing it (off and on) for at least three years, and it was
based on a previous tool which had been written for VB (by others),
which had evolved over about two years.

On the other hand, writing the DOG has also contributed to my
developing skills, and will do so more in the future (at present, it
writes the various class files to disk, and then adds them to a DLL
project. One upgrade is to at least switch to CodeDom/EnvDTE to get
better integration. Final approach is probably authoring a new project
type, so that you'd just add a DOG project to your solution, set the
appropriate options and namespace, and then it'll build the data layer
automagically. Probably be ready to do that in another 3 years, because
I certainly don't have the skills at present)

Damien

 

[Damien]

If your're looking for a Object Relational Mapper that is already
pretty solid, take a look at NHibernate:

http://www.hibernate.org

They have a version for java (Hibernate) and for .NET (NHibernate)

It has a large set of options/sorting options etc...

check it out, might be what your're looking for.

I know a lot of people are writing an awful lot of good stuff using
Hibernate/NHibernate. The reason we haven't gone that route in our shop
is 1) Better the devil you know (everyone knows how dbi/dog data layers
work, we're used to them), and 2) I'd describe DOG as an R/O Mapper
rather than an O/R Mapper - we spend a lot of time getting the data
model right, and then we produce an OO data layer atop that, rather
than producing an OO model and then trying to force it down into a
relational model.

A lot of people have described the impedance mismatch of going from OO
to relational. If we design the relational model first, I find
generating a simple OO layer atop that easy to do. Then we add further
behaviours to these objects through an extension mechanism (basically,
we can add extensions which get added in at generation time - sort of a
poor mans partial classes, since we're on 1.1). We need good firm
relational layers for our projects since not all of it is done in .NET
- in fact some portions of most of our recent projects have required
some pretty solid, set-based SQL.

Anyhow, to the OP, I'd say yes, look at NHibernate and see if it does
what you need it to do.

Damien

 

[Nick Gilbert]

You should do your sorting in the database where the data is properly
indexed and sorting takes next to 0 CPU power. Sorting in .NET is very
inefficient. If your database is really small then you can get away with
it. But the ORDER BY clause is how you should sort your data in a well
designed application.

Nick...

I know about the sql-way of dealing with this, but I was looking for
something like implementing the IComparable interface. I'll dig into it.
Thanks!

If you wish to do this on the data end, you could at an ORDER BY clause
onto your sql statemtent:

select * from someTable ORDER BY field

If you wish to do it in your data layer you have a number of different
options, probably the easiest would be to have your class implement the
IComparable system interface:

http://msdn2.microsoft.com/en-us/library/4d7sx9hd.aspx

hope that helps!

Hi,
I'm, what it turns out to be, fooling around with 3-tier design.
At several websites people get really enthusiastic about using custom
dataobjects instead of datasets/-tables.
While trying to write such layers myself I got stuck on how to get
filtered or sorted data from the data-layer.

This is what I got:

Objects
* class Marble
- MarbleID
- Color
- Size

Data-layer
* SqlHelper - ExecuteNonQuery, ExecuteDataSet, ExecuteReader etc.
* Marbles - int Insert(Marble m), bool Update(Marble m),
Marble[] ListAll, Marble[] Select(int marbleID)

Business-layer
* Marble[] ListMarbles()
* bool UpdateMarble(int marbleID, string color, string size)
etc.

I would like to filter results by using the property-name as column to
filter on. Is this possible? If not... how should I request a filtered
result from the data-layer, without having to create a seperate member
for every possible filter? And is it possible to sort object-properties
in an object-array?

I hope you understand my question....
TIA