If you can't pull in the entire SQL table to your external list, then there's no way you're going to be able query that data set as you could a SharePoint List.
However, I can offer a solution that we've used for scenarios virtually identical to this that has worked extremely well for us. In our scenario we're querying an Oracle database which takes forever to return large data sets.
The approach we took was to use the Factory pattern to determine by what means the data source (SharePoint List, external database, etc) should be queried.
The examples below are a tad trite, but they illustrate the concept well.
So, start out with an interfaced which defines how the data set will be queried and what fields will be returned:
public interface IQueryData
{
string ListToQuery { get; set; }
List<MyResultObject> ExecuteQuery();
}
You'd have a custom object that represents a single record returned by the query
public class MyResultObject
{
public string FileRef { get; }
public string Title { get; set; }
// any other fields you'd like to see potentially returned...
}
Then you'd have a data provider which implements this interface for the SQL data source
public class SqlDataProvider : IQueryData
{
public string ListToQuery { get { return "BigSqlTable"; } }
public List<MyResultObject> ExecuteQuery()
{
// query your external data source here...
// populate a list of MyResultObject's from the result set and return it to the consumer
}
}
You'd also have a data provider which implements the interface for a SharePoint data source
public class SharePointDataProvider : IQueryData
{
public string ListToQuery { get { return "MySharePointList"; } }
public List<MyResultObject> ExecuteQuery()
{
// query your SharePoint list here, using CAML, SharePoint object model, etc...
// populate a list of MyResultObject's from the result set and return it to the consumer
}
}
With this implementation you've encapsulated the logic and the details of the query in your respective data providers.
Now you'd have a Factory that builds the appropriate data provider (based on the specified ListToQuery parameter):
public static class QueryDataProviderFactory
{
public static IQueryData Build(string listToQuery)
{
switch(listToQuery)
{
case "BigSqlTable": return new SqlDataProvider(); break;
case "MySharePointList": return new SharePointDataProvider(); break;
// you can have many other implementations here that query your data sources in different manners
}
}
}
Finally, you'd use your factory to initiate your query, passing in the name of the data source you want to query:
public List<MyResultObject> RunQuery()
{
return QueryDataProviderFactory.Build("BigSqlTable").ExecuteQuery();
}
This pattern keeps your external implementation encapsulated into its own data provider and abstracts away the details of the query from the consumer. All the consumer needs to do is specify the name of the list they want to query and the Factory decides which implementation to initiate.
You can even make your IQueryData interface implement generics for further extensibility:
public interface IQueryData<T>
{
string ListToQuery { get; set; }
List<T> ExecuteQuery();
}
This would open the door for the consumer to also specify the type of object they'd expect to be returned.
Our query data interface actually has many more members that add even more extensibility points to our query providers, but I thought this example illustrates the point in a concise and easy-to-grasp manner.
Just wanted to offer this suggestion as it seems like virtually the same scenario we came across a year or so ago and this strategy has been working very well for us.
