cultofluna posted on Wednesday, August 09, 2006

Development specifications
==========================
OS:    WinXP
DEV:   C#
.NET:   1.1
CSLA:    1.53
DB:    SQL Server

Search Functionality
====================

I'm creating a demo application to learn the CSLA framework. The application allows the user to retrieve a list of customers by choosing a searchmethod

(housenumber and postalcode; telephonenumber; customerid; etc.). I've created a Customers class based on the ReadOnlyCollectionBase that retrieves a list of

Customer classes based on the ReadOnlyBase.
At this point the Customers class is provided with a GetCustomers method that retrieves all customers matching the customerid value specified by the user and

using the dataportal.
However, I have several search methods that need to be supported, so only the Customers class isn't enough. As far as my understanding goes with using the

CSLA (I'm just a rookie :)), I've come up with the following solution:

* Removed the definition for DataPortal_Fetch from the Customers class
* Created classes for every searchmethod that inherit from the Customers class
* Created GetCustomers function that returns a Customers class

My ideas behind this solution are:

* No need for complex conditional structures, meaning several GetCustomers methods for the searchmethods and the use of conditions handling the methods in

the DataPortal_Fetch method.
* Searchmethods can easily be added and removed.
* The "real" search can be handled using Stored Procedures (I use SQL Server).

But is this the way to do it? Should search functionality be handled by the framework? Should search functionality be handled using different classes based

on the fetch method? Should search functionality be created using .NET code? I'm curious about solutions the people on this forum have come up with and

enlighten me! :)

Data Representation vs. Modification
====================================

For the same application I mentioned above I have another question. The user should be able to scroll the list of Customers, this is easy to do using the

binding functionality, so no problem here. However the Customers and Customer classes are both readonly and I want the user to be able to block a Customer

based on a judgement by the user. I've added a GetEditableCustomer method for the Customer class that returns a EditCustomer class that returns the same

customer however this time editable for the user. When the user is done editing the customer, it's saved to the database and the user returns to the list.
I know there is only one customer that has changed, meaning it would be expensive to reload the full list, so I would rather reload the modified customer.

But based on the code samples in the book the Customer class data is provided by the Customers class DataReader, so I'm not able to reload the specific

customer, meaning full reload is the only option. Does anybody recognize this situation? What's the correct way to solve this problem?

Sorry for the long stories, hope you can help me out making the right choices. Please let me know if you need anymore information!

Skafa replied on Wednesday, August 09, 2006

i'm pretty new to csla too, but regarding your second question, how about this:
implement an 'UpdateCustomer' function in your ReadOnlyList, which searches your list (matching by primary key / oid / hash or something) and updates that customer.

cultofluna replied on Wednesday, August 09, 2006

Sounds possible, may be this is also the solutions for my first question. The list class should be provided with methods for the search functionality to iterate through it's items and the Customer classes should be provided with several comparison methods for comparing the searchvalue with it's current values. However in my opnion there are two problems with this solutions:

1. The Customer classes are filled using the Customers SafeDataReader that is created using the DataPortal_Fetch method of the class. Implementing this option means I must be able to crate two definitions for the DataPortal_Fetch method. One for requesting a list and one for updating an updated Customer. This brings me back to my first problem in question 1 :)
I already know which object is modified, should I update it using the EditCustomer object, accepting that it's kind of a dirty read?

2. When the list contains a lot of Customers the search process can get really expensive!

For me it is not clear when several database fetch definitions are desired, how they should be implemented in the framework. In addition to that, I personally prefer always using the database when it's comes to searching and fetching information. Or is that a misinterpretation of the framework?

cultofluna replied on Thursday, August 10, 2006

I found an interesting thread on the use of factory classes, I think this is a good solution for my search problem. Anyone?

ajj3085 replied on Thursday, August 10, 2006

For search functionality, this thread may be helpful:
http://forums.lhotka.net/forums/thread/615.aspx

ajj3085 replied on Thursday, August 10, 2006

Doh.. maybe not... looks like much of that thread is gone..

cultofluna replied on Thursday, August 10, 2006

Thanks for the thread, I could access it.

I will definitely explore the Amazon API.

ajj3085 replied on Thursday, August 10, 2006

Here's another thread as well:
http://forums.lhotka.net/forums/thread/1078.aspx

cultofluna replied on Thursday, August 10, 2006

I understand there are several ways for supporting searches using the CSLA, however I don't understand why anyone wants to choose for dynamic SQL! Isn't this a major performance issue? Isn't it strange not to use the full power of a database?

