Multi Parameter Searches

chiefy81 posted on Saturday, November 04, 2006

Hey team,

I am familiar with getting objects by primary key and also the list class which gets all items (by default), however, I am looking to have a series of search functions (searchbydate, searchbyname, etc) which will each give me a list back.  What is the best way to implement such functionality? 

Feel free to point me to a page in the book or other forum post if there is something I missed here or in the book. 

Thanks,

Bayu replied on Saturday, November 04, 2006

Hey chiefy,

The internal Criteria class of BusinessBase is the object that holds all parameter details for your dp_methods.

To solve your issue, you have 4 options as I see it:

- modify the particular Criteria class so it can hold all relevant information needed by your dp_method to be able to construct the appropriate query (or invoke the correct stored proc with the correct params).

- create separate Criteria classes, one for every type of query you want to support (DateCriteria, NameCriteria, etc.).

- many people have found they use the same (or similar) Criteria object in many of their classes and therefore decided to build a separate hierarchy of non-internal Criteria classes. The advantage here is that you can reuse the same Criteria (eg GuidCriteria or SearchCriteria) in multiple BOs. So while you are at it, perhaps you would like to take this route and then implement a specific class for your search queris (eg SearchCriteria).

Whichever of the former 3 your choose, in your dp_fetch you will then recieve all the info you need in the supplied Criteria. You can choose to create stored procs for every search-scenario you wish to support or you can opt for creating the SQL on the spot and fire an adhoc-query.


- the fourth approach is perhaps the one to favour: use a custom command object, for this scenario it's easier to give an example. Below a snippet of my Customer object which holds the shared Exists function. The UI can invoke this function to check if a particular Customer exists. It should be straightforward to modify this to meet your needs:

    Public Shared Function Exists(ByVal id As Guid) As Boolean
        Dim result As ExistsCommand = DataPortal.Execute(Of ExistsCommand)(New ExistsCommand(id))
        Return result.Exists
    End Function

    <Serializable()> _
    Private Class ExistsCommand
        Inherits CommandBase

        Private _ID As Guid
        Private _Exists As Boolean

        Public Sub New(ByVal id As Guid)
            Me._ID = id
        End Sub

        Public ReadOnly Property Exists() As Boolean
            Get
                Return Me._Exists
            End Get
        End Property

        Protected Overrides Sub DataPortal_Execute()
            Using cn As New SqlConnection(Database.CrmDBConnection)
                cn.Open()
                Using cm As SqlCommand = cn.CreateCommand

                    cm.CommandType = CommandType.Text
                    cm.CommandText = "SELECT COUNT(CustomerID) FROM Customer WHERE CustomerID = @id"
                    cm.Parameters.AddWithValue("@id", Me._ID)

                    Dim count As Integer = CInt(cm.ExecuteScalar)
                    Me._Exists = (count > 0)

                End Using
            End Using
        End Sub

    End Class



Hopefully this gives you some leads to continue on.

Regards,
Bayu

chiefy81 replied on Saturday, November 04, 2006

Bayu,

Thanks for the detailed response.  What you say makes perfect sense.  One of the solutions you mentioned lead me to another idea.  What if I were to have a criteria class with a string fielded, call it 'whereString'.  Now when you do a fetch you can populate this string with the where clause of a sql query.  I can then have methods on my ojbect like GetByDate(datetime) that would just call the fetch with 'where Date = " + theDate' as the criterias "whereString".  Likewise you can see how other functions could use the same criteria object and there would be no change to the object itself other than the method addition.  Has anyone used this approach before, what are the drawbacks?

Thanks!

tetranz replied on Saturday, November 04, 2006

The main issue for me when providing a flexible search has always been how to efficently do the SQL. I'm quite a fan of the dynamic SQL in an stored procedure method that Erland Sommarskog describes here http://www.sommarskog.se/dyn-search.html

As Erland points out, dynamic sql is usually a bad thing but this is one case where it probably is the best way to go and burying the nasty stuff in a sproc keeps it out of sight and still has the advantage of parameters etc to guard against sql injection etc.

Cheers
Ross

chiefy81 replied on Saturday, November 04, 2006

While I am looking for dynamic SQL, I am not looking to make this available to the front end development.  I was thinking along the lines of a stored procedure for each object similar to:

ALTER PROCEDURE [dbo].[GetAddressListDynamic]
    @WhereCondition nvarchar(500),
    @OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
    [AddressID],
    [Line1],
    [Line2],
    [Line3],
    [City],
    [StateOrProvinceID],
    [CountryID],
    [PostalCode],
    [AddressTypeID]
FROM
    [dbo].[Address]
WHERE
    ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
    SET @SQL = @SQL + '
ORDER BY
    ' + @OrderByExpression
END

EXEC sp_executesql @SQL

Once this is in place I can build any filtering fairly simply like below for finding all addresses in one zip code:

public static AddressList GetAddressList(String zipCode)
        {
            if (!CanGetObject())
                throw new System.Security.SecurityException("User not authorized to view a AddressList");
            return DataPortal.Fetch<AddressList>(new FilterCriteria("PostalCode = " + zipCode));
        }

This way the database and sql is protected from users and developers and adding additional functions is the matter of a single of code.  The procedure also uses sp_executesql mentioned in your referenced article.  What down sides does everyone see with this?

Thanks!

SonOfPirate replied on Monday, November 06, 2006

There have been some good discussions about this in the past with some great ideas.  Here are a couple links:

http://forums.lhotka.net/forums/thread/2982.aspx

http://forums.lhotka.net/forums/thread/4742.aspx

 

 

sune42 replied on Tuesday, November 07, 2006

My way of doing this is to create an enum at the top my root class like (quick and dirty sample)

 public enum SearchTypesEnum
{
 GetByCustomerID,
 GetByCustomerName,
 GetByEmail,
 GetAllWaitingForApproval
}

 
Then I have one single criteria object where I pass the
SearchTypesEnum as argument-..

private class FilterCriteria
{

public string SearchString;
public SearchTypesEnum SearchType;
public int SearchInt;

public FilterCriteria(SearchTypesEnum _SearchType, string _SearchString, int _SearchInt)
{
 this.SearchString = _SearchString;
 this.SearchType = _SearchType;
 this.SearchInt = _SearchInt;

}

 

then in my Fetch class I do like

    using (SqlCommand cm = cn.CreateCommand())
            {
                switch (criteria.SearchType)
                {
                    case SearchTypesEnum.GetByCustomerID:
                        cm.CommandType = CommandType.Text;
                        cm.CommandText = "SELECT....";
                        break;
                    case SearchTypesEnum.GetByCustomerName:
                        cm.CommandType = CommandType.Text;
               cm.Parameters.AddWithValue("@CustomerName", criteria.SearchString);
                        cm.CommandText = "SELECT....";
                        break;

            }

               
                using(SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                {
                    while (dr.Read())
                        this.Add(MyObject.GetCustomer(dr));
                }

 

Hope you all think this is a good way and that it is valid!

//andy