Archive for August, 2014

CodeFluent Runtime Database

August 28, 2014 Leave a comment

CodeFluent Entities comes with lots of useful DLL. Of course you all know CodeFluent.Runtime.dll or CodeFluent.Runtime.Web.dll. Today we’ll have a look at CodeFluent.Runtime.Database.dll.

The Runtime.Database DLL contains code to explore databases (schema, tables, views, columns, primary keys, constraints, stored procedures, and data). Currently, it supports:

  • SQL Server,
  • Oracle,
  • MySQL,
  • PostgreSQL,
  • SqlLite,
  • Sql Server CE
  • Access,
  • OleDb,
  • Xmi,
  • EnterpriseArchitect

Let’s see an example:

CodeFluent.Runtime.Database.Management.Database database = new CodeFluent.Runtime.Database.Management.SqlServer.Database("Server=(local)\\SQL2014;Database=Sample;Trusted_Connection=True;");

foreach (var table in database.Tables)

    foreach (var column in table.Columns)
        Console.WriteLine("    {0} {1}", column.EscapedName, column.CodeFluentType.DataType);

    Console.WriteLine("    PK ({0})", ConvertUtilities.ConcatenateCollection(table.PrimaryKey.Columns, "EscapedName", ", "));

    table.MaxRows = 10; // Read only 10 rows

The Database class provides a very great abstraction of the database structure. Whatever the DBMS you use, the code is the same. For example if you are using MySQL, just replace CodeFluent.Runtime.Database.Management.SqlServer.Database by CodeFluent.Runtime.MySQL.Management.Database (located in CodeFluent.Runtime.MySQL) and run the code!

The DLL also contains code to execute SQL queries or SQL scripts.

Moreover you’ll find some graphical components to create or edit connection strings (the ones used by CodeFluent Entities):

Happy exploring,

The R&D team

Store Int128 in a database

August 26, 2014 1 comment

As you may have seen in our previous posts (here and here), the type system of CodeFluent Entities is very powerful. Today we’ll see how to persist a custom type.

Let’s use the Int128 class:

You may notice that the class implements the IBinarySerialize interface which is natively handled by CodeFluent Entities. So by default the value will be stored in a column of type varbinary and serialized by using the IBinarySerialize interface. But the Int128 is as long as a Guid: 128bits, so it may be a better idea to store it in a column of type uniqueidentifier.

Let’s create an entity with a property of type SoftFluent.Int128, and dbType Guid:

<cf:entity name="Sample">
  <cf:property name="Id" key="true" />
  <cf:property name="GuidInt128" typeName="SoftFluent.Int128" dbType="Guid" />

Now the trick is to replace the generated code. The result code will be:

protected virtual bool BaseSave(bool force)
    // ...
    persistence.AddParameter("@GuidInt128", new System.Guid(this.GuidInt128.ToByteArray()), System.Guid.Empty);
    // ...

protected virtual void ReadRecord(System.Data.IDataReader reader, CodeFluent.Runtime.CodeFluentReloadOptions options)
    // ...

    this._guidInt128 = new SoftFluent.Int128(CodeFluentPersistence.GetReaderValue(reader, "GuidInt128", System.Guid.Empty));
    // ...

The BOM producer understands some custom attributes. Among them, three are useful in our case:

  • addParameterExpression=”<attribute value>”
  • addParameterMethodName=”<attribute value>”
  • readValueExpression=”<attribute value>”

The first one allows to define the code when adding the parameter (BaseSave method):

persistence.AddParameter("@GuidInt128", <attribute value>, System.Guid.Empty);

The second one allows to change the method AddParameter by something else:

persistence.<attribute value>("@GuidInt128", this.GuidInt128, System.Guid.Empty);

The third one allows to define the code when reading the value from the DataReader:

this._guidInt128 = <attribute value>;

One way to store value as Guid is to use the combination of addParameterExpression and readValueExpression:

