Is it a oop good design ?

Question

Hi all,

I'd like to know what you think about this part of our program is realized :

We have in our database a list of campsite.

Partners call us to get all the campsites near a GPS location or all the campsites which provide a bar (we call it a service).

So how I realized it ?

Here is our database :

Campsite
- ID
- NAME
- GPS_latitude
- GPS_longitude

CampsiteServices
-Campsite_ID
-Services_ID

So my code (c# but it's not relevant, let say it's an OO language) looks like this

public class SqlCodeCampsiteFilter{
  public string SqlCode;
  public Dictionary<string, object> Parameters;
}

interface ISQLCampsiteFilter{
   SqlCodeEngineCore CreateSQLCode();
}

public class GpsLocationFilter : ISQLCampsiteFilter{
  public float? GpsLatitude;
  public float? GpsLongitude;
   public SqlCodeEngineCore CreateSQLCode()
          {
    --return an sql code to filter on the gps location like dbo.getDistance(@gpsLat,@gpsLong,campsite.GPS_latitude,campsite.GPS_longitude) with the parameters
  }
}
public class ServiceFilter : : ISQLCampsiteFilter{
  public int[] RequiredServicesID;
   public SqlCodeEngineCore CreateSQLCode()
          {
    --return an sql code to filter on the services "where ID IN (select CampsiteServices.Service_ID FROm CampsiteServices WHERE Service_ID in ...)
  }
}

So in my webservice code :

List<ISQLFilterEngineCore> filters = new List<ISQLFilterEngineCore>();
if(gps_latitude.hasvalue && gps_longitude.hasvalue){
  filters.Add (new GpsLocationFilter (gps_latitude.value,gps_longitude.value));
}
if(required_services_id != null){
  filters.Add (new ServiceFilter (required_services_id ));
}
string sql = "SELECT ID,NAME FROM campsite where 1=1"
foreach(ISQLFilterEngineCore aFilter in filters){
  SqlCodeCampsiteFilter code = aFilter.CreateSQLCode();
  sql += code.SqlCode;
  mySqlCommand.AddParameters(code.Parameters);//add all the parameters to the sql command
}
return mySqlCommand.GetResults();

1) I don't use ORM for the simple reason that the system exists since 10 years and the only dev who is here since the beginning is starting to learn about difference between public and private.
2) I don't like SP because : we can do override, and t-sql is not so funny to use :)

So what do you think ? Is it clear ? Do you have any pattern that I should have a look to ?

If something is not clear please ask

Answer 1

Looks reasonably clear, and would probably work. It is a little different from the Query Object pattern (See Fowler, Martin. Patterns of Enterprise Architecture. Addison Wesley, 2003), but not too far off.

This has a class named Query, which has a collection of Criterion objects.

The Criterion objects would have the operator, field, and filter value to filter on (in Java, sorry):

Class FloatCriterion implements Criterion {
    String _operator;  // = "="
    String _fieldName; // = "GPS_latitude"
    Float _value;     // = 43.21

    String getSql(){
        // build the where criteria
    }
    Param  getValue(){
        // return the param value
    }
}

The Query object would have your base query:

Class CampsiteQuery implements Query {
    String _baseQuery = "SELECT ID,NAME FROM campsite where 1=1"
    Collection<Criteria> _criteria;

    void addCriterion(Criterion crit) {
        _criteria.add(crit);
    }

    String buildSql{
        // concat _baseQuery with each Criterion.getSql
    }

    List<Param> getParams {
        // build list of params from criteria
    }

    List<Campsite> get Results {

    }

}

From there there should be a service that will accept a query and do the work of talking to the database.

This will put you in a position where moving to an ORM tool will be a little less arduous when you get to that point.