I recently integrated SS into an MVC project that calls a stored procedure to return some search results. I will post the code below. Recently it started happening that some search terms were returning empty results however if I execute the exact SQL:
exec GetEntitiesInformation 'term', ''
I get results.
I have outputted the sql statement and it is correct and runs fine in SQL Studio. My only guess is it is some kind of mapping issue as the stored procedure is returning some data from a view that does a bunch of unions. Would this cause an issue and if so how would I go about troubleshooting it and/or do you have any advice?
Query with results: https://www.dropbox.com/s/sojcswal7e2nneu/2017-12-20_17-07-34.png?dl=0
Webservice returning no results: https://www.dropbox.com/s/ok70t0cwwnpc5an/2017-12-20_17-11-40.png?dl=0
The code isn’t complex or anything and all fields are strings except the type which always is an int as it is hard coded in the view:
[Alias("EntitiesInformation")]
public class EntityInformation
{
public string EntityID { get; set; }
public string ExternalID { get; set; }
public string Name { get; set; }
public Enums.EntityType EntityType { get; set; }
public string ClientID { get; set; }
}
public class SearchLogic : BaseLogic, ISearchLogic
{
public IRepository<EntityInformation> EntitiesInformation { get; set; }
public List<EntityInformation> GetEntitiesByTextFilter(string textFilter)
{
var data = EntitiesInformation.Exec("GetEntitiesInformation", textFilter, "");
return data;
}
}
public class CommonService : BaseService
{
public ISearchLogic SearchLogic { get; set; }
public List<EntityInformation> Get(SearchByTextFilter request)
{
return SearchLogic.GetEntitiesByTextFilter(request.TextFilter);
}
}
Exec on the Repository:
public List<T> Exec(string spName, params object[] args)
{
var sqlExpression = $"EXEC { spName } " +
string.Join(",", args.Select(x => x is string ? $"'{ x.ToString() }'" : x.ToString()));
using (var db = DbFactory.OpenDbConnection())
{
return db.SqlList<T>(sqlExpression);
}
}