none
Abstraction to generate where clause with different databases. RRS feed

  • Question

  • I need some enlightement about how to design the following:

    I have a class called SearchInfo that works great with SQLServer. Its works is to generate the WHERE clause of a query.

    //Constructor:
    public SearchInfo(string searchText, string field, string criteria, Type dataType)
    
    //usage:
    var text = GetSomeText();
    var search = new SearchInfo(text, "Description", "LIKE", typeof(string));

    The criteria for the string type could be =, <=, >= and LIKE. For integer type, it cannot be LIKE, for example. So I have some validations according to the criteria and dataType.

    However, now I have to use this SearchInfo class with another database, the Progress database, and it has no LIKE; instead it has as function called MATCHES or CONTAINS.

    So I have to restructure the code in order to work either with SQLServer and Progress, but I'm having a hard to time to figure out the best approach to do it, do I create an Abstract class and inherit SQLServerTranslator/ProgressTranslator from it, and use this abstract class in the SearchInfo class?

     

    Monday, February 27, 2012 12:59 PM

Answers

  • First off, instead of using a string to define the operation, I would recommend creating an enum. There is a much lower margin of error for an enum, since it is strongly typed and checked at compile time. If you take a string, it's very easy to mistype it and not know until the functionality breaks at runtime.

    There are always a lot of options for any possible problem. Personally, I think I would go with a strategy pattern here. Your SearchInfo class can be the context, something like this:

    public enum DatabaseType
    {
        SqlServer,
        Progress
    }
    
    public class SearchInfo
    {
        public SearchInfo(string searchText, string field, string criteria, Type dataType)
        {
            // Save everything to properties
        }
    
        public string CreateQuery(IQueryStrategy queryStrategy)
        {
            // Perform your validations here before calling the GenerateQuery method on queryStrategy
        }
    }

    Then for the strategies, I'd create something like this:

    public enum QueryOperator
    {
        Less,
        LessOrEqual,
        Greater,
        GreaterOrEqual,
        Like
    }
    
    public interface IQueryStrategy
    {
        string GenerateQuery(string searchText, string field, QueryOperator operator);
    }
    
    public class SqlServerQueryStrategy : IQueryStrategy
    {
        public string GenerateQuery(string searchText, string field, QueryOperator operator)
        {
            // SQL Server implementation here
        }
    }
    
    public class ProgressQueryStrategy : IQueryStrategy
    {
        public string GenerateQuery(string searchText, string field, QueryOperator operator)
        {
            // Progress implementation here
        }
    }
    Rather than directly creating instances of ProgressQueryStrategy or SqlServerQueryStrategy, a good solution would be to use Dependency Injection. When your application starts (or whenever the database type is changed), just register one implementation of the IQueryStrategy. Then when you need to create this query, just resolve IQueryStrategy to get the correct concrete type and pass it into the SearchInfo.CreateQuery method.


    Check out My Blog. Now updated to actually work!

    • Marked as answer by Joba Diniz Monday, February 27, 2012 8:10 PM
    Monday, February 27, 2012 5:07 PM
  • I think your idea sounds like you're on the right track. Personally, though, I would not put the Validate method within the Criteria concrete instance itself.

    I'm typically a fan of self-validating classes (meaning the class internally checks to make sure it's in a valid state and all the internal properties are consistent, etc). In your case, though, the Validate method is not actually validating the Criteria instance itself. It's validating the combination of the Criteria instance and the data type. Because it is validating based on factors that are really outside the scope of the Criteria, I would put it in an outer scope. You could either put it directly in the SearchInfo class or - to keep up the pattern - put your validations in their own set of strategy classes.

    That may be overkill, and obviously your route would work just fine and be easier to implement. But in terms of strict best-practice guidelines, I'd say separate it out.


    Check out My Blog. Now updated to actually work!

    • Marked as answer by Joba Diniz Monday, February 27, 2012 8:10 PM
    Monday, February 27, 2012 8:04 PM