ajj3085 replied on Thursday, August 10, 2006

Whats the performance issue? Its my understanding that starting in Sql 2000, dyanmic sql is pretty much on par with stored procedure execution, especially if the same query is used over and over again (I believe the first ad-hoc query is compiled, and that pre-compiled query is than cached in the server automatically, so the next time it comes up, it doesn't need to precompile). In my particular case, where I need data from more than one table, I create a view that does the necessary joins, and my searcher will query the view.

For me, its a question of maintence. I'm not sure if its mentioned in any of the threads, but building really complex search criteria in a stored procedure call costs a lot in terms of maintence. You usually end up with a really ugly procedure that takes 30 + parameters, and the where clause is a bunch of case statements or repetitions of ' AND (LastName = @LastName OR @LastName IS NULL )' Then if you have to support exact matches, or LIKE matches, or range (BETWEEN) matches... it gets even uglier and quickly becomes a nightmare to maintain. FWIW, I really really hate doing anything too complex in Sql, because of the maintence. Thus far, peformance hasn't been an issue.

Andy

cultofluna replied on Thursday, August 10, 2006

But why not create several "dedicated" SP's. Of course there is maintenance here as well, however what happens when a change occurs in your data structure? I'd rather modifiy some sql than modifying a BO. Or in your database of choice? I understand this works fine for you and there probably isn't a solution that is best.
I'm going to find out what works best for me.
Thanks!

ajj3085 replied on Thursday, August 10, 2006

Well, now you've just put the cost of maintence to astronomical levels. When the user can specify if the criteria is a like, equals or range, and they can enter anywhere from one to 30 of such criteria, you're talking about a HUGE number of combinations here. Typically what ends up happening is your build dynamic sql in the stored proc, and then executing that! Debugging such a procedure is orders of magnitude harder because you lack many of the tools you have debugging in VS.

If a change occurs in my data schema, it have to modify the appropriate data objects (not that the DataSearcher is part of my DAL, NOT part of my business layer). This will break the BO layer, but its easy to find; compile, and you have most your errors caught by the compiler. The rest are caught from unit tests, because attempting to add a SelectionCriteria to the DataSearcher throws an error if the property doesn't actually exist on the type being searched. I find that much preferable than manually hunting for all the embedded sql in my business layer; at least the compiler was able to help me out quite a bit.

It should be noted that the DataSearcher method was suggested; the idea translates well to the business layer (I believe). The result would be that the business level criteria classes end up in a DataPortal_Fetch somehow. That method would translate the intent specified by the busines level data searcher into the data level data searcher. Using this technique, the BO layer doesn't change its public API at all, which is part of the point.. you save your UI code from changes as well.

Of course you should find what works best for you, and I was mearly suggesting a few ways to go. However you may want to try things out / think about them more, before just blindly saying 'ew, dynamic sql, screw that' and ruling out the suggestion prematurely.

Andy

cultofluna replied on Thursday, August 10, 2006

Thanks for your comments, you've given me good arguments for the choice of at least the use of dynamic sql where it comes to a high level of diversity in possible sql structures.
My last few questions for now:

1. Do you use dynamic sql for everything or just the diverse complex parts?

2. Could you be more specific on how the place the DataSearcher in the frameworker and the use of the DataSearcher? Maybe it's to specifically designed for you, but it's more to give myself a good starting point.

Thanks again so far, I will keep this thread informed about my findings!

ajj3085 replied on Thursday, August 10, 2006

I use dynamic sql for all selects from the database (which are always against views). I use stored procedures for all data modification. The reasoning is that I don't want anything directly touching the tables.

Use of the DataSearcher is described here: http://forums.lhotka.net/forums/permalink/1024/1080/ShowThread.aspx#1080

The concept of the DataSearcher moves pretty easily into the business layer. You'd have the searcher class itself, selection and order criteria. The main difference is that the Find method would have to delegate the call back to the business object itself, so that it can perform the search (which would likely just be packaging of the criteria and calling a DataPortal_Fetch with a criteriaBase class which contains all the criteria from the searcher). The DataPortal_Fetch would translate the BO data searcher criteria into DAL data searcher criteria somehow.

Does that help at all?

Mark replied on Thursday, August 10, 2006

Can't speak for others, but I only use dynamic SQL when absolutely necessary - when I need to build complex queries based on a variety of parameters (possible subqueries, etc).

However, even when building dyanmic SQL, you want to create parameterized SQL statements to prevent SQL injection attacks...

- Mark

ajj3085 replied on Thursday, August 10, 2006

Of course, and one of the advantages of the DAL I built is that it handles this for me, so I can't possibly forget to do this.

cultofluna replied on Saturday, August 12, 2006

Hello Andy,

I'm trying to figure out your code for the DataSearcher and I have a few questions about the code:

1. What does searcher = new DataSearcher<Data.Contacts.Contact>() do exactly, meaning the <Data.Contacts.Contact> part?

2. In the foreach you are iterating through the resultset using the Data.Contacts.Contact. Does your searcher understand, based on the structure in question 1, how to return the data?

3. Is ContactInfo a struct or a class?

4. When you want an editable class for a result ContactInfo, do you provide it with a "Get..." method returning an editable version or ... ?

Thanks for your answers!

Best regards,

cultofluna replied on Saturday, August 12, 2006

Oh, and another question, how does it handle different datatypes? The where clause needs ' chars for date and string, while numeric values don't etc.

Thanks again,...

cultofluna replied on Sunday, August 13, 2006

After studying your code some more, reading the threads and doing some Googling, I understand more on your search mechanism. My main question remaining is, you set your searchoperators, fieldnames of your views, etc. all in the DataPortal_Fetch. Does this mean selectioncriteria cannot be accessed from outside the class? Meaning for example the operator for a property can't be set from the UI?

ajj3085 replied on Monday, August 14, 2006

Hi,

Correct, SelectionCriteria cannot be accessed from the UI, and it should not be, since the class is part of my DAL. That said, you can build SelectionCriteria in your business library and expose THAT to the UI.

In your DataPortal_Fetch method, you'd use your BO level SelectionCriteria to translate into the SelectionCriteria from the DAL (assuming you have something like this in your DAL..you may not, but you could just build Sql there if you don't have a more formal DAL).

