TableLookupEx

Purpose

Executes a SQL Query with the supplied parameters. Returns an ADO.NET data table object that is filled with columns and rows from the query. The return is a complete data table, and should be defined as DataTable type.

The "Imports System.Data" declaration can also be added at the beginning of the script. This avoids adding a prefix to each line in your script, where needed, with “System.Data.DataTable”. From there, use DataTable functions to count or specify table positions. These are standard VB.net concepts you can find in MSDN.

You can use this function in most scripting scenarios. This function allows for flexible post-query filtering, sorting and manipulations of the DataTable. You can also access all of the data returned from the database. This means you can access any data row at any time.

This function is heavy on memory usage. The DataTable object is a complete in-memory copy of all the rows and columns that the SQL returned. You could consume all available memory.

Syntax


Dim variable As Object = TableLookupEx(QueryName, DataTableName[, paramN])

As an alternative, you can use this syntax. This syntax supports named query arguments.


Dim MyDataTable As DataTable = TableLookupEx(QueryName, DataTableName, QueryArgsNamed[, paramN])

Arguments


Part	Description
`QueryName`	Name of the query in FSQUERY to be executed. The tokens are replaced by the system in the SQL for these names: `[%USER]` `[%LAB]` `[%GROUP]` `[%%CURRENT_DB_NAME]` `[%%SEARCHTABLE]` - This token is only available during the Search's `PostSearch` event.
`DataTableName`	Optional. The name of the returned ADO.NET data table. The default is `“TableLookup”`. To draw an analogy, a DataTable is a sheet in an Excel workbook. It has the columns and rows from the SELECT statement you used to fill that table. The object is in-memory and not directly attached to the database. You can update values on this `datatable` object and it does not update the database. You can filter or sort the data in this table. The `DataTableName` argument that is in the `TableLookupEx` workflow function is optional. The default value is `“TableLookup”`. Whatever you have for that value becomes the name of the `DataTable` object. It is not likely that you are going to change this value. Most of the time you are not working with the name of the `DataTable`. You are working with the `DataTable` object itself. You can do this command multiple times in your script, each time returning a `DataTable` object. To merge all of these tables into a single `DataSet` object, you may want each `DataTable` to have a unique table name. Or, each table can be merged into a single `DataTable` object. See more information about this at the Microsoft help link: http://msdn.microsoft.com/en-us/library/y4b211hz(v=VS.90).aspx
`paramN`	Optional. The parameters can be in the form of an array or a comma-delimited string of the individual arguments of the query.
`QueryArgsNamed`	Enables you to use custom named query arguments in your SQL. Numbered query arguments, which are used by the `QueryArgs` argument. The arguments are wrapped with single quotes in the SQL. Named query arguments are not wrapped with quotes automatically. This can be useful when you pass a numeric value to your SQL. When you use the `QueryArgsNamed` argument, add the namespace, `Imports System.Collections.Generic`, to the top of your script .

Example 1


Dim rv As DataTable = TableLookupEx("MyCustomQueryCode", "TABLELOOKUP", 
arg2, arg3…)

or


Dim argArray as Object = array(arg1, arg2, arg3)
Dim rv As DataTable = TableLookupEx("MyCustomQueryCode", "TABLELOOKUP", 
argArray)

Example 2

This example uses named query arguments.


Dim NamedArgs As New Dictionary(Of String, String) From {{"%TOKEN1", 
"VALUE1"}, {"%TOKEN2", "VALUE2"}}
NamedArgs.Add("%TOKEN3", "VALUE3")
Dim MyDataTable As DataTable = TableLookupEx("MYQUERYCODE", NamedArgs, 
arg1, arg2, arg3)