Methods

More than a simple Object Relational Mapper (ORM) tool, or just generating the Create, Read, Update, Delete (“CRUD”) methods, SoftFluent CodeModeler - through its Method concept - allows to go way further. It allows architects and developers to define methods that will be available in all generated layers. Defining them in the model results in adding platform independent methods to the meta-model, which then will be interpreted by each producer to produce platform specific and optimized code.

For instance, from a given method, the Microsoft SQL Server Producer will generate a stored procedure, the Business Object Model Producer will generate an actual C# method in the defined class and mapped on the previous stored procedure, and other producers will generate the corresponding operation and contract mapped on the method defined in the object model.

Using methods truly enables development teams to streamline their developments since each business rule provided by a method will be useable in all layers of the application.

CodeModeler can generate two types of methods out-of-the-box:

• Model methods: methods within a scope bound to the object model (e.g. the Validate or Sort methods),

• Persistence methods: methods within a scope including the persistence layer as well (e.g. the Load, Search, Count, Save, Delete methods).

 By default, CodeModeler generates a set of methods including, but not limited to, CRUD methods, methods which are a physical implementation of the designed relations. For instance, if an entity Order is related to a Customer entity, a LoadByCustomer method will be generated automatically for the Order entity.

At last, from a given model, here are the default methods generated in output:

• Load / LoadBy[CollectionKey] / LoadBy[RelatedEntity] / Save / Delete in the entity class,

• LoadAll / SaveAll in the entity set class,

• Load / Save / Delete for all Binary Large Objects (Blobs) properties if any.

Load

load methods allow you to define a platform independent query, loading sets of data.

This query will then be translated into a platform specific code by a producer. For instance, the Microsoft SQL Server Producer will translate it into a T-SQL query, when the MySQL Database Producer will translate it into a MySQL one.

Since the generated method manipulates sets of entities, in the generated Business Object Model, the method is generated in the collection class.

For example, the following image demonstrates how to declare an Employee entity with:

• three properties: Id, Name, and Address

• a custom method LoadByCity to load all the employees by the City where they live, stored in an Address entity.

The SQL Server producer will output the following code for the Employee_LoadByCity stored procedure, automatically creation appropriate joins:

CREATE PROCEDURE [Commerce].[Employee_LoadByCity]
(
 @cityName [nvarchar] (256),
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Commerce].[Employee].[Employee_Id], [Commerce].[Employee].[Employee_Name], [Commerce].[Employee].[Employee_Address_Id], [Commerce].[Employee].[_trackLastWriteTime], [Commerce].[Employee].[_trackCreationTime], [Commerce].[Employee].[_trackLastWriteUser], [Commerce].[Employee].[_trackCreationUser], [Commerce].[Employee].[_rowVersion] 
    FROM [Commerce].[Employee]
        LEFT OUTER JOIN [Commerce].[Address] ON ([Commerce].[Employee].[Employee_Address_Id] = [Commerce].[Address].[Address_Id]) 
    WHERE ([Commerce].[Address].[Address_City] LIKE @cityName)

In the Business Object Model (BOM), CodeModeler will generate a LoadByCity method in the EmployeeCollection class that returns an EmployeeCollection. This method will be mapped on the generated stored procedure in the persistence layer.

LoadOne

loadone methods allow you to define a platform independent query, loading a single line of data.

This query will then be translated into a platform specific code by a producer. For instance, the Microsoft SQL Server Producer will translate it into a T-SQL query, when the MySQL Database Producer will translate it into a SQL one.

Since the generated method manipulates a single line of data, in the generated Business Object Model, the method is generated in the entity class and not the collection class as for classic Load methods.

For example, in the following image demonstrates how to declare a loadone method using CMQL to load just one line from the persistent layer:

Note: CMQL doesn’t need an argument type if the argument name matches this one if the entity property. The Name argument here matches the Name property so there’s no need to explicit its type, it’s implicitly string.

Looking at the generated stored procedures for the Employee entity, we can see how the Employee_LoadByName stored procedure is defined.

