ISqlExecutor using model created a runtime

Hi - We are exploring potential usages for your C# libraries. We have clients who have created custom datawindows for reporting purposes and are trying determine how/if this can be supported. We are thinking of using the PowerScript.Bridge.DataObject to load the .SRD and do the retrieve.

string path = @"C:\Temp\y_dw_custom_rpt.srd";
var syntax = File.ReadAllText(path);
// Creates a DataObject object using the SRD syntax
var dataobject = new DataObject("partdatatst", syntax);

// Creates a DataStore object using the DataObject object and DataContext object
var dataStore = DataStore.Create(dataobject, _dataContext);
dataStore.Retrieve();

QUESTION: Is it also possible to use the ISqlExecutor to execute other queries matching the model and populate the dataStore (from above) that was created at runtime?

string sql = @"SELECT --- FROM -- WHERE";
var listData = _dataContext.SqlExecutor.Select<TModel>(sql);
dataStore.AddRow<TModel>(listData);

I am having issue specifying the TModel created from the .SRD. I imagine this needs to use Reflection (and/or dataStore.DwMeta.ModelType) but am unclear how to do this (if possible). Any help would be much appreciated.

Thank you,

Tony

// 1. Create a .NET DataStore object from .srd file. string srdPath = @"E:\d_dept.srd"; var srd = File.ReadAllText(srdPath); var dataObject = new DataObject(srd); var dataStore = DataStore.Create(dataObject, _context); dataStore.Retrieve(); // 2. Paste your SQL, which can retrieve the same columns as the dataStore created above. var sql = "SELECT ... FROM ..."; // 3. Get a sequence of DynamicModel objects. var dataList = _context.SqlExecutor.Select<DynamicModel>(sql); dataStore.AddRow<DynamicModel>(dataList);

Comment

Tony Fahlberg
Wednesday, 17 June 2020 18:13 PM UTC

Using the DynamicModel instead of the model from the dataSource created from the .SRD appears to be limiting. I do not seem to have access to the computed columns (DWCompute) from the .SRD. I then tried to use dataSource.evaluate to execute some of the computed expressions retrieved from the .SRD meta data and it fails (most likely because of the DynamicModel).

So, there is no way to use the model from the dataStore created from the .SRD as the <TModel> in SqlExecutor?

Helpful 0

Logan Liu @Appeon
Thursday, 18 June 2020 15:19 PM UTC

Yes. If the properties of the two DynamicModel objects are different, it may cause some issues.

There still seems to be no better way to use the model from the dataStore created from .SRD as <TModel> in SqlExecutor.

I suggest to submit this issue as an enhancement request to improve the compatibility of different DynamicModel objects.

There are no comments made yet.

Tony Fahlberg
Tuesday, 16 June 2020 21:33 PM UTC

Hi - I did see that documentation but it is only half of what I am attempting to do. I can get that part working (as documented) but was also trying to use the ISqlExecutor to execute other queries matching the model and then adding the results to the dataStore (created from the .SRD). Something like the following...

string sql = @"SELECT --- FROM -- WHERE";

var listData = _dataContext.SqlExecutor.Select<dataStore.DwMeta.ModelType>(sql);

dataStore.AddRow<dataStore.DwMeta.ModelType>(listData);

Where I am having issues is when executing the SqlExecutor I am trying to specify the <TModel> from the dataStore created from the .SRD and am unclear as to how to do this (if possible).

Thanks,

Tony

Resolved ISqlExecutor using model created a runtime