Calling a database function

Calling a database function to perform additional logic onto a field and/or set of values is an advanced feature which can be useful in e.g. filters and in DynamicList fetches, but also in TypedViews and TypedLists.

A function call is implemented in the form of a DbFunctionCall object. This object implements the interface IDbFunctionCall and also IExpression, so it can be added to an existing Expression object and also be set to a field's ExpressionToApply property. This section describes the details about a database function call, what you can do with it, and illustrates the feature with an example.

On some databases, the difference between a stored procedure and a function is non-existent. The database function meant here is a function which is either a system function, like GETDATE() on SQL Server, or a user defined function in a schema and is not directly callable from the outside through ADO.NET unless a special crafted SQL statement is used.

Rule of thumb: if you need an explicit EXEC statement to execute the function, it's not callable by this mechanism: the function has to be callable from within a SELECT statement for example.

Linq and QuerySpec

When you use Linq or QuerySpec, function calls are not directly defined by you, but specified indirectly by using .NET methods which are mapped onto SQL functions through function mappings. The code below is part of our lower-level API.

See for calling a DB function in Linq and Queryspec the sections: Function mappings in Linq and Function mappings in QuerySpec.

Definition and scope

A database function call in the form of a DbFunctionCall object contains hardcoded the catalog, schema and function name. The catalog/schema names can be left empty, in which case the default catalog and schema as defined in the connection string will be used by the RDBMS to find the function called.

So, in general only leave catalog and/or schema name empty if you're calling a system function. The names specified for catalog/schema are renamed in runtime catalog/schema name overwriting, if catalog/schema name overwriting is applied at runtime to the generated code.

The function can receive any number of parameters, and they can be of type: value (any value), an Entity field object and Expression (which implies function call, as DbFunctionCall is an Expression). These parameters are passed in as an object[] array, and for functions without parameters, you can pass null / Nothing.

If NULL has to be passed as a parameter, pass either DBNull.Value or null/Nothing. The order in which the parameters are specified in the object[] array is also the order in which they'll be emitted into the actual function call.

Specifying constants for function parameters

It sometimes is necessary to specify constants for a function call, and these constants can't be specified with a parameter. An example is the function CAST or CONVERT. To do this, you have to specify the function name with {n} placeholders.

When there are {n} placeholders in the function name, LLBLGen Pro will use the function name as-is and will replace the placeholders with the parameters specified, otherwise the function name is build using the name specified plus a suffix with the parameters (e.g. MONTH(field)). See for the normal specification of a function name the Examples at the bottom of this section.

The following example shows how to specify a call to CAST with a constant.

var fields = new ResultsetFields(2);
fields.DefineField(OrderFieldIndex.OrderId, 0, "OrderID");
fields.DefineField(OrderFieldIndex.OrderDate, 1, "Month");

fields[1].ExpressionToApply = new DbFunctionCall("CAST({0} AS bigint)",
                            new object[] 
                            { 
                                new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) 
                            });

CASE support

Although it's directly a function, we could use the function call with constant feature to specify CASE fragments for a select. Below is an example how to do that.

var fields = new ResultsetFields(2);
fields.DefineField(SupplierFields.Id, 0);
fields.DefineField(SupplierFields.Region, 1);

fields[1].ExpressionToApply = new DbFunctionCall(
    "CASE {0} WHEN 'MA' THEN 'Massachusetts' WHEN 'OR' THEN 'Oregon' ELSE 'Unknown' END", 
    new object[] { SupplierFields.Region });

Function calls in expressions.

DbFunctionCall implements IExpression, so you can add it to any Expression object as an operand to participate in an expression. DbFunctionCall returns itself as the left operand inside an ExpressionElement and type is set to FunctionCall.

The examples later on in this section will illustrate the usage of DBFunctionCall in Expressions. DbFunctionCall objects are ignored in in-memory filtering.

All supported databases support function calls, and you can call any function offered by the target database system. This thus means that you can use system functions like GETDATE or DATEVAL on SQL Server, and for example VB functions on MS Access, as these are available to you in SQL.

It's a powerful feature, but also comes with a restriction: as the function name is hardcoded as a string in your code, and the order in which the parameters have to be specified is tied to the order of the parameters in your function definition in the database system, it means that changes to the function's parameter order or the name of the function can lead to errors at runtime.

Be aware that this can happen, so document where you use which database function, so you can always find back usage of a database function in your code if the database function changes.

Using a function call in a predicate

DbFunctionCall objects are expressions, so you can use them in predicates as well. Take for example the DbFunctionCall used in the first example in this section. It can be used in a predicate to filter on orders in a given month:

// create a filter which filters on month equals 3
var monthFilter = new EntityField2("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
                        new object[] 
                        { 
                            new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) 
                        })).Equal(3);

