CSLA in search form

kucing posted on Monday, May 29, 2006

Does the framework supports searching for objects? For example, at the moment, I would like to create a search form and functionality for a web application. Is there a CSLA way to do it? Thanks.

ChrisD replied on Tuesday, May 30, 2006

Morning,

This is absolutely possible.

You would handle it something like this...

1) Build a read only list object that accepts criteria objects holding the criteria the user can search by.

2) pass those criteria in and return the list object.

You can even go as far as returning a Read only list of the PK values of the objects that meet the search criteria which can then be passed into a main form allowing user to navigate thriough and edit each selected record.

Hope that helps and get back in touch if you need more info.

Cheers

ChrisD

kucing replied on Wednesday, May 31, 2006

Thanks for that guys!

But let's say I have around 10 fields (all optional) that the user can enter as a search criteria. How can I build the Criteria class properly? Similarly, how can I create my stored procedure properly?

In my mind, these are the constructors for the criteria class:
Criteria(name)
Criteria(name, size)
Criteria(size)
Criteria(name, size, colour)
Criteria etc....

Which will be extremely long. Similarly for the stored procedures, I will need to create stored procedures for all of those combinations, e.g.

sp_GetAnimalGivenName @name
sp_GetAnimalGivenNameSize @name, @size
sp_GetAnimalGivenSize @size
sp_GetAnimalGivenNameSizeColour @name, @size, @colour
....

I know I am missing something or misunderstand how it will work. Can anyone enlighten me?

ajj3085 replied on Wednesday, May 31, 2006

I have search support built into my DAL. My BO takes its search criteria and translates that into DAL criteria. The DAL does searches by building an ad hoc Sql statement. I don't use stored procedures for selecting data exactly for the reasons you describe; you end up with a complex SP, or you have to make a bunch of them. So my DAL builds a Sql statement which just does a select from a view. (For security reasons, tables cannot be touched directly, they can only be modified by procs, and selected from via views).

HTH
Andy

ajj3085 replied on Wednesday, May 31, 2006

Oh, for your Criteria class... don't provide all those overloads on the constructor.

Just provide a default constructor, and allow the 'parameters' to be set via properties. Your BO will determine which properties have values and use the DAL to filter on those properties.

Andy

ajj3085 replied on Wednesday, May 31, 2006

If anyone's interesting, here is my data access code which actually performs the search:

        private void DataPortal_Fetch( SearchCriteria criteria ) {
            DataSearcher<Data.Contacts.Contact> searcher;
            List<Data.Contacts.Contact> results;
            SelectionCriteria crit;

            searcher = new DataSearcher<Data.Contacts.Contact>();

            if ( !string.IsNullOrEmpty( criteria.AddressLine1 ) ) {
                crit = new SelectionCriteria( "Line1", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.AddressLine1 ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.AddressLine2 ) ) {
                crit = new SelectionCriteria( "Line2", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.AddressLine2 ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.AddressLine3 ) ) {
                crit = new SelectionCriteria( "Line3", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.AddressLine3 ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.City ) ) {
                crit = new SelectionCriteria( "City", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.City ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.Country ) ) {
                crit = new SelectionCriteria( "CountryName", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.Country ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.Email ) ) {
                crit = new SelectionCriteria( "Email", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.Email ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.FirstName ) ) {
                crit = new SelectionCriteria( "FirstName", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.FirstName ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.LastName ) ) {
                crit = new SelectionCriteria( "LastName", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.LastName ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.PostalCode ) ) {
                crit = new SelectionCriteria( "PostalCode", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.PostalCode ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.Salutation ) ) {
                crit = new SelectionCriteria( "Salutation", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.Salutation ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.State ) ) {
                crit = new SelectionCriteria( "StateName", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.State ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.Title ) ) {
                crit = new SelectionCriteria( "Title", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.Title ) );
                searcher.AddSelectionCriteria( crit );
            }

            if ( !string.IsNullOrEmpty( criteria.Organization ) ) {
                crit = new SelectionCriteria( "OrganizationName", Operator.Like );
                crit.Values.Add( string.Format( "*{0}*", criteria.Organization ) );
                searcher.AddSelectionCriteria( crit );
            }

            results = searcher.Find();

            IsReadOnly = false;
            foreach ( Data.Contacts.Contact p in results ) {
                this.Add( ContactInfo.GetContactInfo( p ) );
            }
            IsReadOnly = true;

        }

DataSearcher is part of my custom DAL. Data.Contacts.Contact is a view behind the scenes, but isn't tied to a specific database server; the db server type is set in a config file. (Well, currently my DAL only supports Sql Server, but I could add other providers in the future).

Andy

JHurrell replied on Thursday, August 10, 2006

ajj3085:
If anyone's interesting, here is my data access code which actually performs the search:

...

DataSearcher is part of my custom DAL. Data.Contacts.Contact is a view behind the scenes, but isn't tied to a specific database server; the db server type is set in a config file. (Well, currently my DAL only supports Sql Server, but I could add other providers in the future).

Andy,

That's pretty cool. Would you be willing to share the DataSearcher code with us?

- John

ajj3085 replied on Thursday, August 10, 2006

I could, but it'd be rather pointless. The class itself delegates the actual find logic to the Provider for the DataEntity subclass. Provider uses an internal class called EntityReflector to find out about the DataEntity subclass. The Provider then uses this information to build the Select and From clauses of the Sql statement, and uses the SelectionCriteria list and OrderCriteria list to build the Where and Order by clauses.

That said, I am working on the solution at home, and will post when I've finished it. The problem is that the code itself really isn't anything that's not obvious, but I built it at work... so I'll need to start at scratch at home to 'rebuild' the library again before I can feel comfortable with posting it.