<cf:property name="GuidInt128"
              cfom:readValueExpression="new SoftFluent.Int128(CodeFluentPersistence.GetReaderValue(reader, &quot;GuidInt128&quot;, System.Guid.Empty))"
              cfom:addParameterExpression="persistence.AddParameter(&quot;@GuidInt128&quot;, new System.Guid(this.GuidInt128.ToByteArray()), System.Guid.Empty)" />

The second way to store value as Guid is to use addParameterMethodName and readValueExpression and to write an extension method:

<cf:property name="GuidInt128"
              cfom:readValueExpression="new SoftFluent.Int128(CodeFluentPersistence.GetReaderValue(reader, &quot;GuidInt128&quot;, System.Guid.Empty))"
              dbType="Guid" />


public static class PersistenceUtilities
    public static void AddParameter(this CodeFluentPersistence persistence, string name, Int128 value, Type type, PersistenceSerializationMode mode)
        persistence.AddParameter(name, new Guid(value.ToByteArray()), Guid.Empty);

This way the AddParameter will use the extension method.

When you have more than one custom property you may want to write an aspect to automate this process.

Thanks to the power of CodeFluent Entities, we have a column of type Guid and a property of type Int128, and all of this without extra code such as adding one unneeded property to convert value from Int128 to Guid.

Happy storing,

The R&D Team

Extended Search Aspect

August 20, 2014 1 comment

This aspect requires CodeFluent Entities build 774.

Third-party component providers such as Infragistics, Telerik or DevExpress, often provide data-grid with advanced filtering options:

CodeFluent Entities allows to generate Search methods by using CodeFluent Query Language (CFQL). For instance, searching for customers by name and date of birth can be done as so:

And the following method will be generated:

public static
 CustomerCollection Search(string fullName, System.DateTime dateOfBirth)

The function used to filter rows (Equals, Contains, StartsWith, etc.) is not dynamic: FullName will use the Equals or StartsWith functions depending of your configuration and DateOfBirth will use Equals function.

As its name implies, the extended search aspect extends these features. After registering the aspect in your project, you can configure the method to use dynamic filtering functions:

Now the generated method looks like:

public static CustomerCollection Search(
 string fullName, FilterFunctions fullNameFilterFunction,
 System.DateTime dateOfBirth, FilterFunctions dateOfBirthFilterFunction)

The aspect automatically create new parameters that allow to specify the function to use for each search parameter. FilterFunctions enumeration defines commonly used filters:

Here’s an example:

 "John", FilterFunctions.Contains,
 new DateTime(2014, 1, 1), FilterFunctions.IsLessThanOrEqualTo);

This enumeration can be multi-valued (optional). In this case you can combine functions, but the generated stored procedure is more complex so use it only when needed.

 "John", FilterFunctions.Contains | FilterFunctions.IsLessThan,
 new DateTime(2014, 1, 1), FilterFunctions.IsLessThanOrEqualTo);

You can also specify which functions should be supported:

Now we’re going to register the aspect in our solution. First, copy the SoftFluent.Samples.ExtendedSeach.Aspect project in your solution.

Then, add a reference in the model to the aspect project:

And select the ExtendedSearch aspect:

Finally, build your model! Search methods now have new parameters J.

With the power of CodeFluent Entities and a few lines of code, we have added the possibility to extend search method of our choice. Of course this aspect 100% reusable across all your projects. Can you do that without CodeFluent Entities?

The code is available on our GitHub repository:

Happy coding,

The R&D Team

Categories: Aspects Tags: , ,

What’s new with CodeFluent Entities 773?

August 18, 2014 1 comment

This new build includes one new feature : Database producers now support custom COUNT methods expression (including DISTINCT).

Until now, CodeFluent Entities could only generate COUNT(*) statement using the CFQL COUNT method. This new version (build 773) introduce new XML attributes in order to let you change the generated count expression. For instance you can now generate the following SQL code:


This is possible by using the new ‘countExpression’ xml attributes. The value of this attribute will be copied as is into the SQL COUNT statement.

As you are setting raw SQL, countExpression attribute is database producer specific, this means you have to set it once by database producer you use (SQL Server, Oracle, MySQL, and PostgreSQL).