CREATE PROCEDURE [Commerce].[Employee_LoadByName]
(
 @Name [nvarchar] (256)
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Commerce].[Employee].[Employee_Id], [Commerce].[Employee].[Employee_Name], [Commerce].[Employee].[Employee_Address_Id], [Commerce].[Employee].[_trackLastWriteTime], [Commerce].[Employee].[_trackCreationTime], [Commerce].[Employee].[_trackLastWriteUser], [Commerce].[Employee].[_trackCreationUser], [Commerce].[Employee].[_rowVersion] 
    FROM [Commerce].[Employee] 
    WHERE ([Commerce].[Employee].[Employee_Name] = @Name)

And this is how the associated LoadByName method which returns a single Employee instance is defined in the BOM.

// Employee class: Custom LoadOne method
public static Employee LoadByName(string name);

Search

Basics

Search methods are CMQL methods which allow you to define platform independent queries to implement searching capabilities in your application. Persistence producers will then translate those platform independent queries into actual SQL queries.

For example, the following image defines a Cinema entity with two properties Id and Name. A SimpleSearch method can be added with just the two properties defined as arguments:

This is SQL Server producer output:

CREATE PROCEDURE [Commerce].[Cinema_SimpleSearch]
(
 @Name [nvarchar] (256) = NULL,
 @CinemaId [nvarchar] (256) = NULL,
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max), @paramlist nvarchar(max)
 
SELECT @sql=
'SELECT DISTINCT [Commerce].[Cinema].[Cinema_Id], [Commerce].[Cinema].[Cinema_Name], [Commerce].[Cinema].[Cinema_CinemaId], [Commerce].[Cinema].[_trackLastWriteTime], [Commerce].[Cinema].[_trackCreationTime], [Commerce].[Cinema].[_trackLastWriteUser], [Commerce].[Cinema].[_trackCreationUser], [Commerce].[Cinema].[_rowVersion] 
    FROM [Commerce].[Cinema] 
    WHERE (1 = 1)'
SELECT @paramlist = '@Name nvarchar (256), 
    @CinemaId nvarchar (256), 
    @_orderBy0 nvarchar (64), 
    @_orderByDirection0 bit'
IF @Name IS NOT NULL
    SELECT @sql = @sql + ' AND @Name = [Commerce].[Cinema].[Cinema_Name]'
IF @CinemaId IS NOT NULL
    SELECT @sql = @sql + ' AND @CinemaId = [Commerce].[Cinema].[Cinema_CinemaId]'
EXEC sp_executesql @sql, @paramlist,
    @Name, 
    @CinemaId, 
    @_orderBy0, 
    @_orderByDirection0

Wildcard ‘*’

The * wildcard is available in search methods. It indicates the method to search by all persistent properties of the entity:

Wildcard ‘**’

The ** wildcard is useful when working on entities deriving from another entity. Say you have a BaseProduct, which has several derived entities such as Product, OnlineProduct, etc. The ** wildcard allows you to search on the base entity properties as well as all derived entity properties as well:

Text Search (“Is Auto Starts With” and Contains)

Furthermore, since the content you’re searching for might not exactly match the stored content you can induce some flexibility using the “Is Auto Starts With” attribute (“Advanced Properties” tab) on a method:

Which generates:

(...)
IF @Name IS NOT NULL
    SELECT @sql = @sql + ' AND [Cinema].[Cinema_Name] LIKE (@Name + ''%'')'
IF @CinemaId IS NOT NULL
    SELECT @sql = @sql + ' AND [Cinema].[Cinema_CinemaId] LIKE (@CinemaId + ''%'')'
(...)

For full-text searches, you can use the CMQL Contains keyword:

Which generates:

(...)
IF @Name IS NOT NULL
    SELECT @sql = @sql + ' AND CONTAINS([Cinema].[Cinema_Name],@Name)'
