Using Queries in Custom Applications

Article number: KB000016

General Information

Related blocks:

Description

There are several methods of running database queries in a custom application: using standard programming interfaces or using the Query object from the repository.

Using Interfaces of Dal Assembly

The first version is used to perform a query using the Dal assembly interfaces. This method has a number of drawbacks in cases when a complex query is run:

Editing a query becomes more complex.
It is more difficult to "read" the code of the custom application.

Consider an example of parametric query performance, which adds a record to any database table. Fields values are passed by the function input parameters. The Database with the OBJ_DB identifier is required in repository to perform an example. Working result of a function is a number of added records.

Fore

Fore.NET

An extract values example. A function work result is a cursor with a resulting data set.

Fore

Fore.NET

Using the Query Repository Object

Second version is used to use the Query repository object with the required parameter. The application should include a function that allows obtaining results of this query. Advantages of this method:

The query is stored in a known location, it can be easily edited.
Amount of application code is decreased and the debugging efficiency is increased.

The example of getting data by executing the Query object. Identifier in the repository is passed in the QueryId function parameter and the ParamId query parameter identifier is passed in the ParamValue parameter value. The result of function operation is an opened sample of an object providing an access to the cached data.

Fore

Fore.NET

In the application this code looks like follows:

Var
Tasks: IDatasetInstance;
Begin
Tasks := RunQueryWithParam(< Query identifier >, "ID", < Parameter bvlue >);

To get access to data use the IDatasetInstance.FieldsIDatasetInstance.Fields property or address to the data source cache using the IDatasetInstance.OpenCached:

Var
    Tasks: IDatasetInstance;
    TasksData: ICachedDataset;
Begin
    Tasks := RunQueryWithParam(< Query identifier >, "ID", < Parameter value >);
    TasksData := Tasks.OpenCached;

To pass the multiple value to the parameter use the following structure:

Var
    Tasks: IDatasetInstance;
    Ls: ArrayList;
Begin
    Ls := New ArrayList.Create;
    For i := 1 To 2 Do
        Ls.Add(i);
    End For;
    Tasks := RunQueryWithParam(< Query identifier >, "ID", Ls.ToArray);

Constraints

The DBMS servers configuration constraints the number of simultaneously opened cursors that is why it is required to close not used cursors using the Close method.

Implementing the Microsoft SQL Server 2008 drivers the MARS mode (Multiple Active Result Set) is used. Using this mode enable the user to have more than one resulting dataset opened within a single connection. But it is not available to start a new transaction within this connection until data of one resulting dataset are not read up to the end. There are two ways to solve this problem:

Use different connections. To do this, create different Database objects in repository or create a connection directly in the application code.
Read the resulting dataset to the end. Working with dataset which describes the IDatasetInstance interface, to read all records it is required to open the cache and get the property of the following value: ICachedDataset.RecordCountAll. Treatment to this property provides the reading of all records.

Also consider recommendations of development of applications functioning in the MARS mode. These recommendations are represented in the MSDN.