What is the right, Object Oriented Way to do this?
We have products, about a hundred thousand in our database. Each product has over 300 fields of possible information held in a couple of different tables. There are also some other 1-to-many child tables of info but we'll leave them out for now. So our Product.cs object has about 300 properties. Most work so far has been data entry and has been a lot of grid work and updating. Now we have reached the point of searching and reporting.
We need to allow our users to effectively search through our product database. 80% of the time they'll be using the same 10 criteria fields. Price To and Price From, Price Type, Category, Name, Model Number, Brand (or several brands), Status, and Country of Origin. They will also specify a sort. We also want to be able to eventaully expand this search to include any of hte 300 fields and a value and roll that into the rest of the fixed criteria.
The three way I've been considering doing this are:
I think the last option, #2.2 might be the best option, but I'm not sure if that's the "right" way to do it, OOP wise. Anyone have any advice or maybe a pattern I could read up on? Or, even better an example I could look at.
Thanks, lg
That was something else I considered, creating a SearchCriteria class. Just a simple class, no Data Access. So I would creation the object, set the properties, and then pass the object into my ProductSearch.GetResults method.
I just wasn't sure if there was a good reason to break it out into it's own class or keep it all within one object.
My problem is that I'm having a hard time thinking business objects, i'm having a hard time breaking the SQL and data access part of it out of my head to create a logical business object.
That's a pretty good point. THey haven't asked yet, but I suppose that's something I should be ready for and building for now.
Andy, I'm going to repost your code since it was the second post in what is an old, long thread.
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 );
}
// Many more options
results = searcher.Find();
LoadList( result );
}
I have been attempting to abstract the bold code above, allowing one to write code like this instead:
private void DataPortal_Fetch( SearchCriteria searchCriteria)
{
DataSearcher <Data.Contacts.Contact> searcher = new DataSearcher<Data.Contacts.Contact>();
List<Data.Contacts.Contact> results = searcher.Find(searchCriteria);
LoadList( results );
}
My Find() method inside DataSearcher looks like this:
public List<T> Find(object searchCriteria)
{
List<T> retList = new List<T>();
SelectionCriteria crit;
foreach (System.Reflection.PropertyInfo property in searchCriteria.GetType().GetProperties())
{
string sPropertyValue = property.GetValue(searchCriteria, null).ToString();
if (sPropertyValue != "")
{
crit = new SelectionCriteria(property.Name, SqlOperator.Like);
crit.Values.Add(string.Format("{0}*", sPropertyValue));
AddSelectionCriteria(crit);
}
}
// ToDo: Create the sql string to execute
// ToDo: Implement the data access
return retList;
}
Notes:
- if the property name is different than the field name in the backend, I'll use a view and make the field name match the property name of my business object.
- I haven't quite figured out yet how I will determine which operator I should be using, so for now just using "Like".
- as you can see by my "ToDo's" I haven't created the code that will build my sql string or implemented the data access.
Questions:
- My current road block is implementing the data access inside of DataSearcher under the confines of the CSLA dataportal. Do you see any way to do this? I tried making my DataSearcher a BusinessBase, but I don't see how to do that and hold onto the generic type I'm passing in (Data.Contacts.Contact)
- I know you have said that your DataSearcher is part of your own DAL. Does that mean you are not using the CSLA dataportal?
- You see what I'm trying to do in creating this DataSearcher that does the work of evaluating the properties with a generic loop. It's working as hoped up to the point that I'm at (in other words, it correctly adds a SelectionCriteria object to DataSearcher for each property that has a value), but I'm still not sure I'm headed down the right path. Do you see any problems ahead?
Sorry so many questions. I'm a newbie at all this and am trying my best to pick it up.
Though this is directed at Andy, anyone please feel free to comment.
thanks, Ryan
First, I'm in the same boat wrt using "LIKE". I have an interface for higher-end searching that mimics the advanced search dialog in Outlook and allows the user to select the condition (like, equal to, not equal to, etc.). But, short of this, I don't know that there's anyway for us to tell what the user's (or client's) intention is for the criteria.
Anyway, as for your question about the data portal. Keep in mind that the code you posted will be running on the server. The DataPortal_Fetch method gets triggered by the data portal after the request has been routed to either the local or remote host. As a result, your DataSearcher, as described, is being run entirely on the host server. So, I'm not sure what else you are looking for wrt the data portal. Can you explain?
Otherwise, let me say that I like the approach and it very similar to what we've done. As much as Reflection gets knocked around a bit, we do make use of it pretty extensively in our "out-of-the-box" base classes so that we can develop our objects more quickly. This is certainly an example where reflection simplifies the BO code. Nice post.
Proper OO design isn't just about building objects in an object-oriented language, it's about building objects that take proper advantage of the tools and resources being used. Too often the database capability gets ignored because it's "not OO".For very large tables, once you start adding lots of child and reference tables into the query along with plenty of searchable properties, it becomes important to use dynamic sql or lots and lots of stored procedures in order to get good performance. The more properties you have, the less pleasant the many stored procedure option gets.However, this type of search typically lets lots of free-text fields into the query, which is an invitation to hack the database with a sql injection attack.Here's an approach I came up with to avoid most of the pain and get pretty good performance.First of all, in sql server 2005 and in oracle, you can define a procedure to run as a given user or role.
Example (sql server 2005 syntax):
create procedure GetProductDataForWeb WITH EXECUTE AS 'ReallySafeUserRole' AS ...
So, ReallySafeUserRole could be defined to only have the authority to select privileges on the tables that are necessary to service the allowable dynamic queries. If someone slipped in some nasty dynamic sql, it simply wouldn't be able to do very much - other than keep them from seeing data you otherwise would have shown them. :)
Secondly, if you needed to add some more protection (at the cost of more load on the database), you could make a change to the ReallySafeUserRole privileges. Instead of having select access on the necessary tables, it would only have execute authority to run a specific stored procedure (which I explain next).
Now, some of your parameters are suitable for a static sql statement and some aren't. Pass the ones to use with a static statement to this second stored procedure.
This second stored procedure would return a list of the products filtered by the static parameters. This is how you invoke it:
create table #product_list
(product_id int null
, etc...
)insert
into #product_list exec GetProductDataInternally @param1, @param2
You then build a dynamic query against #product_list.
This will be slower than doing it all in one procedure, but it really locks down what a hacker using sql injection can accomplish. Even better, you don't have to spend any time coding to stop them from trying to slip something in via the free-text search fields.
The only table they can see is #product_list, which already contains data they are allowed to see. :)
David - I like your approach, but I can't use it with the approach I am attempting, correct? I just want to make sure I'm not missing something. I am grabbing only the properties that are populated and passing those to the data so obviously I wouldn't be able to use a proc.
What we need is a way to set a parameter name at runtime. Then the parameter name could be analyzed for which column the value represents. ![Stick out tongue [:P]](/emoticons/emotion-4.gif)
Let me take those last 3 posts in order:
1. I have an enumeration that defines the list of available "conditions". I believe, for an added touch, there is an attribute attached to each element that specifies the text that should be used when each is converted to a string. I don't remember the attribute of the top of my head and don't have access to the code right now, somaybe someone else can help me.
Anyway, each "clause" in our search criteria is specified using the property name, search value and the condition to apply. Then, when the SQL is constructed, the appropriate syntax is used based on the condition indicated (and reflection evaluates the property from the specified name to see if our attribute maps the property to a different column name).
Make sense?
2. Don't get me started on NHibernate! I have to suffer through it for a project for about 6 months and... Well, those that use it love it and those that don't, don't. It's one of those items that you either love or hate. I have yet to find anyone that uses it occasionally - you either do or you don't.
I don't know that there are any clear cut reasons for anyone's opinion that can't be countered by someone on the other side. For myself, I just didn't like the requirements it placed on your object design. It takes away some of the core principals behind the Csla architecture in that your objects are 'dumb' when it comes to data access. I think the data-mapping aspect of NHibernate is great and it does allow you to create objects based on behavior and not strictly a one-to-one matchup with your tables and views; although this is where it starts getting complicated because of the restrictions it imposes and the limitations on Nhibernate's syntax.
You can certainly use it. Come to your own conclusions and join the debate. For me, it takes away an important aspect of Csla so I don't use it.
3. I guess I'm not understanding your question. I believe you are referring to the temp table, all in SQL route. I would agree that this limits the flexibility a bit because you are bound to whatever implementation you have provided in the SQL code. Add this to the complexity of the solution and I still find my approach, which matches Andy's, the most condusive. By using a DataSearcher-type approach where you specify your SearchCriteria in code, you are able to have a more dynamic solution. In our case, only the properties that have been specified are included in the search criteria. So, we are not specifying nulls or wildcards to be passed on and handled in our SQL - we only pass what we need to retrieve the requested data.
Not sure if that follows your question or not.
HTH
rhinoishere:
David - I like your approach, but I can't use it with the approach I am attempting, correct? I just want to make sure I'm not missing something. I am grabbing only the properties that are populated and passing those to the data so obviously I wouldn't be able to use a proc.
What we need is a way to set a parameter name at runtime. Then the parameter name could be analyzed for which column the value represents.
I've refined/better explained my approach on this thread:
http://forums.lhotka.net/forums/2/9912/ShowThread.aspx
Some of the variants I explained would work simply because the only "parameter" you need to pass is the select statement you just constructed. Other variants would require some parameters, but you could just pass them all and have the stored procedure ignore the ones that don't matter.
First, I'm in the same boat wrt using "LIKE". I have an interface for higher-end searching that mimics the advanced search dialog in Outlook and allows the user to select the condition (like, equal to, not equal to, etc.). But, short of this, I don't know that there's anyway for us to tell what the user's (or client's) intention is for the criteria.
Let's say the user chooses to use "Equals" as their operator on a particular field. How do I get that to my Find method in DataSearcher? One idea, and what I have working right now, is to add another property to my search criteria which is the property name with "_Operator" added. For example:
private String _city = String.Empty;
private SqlOperator _city_Operator = SqlOperator.Like;
public String City
{
get {return _city; }
set{_city = value;}
}
public SqlOperator City_Operator
{
get { return _city_Operator; }
set {_city_Operator = value; }
}
Note that the default is "Like".
I've changed my Find method to this:
public List<T> Find(object searchCriteria)
{
List<T> retList = new List<T>();
SelectionCriteria crit;
foreach (System.Reflection.PropertyInfo property in searchCriteria.GetType().GetProperties())
{
string sPropertyValue = property.GetValue(searchCriteria, null).ToString();
if (sPropertyValue != "")
{
SqlOperator sqlOperator = (SqlOperator)searchCriteria.GetType().GetProperty(property.Name + "_Operator").GetValue(searchCriteria, null);
crit = new SelectionCriteria(property.Name, sqlOperator);
crit.Values.Add(string.Format("{0}*", sPropertyValue));
AddSelectionCriteria(crit);
}
}
// ToDo: Create the sql string to execute
// ToDo: Implement the data access
return retList;
}
So as you can see, I grab the current property.name and add "_Operator" to get the operator value that should be used for that field. Seems to work ok, and doesn't bother me too much since I'm just adding the new properties to the SearchCriteria object not the business object itself. Do you do something along these lines?
Anyway, as for your question about the data portal. Keep in mind that the code you posted will be running on the server. The DataPortal_Fetch method gets triggered by the data portal after the request has been routed to either the local or remote host. As a result, your DataSearcher, as described, is being run entirely on the host server. So, I'm not sure what else you are looking for wrt the data portal. Can you explain?
![Smile [:)]](/emoticons/emotion-1.gif)
rhinoishere:
Notes:
- if the property name is different than the field name in the backend, I'll use a view and make the field name match the property name of my business object.
- I haven't quite figured out yet how I will determine which operator I should be using, so for now just using "Like".
- as you can see by my "ToDo's" I haven't created the code that will build my sql string or implemented the data access.
Here's what Data.Software.SerialNumberDetail looks like:
/// <summary>Represents the <c>vSerialNumberDetail</c> table.</summary>
[EntityAttributes( EntityName = "vSerialNumberDetail", IsView = true )]
public sealed class SerialNumberDetail : DataEntity {
#region Fields
/// <summary>Backer for the field <c>SerialNumberId</c>.</summary>
private Int32? _SerialNumberId;
/// <summary>Backer for the field <c>SerialNumber</c>.</summary>
private String _SerialNumber;
/// <summary>Backer for the field <c>PurchaseDate</c>.</summary>
private DateTime? _PurchaseDate;
/// <summary>Backer for the field <c>LicenseType</c>.</summary>
private String _LicenseType;
// Etc.
#endregion
#region Properties
/// <summary>Gets or sets the <c>SerialNumberId</c>.</summary>
[DataField( IsKeyColumn = false, FieldName = "SerialNumberId" )]
public Int32? SerialNumberId {
get { return _SerialNumberId; }
set { _SerialNumberId = value; }
}
/// <summary>Gets or sets the <c>SerialNumber</c>.</summary>
[DataField( IsKeyColumn = false, FieldName = "SerialNumber" )]
public String SerialNumber {
get { return _SerialNumber; }
set { _SerialNumber = value; }
}
/// <summary>Gets or sets the <c>PurchaseDate</c>.</summary>
[DataField( IsKeyColumn = false, FieldName = "PurchaseDate" )]
public DateTime? PurchaseDate {
get { return _PurchaseDate; }
set { _PurchaseDate = value; }
}
/// <summary>Gets or sets the <c>LicenseType</c>.</summary>
[DataField( IsKeyColumn = false, FieldName = "LicenseType" )]
public String LicenseType {
get { return _LicenseType; }
set { _LicenseType = value; }
}
// Etc.
#endregion
}
My current road block is implementing the data access inside of DataSearcher under the confines of the CSLA dataportal. Do you see any way to do this? I tried making my DataSearcher a BusinessBase, but I don't see how to do that and hold onto the generic type I'm passing in (Data.Contacts.Contact)
Well the DataSearch class belongs to my DAL library, which is referenced by my business libraries. The DataSearcher only works with DataEntity subclasses.. so DataSearcher is only used in DP methods.
I know you have said that your DataSearcher is part of your own DAL. Does that mean you are not using the CSLA dataportal?
Not at all, I'm using the dataportal just like everyone else. The difference is that instead of using ADO.Net or NHibernate in my DP methods, I'm using a DAL of my own rolling, which saves me from having to worry about building Sql stored procedure calls or dynamic selects.
You see what I'm trying to do in creating this DataSearcher that does the work of evaluating the properties with a generic loop. It's working as hoped up to the point that I'm at (in other words, it correctly adds a SelectionCriteria object to DataSearcher for each property that has a value), but I'm still not sure I'm headed down the right path. Do you see any problems ahead?
My only concern is that you're building the DataSearcher in the business layer, and that's not the appropriate place to build it. Also, keep in mind that my DAL is similar in intent to NHibernate... I hadn't actually found out about NHibernate until after I had my DAL, otherwise I may have looked into it instead of building my own.
Sorry, forgot the point about mapping column to property names. Not sure what Andy was trying to say there - kinda lost in all that code. So, maybe I'm reiterating what he's already said...
We created new Attribute classes based on the System.ComponentModel.DataObjectAttribute and System.ComponentModel.DataObjectFieldAttribute classes. We've actually wrapped these classes since they are not inheritable, exposed all of the same properties and added a few of our own, including a ColumnName property to the DataObjectFieldAttribute class.
In our "schema builder" class (for lack of a better name), we check for the existance of this attribute and, if it exists, check to see if the ColumnName property has a value. If so, that is what we use for our SQL. This is also used by our CommandBuilder and a few other places to accomplish the same data mapping.
FWIW - our schema builder is smart enough to also check for the actual System.ComponentModel.DataObjectFieldAttribute just in case that is what was implemented instead of our custom one. It actually checks for this first so that our custom one supercedes the built-in attribute if both are used. Afterall, as I've posted a few times, we have an extensive team development environment we are supporting that includes on-site, off-site and even off-shore personnel, so we have to make it as easy and intuitive as possible to accomplish - and bullet-proof.
HTH!
My DAL uses attributes to map column names to property names.
It seems you have all the plumbing to do what I am attempting. Is there a reason you have not gone this route?
Not at all, I'm using the dataportal just like everyone else. The difference is that instead of using ADO.Net or NHibernate in my DP methods, I'm using a DAL of my own rolling, which saves me from having to worry about building Sql stored procedure calls or dynamic selects.
![Wink [;)]](/emoticons/emotion-5.gif)
I talked to a couple fellow programmers today, more senior than myself, and got their thoughts on using NHibernate (or something like it). They weren't overly keen on it for several reasons.
My only concern is that you're building the DataSearcher in the business layer, and that's not the appropriate place to build it.
It seems you have all the plumbing to do what I am attempting. Is there a reason you have not gone this route?
Anyway, when I finally do finish that, I will post it.Oh man, I'm trying to learn CSLA, along with proper Object Oriented Programming, and now I've started looking into NHibernate. Kill me now.
Well you certainly have your work cut out for you. I've found that needing to learn never stops. Everything I think I know alot about what Windows can do, I find out about this whole new feature it has.
Here's a few of their comments: "Good for small projects, not for Enterprise level; It brings some security concerns; .Net makes reflection makes things easy enough that if you wanted to go the ORM route, just roll your own; Ruby on Rails would be a better way to go."
You may want to press them. I know people here use NHibernate in what I would think would be considered enterprise applications. You could roll your own (which is what I have done) and while its not hard, it takes time. And then its easy to build yourself into a corner, so that you're DAL can't support something you need to do now.. Honestly I don't get the whole Ruby on Rails thing. I've looked at it, and it seems (like anything new in this industry) that its wildly overhyped. And the hype seems to be dying down already, at least from my perspective.
Regardless, I love functionality that NHibernate provides, so I'm still going to look into it.
I have to look at it as well.
Well remember that your BOs may not (and likely will not, the more Csla you do) resembly your db tables at all. So having a DataSearcher exposed to the UI layer from the business layer wouldn't be that useful. The concept of SearchCriteria (or SelectionCriteria, as my DAL calls it) I think DOES translate nicely into the BO would. Basically your BO would take a Business level SearchCriteria and know how touse the DAL's DataSearchers and SelectionCriteria to preform the requested search.I don't really get that. Kind of just seems like semantics to me. Your DataSearcher class is part of your data access library. But since I am (currently) using ADO.Net from my DP methods, I don't have a data access library to add this class to. My plan is to use ADO.Net in my DataSearcher, and this class will only be used when the user is literally doing a search of some sort, so it's not going to be a data access layer really. I dunno... where am I going wrong?
I really like the idea posted by ajj3085. I was on the site looking to see if anyone had posted anything about overloads for the Exists method or others that would require more flexibility in the Command objects but I think that what you've described here might do the trick across the board for us.
Our questions starts because we wanted to be able to overload the Exists method to accept other key fields that would commonly be used to look-up an object besides it "hidden" unique identifier. But in the back of our minds was also the need to do more complex searching, including Advanced Search-like functionality. Using the CommandBase approach, we would have to create a separate (nested) class for each overload we created and it still didn't address the need for user-definable, on-the-fly type searches.
It sounds like the method you've described is flexible enough to work for a simple, single criteria search up to a user-defined, complex, multi-parameter search. I really like the fact that it appears you can add multiple criteria to each property, such as "LIKE 'a%' OR 'b%'" type searches. That is the flexibility we are looking for.
So, thanks for getting us on the right path!!!
What I am still wondering is the role each class plays in your approach. It is obvious that the SearchCriteria class includes properties corresponding to each possible search parameter. Then the SelectionCriteria class is used to map this information for use by the DataSearcher object. Am I correct to assume that the DataSearcher class is simply a helper class that then converts the SelectionCriteria objects into the SQL necessary to carry out the search and executes it against the data source? If that's the case, then this method uses dynamic SQL exclusively (i.e. no stored procedures)?
It sounds like the method you've described is flexible enough to work for a simple, single criteria search up to a user-defined, complex, multi-parameter search. I really like the fact that it appears you can add multiple criteria to each property, such as "LIKE 'a%' OR 'b%'" type searches. That is the flexibility we are looking for.
Am I correct to assume that the DataSearcher class is simply a helper class that then converts the SelectionCriteria objects into the SQL necessary to carry out the search and executes it against the data source? If that's the case, then this method uses dynamic SQL exclusively (i.e. no stored procedures)?
It sounds like the method you've described is
It doesn't use stored procedures, it builds a select against a view. The reason I chose this route is because I didn't want to have a complex search stored procedure or many stored procedures, one for each criteria combination. You could certainly go an stored procedure route though.
The problem with search procs is that you end up building dynamic sql in the procedure itself, or needing HUGE numbers of slightly different procs, each which take different arguments. Neither is easy to maintain.
I'm looking through that example. Mine gets a little more complex. I'm wondering if it fits.
Some items will ahve multiple values. For instance the person can pass in one or more brand_ID's. So I wonder if I should pass in an ArrayList or create a collection?
Also, after the basic criteria is passed in there is an option to do advanced. In this situation any field in teh dbase can be selected and a value paired with it. One or many of these value pairs.
For instance my criteria may be something like this
Brand A OR Brand X
From $35 MSRP to $85 MSRP
Model like '%tent%'
Then they pass in
Color = Blue
ShipDate < 1/1/07
They aren't defined criteria, they would be Advanced Criteria and could be one of 100's of fields.
So I would be looking at something liek this from my UI.
SearchCriteria sc = new SearchCriteria()
ArrayList alBrands = new ArrayList();
alBrands.Add(Brand_ID1);
alBrands.Add(Brand_ID2);
alBrands.Add(Brand_ID3);
sc.Brands = alBrands:
sc.PriceType = "MSRP";
sc.PriceFrom = 35;
sc.PriceTo = 85;
sc.TextSearch="tent";
Hashtable hAdvCriteria = new Hashtable();
hAdvCriteria.Add("Prd_Color","Blue");
hAdvCriteria.Add("Prd_ShipDate","1/1/07");
sc.AdvCriteria = hAdvCriteria;
ProductSearchInfo pSearchInfo = ProdcutSearch.GetProducts(sc);
gvProductResults.DataSource = pSearchInfo;
gvProductResutls.DataBind();
The problem I see is that the advanced criteria may also require an operator. What if I want to say < or > or <= instead of just equals. I can't use a hash table, then I have to use a some kind of simple object instead of a hashtable I guess.
ProductSearch would be a CSLA ReadOnlyCollection that fills with the prodcuts I want (Model, Name, ID, Price, etc.).
I get a little lost after this when I get inot my objects and DAL. I think I'd like to set all of the CSLA Criteria and then pass it all into a big Stored Procedure with CASE selects. However, I need to somehow pass those Brand_IDs in (and another field that could have 0 to many values selected) and I need the Advanced Criteria to probably be generated into a dynamic SQL clause that I pass in and append to the end of hte WHERE. I'm not sure.
I don't think I can squeeze that into ajj's example. ANyone have any other directions to go in or suggestions?
My impression from all of this is that it is all valid. I think the ultimate goal is to move the DataSearcher functionality into the business objects so that they have control/oversight on what criteria are specified and how. I am visioning a common SearchCriteria object that, in my mind, can be based on a hashtable. Let me explain...
The UI element(s) that you are using to collect the search parameters would have to be wired up and would presumably be based on the object being searched for. Ours is very similar to the Advanced Search dialog in Outlook - thus our need for tremendous flexibility. This "wiring" would dictate what fields were available for inclusion in the search criteria and the interface would essentially create a list of clauses that would be used in the search. When submitted, the UI information needs to be translated into a form usable by our object and, ultimately, our data access code. So, each item in the list of criteria becomes a SearchClause item, for instance, which defines the field/column/property name, the condition (like, between, less than, equal to, etc. - enumerated like Andy's done perhaps) and the value entered by the user. The SearchCriteria object would be a hashtable using the SearchClause.ColumnName (or whatever you name it) as the key and the SearchClause object as the value. Following this approach allows us to reuse the SearchCriteria and SearchClause objects for any and all searches and the use of the hashtable is condusive to grouping multiple criteria for the same column. Then, our business object will expose a Search or Find method accepting a SearchCriteria object.
We have already implemented a CommandBuilder for our business objects to create our Select, Insert, Update and Delete commands. We are able to do this because we have a rigid set of design standards which allow us to automate the process. I can see us adding an additional method called GetSearchCommand which takes the SearchCriteria object as a parameter - as well as the name of the table/view being used, I suppose - and parses the SearchCriteria object to create the necessary SQL (e.g. SELECT * FROM tableName WHERE ...). The result would be the IDbCommand object that can be used by the business object.
Then, there really is no reason that the Data Portal can't be expanded to include a Search (or Find) method as part of its interface making it cleaner to implement the call in BO code. Then the BO would have a DataPortal_Search method that would execute the command on the server, retrieve the records and do something with it.
In our case, Search functionality will only be available through our collection classes and I am thinking it would be exposed as a static method returning the collection itself. The result will be the same collection object filtered to only include the objects returned from the search. In addition, we would add a SearchCriteria property that would return the search criteria used to create the collection. This would allow UI code to recognize that the collection is a subset of data and/or display what criteria was used.
Obviously, you can tell I am thinking on-the-fly about this and haven't implemented any of it yet. But, this is where my thoughts are.
Anyway, the nice thing about this is that we can always change the default behavior when and where it is applicable. So, if we have only a small number of fields and want to implement the query as a stored procedure, we can do so by replacing the command created by our CommandBuilder with a call to a stored procedure with each SearchClause representing a parameter to be passed in the DataPortal_Search method of our BO.
Make sense?
One thing that comes to mind is the concept of searching "within these results". Any ideas on how this could be implemented within this context? My goal when implementing this is to not incur another round-trip to the database since we already have the data. I was going to return the new resultset as a View of the original, but it seems that implementing this would require the same criteria selection features but a totally different implementation. Thoughts?
btw - great discussion! An example of how forums are supposed to be used and can benefit us all!
Essentially you can create a SearchCriteriaBase that everything can inherit from that has essentials like "GetSQLWhereClause", but could also have things like "GetXML" so you could save the query as XML for re-use, etc..
Each implementation of SearchCriteriaBase can be as specific or generic as you desire, you could create a SearchCriteriaStandard that has your Brands, PriceType, PriceFrom, etc. as specific properties so that you aren't adding those criteria 1-by-1. The downside to less generic objects is when the user decides that they want to see: "From $35 MSRP to $85 MSRP OR From $20 Actual Retail to $60 Actual Retail" or something like that.
One issue I see with all of this is that forcing your BO consumer to add criteria by entering a string literal (such as "PriceType" or "Prd_Color") is dangerous.
To fill you all in on our progress, we have begun implementing our concepts. The first part of this is the SearchCriteria class which is similar to the ValidationRules class in that it is a collection of lists. Each list corresponds to a single property/column used in the criteria and each item in the list is a specific SearchClause item containing the condition (equals, like, between, etc.) and the value. I believe this mimics what has been discussed/suggested thus far.
We can iterate through all of the clauses as follows:
Console.Write("WHERE ");
foreach (Acme.SearchClause clause in list)
{
Console.Write(String.Format("([{0}] {1} {2})", clause.PropertyName, clause.Condition.ToString(), clause.Value.ToString()));
Console.WriteLine(" OR ");
}
(Okay, not perfect - quick & dirty example)
We have created a SearchCommandBuilder class which accepts the SearchCriteria object and generates the necessary SQL. Part of the rationale for this is that our foundation is data agnostic with our base CommandBuilder class equipped to handle variations between SQL Server, Oracle, Access, MySql, etc. Our new class is based on the original which allows us to automatically and cleanly generate the proper SQL for the particular database in use. For each list in the SearchCriteria class, we OR the list items then AND each list together to form the WHERE clause. This allows us to have conditions like those referenced earlier (Brand 'A' OR Brand 'B' AND Price BETWEEN $35 AND $85, etc.).
We've added a Search method to the data portal which takes the type of collection being created and the SearchCriteria object as parameters. On the server, the DataPortal_Search method passes the SearchCriteria object to the SearchCommandBuilder.CreateSearchCommand method which returns a System.Data.IDbCommand object. We then execute the command just as any other Fetch operation. The end result is a populated collection returned to client code.
To handle managing searches, such as persisting a restoring, we have a SearchManager class. Because our items are all serializable, we are able to easily convert the SearchCriteria class and all of its contents into a useable form. The SearchManager class exposes SaveAsBinary, SaveAsXml, LoadFromBinary and LoadFromXml methods which hande the rigors of persisting and restoring the objects. Overloads allow us to specify either a Stream object or filename for all four methods and gives us pretty good control over the whole thing.
FYI - we considered adding ToXml, ToBinary, FromXml and FromBinary methods to our base classes to centralize these operations and ended up creating our own Serialization class that exposed these methods and our objects collaborate with this class for the work. This class allows derivation so that we can customize/replace this in the future if needed.
So far so good. This model is working for us and working well. It is consistent with other areas of the framework, which was a plus, and generic enough to work with any object in our applications - which was our goal.
Hope that helps.
To be consistent with the book, the Fetch method is expecting an object derived from CriteriaBase to be passed as the argument. For searching, we need to pass our new SearchCriteria object. I suppose we could derive this object from CriteriaBase but we based the class on one of our collection classes instead. As a result, we couldn't pass this to the existing Fetch method.
In addition, even though they follow a parallel path, we have to do two different things when Fetching and Searching. In our DataPortal_Fetch() method, we make use of the CommandBuilder.GetSelectCommand() method to execute the necessary SQL and populate the collection. In the case of the DataPortal_Search() method, we pass the SearchCriteria object to the CommandBuilder.GetSearchCommand() method then proceed with populating the collection. Obviously, we could have still done all of this in a single method by evaluating the type of object passed to the DataPortal_Fetch method.
In our original design, we did back-out the CriteriaBase type from the DataPortal_Fetch method and went with an arbitrary System.Object type for the criteria argument instead. This allowed us to pass both a CriteriaBase-derived object and SearchCriteria object to the method. But, as we proceeded, we decided to split it into a separate method so that the function of each method was more clearly delineated. You see, one of the major factors in all of our design decisions is that our framework is to be used by developers not involved the design and development of the framework; not part of these discussions and probably without the same level of understanding of the principals. So, KISS rules! That being Keep It Simple, Stupid - not the guys in makeup and spandex...
When we thought about the eventual needs of our development team and trying to keep everything as simple and straight forward as possible, having a separate method seemed like the most logical way to go. This, again, clearly delineates what purpose each method serves plus it allows us to override each method independantly if and when needed.
Hope that all makes sense.
To be consistent with the book, the Fetch method is expecting an object derived from CriteriaBase to be passed as the argument. For searching, we need to pass our new SearchCriteria object. I suppose we could derive this object from CriteriaBase but we based the class on one of our collection classes instead. As a result, we couldn't pass this to the existing Fetch method.
Obviously there are many ways to accomplish the same thing. Everyone will have their own requirements, thought processes, preferences, etc. This is just how we did it. If you are uncomfortable with this approach, by all means, change it to suit your needs.
Our choice was to inherit our SearchCriteria class from a base collection class thereby making it impossible to also inherit from CriteriaBase, but you could obviously go the other direction. Or, as you said, embed the collection in another object that is derived from CriteriaBase. And, I'm sure there are many other ways that this could be done. As long as it works in the end, right?
).That's an interesting way of doing it John. I'm no SQL guru and I don't want to knock your suggestions but I think you lose effective use of indexes when you put CASE and other stuff into the WHERE clause so its not good for big tables.
Copyright (c) Marimer LLC