I am using LLBLGenPro version 3.5 with self servicing. I have a logic which generate SQL query and then populate it in datatable using function GetMultiAsDataTable. Now i am not able to find some of the columns in the datatable, even though the generated SQL is correct and those columns have value in SQL Database.

Pseudo Code:

Dim fields As HelperClasses.ResultsetFields Dim dtTable as new Datatable dtTable .Columns.Add("Code", GetType(System.String)) fields = New HelperClasses.ResultsetFields(2) /* Code for Query Generation */ With fields .DefineField(TableA.Active, 1, "Active") .DefineField(TableB.RID, 2, "RID") End With

Dim dao As New DaoClasses.TypedListDAO dao.GetMultiAsDataTable(fields, dtTable, 0, sortQuery, selectFilterA, relationsToUse, False, Nothing, Nothing, 0, 0)

For example, above RID will not be populated in dtTable. dttable.Columns.Contains("RID") returns false in this case. What could be reasons behind this?

Please note, same code work in LLBLGen version 2.5

• Are you sure that RID column exists on TableB?
• Also, What is the relation between TableA and TableB? (shouldn't you specify the involved relation?)

Finally, specifying the real code is key, because the problem could be due to something in the actual code.

Yes, the column RID exists in TableB in datatbase. As i have mentioned there is no problem in query generation. If i run the generated SQL query in SQL Manager, then it show all column.

I dont think reation between TableA and TableB has to do something with this problem, had they being wrong, i would have got wrong SQL Query, hence incorrect output in SQL Manager itself.

One important thing to note here is if i skip the line dtTable .Columns.Add("Code", GetType(System.String)), then everything works fine.

One more query, is there a way i can get the source code of ORMSupportClasses and DQEClaasses. I remember it was provided in older version (v2.5), but could not find this for version 3.5. My intention is to debug the code of GetMultiasDataTable, is that possible in version 3.5.

nmeena wrote:

One important thing to note here is if i skip the line dtTable .Columns.Add("Code", GetType(System.String)), then everything works fine.

Aha. It should because the way the dataTable is filled. Please add your custom column AFTER it's populated.

nmeena wrote:

One more query, is there a way i can get the source code of ORMSupportClasses and DQEClaasses. I remember it was provided in older version (v2.5), but could not find this for version 3.5. My intention is to debug the code of GetMultiasDataTable, is that possible in version 3.5.

It's available at LLBLGen website -> CustomerArea -> v3.5 Downloads.

Hi,

I debugged the ORMSupportClasses code and found the root cause for the missing column. Consider the following scenario:

I have a datatable with some columns, now i am firing SQL Query to populated this datatable, now some of the columns from the datatable are not there in select query. Also the order of datatable columns are bit different from the order given in SQL Query.

Problem: Now, the constructor in Doabase is always setting _stringCacheForFetcher. After setting this, the code uses datareader to fill the datatable. but i found due to a code logic problem in function Fill in ORMSupportClasses\Miscellaneous\DataTableFiller.cs, the ordinals of the datatable columns are not set properly and resulting in missing columns.

Solution: For now i have set the _stringCacheForFetcher to NULL in DoaBase constructor, but i am looking for a better solution if any? I can think of setting the property StringCacheForFetcher from the constructor in CommonDaoBase as well, But more or less it same. I have also modified the Fill method in case we stick to setting the _stringCacheForFetcher from consturtor. Please see the changed functin Fill from the attachment.

Please let me know if you have any better approach toward this.

We'll look into this.

Your modification skips situations at line 90, as you simply continue, but the situation where the column is already there is then not honored (the type specified in the column).

the string cache presence is an indication it can use our own datatable filler. If the string cache is absent, then it uses the .NET's own datatable filler inside the DbDataAdapter.

Projections of object values work on ordinal, not name. The thing is that the datatable fetcher works best if it can create its own datatable to fill. If you want to fetch the data into a datatable which is pre-setup, you can, but it can't deal with a different datatable which doesn't match the resultset of the query.

My question then is: why do you run a mismatching query to fetch data in a datatable which doesn't match the resultset? After all: the more mismatches you create, the more chance you have to run into a problem at runtime.

We have to populate some columns dynamically during filling of the datatable using datacolumn expression, that why we need to create a datatable with pre defined columns which eventually results in mismatching query.

Initially there is only one for loop to set the ordinals, but in the modified code there are two for loops, starting at 79 and another one from line 113, the first for loop is setting the ordinals of the already created columns in the datatable matching with the columns output by the query, second loop is setting the ordinal of remaining output columns.

this solves the problem and covers all the scenarios. Can you please explain a bit the scenarios which can still not be covered here from the modified code?

We have to populate some columns dynamically during filling of the datatable using datacolumn expression, that why we need to create a datatable with pre defined columns which eventually results in mismatching query.

IMHO, you can just fetch a dynamicList (new dataTable, and then merge it with the dataTable you have.

(answering question about the code)

nmeena wrote:

Initially there is only one for loop to set the ordinals, but in the modified code there are two for loops, starting at 79 and another one from line 113, the first for loop is setting the ordinals of the already created columns in the datatable matching with the columns output by the query, second loop is setting the ordinal of remaining output columns. this solves the problem and covers all the scenarios. Can you please explain a bit the scenarios which can still not be covered here from the modified code?

Projections use a 2 step process. Step 1 is filling an object array where each position is filled by the value projector at that index by passing in the values array obtained from the datareader. Step 2 is consuming the array created in step 1 to an element in the destination, be it a datatable row, entity or other.

I now see you have two loops, not one. I overlooked that. Say I have a datatable with the columns: A, B, C, D.

I have a query which returns: C, B, D, E. 'fieldsPersistenceInfo' is the array with the persistence infos for this query, and it contains the persistence infos for C, B, D, E in that order.

to produce a projection row for A, B, C, D, I have to add the projectors for A, B, C and D, in that order, to the valueProjectors list. Your first loop skips 'A', as it's not in the schemaRowPerColumnName list (meaning it's not in the query's resultset). So it doesn't handle all cases. It only handles the cases where you have appended columns to the datatable. Our code doesn't do this either, I'm not blaming you for anything, just to show you it's not a 'fix for all'.

It should do this: - per column in the datatable, it should create a valueprojector which either reads the value from index X if the column is in the resultset at index X, or insert NULL if not (so a dummy projector) - skip the rest. - send this valueProjector list to the projector.

IMHO you can achieve this with the projector api: setup the projectors as you want to, which match the datatable you want to read into, and then use the DataProjectorToDataTable class to project the resultset to the existing datatable. The DataProjectorToDataTable class already can deal with existing columns and doesn't re-create them. You can also pick that class' sourcecode and alter it a bit (it now always tries to recreate columns, and adds a column for each projector in the list).

As this type of code is very cumbersome to get right, and the getmultiasdatatable is primarily meant for fetching into an empty datatable, we won't change the runtime at this point. I'd like to suggest to you to use the projector api instead for this particular query/datatable. If you need help with that, please let me know.

Could you please provide me any documentation or example relating to usage of projection API? It will be helpful..

Please start here: http://www.llblgen.com/documentation/3.5/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/SelfServicing/gencode_datareadersprojections.htm