HTH
Andy

cultofluna replied on Tuesday, August 15, 2006

Hello,

I've created the following as a first attempt to support a searchsystem for the CSLA. The project has the following BO's:

* Companies: ReadOnlyCollectionBase
* Company: ReadOnlyBase
* CompanyEdit: BusinessBase
* CompanySearch: SearchBase
* CommandBuilderBase

The Companies loads all available Company's. When the user selects a Company from the UI the Company returns a CompanyEdit, so the user is able to modify the company.
I've added a CompanySearch, this is the class to build a search action for Company's. The CompanySearch has the same properties as Company(Edit), but with the functionality to add criteria for this specific property. Maybe furthur use will lead to a situation where this isn't handy, however for now I like it :)
The Companies object has a method GetCompanies(), and I have added the method GetCompanies(CompanySearch search).
So the second method is directly the problem. I create the CompanySearch from the UI, pass it to the method...so far so good, however the DataPortal can't handle this object.
I've debugged into the problem method in DataPortal.cs, the problem method is GetObjectType(). The method tries to determine the type of the BO calling the DataPortal, however this fails because the CompanySearch does "carry" any information on the BO.
I've been thinking about creating a property to assign the BO to the Search, however, that's not really pretty, and next to that I need do modifications in the DataPortal.cs, with I don't want to do for now.
Do you have any idea how to let the DataPortal determine the type without modifying the base CSLA?

Thanks!

Best regards,

ajj3085 replied on Tuesday, August 15, 2006

Create a private nested class in Companies called SearchCriteria. Have SearchCriteria inherit CriteriaBase.

SearchCriteria should have a property called Criteria, which is of type CompanySearch. Your factory method which takes CompanySearch should look like this:

public static Companies GetCompanies( CompanySearch search ) {
    return DataPortal.Fetch<Companies>(
         new SearchCriteria( search ) );
}

// Dataportal
private void DataPortal_Fetch( SearchCriteria crit ) {
    CompanySearch search;

   search = crit.SearchCriteria;

   // use CompanySearch here
}

HTH
Andy

cultofluna replied on Tuesday, August 15, 2006

Hmmm...amazing what you can think of just by posting a message. I've extended the Criteria class with a member for the CompanySearch class, and this does the trick. So no modifications to the DAL and Search Command creation from the UI. I'm a happy man (for now, need todo some more testing of course! :) )

Cheers!

ajj3085 replied on Tuesday, August 15, 2006

Glad the concept is working out for you.

Happy coding
Andy