private string buildFeatureExpression(LookupRequest lookupRequest, LookupFilter filter)
{
string expression = string.Format("{0} {1} ", filter.Filter, filter.Operator);
string dataType = featureDataType(lookupRequest.Table, filter.Filter);
switch (dataType)
{
case "int":
expression += string.Format("{0}", Convert.ToInt16(filter.FilterValue));
break;
case "varchar":
case "date":
case "datetime":
expression += string.Format("'{0}'", filter.FilterValue);
break;
case "float":
case "double":
expression += string.Format("{0}", Convert.ToDouble(filter.FilterValue));
break;
default:
expression += string.Format("{0}", filter.FilterValue);
break;
}
return(expression);
}
private string createFeatureLookupQuery(LookupRequest lookupRequest)
{
string featureQuery = createTargetFeatureSearchQuery(lookupRequest);
string featureFilter = createFeatureFilterQuery(lookupRequest);
string query = string.Format("SELECT TOP 1 {0} FROM {1} WHERE {2}", featureQuery, lookupRequest.Table, featureFilter);
Log.Information("Feature Lookup Query: {0}", query);
return(query);
}
private string createFeatureFilterQuery(LookupRequest lookupRequest)
{
string query = "";
var end = lookupRequest.LookupFilters.Last();
foreach (var filter in lookupRequest.LookupFilters)
{
if (filter.Equals(end))
{
query += buildFeatureExpression(lookupRequest, filter);
}
else
{
query += (buildFeatureExpression(lookupRequest, filter) + " AND ");
}
}
Log.Information(string.Format("Target Feature Filter Query: {0}", query));
return(query);
}
private string createTargetFeatureSearchQuery(LookupRequest lookupRequest)
{
string query = "";
var end = lookupRequest.LookupFeatures.Last();
foreach (var feature in lookupRequest.LookupFeatures)
{
if (feature.Equals(end))
{
query += feature;
}
else
{
query += feature + ", ";
}
}
Log.Information(string.Format("Target Feature Search Query: {0}", query));
return(query);
}
public LookupResponse GetFeatures(LookupRequest lookupRequest)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = this._config.connectionString;
conn.Open();
string query = createFeatureLookupQuery(lookupRequest);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
SqlDataReader rdr = cmd.ExecuteReader();
DataTable t = new DataTable("results");
t.Load(rdr);
DataSet set = new DataSet();
set.Tables.Add(t);
if (t.Rows.Count <= 0)
{
throw new Exception(string.Format("Error: no tables found for data source"));
}
JObject queryResult = JObject.FromObject(set);
LookupResponse lookupResponse = new LookupResponse();
lookupResponse.Query = query;
lookupResponse.Table = lookupRequest.Table;
JArray features = (JArray)queryResult["results"];
lookupResponse.Results = features;
rdr.Close();
conn.Close();
return(lookupResponse);
}