IF @CinemaId IS NOT NULL
    SELECT @sql = @sql + ' AND CONTAINS([Cinema].[Cinema_CinemaId],@CinemaId)'
(...)

Note: Using the Contains CMQL function compilation and execution requires some full-text search capabilities to be enabled on the used columns, depending on the target persistence producer.

String Arrays

Going a little further, you can also pass string arrays to search methods such as:

Which generates:

IF @args IS NOT NULL
    SELECT @sql = @sql + ' AND [Cinema].[Cinema_Name] IN ((SELECT [Item] FROM [Commerce].cm_SplitString(@args, nchar(1))))'

Note: The cm_SplitString function is generated by the SQL Server. For now, other producers don’t support this construction.

Custom Queries

You can also embed hand-made SQL code in your CMQL search query such as:

For CodeModeler to sanctify this type of code, you must set the “Check Level” attribute (“Advanced Properties” tab) to the method to “None”:

Finally, you can also entirely code your own custom Search Raw methods:

Or you own Raw method:

Which generates this:

CREATE PROCEDURE [Commerce].[Cinema_PartialRawSearch]
(
@Name [nvarchar] (256) = NULL,
@CinemaId [nvarchar] (256) = NULL,
@_orderBy0 [nvarchar] (64) = NULL,
@_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max), @paramlist nvarchar(max)
SELECT @sql=
'// TODO: implement T-SQL logic'
SELECT @paramlist = '@Name nvarchar (256),
    @CinemaId nvarchar (256),
    @_orderBy0 nvarchar (64),
    @_orderByDirection0 bit'
EXEC sp_executesql @sql, @paramlist,
    @Name,
    @CinemaId,
    @_orderBy0,
    @_orderByDirection0
RETURN
...
CREATE PROCEDURE [Commerce].[Cinema_RawSearch]
(
@Name [nvarchar] (256) = NULL,
@CinemaId [nvarchar] (256) = NULL
)
AS
SET NOCOUNT ON
// TODO: implement T-SQL logic

Sorting

If entity contains sortable properties, those will be considered by the search method. You can see the Sortable Properties chapter for more on this.

Note: Sorting and multiple order by columns are also supported when working with array parameters.

Delete

Delete methods allow you to define a platform independent query, deleting data. This query will then be translated into a platform specific code by a producer. For instance, the Microsoft SQL Server Producer will translate it into a T-SQL query, when the MySQL Database Producer will translate it a MySQL one.

The following example describes how to declare a Delete method using CMQL to delete entities in the persistence layer. It declares an Employee entity with:

• two properties: Id and Name

• a custom method DeleteByName to delete an employee by its name.

This is the SQL Server procedure output (note a Delete method returns the number of deleted rows):

CREATE PROCEDURE [Commerce].[Employee_DeleteByName]
(
 @Name [nvarchar] (256)
)
AS
SET NOCOUNT ON
DECLARE @deletedcount int
DELETE [Commerce].[Employee] FROM [Commerce].[Employee] 
    WHERE ([Commerce].[Employee].[Employee_Name] LIKE (@Name + '%'))
SELECT @deletedcount = @@ROWCOUNT
SELECT @deletedcount

This is the BOM output:

// EmployeeCollection class : custom delete method
public static int DeleteByName(string name);

Count

Count methods allow you to define a platform independent query, counting lines of data. This query will then be translated into a platform specific code by a producer. For instance, the Microsoft SQL Server Producer will translate it into a T-SQL query, when the MySQL Database Producer will translate it into a MySQL one.

Count methods are very similar to Load methods except that, instead of deleting entities, they return an integer, as the result of the count operation. Since the generated method works on sets of entities, in the generated Business Object Model (BOM), the method is generated in the collection class.

The following example describes how to declare a Count method using CMQL to count entities in the persistent layer. It demonstrates how to declare an Employee entity with:

• three properties: Id, Name, and Address

• a custom method CountByCountry that returns the count of employees living in a specific country.

This is the SQL Server procedure output:

