Obtaining Query Result sets

For queries which returned a DbDataReader when they were executed, the GUI offers the ability to obtain the resultset of the query. This is done by re-executing the query with its parameters on the database server. This means it can be the resultset is different from the one that was returned when the query was originally executed in your application.

To obtain the result set for a query, select the query in one of the views on the snapshot tab in the GUI and navigate to the Result set tab. If the query didn't return a resultset because it wasn't a query expected to return a resultset the tab isn't available.

Make sure the connection string is correct and click 'Execute' on the right. It's mandatory that the ADO.NET provider related to the factory selected is available on your system. It will use a quick check (which times out after 1 second) whether the connection succeeds. If the connection takes longer than 1 second to establish the connection open will fail. The query is executed with a DbDataAdapter into a DataSet. If the query results in just 1 resultset, the dataset has just one datatable, otherwise one for each resultset.

PostgreSql specific

If you're using PostgreSql, you have to adjust the DbProviderFactory definition in the ormprofiler.exe.config file, as Npgsql isn't registered in the GAC by default, so the factory isn't found on the system where the client is ran.

A pre-defined example is defined in the ormprofiler.exe.config file, just uncomment it and adjust the version and file location. If the factory isn't found, the client can't obtain the execution plan because it can't instantiate ADO.NET objects without a factory.

Row limits

All rows of a result set are retrieved in one go: it's not possible to do paging. As we do know the number of rows read by the reader on the original query, we can specify this amount as the limit to retrieve with the DbDataAdapter: this will order the DbDataAdapter to limit the loop over the DbDataReader to the specified amount.

The row-limit is enforced by the used DbDataAdapter on the first resultset retrieved. Although a DbDataAdapter stops reading rows when the limit is reached, it can be the underlying layer might have passed more rows to the client. So it can be that with a large resultset more rows than expected are returned from the server to the client. The row limit is specified in a numeric up/down control on the control which forms the user interface for fetching the result set.

Displaying the result set

The retrieved result set is retrieved together with its schema. For every resultset returned by the query, a schema is retrieved as well. The schema tables together with the resultsets returned by the query are bound to a selector which allows you to select which table to view in the associated grid. As there's no relationship info known between multiple result sets returned from the query, the grid won't show them hierarchically in a master-detail form. The result set pane wil display how many rows are retrieved in total and when the query was executed.

Re-executing the query at will

The user can re-execute the query at any time. When the query is re-executed, the original resultset is thrown away.

Background fetch

The query itself is executed on a background thread. The UI provides a cancel button which will terminate the command and connection. This might take a while as most ADO.NET providers will wait for a reply back from the database server. The result set retriever logic anticipates on this. The UI itself is not blocked by fetching a resultset. If the UI is focused on another command while busy executing the query, the active query is canceled. The command the UI focuses on is queued up and used when the cancel process is complete.