Using DataTable.Select in the slickest way possible
-
I recently have had the task of converting SQL statements that select directly from the database to DataTable.Select statements that select from a cache of records in memory. The SQL statements use a single table and are along the lines of:
SELECT node.Name, parentNode.Name, rootNode.Name
FROM (nodes node INNER JOIN nodes parentNode ON node.ParentID = parentNode.ID)
INNER JOIN nodes rootNode ON node.RootID = rootNode.ID
WHERE node.format = 5;Assuming I have a cache of the "nodes" table in memory (as a large DataTable), I can simulate this SQL statement with nested for / foreach loops, intermediate DataTables, and simple DataTable.Select statements. However, I was wondering if there's a slicker way of using the DataTable.Select that eliminates the need of doing so much looping and creating intermediate tables for the results.
-
I recently have had the task of converting SQL statements that select directly from the database to DataTable.Select statements that select from a cache of records in memory. The SQL statements use a single table and are along the lines of:
SELECT node.Name, parentNode.Name, rootNode.Name
FROM (nodes node INNER JOIN nodes parentNode ON node.ParentID = parentNode.ID)
INNER JOIN nodes rootNode ON node.RootID = rootNode.ID
WHERE node.format = 5;Assuming I have a cache of the "nodes" table in memory (as a large DataTable), I can simulate this SQL statement with nested for / foreach loops, intermediate DataTables, and simple DataTable.Select statements. However, I was wondering if there's a slicker way of using the DataTable.Select that eliminates the need of doing so much looping and creating intermediate tables for the results.
The Select method in DataTable is quite restricted. You cannot for example join tables, create hierarchical queries etc. I didn't quite understand why you have to loop or create intermediate results since you didn't include your actual data table usage. I'm guessing that it's because of those restrictions I mentioned. If that's the case, I'd suggest that when building the cached datatable, use the database server ability to execute complex queries and in the datatable store the information in simpler form. Since this is mostly for reading the data from the cache, denormalization is one way to make the data more easy to use.
The need to optimize rises from a bad design.My articles[^]