Using CFQL (CodeFluent Query Language) you can define platform independent persistent methods in your model which will then be translated into actual stored procedures by persistence producers such as SQL Server producer and the Oracle Database producer.
Moreover, upper layers producers will also generate the corresponding code mapped onto the generated stored procedure.
- In the following model:
<Employee> <Id /> <Name /> <Address typeName="Address" /> </Employee> <Address> <Id /> <Line1 /> <Line2 /> <Zip /> <City /> <Country /> </Address>
- Adding the following CFQL method to the Employee entity:
<cf:method name="LoadByCity" body="load(string cityName) where Address.City Like @cityName" />
- Will generate the following T-SQL stored procedure using the SQL Server producer:
CREATE PROCEDURE [dbo].[Employee_LoadByCity] (@cityName [nvarchar] (256), ...) AS SELECT DISTINCT [Employee].[Employee_Id], [Employee].[Employee_Name], [Employee].[Employee_Age], (...) FROM [Employee] LEFT OUTER JOIN [Address] ON ([Employee].[Employee_Address_Id] = [Address].[Address_Id]) WHERE ([Address].[Address_City] LIKE @cityName)
Here’s how to do it in your application using the Modeler: