Calling a database function
This section describes a feature that's mostly used in the Low-level API. If you're using Linq or QuerySpec (which is recommended), please see Function mappings in Linq and Function mappings in QuerySpec instead.
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);
}