CREATE PROCEDURE [Commerce].[Employee_CountByCountry]
(
 @countryName [nvarchar] (256)
)
AS
SET NOCOUNT ON
SELECT COUNT(*) FROM [Commerce].[Employee]
    LEFT OUTER JOIN [Commerce].[Address] ON ([Commerce].[Employee].[Employee_Address_Id] = [Commerce].[Address].[Address_Id]) 
    WHERE ([Commerce].[Address].[Address_Country] LIKE @countryName)

This is the BOM output:

// EmployeeCollection class: Custom Count method
public static int CountByCountry(string countryName);

CMQL Operators

CMQL supports operators such as:

• unary operators: not, exists

• binary operators: and, or, equals, contains, freetext, like, =, <>, >=, <=, >, <

• set operator: in (expression1, expression2, ... , expressionM)

Unary Operators

Those operators are useful if you want to load all line where a column is null or non null. For instance, this example loads all menus having a parent menu:

The not operator can be used to reverse the following predicate:

Note: Parenthesis are optional and where specified for readability.

Binary Operators

The most common ones are and and or, which are very straightforward to use:

Note: As you can see in the example above, types of arguments passed to the method don't have to be specified since they correspond to properties declared on the current entity. Writing CFML methods this way allows to have a very flexible model since arguments types will automatically be updated if its related property type was changed.

Equality and Inequality Operators

Equality and inequality operators (=, <>, >=, <=, >, <) allow you to compare values between one another:

Equality and inequality operators also exist under their literal form:

• = equals, or is equal to

• <> not equals, is not equal to, or !=

• > greaterthan, or is greater than

• >= is greater than or equal to, is greater than or equal, greaterthanorequal, or greaterthanorequalto

• < lesserthan, or is lesser than

• <= is lesser than or equal to, is lesser than or equal, lesserthanorequal, or lesserthanorequalto

String Operators

