How to: Access Data in SQL Query Results

• Aug 24, 2020
• 2 minutes to read

This example demonstrates a way of accessing data in result sets returned by SQL queries and stored procedures.

The Session.ExecuteQuery and Session.ExecuteSproc methods return their results as a SelectedData object instance. This object provides the SelectedData.ResultSet array that stores the resulting data. The array contains one SelectStatementResult item per each SELECT statement executed in a SQL query (Session.ExecuteQuery) or stored procedure (Session.ExecuteSproc). SelectStatementResult items contain data retrieved by executing a respective SELECT statement. Some SQL databases may return a Return Code when executing a stored procedure. In this situation, an additional SelectStatementResult item containing the Return Code, is appended to the SelectedData.ResultSet array.

The SelectStatementResult items provide the SelectStatementResult.Rows property, which allows you to access resulting rows as an array of SelectStatementResultRow objects. Each SelectStatementResultRow object has a Values property, providing an array of values that correspond to individual columns in a resulting row.

The following code snippet shows how to display values of the first two columns from a SelectedData object:

C#

foreach (SelectStatementResultRow row in selectedData.ResultSet[0].Rows)
    Console.WriteLine(string.Format("{0}\t{1}", row.Values[0], row.Values[1])); 

VB.NET

For Each row As SelectStatementResultRow In queryResult.ResultSet(0).Rows
    Console.WriteLine(String.Format("{0}" & Constants.vbTab & "{1}", row.Values(0), row.Values(1)))
Next row

The Session.ExecuteQueryWithMetadata method retrieves the types and names of retrieved columns along with the query result set. The SelectedData.ResultSet array returned by this method contains one SelectStatementResult holding the metadata plus one SelectStatementResult item per each SELECT statement. The metadata SelectStatementResult contains rows corresponding to retrieved columns. Each row contains three values - the name of a column, the underlying SQL data type, and a .NET type to which the columns value is mapped.

The following code snippet shows how to display names of the retrieved columns from a SelectedData object:

C#

foreach (SelectStatementResultRow row in selectedData.ResultSet[0].Rows)
    Console.WriteLine(row.Values[0]); 

VB.NET

For Each row As SelectStatementResultRow In queryResult.ResultSet(0).Rows
    Console.WriteLine(row.Values(0))
Next row