Programmable object

What are programmable objects ?

Programmable objects are used to execute database stored procedures and functions on the server-side.

Programmable object properties

Name : name of the programmable object, used to name the corresponding C# class and its interface.
Description : description of the programmable object

Type : type of the programmable object

Name	Description
`Scalar function`	A user-defined SQL function that can take parameters an return a single value.
`Table function`	A user-defined SQL function that can take parameters and return multiple records composed of columns.
`Stored procedure`	A precompiled set of SQL statements that can take parameters. SQL Server stored procedures can return a return code and the result of their last `SELECT` statement while PostgreSQL and Oracle stored procedures are not designed to return values (except via the `InputOutput` and `Output` parameters).

Return data .Net type : type of the data returned by the programmable object. For table functions and stored procedures, the specified type will be used as the generic type of the returned collection.

Warning

Scalar functions can only return simple types (int, string, ...). If you need to return a complex type, set the programmable object as a table function.
Complex types cannot be interfaces. Their property names must match (case-insensitive) the returned column names.

Parameters : list of the parameters that must be passed to the SQL function or stored procedure.

Name : name of the parameter as defined in the database
Description : description of the parameter

Direction : direction of the parameter

Name	Description
`Input`	Parameter whose value is passed to the programmable object and cannot be changed.
`InputOutput`	Parameter whose value is passed to the programmable object and can be updated by the programmable object.
`Output`	Parameter whose value can only be updated by the programmable object.
`ReturnValue`	Parameter whose value is the code returned by a Microsoft SQL Server stored procedure.

Size : maximum size in characters for varying-length parameters

Note

If the programmable object name or its names in database are correctly configured for your development database, Neos can automatically created parameters from the database using the Add from database button.

Names in database : Indicate the name of the function or stored procedure to call in the different supported database types.
- Database type : type of database in which the programmable object can be found. Supported database types are as follow :
  
  Name Description
  
  Oracle Oracle database.
  
  PostgreSQL PostgreSQL database.
  
  SQL Server Microsoft SQL Server database.
- Schema : database schema to which the SQL function or stored procedure belongs. Default schemas in the different database types are :
  - Oracle : no default schema
  - PostgreSQL : public.
  - SQL Server : dbo.
- Name : name of the stored procedure or SQL function in the specified database
Note

You only need to manually configure names in database when your database uses a custom schema or when the name of the programmable object does not match the name of the SQL function or stored procedure in the database.
When calling the programmable object, if no name in database is specified for the current database, Neos automatically uses the name of the programmable object and prefixes it with the default schema.

Name	Description
`Oracle`	Oracle database.
`PostgreSQL`	PostgreSQL database.
`SQL Server`	Microsoft SQL Server database.

Calling the programmable object from the server

Programmable objects can only be called server-side.
An interface named after each programmable object can be injected where needed.

Scalar functions

Scalar function programmable objects are called using the ExecuteAsync method that takes the configured Input parameters. They return a simple value of the configured type.

Example with a scalar function programmable object that uses a string Input parameter and returns an integer :

public class CallProgrammableObject
{
    private readonly IFunctionGetId _functionGetId;

    public CallProgrammableObject(IFunctionGetId functionGetId)
    {
        _functionGetId = functionGetId;
    }

    public async Task ExecuteAsync()
    {
        int id = await _functionGetId.ExecuteAsync("Apple");
    }
}

Table functions

Table function programmable objects are called in the same way as scalar functions. They return a list of records of the configured type.

Example with a table function programmable object that uses an integer Input parameter and returns a record as a data object :

public class CallProgrammableObject
{
    private readonly IFunctionGetProduct _functionGetProduct;

    public CallProgrammableObject(IFunctionGetProduct functionGetProduct)
    {
        _functionGetProduct = functionGetProduct;
    }

    public async Task ExecuteAsync()
    {
        IEnumerable<GetProductResult> products = await _functionGetProduct.ExecuteAsync(1);
        GetProductResult? product = products.FirstOrDefault();
    }
}

Stored procedures

Table stored procedure programmable objects are called using the ExecuteAsync method that takes the configured Input and InputOutput parameters. They return an object that contains :

updated InputOutput and Output parameter values
a return code (for SQL Server stored procedures only)
return data as a list of records of the configured type (for SQL Server stored procedures only)

Warning

PostgreSQL only supports output parameters in stored procedures starting with version 15

Example with a stored procedure programmable object that uses an integer Input parameter, a string InputOutput parameter and a double Output parameter. It updates the string InputOutput parameter and sets the double Output parameter. In a SQL Server database, it returns a code as well as a record as a data object (last SELECT statement) :

public class CallProgrammableObject
{
    private readonly IStoredProcedureUpdateProduct _storedProcedureUpdateProduct;

    public CallProgrammableObject(IStoredProcedureUpdateProduct storedProcedureUpdateProduct)
    {
        _storedProcedureUpdateProduct = storedProcedureUpdateProduct;
    }

    public async Task ExecuteAsync()
    {
        StoredProcedureUpdateProductResult result = await _storedProcedureUpdateProduct.ExecuteAsync(1, "Apple");
        string updatedName = result.Name;
        double newPrice = result.NewPrice;

        // SQL Server only
        int? returnCode = result.ReturnCode;
        IEnumerable<GetProductResult> products = result.ReturnData;
        GetProductResult? product = products.FirstOrDefault();
    }
}

Known issues, limitations, and workarounds

Functions cannot use output parameters
- PostgreSQL allows to pass output parameters to a function, but these parameters are used to define the columns of the records returned by the function. Use a Neos data object as the return type for these functions.
- Oracle allows to pass output an input/output parameters to a function, but you cannot use this kind of functions in SQL expressions. You can only use them in PL/SQL statements.
Getting records from cursors is currently not supported by programmable objects.

Table of Contents

Programmable object

What are programmable objects ?