Is there a way to enable CacheResultset for stored procedure?

Usually we can write DynamicQuery & enable CacheResultset to retrieve data from cahce
But for some scenario, we need to retrieve data table from stored procedure (with cache)

I can get IRetrieveQuery from StoredProcedureCall class
But I'm not sure which function I can use to retrieve data table from stored procedure (with cache)

We are using LLBLGen Pro version 5.8

Example:

var qf = new QueryFactory();
var query = RetrievalProcedures.GetCustOrdersDetailCallAsQuery(10254);
query.CacheResultset = true;
query.CacheDuration = new TimeSpan(0, 0, 10);
var rows = new DataAccessAdapter().FetchQueryFromSource(qf.GetCustOrdersDetailQsTypedViewProjection(), query);

Thanks for your prompt reply

I got one more question
Based on your example code, we need to create typed view from our stored procedure to call this function
Is it the only way to retrieve data table from stored procedure with cache?
I mean, is there any other similar function that doesn't require typed view

Walaa wrote:

Example:

var qf = new QueryFactory();
var query = RetrievalProcedures.GetCustOrdersDetailCallAsQuery(10254);
query.CacheResultset = true;
query.CacheDuration = new TimeSpan(0, 0, 10);
var rows = new DataAccessAdapter().FetchQueryFromSource(qf.GetCustOrdersDetailQsTypedViewProjection(), query);

It can be done but requires a lot more code. FetchQueryFromSource calls FetchProjection(List<IDataValueProjector> valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute), where you pass in a DataProjectorToObjectList. You can also go the route of adapter.FetchProjection and setup the projection using that method, the caching parameters are set on the IRetrievalQuery anyway, so any projection method works (as long as you setup the projection properly of course).

However as the stored procedure resultset is likely fixed, setting up the projection in the designer and generate the code for obtaining the projection from the stored proc seems easier to me.

I can get the data table from stored procedure (with cache) now
But I pass the DataProjectorToDataTable instead of DataProjectorToObjectList.

Anyway, thanks for the help!

Otis wrote:

It can be done but requires a lot more code. FetchQueryFromSource calls FetchProjection(List<IDataValueProjector> valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute), where you pass in a DataProjectorToObjectList. You can also go the route of adapter.FetchProjection and setup the projection using that method, the caching parameters are set on the IRetrievalQuery anyway, so any projection method works (as long as you setup the projection properly of course).

However as the stored procedure resultset is likely fixed, setting up the projection in the designer and generate the code for obtaining the projection from the stored proc seems easier to me.

Yes it doesn't matter which projector you pass As long as it matches the target object you want to store the data in