All replies

  • First off, instead of using a string to define the operation, I would recommend creating an enum. There is a much lower margin of error for an enum, since it is strongly typed and checked at compile time. If you take a string, it's very easy to mistype it and not know until the functionality breaks at runtime.

    There are always a lot of options for any possible problem. Personally, I think I would go with a strategy pattern here. Your SearchInfo class can be the context, something like this:

    public enum DatabaseType
    {
        SqlServer,
        Progress
    }
    
    public class SearchInfo
    {
        public SearchInfo(string searchText, string field, string criteria, Type dataType)
        {
            // Save everything to properties
        }
    
        public string CreateQuery(IQueryStrategy queryStrategy)
        {
            // Perform your validations here before calling the GenerateQuery method on queryStrategy
        }
    }

    Then for the strategies, I'd create something like this:

    public enum QueryOperator
    {
        Less,
        LessOrEqual,
        Greater,
        GreaterOrEqual,
        Like
    }
    
    public interface IQueryStrategy
    {
        string GenerateQuery(string searchText, string field, QueryOperator operator);
    }
    
    public class SqlServerQueryStrategy : IQueryStrategy
    {
        public string GenerateQuery(string searchText, string field, QueryOperator operator)
        {
            // SQL Server implementation here
        }
    }
    
    public class ProgressQueryStrategy : IQueryStrategy
    {
        public string GenerateQuery(string searchText, string field, QueryOperator operator)
        {
            // Progress implementation here
        }
    }
    Rather than directly creating instances of ProgressQueryStrategy or SqlServerQueryStrategy, a good solution would be to use Dependency Injection. When your application starts (or whenever the database type is changed), just register one implementation of the IQueryStrategy. Then when you need to create this query, just resolve IQueryStrategy to get the correct concrete type and pass it into the SearchInfo.CreateQuery method.


    Check out My Blog. Now updated to actually work!

    • Marked as answer by Joba Diniz Monday, February 27, 2012 8:10 PM
    Monday, February 27, 2012 5:07 PM
  • The only way I've seen this done (and the way I do it myself) is to create an class hierarchy with an abstract class at the root that defines methods for formatting queries, and then implementing derived classes for each database engine you support.  For example,

    public string Where(object value, WhereComparison, comparison, object compareTo);
    
    public enum WhereComparison
    {
      Equals,
      DoesNotEqual,
      Like,
      In,
    }
    

    That's a hasty example.  In my own data access library I have methods with names like FormatRangeWhereClause() and FormatInWhereClause() and so on.  But you get the idea.

    You'd instantiate the derived class that is specific to the database you use.  In my app.config, I usually include another key (aside from ConnectionString) which indicates which database system I'm trying to connect to.  Technically, this information can be embedded into the connection string (I think it's the Provider key) but I've run into problems with this whose details I can't remember off hand.

    Monday, February 27, 2012 5:13 PM
  • Tim, this pattern is great, and I know about Dependency Injection, I use it extensively.

    Before you posted, I was thinking about an abstract class called Criteria, and then I would create EqualsCriteria, LikeCriteria (or Operator), see the code below:

    public abstract class Criteria
        {
            private Type[] validTypes =
                new Type[] { typeof(short), typeof(int), typeof(long), typeof(bool), typeof(float), typeof(double),
                    typeof(decimal), typeof(DateTime), typeof(char), typeof(string) };
    
            public abstract string Text { get; }
            public abstract bool Validate(Type dataType);
    
            protected bool IsValidType(Type dataType)
            {
                return this.validTypes.Contains(dataType);
            }
    
            protected bool IsValidNullableType(Type dataType)
            {
                if (dataType.IsNullable())
                    return this.validTypes.Contains(Nullable.GetUnderlyingType(dataType));
    
                return this.IsValidType(dataType);
            }
        }


    public class EqualsCriteria : Criteria
        {
            public override bool Validate(Type dataType)
            {
                return base.IsValidType(dataType); //EQUALS serve para qualquer tipo dos tipos válidos não-nulos.
            }
    
            public override string Text
            {
                get
                {
                    return "=";
                }
            }
    
            //more code to go...
        }

    The validation of EQUALS is plain simple, but others operators are more elaborated.

    Also, I could wrap the instanciation in another class like:

    public class SqlCriterias
        {
            public static Criteria Equals { get { return new EqualsCriteria(); } }
        }

    In the SearchInfo: new SearchInfo(text, "field",SqlCriterias.Equals, typeof(int));

    So I thought using your idea of StrategyPattern with my code.

    What do you think? Is it good practice or I'm complicating things here?

    Monday, February 27, 2012 5:35 PM
  • I think your idea sounds like you're on the right track. Personally, though, I would not put the Validate method within the Criteria concrete instance itself.

    I'm typically a fan of self-validating classes (meaning the class internally checks to make sure it's in a valid state and all the internal properties are consistent, etc). In your case, though, the Validate method is not actually validating the Criteria instance itself. It's validating the combination of the Criteria instance and the data type. Because it is validating based on factors that are really outside the scope of the Criteria, I would put it in an outer scope. You could either put it directly in the SearchInfo class or - to keep up the pattern - put your validations in their own set of strategy classes.

    That may be overkill, and obviously your route would work just fine and be easier to implement. But in terms of strict best-practice guidelines, I'd say separate it out.


    Check out My Blog. Now updated to actually work!

    • Marked as answer by Joba Diniz Monday, February 27, 2012 8:10 PM
    Monday, February 27, 2012 8:04 PM
  • I get it.

    When I was writing the SqlQueryStrategy I saw that is odd to validate in the Criteria. I wrote, besides the IQueryStrategy, a base class BaseQueryStrategy, and there I put the validating methods.

    Thanks.

    Monday, February 27, 2012 8:10 PM
  •  The query operators can also be made flexible by using composite pattern to generate any combination of operation grouping. QueryOperator Enum may limit the number of operations at compile time. I have following suggestion: -


    Create an abstract class SQLOperation and SQLOperator to get the SQL result. SQLOperation can be inherited to add more operation as per the db. Left and right arguments are objects (can be number, strings or result of another SQLOperator object). Operate method of SQLOperator can be overridden to reuse the logic for default operations and add logic for additional operator specfic to db.

    public SQLOperator<T,S>{ T LeftArg{get;set;} S RightArg{get;set;} public string Operate(Operation op){ string result=string.empty; switch(op.Current){ case op.EQUALS:..... case op.GREATER:..... } return result; } } public abstract SQLOperation{ public string Current{get;set;} public const string EQUALS="EQUALS"; public const string GREATER=">";

    ..... }

    IQueryStrategy signature can be modified to use this class: -

    public interface IQueryStrategy
    {
        string GenerateQuery(string searchText, string field, SQLOperator operator);
    }



    The above implementation can be improved further but like to know how.

    Thanks

    Ven

    Tuesday, February 28, 2012 12:22 PM
  • I don't think the code you posted actually implements the composite pattern. The idea of the composite pattern is that you have a particular interface which is implemented by both individual instances and collections. What you have is a generic class which combines the operator and operands into one wrapper. It's not a bad idea, but it's not really a composite pattern.

    If you did go this route, you could place all of your validation logic inside of the SQLOperator class. Even so, I think this solution might add more complexity than it saves in this case. It might be easier if you include a Factory for the SQLOperator instances to make it easier to create and use them, but even then it can get really complex as you try to support more complex operators. For example, the between operator needs 3 operands to work on, which might require a third generic type parameter (unless you're going to reuse the second type). The 'is' operator doesn't really take a second type at all, it just checks is null or is not null.

    If you need to support those kinds of operations, you will need a relatively complex contract for the Operate method.


    Check out My Blog. Now updated to actually work!

    Tuesday, February 28, 2012 2:35 PM
  •   Sorry, I started with composite pattern but ended up with generics to have type safety :) that ended up in restriction of number of operands. Well indeed i was thinking of re-using the second type as you mentioned but the type declarations will go unreadable . But how about the below code: -

    public class SQLOperator{ 
        SQLOperator[] arguments; 
        SQLOperation operation; 
        string Value {get;set;};
    
        public SQLOperator(int arg){ Value = arg.ToString(); }
    
        public SQLOperator(string arg){ Value = arg; }
    
        public SQLOperator(DBType type){
           if(type == DBType.Null) Value = "null";
        }
    
        public SQLOperator(SQLOperation op, params SQLOperator[] args){
            arguments = args;
            operation = op;
            Operate();
        }
    
        public void Operate(){ 
            switch(op.Operator){ 
                case "BETWEEN":...//there should be 3 parameters,operate and return
                     arguments = (SQLOperator[])args;
                     Value = "BETWEEN("+arguments[0].Value+","arguments[1].Value+","+arguments[2].Value+")";
    
                      ..... 
    
             } 
         } 
    }
    

    I have treated values and operators(containing values) both as a sql operation. The contructor can be initialized with an operation and parameters or just values. In case of complex operation one can write

    new SQLOperator(
        SQLOperation.BETWEEN,
        new SQLOperator(1),
        new SQLOperator(10),
        new SQLOperator(SQLOperation.MAX, new SQLOperator("column name"))
    ).Value;

    new SQLOperator(
        SQLOperation.BEGINSWITH, new SQLOperator("longstring"), new SQLOperator("long")
    ).Value;
    
    above one for simple cases (two operands). Now i can create as many subclasses to add more complex operations like GROUP BY, AGGREGATE fns, DateTime, DBType.Null, etc.

    Will this work?

    Thanks

    Ven

    • Edited by venkyzealous Wednesday, February 29, 2012 3:24 PM
    Wednesday, February 29, 2012 7:58 AM