// create a filter which filters on month equals 3
var monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",
                        new object[] 
                        { 
                            new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) 
                        })).Equal(3);

Examples

Below is an example given for both Selfservicing and adapter, which fetches a set of order objects based on a filter which uses a function call.

The database function used is a user defined function created for this example, defined in the Northwind database on SQL Server. If you're unfamiliar with T-SQL syntaxis, the function, fn_CalculateOrderTotal accepts two parameters, @orderID and @useDiscounts, and based on @useDiscounts' value (0 or 1), it calculates the order total for the order with the orderID in the parameter @orderID.

CREATE  FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit)
RETURNS DECIMAL
AS
BEGIN
    DECLARE @toReturn DECIMAL

    IF @useDiscounts=0
    BEGIN
        SELECT @toReturn = SUM(UnitPrice * Quantity)
        FROM [Order Details] 
        WHERE OrderID = @orderID
        GROUP BY OrderID
    END
    IF @useDiscounts=1
    BEGIN
        SELECT @toReturn = SUM((UnitPrice-Discount) * Quantity)
        FROM [Order Details] 
        WHERE OrderID = @orderID
        GROUP BY OrderID
    END

    RETURN @toReturn
END

The example code below fetches a single row of data all using a function call, using a DynamicList. Assert statements have been left in the code to illustrate the values to expect for the various fields fetched.

var fields = new ResultsetFields(8);
fields.DefineField(OrderFields.OrderId, 0, "OrderID");
fields.DefineField(OrderFields.OrderDate, 1, "Month");
fields.DefineField(OrderFields.OrderDate, 2, "Year");
fields.DefineField(OrderFields.OrderDate, 3, "YearPlus4");
fields.DefineField(OrderFields.OrderDate, 4, "OrderTotalWithDiscounts");
fields.DefineField(OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts");
fields.DefineField(OrderFields.OrderDate, 6, "YearOfGetDate");
fields.DefineField(OrderFields.OrderDate, 7, "RealOrderDate");

fields[1].ExpressionToApply = new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate });
fields[2].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { OrderFields.OrderDate });
fields[3].ExpressionToApply = new Expression(new DbFunctionCall("YEAR", new object[] { OrderFields.OrderDate }), ExOp.Add, 4);
fields[4].ExpressionToApply = new DbFunctionCall("dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 1 });
fields[5].ExpressionToApply = new DbFunctionCall("dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 0 });
fields[6].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { new DbFunctionCall("GETDATE", null)  });

var results = new DataTable();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, null);
}
foreach(DataRow row in results.Rows)
{
    DateTime realOrderDate = (DateTime)row[7];
    Assert.AreEqual((int)row[1], realOrderDate.Month);
    Assert.AreEqual((int)row[2], realOrderDate.Year);
    Assert.AreEqual((int)row[3], realOrderDate.Year + 4);
    Assert.AreEqual((int)row[6], DateTime.Now.Year);
}

var fields = new ResultsetFields(8);
fields.DefineField(OrderFields.OrderId, 0, "OrderID");
fields.DefineField(OrderFields.OrderDate, 1, "Month");
fields.DefineField(OrderFields.OrderDate, 2, "Year");
fields.DefineField(OrderFields.OrderDate, 3, "YearPlus4");
fields.DefineField(OrderFields.OrderDate, 4, "OrderTotalWithDiscounts");
fields.DefineField(OrderFields.OrderDate, 5, "OrderTotalWithoutDiscounts");
fields.DefineField(OrderFields.OrderDate, 6, "YearOfGetDate");
fields.DefineField(OrderFields.OrderDate, 7, "RealOrderDate");

fields[1].ExpressionToApply = new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate });
fields[2].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { OrderFields.OrderDate });
fields[3].ExpressionToApply = new Expression(new DbFunctionCall("YEAR", new object[] { OrderFields.OrderDate }), ExOp.Add, 4);
fields[4].ExpressionToApply = new DbFunctionCall("dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 1 });
fields[5].ExpressionToApply = new DbFunctionCall("dbo", "fn_CalculateOrderTotal", new object[] { OrderFields.OrderId, 0 });
fields[6].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { new DbFunctionCall("GETDATE", null) });

var results = new DataTable();
var dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0);
foreach(DataRow row in results.Rows)
{
    DateTime realOrderDate = (DateTime)row[7];
    Assert.AreEqual((int)row[1], realOrderDate.Month);
    Assert.AreEqual((int)row[2], realOrderDate.Year);
    Assert.AreEqual((int)row[3], realOrderDate.Year + 4);
    Assert.AreEqual((int)row[6], DateTime.Now.Year);
}