Available operators are: equals (= is also supported), contains, freetext and like (is like, starts with, startswith and # are also supported):

Note: A column must be having a full-text index defined somehow to be used with the freetext and contains operators.

Set Operator

Using the in operator you can exclude values that aren't included in a list. For instance:

The in operator can also be used with method parameters:

Furthermore, if sending a string array as a parameter, the in parameter can be used to filter values by the passed array:

Note: The last method with an array-type argument is only supported by the SQL Server produce as of today.

Top

There isn't a TOP operator in CMQL, but you can set the “Maximum Count” attribute (“Advanced Properties” tab) at method level:

Unchecked

This topic is about unchecked methods that are a mix of CMQL and platform specific code.

Unchecked methods allow developers to add platform specific code (e.g. T-SQL) into platform independent (CMQL) methods.

The following example demonstrates how to declare an Employee entity with:

• three properties: Id, Name and BirthDate

• a custom method CountOlderThan to count all employees who are older than a minimum threshold.

This is the SQL Server procedure output with the code in red between parenthesis injected as is:

CREATE PROCEDURE [Commerce].[Employee_CountOlderThan]
(
 @minimumAge [int]
)
AS
SET NOCOUNT ON
SELECT COUNT(*) FROM [Commerce].[Employee] 
    WHERE DATEDIFF(year, [Employee].[Employee_BirthDate], GETDATE()) > @minimumAge

This is the BOM output:

// EmployeeCollection class: Custom Raw method
public static int CountOlderThan(int minimumAge);

Raw

Raw methods are methods in which a developer can code whatever he wants: platform specific code (e.g. C#, SQL, etc.), CodeModeler Query Language (CMQL), or a mix of both.

Note: The Target Name Transformation feature (e.g. $[EntityName]::[PropertyName]$) is available in raw ones so that you can use model entity names even in raw code.

For instance, the following example creates a raw method which bulk inserts a file. By defining that method in the CodeModeler, bulk inserting in this specific entity is now possible from the object model.

Note the two parameters firstRow and maxErrors have been explicitly declared in the Employee shape. This allows us to set attributes to them, for example the “Use Persistence Default Value“.

Since raw methods can contain platform specific code, and that a model is platform independent, it's possible to provide several bodies to the raw method so that the model continues to be functional on several platforms. For instance, in the following example an Order entity has a raw method with two bodies: one in T-SQL, and one in PL-SQL, which producers will use according to their targeted platform (i.e. the SQL Server Producer will ignore non T-SQL languages and vice versa):

Persistence raw methods can also return entities defined in your model or be mapped to .NET types such as integers (int, etc.) or even data sets (System.Data.DataSet).

For example, the following image shows a MyConfiguration entity with two raw methods GetUser and GetUser2:

GetUser has two special attributes set. First, we set the “Return Type Name” attribute of the method to be string:

And then we set the “Return Ordinal” attribute (“Aspects and Producers Properties” tab, Business Object Model category) to 0 to indicate to CodeModeler to use the first column as the method return value:

For the GetUser2, we set the “Return Type Name” attribute of the method to be System.Data.DataSet:

Note: DataSet is a well-known useful type for CodeModeler that knows how to generate code to support it.

Snippets

Snippets are chunks of code that are declared in a CodeModeler model and injected in generated classes. Two types of snippets are available in CodeModeler:

• Snippets: entity level snippets,

• Set Snippets: entity collection (a.k.a. set) level snippets.

Code snippets are used to inject code chunks in standard generated classes. They have the following advantages:

• snippets can be injected in all layers of the application automatically (e.g. business layer, service layer, etc.),

• since snippets are declared in the model, they survive model generations,

• snippets are practical because they use the programming languages we know: a snippet is coded in the targeted language directly since it will be copied and pasted as is in the generated code.

Note: Another solution to extend the generated code is to use partial classes which is generally a good practice when involved snippets are big chunks of code.

In this case, we recommend the following convention: for an entity named MyEntity that is generated in the files MyEntity.cs and MyEntityCollection.cs, place the entity extra-code in the file MyEntity.Partial.cs and the entity collection extra-code in the file MyEntityCollection.Partial.cs.

The following example demonstrates how to define:

• a snippet for a method GetName that will be injected in the generated Customer class,

• a set snippet for a method SearchFor that will be injected in the generated CustomerCollection class.

This is the BOM output:

// Snippet generated in Customer class
public string GetName()
{
    return _firstName + " " + _lastName;
}
 
// SetSnippet generated in CustomerCollection class 
public bool SearchFor(string firstName, string lastName)
{
    foreach (var customer in this)
    {
        if (customer.FirstName == firstName && customer.LastName == lastName)
            return true;
    }
    return false;
}

Body Snippets

Body snippets are chunks of code that are declared in a CodeModeler model and injected in generated classes. However, unlike Snippets, they only contain the body of the method and not its signature nor its curly brackets. Two types of body snippets are available:

• Body Snippets: for custom methods generated at the entity level,

• Set Body Snippets: for custom methods generated at the entity collection (a.k.a. set) level.

Best Practice: It is recommended to use Body Snippets rather than Snippets since using Body Snippets adds automatically an equivalent operation in the entity contract, whereas using Snippets don't.

The following example demonstrates how to define:

• a Body Snippet method type for a method named GetFullName that will be generated in the Customer class,

• a Set Body Snippet method type for a method named SearchByPattern that will be generated in the CustomerCollection class.

As we see body snippets contain only the body of a method which is defined at entity level. For a body snippet, the parameters of the method must be defined as well.

This is the BOM output:

// BodySnippet method generated in the Customer class
public virtual string GetFullName(string separator)
{
    return _firstName + separator + _lastName;
}
 
// SetBodySnippet method generated in the CustomerCollection class
public virtual CustomerCollection SearchByPattern(string pattern)
{
    var customers = new CustomerCollection();
    foreach (var customer in this)
    {
        if (customer.FirstName.Contains(pattern) || customer.LastName.Contains(pattern))
        {
            customers.Add(customer);
        }
    }
    return customers;
}