Archive

Archive for the ‘Developing Using CodeFluent Entities’ Category

How to enable Intellisense for CodeFluent Entities runtime configuration?

September 16, 2014 Leave a comment

CodeFluent Entities generated code can be configured using the application configuration file (App.config or web.config):

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="Sample"
     type="CodeFluent.Runtime.CodeFluentConfigurationSectionHandler,
           CodeFluent.Runtime" />
  </configSections>
  <Sample connectionString="<connection string>" useDateTime2="true" />
</configuration>

The configuration section is describe in the documentation: http://www.softfluent.com/documentation/BOM_ApplicationConfiguration.html.

This section is not known by Visual Studio, so you may have the following message:

ErrorXML

In fact Visual Studio doesn’t have an XML schema for this section and so it can’t validate it. At the same time we cannot provide a generic schema as the section name is Project specific. So the solution is to use The Template producer  which will generate the XML  schema based on the information from your Project.

  1.  

  2. Add a template producer

     

  3. Build the model
  4. Open the app.config or web.config file and add the following xml attributes (replace <Default namespace> by your project default namespace)

<configuration

xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221;

xsi:schemaLocation=”http://www.softfluent.com/<Default Namespace>/CodeFluentConfiguration.xsd CodeFluentConfiguration.xsd”>

You should now have auto-completion of the CodeFluent Entities configuration section.

binary services

 

 

 

 

 

 

 

Note: this xml schema includes only most common settings. If you think we forgot some useful settings, please leave a comment.

Happy configuration,

The R&D Team

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)
{
    Console.WriteLine(table.FullName);

    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
    Console.WriteLine(DatabaseUtilities.Trace(table.Data));
}

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

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

SQL Server specific data types

July 24, 2014 Leave a comment

CodeFluent Entities can use SQL Server specific data types such as Geography, Geometry and HierarchyId.

The first step is to register “Microsoft.SqlServer.Types.dll” into the model:

 

Add Reference

 

SQL Server Reference

Note: You must add the same reference in the BOM project.

Then set the type name of the property to Microsoft.SqlServer.Types.SqlGeography:

Type Name Geography

Choose Type SqlGeography

We also have to set the database type. As this is specific to SQL Server, we have to use the SQL Server producer attribute “sqlType”:

Sql Data Type

The same apply for Geometry and HierarchyId data types.

The table is generated, let’s add a simple method that compute intersection of two geography object. The code is really specific to SQL Server so we have to create a RAW method:

CFQL SqlGeography

Don’t forget to set the return type name of the method to SqlGeography:

Return Type Name SqlGeography

Let’s use the generated code:

Sample sample1 = new Sample();
sample1.Geography = SqlGeography.Parse("LINESTRING(-122.360 47.656, -122.343 47.656)");
sample1.Save();

Sample sample2 = new Sample();
sample2.Geography = SqlGeography.Parse("LINESTRING(-122.360 47.656, -122.343 47.656)");
sample2.Save();

var intersection = Sample.GetIntersection(sample1.Id, sample2.Id);
Console.WriteLine(intersection.ToString()); //LINESTRING (-122.34300000005148 47.656000000089243, -122.3599999999485 47.655999999910769)

If intersection is null, this means that you need to add an assembly binding in the app.config/web.config file:

<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Types" culture="neutral" publicKeyToken="89845dcd8080cc91"/>        
        <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

This example shows how to use SqlGeography with CodeFluent Entities. The same works with SqlGeometry, HierarchyId, and any types that implement IBinarySerialize.

Happy storing,

The R&D Team

Follow

Get every new post delivered to your Inbox.

Join 51 other followers