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
