TableReader

You can use this function for Optiva Workflows.

Purpose

The TableReader function exposes an ADO.Net DbDataReader object.

A DbDataReader is a fast, light weight, and read only mechanism. It is useful when reading large amounts of data, which may otherwise result in an out-of-memory exception. The trade-off is that the DbDataReader is more complex to work with and has to be closed manually when done reading the data.

You should only use this function when the amount of data being queried is large. Only a single row of the returned data from the database is in memory at a time, so the memory usage is minimal. When you are using this function, complicated coding is required for reading values and advancing the record pointer. This function is forward-only. For example, once you have read row 5 and moved the pointer to row 6, you cannot get back to row 5 without re-running the query.

Syntax


Dim MyReader As DbDataReader = TableLookupEx(QueryCode, QueryArgsNumbered, 
QueryArgsNamed[, DataReaderTimeOut])

The arguments for TableReader are similar to the TableLookupEx in purpose and usage.

For TableReader, the numbered arguments are passed in a List object instead of individually listed. Optionally, you can specify a time-out value, in seconds, for queries that run a long time.

Recommendations

  • Use the Visual Basic Using statement. This allows .Net to automatically close the reader and dispose of it when the statement is finished. See Example 1.
  • Add the Imports System.Data.Common namespace to the top of your script when using the TableReader method.
  • Add the Imports System.Collections.Generic namespace to the top of your script when using the QueryArgsNamed argument or the QueryArgsNumbered argument. See Example2.

Example 1


Using TPReader As DbDataReader = co.TableReader("MYQUERYCODE", Nothing, 
Nothing)
   If TPReader Is Nothing Then
   MessageList("Unable to open DbDataReader for QueryCode = 'MYQUERYCODE'.  
Missing FsQuery entry?")
   Else
      While TPReader.Read()
         Dim Field1Value As String = TPReader(1).ToString()
      End While
   End If
End Using

Example 2


Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Dim NumberedArgs As New List(Of String) From {"A", "B", "C"}
Dim NamedArgs As New Dictionary(Of String, String) From {{"%TOKEN1", 
"VALUE1"}, {"%TOKEN2", "VALUE2"}}
NamedArgs.Add("%TOKEN3", "VALUE3")
Using TPReader As DbDataReader = co.TableReader("MYQUERYCODE", 
NumberedArgs, NamedArgs, 120)
   If TPReader Is Nothing Then
      MessageList("Unable to open DbDataReader for QueryCode = 
'MYQUERYCODE'. Missing FsQuery entry?")
   Else
      While TPReader.Read()
         Dim Field1Value As String = TPReader(1).ToString()
      End While
   End If
End Using

The system automatically replaces the tokens in the SQL for these names:

  • [%USER]
  • [%LAB]
  • [%GROUP]
  • [%%CURRENT_DB_NAME]
  • [%%SEARCHTABLE] - This token is only available during the Search's PostSearch event.