It's a project I am pursuing, but since its in my off time (of which I don't have much) its going slow. The idea is that next time I start a new .Net position (which hopefully won't be for a while) I can bring in my library and let them use it under alicense I choose. I've already built the library twice now and I'm sick of it.. this would help me in that regard. Smile [:)]

JHurrell replied on Thursday, August 10, 2006

Gotcha.

- John

SonOfPirate replied on Wednesday, August 16, 2006

FYI - there is another topic where Andy describes his process in more detail: http://forums.lhotka.net/forums/thread/2781.aspx

We've implemented something along the lines of what Andy described into our CSLA-based framework. It too uses dynamic SQL to perform the search but even though it is a frowned upon practice to not use stored procedures, dynamic searches are outside the spectrum for which this rule applies. One of the big reasons you'd want to try to stick to stored procedures is the performance gain achieved with having the execution plan 'compiled' into the database. You only realize this with static code. A dynamic search, by its very nature, can't be pre-compiled into an execution plan - unless you get into writing some really nasty SQL with case statements, etc. (which can be done if you're into migraines).

Of course, the other big reason for sprocs is security/isolation which is violated with dynamic SQL, but having an object in your framework responsible for generating the SQL and interacting with the database via a CommandObject helps get some of this back.

Hope that helps.

DavidDilworth replied on Thursday, August 17, 2006

I'd just like to add something here for anyone that's interested. As I've mentioned on other posts we're using NHibernate as our DAL/ORM mechanism. And we've used that in conjunction with the the new nullable types in .NET 2.0 in our Criteria objects to achieve the following.

We can define a nullable criterion inside the Criteria class. This means it can take an explicit value (meaning we want to use it in the filter search) or be left null (meaning we don't want to use it in the filter search). For example:

public partial class Criteria : CriteriaBase
{
private int? _noOfCovers = null;

/// <remarks><code>null</code> is used to indicate no filter constraint is required.</remarks>
public int? NoOfCovers
{
get { return _noOfCovers; }
set { _noOfCovers = value; }
}
}

Then inside the DataPortal_Fetch() we convert the passed in Criteria class to NHibernate criteria, but only for those criterions which have been given a value. For example

...
// NoOfCovers
if (criteria.NoOfCovers.HasValue))
AddCriteriaNoOfCovers(criteria.NoOfCovers.Value, nhibernateCriteria);
...

And then the final piece in the jigsaw is the actual bit of code that builds up the NHibernate expression to be added to the NHibernate criteria. For example, the following method adds a simple equals expression.

private void AddCriteriaNoOfCovers(int noOfCovers, ICriteria nhibernateCriteria)
{
EqExpression expression = new EqExpression("NoOfCovers", noOfCovers);
nhibernateCriteria.Add(expression);
}

Obviously, our Criteria classes are more complex than this simple example and have multiple criterions. But this pattern (which is very similar to the one shown by Andy), allows for easy extension to add as many different criterion as there are properties in your BO. And NHibernate takes care of working out the correct SQL required in your chosen dialect (MS SQL, Oracle, MySQL, etc).

SonOfPirate replied on Thursday, August 17, 2006

Same basic concept as what we've done and Andy's approach, just a different implementation that satisfies the need to make it work with NHibernate. I agree that Nullables make this process (among others) a whole lot easier.

ajj3085 replied on Thursday, August 17, 2006

I make use of Nullable in my DAL as well (just not in this particular functionality). Its been a godsend... nothing has felt worse to me than picking an arbitrary value out of a limited set to represent null..

SonOfPirate replied on Thursday, August 17, 2006

Yea, I'm all for MS making ValueTypes nullable by default. What a difference that would make!

abhicbsa replied on Tuesday, September 23, 2008

hi ajj3085,

can you share how this DataSearcher class has been implemented.
I mean can you give a brief description of your custom DAL.

thank
abhi

ajj3085 replied on Wednesday, September 24, 2008

Well, it's the same concept as anything you'd find on codeplex or codeproject. Actually it was very similar to Linq (as far as defining classes to map to tables, and using attributes to map properties to columns), except in mine you had to inherit from a base class as well.

At any rate, I'm not sure if it'd be worth going into further, given that I've ditched my DAL in favor of Linq to Sql. It didn't do anything that L2S couldn't do, and if you can't use L2S it may be worth learning about NHibernate. I just didn't learn about it until after I had done mine.

kucing replied on Thursday, June 01, 2006

Thanks. I realized about the my idiotic idea of having many constructors a few minutes after posting.

However, you said that you constructed the SQL query manually. I also had that in mind but I thought it is a bad practice. I guess, dynamic string construction is not so bad.

ajj3085 replied on Thursday, June 01, 2006

Well, its sort of manually.

I have a custom DAL layer which I wrote (and created tests for) which builds the SQL for me. I never hand code SQL myself, I creating the DataSearcher, SelectionCriteria and OrderCriteria classes for this purpose. When Find is called on the DataSearcher, it looks at the type (in the code I posted, Data.Contacts.Contact, which is a class that represents a View in Sql Server), build the SELECT columns FROM vContact, and then loops through the collection of SelectionCriteria and OrderCriteria to add on WHERE and ORDER BY clauses.

So my DAL builds the sql statement for me, I never craft it by hand.

Inherently there's nothing wrong with ad-hoc queries; i think there IS something wrong with hardcoding "SELECT * FROM vContact WHERE x = 1" in the code.

HTH
Andy

rhoeting replied on Tuesday, May 30, 2006

And to take things a bit further.... you can get fancy with search criteria. Suppose your search criteria is first name, last name and address. But you do want them to enter in a something on your search form. In other words, all fields blank is not allowable.

You can make your criteria object a BO in it's own right and add rules to it. You can bind the controls to the BO props, and bind the "Search" button to the IsValid flag of your Criteria object.

Rob