<cf:method name="CustomByName" body="COUNT(Name) WHERE Name STARTSWITH @Name"
  cfps:countExpression="DISTINCT $Customer::Name$" 

  cfor:countExpression="DISTINCT $Customer::Name$" 

  cfpg:countExpression="DISTINCT $Customer::Name$" 

  cfmy:countExpression="DISTINCT $Customer::Name$"

This model will produce the following procedure:

CREATE PROCEDURE [dbo].[Customer_CountByName]
 @Name [nvarchar] (256)
SELECT COUNT(DISTINCT [Customer].[Customer_Name]) FROM [Customer]
    WHERE ([Customer].[Customer_Name] LIKE (@Name + '%'))


Note that Target Name Transformation (TNT) in countExpression is supported. Target Name Transformation (TNT) is a very powerful and important feature of CodeFluent which allows developers to use the names of entities, properties, and views inside SQL snippets and raw queries. Therefore, it allows developers to use actual output names of objects (tables, columns, views) with the support of the defined naming convention:

If you are not a current customer, you can always download our free evaluation and see it in action. Remember that you can follow the latest new features and bug fixes of CodeFluent Entities subscribing to this RSS Feed.

Happy Counting,

The R&D Team

Categories: News

MySQL and MariaDB Storage Engine

August 8, 2014 Leave a comment

MySQL and MariaDB support several storage engines that act as handlers for different table types. They include both those that handle transaction-safe tables and those that handle nontransaction-safe tables.

You can see supported engine by running the SQL command “SHOW ENGINES” of your favorite MySQL Client tool:

Show Engines

We understand that you may want to use another storage engine. This is why we introduce a new options.

You can define the default storage engine at producer level:

Configuration Storage Engine

By using XML:

<cf:producer name="MySQL" 
    <cf:configuration defaultStorageEngine="MyISAM" />

Or at entity level:

<cf:entity name="Customer" 
    <cf:property name="Id" key="true" />
    <cf:property name="Name" />

The generated code will now use the storage engine defined at entity level, or at producer level or InnoDB if nothing is specified:

CREATE TABLE `StorageEngine`.`Customer`
    `Customer_Id` BINARY (16) NOT NULL,
    `Customer_Name` VARCHAR (256) CHARACTER SET utf8 NULL,
    `_trackLastWriteTime` DATETIME NOT NULL,
    `_trackCreationTime` DATETIME NOT NULL,
    `_trackLastWriteUser` VARCHAR (64) NOT NULL,
    `_trackCreationUser` VARCHAR (64) NOT NULL,
    `_rowVersion` BINARY (16) NOT NULL,

Happy storing,

R&D Team

Categories: MySQL Tags: , ,

Persistence Tracking Columns are UTC

August 7, 2014 Leave a comment

CodeFluent Entities automatically generates tracking columns. Those columns contains:

  • Creation time
  • Creation user
  • Last write time
  • Last writer user

Tracking columns

By default creation time and last write time use GETDATE function (local date of the server). We think it’s a better practice to use UTC date for this kind of data. Two years ago, we wrote an aspect to replace GETDATE function by GETUTCDATE function:

In the latest build of CodeFluent Entities (build 772) we introduce a new built-in setting at Project Level to use UTC date instead of local date:


Persistence Track Columns Are UTC

This option is used by all persistence producers: Microsoft SQL Server, Microsoft SQL Azure, Oracle, MySQL and PostgreSQL.

For example with SQL Server:

CREATE TABLE [dbo].[Test] (
  [Test_Id] [uniqueidentifier] NOT NULL,
  [_trackLastWriteTime] [datetime] NOT NULL CONSTRAINT [DF_Tes__tc] 
DEFAULT (GETUTCDATE()), -- instead of getdate()
  [_trackCreationTime] [datetime] NOT NULL CONSTRAINT [DF_Tes__tk] 
DEFAULT (GETUTCDATE()), -- instead of getdate()
  [_trackLastWriteUser] [nvarchar] (64) NOT NULL,
  [_trackCreationUser] [nvarchar] (64) NOT NULL,
  [_rowVersion] [rowversion] NOT NULL

Happy tracking,

The R&D Team