Store Int128 in a database

August 26, 2014 Leave a 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: https://int128.codeplex.com/SourceControl/latest#SoftFluent.Int128/Int128.cs

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" />
</cf:entity>

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"
              typeName="SoftFluent.Int128"
              dbType="Guid"
              xmlns:cfom="http://www.softfluent.com/codefluent/producers.model/2005/1"
              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"
              xmlns:cfom="http://www.softfluent.com/codefluent/producers.model/2005/1"
              cfom:readValueExpression="new SoftFluent.Int128(CodeFluentPersistence.GetReaderValue(reader, &quot;GuidInt128&quot;, System.Guid.Empty))"
              cfom:addParameterMethodName="AddParameter"
              typeName="SoftFluent.Int128"
              dbType="Guid" />

And:

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 Leave a 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:

CustomerCollection.Search(
 "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.

CustomerCollection.Search(
 "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: https://github.com/SoftFluent/CodeFluent-Entities/tree/master/Extensions/SoftFluent.ExtendedSearch

Happy coding,

The R&D Team

Categories: Aspects Tags: , ,

What’s new with CodeFluent Entities 773?

August 18, 2014 Leave a 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:

SELECT COUNT(DISTINCT Customer_Name) FROM Customer

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).

&amp;lt;cf:method name=&amp;quot;CustomByName&amp;quot; body=&amp;quot;COUNT(Name) WHERE Name STARTSWITH @Name&amp;quot; 
  xmlns:cfps=&amp;quot;http://www.softfluent.com/codefluent/producers.sqlserver/2005/1&amp;quot;
  cfps:countExpression=&amp;quot;DISTINCT $Customer::Name$&amp;quot; 
  
  xmlns:cfor=&amp;quot;http://www.softfluent.com/codefluent/producers.oracle/2009/1&amp;quot;
  cfor:countExpression=&amp;quot;DISTINCT $Customer::Name$&amp;quot; 
  
  xmlns:cfpg=&amp;quot;http://www.softfluent.com/codefluent/producers.postgresql/2012/1&amp;quot;
  cfpg:countExpression=&amp;quot;DISTINCT $Customer::Name$&amp;quot; 
  
  xmlns:cfmy=&amp;quot;http://www.softfluent.com/codefluent/producers.mysql/2012/1&amp;quot;
  cfmy:countExpression=&amp;quot;DISTINCT $Customer::Name$&amp;quot; 
/&amp;gt;

This model will produce the following procedure:

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

RETURN
GO

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" 
             typeName="CodeFluent.Producers.MySQL.MySQLProducer, 
                       CodeFluent.Producers.MySQL">
    <cf:configuration defaultStorageEngine="MyISAM" />
</cf:producer>

Or at entity level:

<cf:entity name="Customer" 
    cfmy:storageEngine="MyISAM" 
    xmlns:cfmy="http://www.softfluent.com/codefluent/producers.mysql/2012/1">
    
    <cf:property name="Id" key="true" />
    <cf:property name="Name" />
</cf:entity>

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,
    CONSTRAINT `PK_Cus_Cus_Cus` PRIMARY KEY
    (
        `Customer_Id`
    )
) ENGINE = MyISAM;

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: http://www.softfluent.com/forums/codefluent-entities/utc-date-times.

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

“IS NOT NULL” and “IS NULL” in CFQL

July 31, 2014 Leave a comment

In CodeFluent Query Language (CFQL), the SQL statement “expression IS NOT NULL” is “expression Exists” and the SQL statement “expression IS NULL” is “NOT expression EXISTS”.

There is no need to write raw SQL to test if a value exists in a method as we sometimes see in models. This can be done in full CFQL thanks to the Exists operator, this way keeping your model as platform independent as possible:

LOAD() WHERE (NOT FirstName EXISTS) AND (LastName EXISTS)

More about CFQL:

Happy CFQL-ing,

The R&D team

Using SQL Server datetime2 data type

July 28, 2014 1 comment

CodeFluent Entities supports DateTime2 since the build 714. A datetime2 defines a date that is combined with a time of day that is based on 24-hour clock. Datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

To use DateTime2 instead of DateTime you have to configure the SQL producer:

SQL Server Use datetime2

The created table uses datetime2:

CREATE TABLE [dbo].[Customer] (
 [Customer_Id] [uniqueidentifier] NOT NULL,
 [Customer_DateOfBirth] [datetime2] NULL,
)

Note: To use datetime2 in your application you have to set useDateTime2=”true” in the configuration file:

<configuration>
  <configSections>
    <section name="MyDefaultNamespace" type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler, CodeFluent.Runtime" />
  </configSections>
  <MyDefaultNamespace connectionString="..." useDateTime2=”true” />
</configuration>

DateTime2 allows to specify the precision from 0 to 7 digits. The default precision is 7. This value is configurable at property level by setting the SQL Server specify attribute “sql size”:

SQL Server Sql Size
Now the generated script looks like:

CREATE TABLE [dbo].[Customer] (
 [Customer_Id] [uniqueidentifier] NOT NULL,
 [Customer_DateOfBirth] [datetime2] (6) NULL,
)

Note that Microsoft recommends using datetime2 data type for new work:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffsetprovides time zone support for globally deployed applications.

Happy coding,

The R&D team

Follow

Get every new post delivered to your Inbox.

Join 49 other followers