Database query results as Objects?

[sven.wullum1]

Hi!

I have a library which contains activities that return results from a number of different database queries.
(Examples include an activity for getting case information, another for getting events on a case, they basically all return the result from a given table based on case number input, and there could be anywhere between 3 - 10 columns). The output is set to the query result which is a datatable.

I find myself having to constantly go back to the libraries or queries to check what the column names are for the results to use in my process, and occasionally i trip up and use uppercase instead of lower case in my reference and vice-versa.

dtMyQueryResult(0)("eventNumber")

I’m currently in an “everything is an object”-phase and was thinking about returning the results as objects (specifically of type record) instead of datatables, this would give me the structure directly in my process and would eliminate the need to look up what the column names are.

myQueryResultObject.ElementAt(0).EventNumber()

Does anyone have any suggestions, has anyone thought about doing the same?

[pikorpa (Piotr Kołakowski)]

Hey @sven.wullum1
Yes, converting your DataTable output into objects (records) is a good approach to make your process more structured and avoid case-sensitive column name issues.

You can achieve this by creating a class for each table structure and converting the DataTable into a list of objects. This allows you to access the data in a more intuitive way using myQueryResultObject.ElementAt(0).EventNumber.

If you want a more flexible approach without defining classes, you can also use a List(Of Dictionary(Of String, Object)) where each row is stored as a dictionary, ensuring case-insensitive lookups.