Documentation

Creatio development guide
PDF
This documentation is valid for Creatio version 7.15.0. We recommend using the newest version of Creatio documentation.

Working with the localized data via Entity

Glossary Item Box

Introduction

Starting with version 7.9.1, an ability of getting the multilingual data was added to the Entity.FetchFromDB() method. The data fetching algorithm is similar to the EntitySchemaQuery algorithm (see “ Reading multilingual data with EntitySchemaQuery” article):

  1. The object will receive the data from the main table if current user culture (language) is the primary culture for the application.
  2. The object will receive the data from the localization table if current user culture (language) is different from the primary culture. If the localization table contains no data for the user’s culture, the main table data is returned.

The examples of using the Entity.FetchFromDB() and Entity.Save() method overloads and the analysis of their execution for the user with the main (English) and additional (German) cultures (languages) are given below. These methods can be used in the user service methods (see the " Creating a user configuration service” article).

Reading the data

The example of source code for getting the data from the Name localized column of the AccountType schema object on the server side (C#);

// A user connection.
var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
// Getting the [Account Type] schema.
EntitySchema schema = userConnection.EntitySchemaManager.FindInstanceByName("AccountType");
// Creating an instance of the Entity (object).
Entity entity = schema.CreateEntity(userConnection);
// A collection of column names for the fetch.
List<string> columnNamesToFetch = new List<string> {
    "Name",
    "Description"
};
//Get the data for an object with the "Customer" value in the [Name] column.
entity.FetchFromDB("Name", "Customer", columnNamesToFetch);
// Forming and sending a response.
var name = String.Format("Name: {0}", entity.GetTypedColumnValue<string>("Name"));
return name;

If a user who has a default language selected in the profile executes the method containing this code, the following query will be sent to the database:

exec sp_executesql N'
SELECT
    [AccountType].[Name] [Name],
    [AccountType].[Description] [Description]
FROM
    [dbo].[AccountType] [AccountType] WITH(NOLOCK)
WHERE
    [AccountType].[Name] = @P1',N'@P1 nvarchar(6)',@P1=N'Customer'

In the above query, the "Customer” value is specified in the @P1 parameter, it determines the corresponding record of the database table.

NOTE

You can view the request using the SQL Server Profiler (Fig. 1).

Fig. 1. Profiling a query into a database via SQL Server Profiler

If a user with an additional language (such as German) selected in the profile executes the method, the following query will be sent to the database:

exec sp_executesql N'
SELECT
    ISNULL([SysAccountTypeLcz].[Name], [AccountType].[Name]) [Name],
    ISNULL([SysAccountTypeLcz].[Description], [AccountType].[Description]) [Description]
FROM
    [dbo].[AccountType] [AccountType] WITH(NOLOCK)
    LEFT OUTER JOIN [dbo].[SysAccountTypeLcz] [SysAccountTypeLcz] WITH(NOLOCK) ON ([SysAccountTypeLcz].[RecordId] = [AccountType].[Id]
    AND [SysAccountTypeLcz].[SysCultureId] = @P2)
WHERE
    [AccountType].[Name] = @P1',N'@P1 nvarchar(6),@P2 uniqueidentifier',@P1=N'Customer',@P2='A5420246-0A8E-E111-84A3-00155D054C03'

In the above query, the "Customer” value is specified in the @P1 parameter, it determines the corresponding record of the main database table. The indicator of additional culture from the SysCulture table will be in the @P2 parameter. It will define the corresponding record from the SysAcountTypeLcz localization table.

Thus, for the user with English culture the name variable will have the “Customer” value and for the user with German culture it will be the “Kunde” value.

Saving the localized data

The Entity.SetColumnValue() method is used for adding and modifying the localized data. This method can accept arguments of string and LocalizableString types.

Saving the localized data using string argument

The following saving algorithm is used in passing the string argument to the Entity.SetColumnValue() method:

  • when the user with an additional culture creates a new record, the data is added to both the main table and the localization table (for the corresponding culture);
  • when the user with an additional culture modifies the existing Entity instance, the result is saved only in the localization table (for the corresponding culture);
  • when the user with the main culture creates or modifies the Entity object, the data will be added or modified in the main table of the object.

The code example of saving the data using string argument:

var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
EntitySchema schema = userConnection.EntitySchemaManager.FindInstanceByName("AccountType");
Entity entity = schema.CreateEntity(userConnection);
// Set the default values for the columns.
entity.SetDefColumnValues();
// Setting the value for the [Name] column.
entity.SetColumnValue("Name", "New customer");
// Saving.
entity.Save();
var name = String.Format("Name: {0}", entity.GetTypedColumnValue<string>("Name"));
return name;

When the user with the default (English) culture executes this code, the following query will be executed in the database:

exec sp_executesql N'
INSERT INTO [dbo].[AccountType]([Id], [Name], [CreatedOn], [CreatedById], [ModifiedOn], [ModifiedById], [ProcessListeners], [Description])
    VALUES(@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)',N'@P1 uniqueidentifier,@P2 nvarchar(12),@P3 datetime2(7),@P4 uniqueidentifier,@P5 datetime2(7),@P6 uniqueidentifier,@P7 int,@P8 nvarchar(4000)',@P1='3A820BC8-006D-42B7-A472-E331FBD73E20',@P2=N'New Customer',@P3='2017-02-10 09:40:23.0909251',@P4='410006E1-CA4E-4502-A9EC-E54D922D2C00',@P5='2017-02-10 09:40:23.0929256',@P6='410006E1-CA4E-4502-A9EC-E54D922D2C00',@P7=0,@P8=N''

In the above query, the "New customer” value is specified in the @P2 parameter, it is saved in the main database table.

If the user has an additional culture (German) set in their profile, the following code must be executed to save the data with the string argument:

var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
EntitySchema schema = userConnection.EntitySchemaManager.FindInstanceByName("AccountType");
Entity entity = schema.CreateEntity(userConnection);
entity.SetDefColumnValues();
entity.SetColumnValue("Name", "Neue kunden");
entity.Save();
var name = String.Format("Name: {0}", entity.GetTypedColumnValue<string>("Name"));
return name;

The query to the AccountType main table will be the same as to the main localization, but the “Neue Kunden” value will be specified in the @P2 parameter.

exec sp_executesql N'
INSERT INTO [dbo].[AccountType]([Id], [Name], [CreatedOn], [CreatedById], [ModifiedOn], [ModifiedById], [ProcessListeners], [Description])
    VALUES(@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)',N'@P1 uniqueidentifier,@P2 nvarchar(12),@P3 datetime2(7),@P4 uniqueidentifier,@P5 datetime2(7),@P6 uniqueidentifier,@P7 int,@P8 nvarchar(4000)',@P1='94052A88-499D-4072-A28A-6771815446FD',@P2=N'Neue Kunden',@P3='2017-02-10 10:07:00.3454424',@P4='410006E1-CA4E-4502-A9EC-E54D922D2C00',@P5='2017-02-10 10:07:00.3454424',@P6='410006E1-CA4E-4502-A9EC-E54D922D2C00',@P7=0,@P8=N''

In addition, the query will be executed in the localization table:

exec sp_executesql N'
INSERT INTO [dbo].[SysAccountTypeLcz]([Id], [ModifiedOn], [RecordId], [SysCultureId], [Name])
    VALUES(@P1, @P2, @P3, @P4, @P5)',N'@P1 uniqueidentifier,@P2 datetime2(7),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5 nvarchar(12)',@P1='911A721A-0E5A-4CC3-B6D9-9E5FE85FEC64',@P2='2017-02-10 10:07:00.3664442',@P3='94052A88-499D-4072-A28A-6771815446FD',@P4='A5420246-0A8E-E111-84A3-00155D054C03',@P5=N'Neue Kunden'

The “Neue Kunden” value will be specified in the @P5 parameter in the above request.

The value that does not correspond to the default culture will be added to the AccountType table.

To avoid this save the localized data using the localized strings.

Saving the localized data using localized string argument

The code example of saving the data using the localized string:

var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
EntitySchema schema = userConnection.EntitySchemaManager.FindInstanceByName("AccountType");
Entity entity = schema.CreateEntity(userConnection);
entity.SetDefColumnValues();

// Creating a localized string with localized values for different cultures.
var localizableString = new LocalizableString();
localizableString.SetCultureValue(new CultureInfo("en-US"), "New customer en-US");
localizableString.SetCultureValue(new CultureInfo("de-DE"), "Neue Kunden de-DE");

// Seting the value of the column using the localized string.
entity.SetColumnValue("Name", localizableString);
entity.Save();

// The result will be displayed in the current user culture.
var name = String.Format("Name: {0}", entity.GetTypedColumnValue<string>("Name"));
return name;

Regardless of the language in the user's profile, the following queries will be sent to the database upon the code execution:

1. The query with the “New customer en-US” value in the @P2 argument will be sent to the main page:

exec sp_executesql N'
INSERT INTO [dbo].[AccountType]([Id], [Name], [CreatedOn], [CreatedById], [ModifiedOn], [ModifiedById], [ProcessListeners], [Description])
    VALUES(@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)',N'@P1 uniqueidentifier,@P2 nvarchar(18),@P3 datetime2(7),@P4 uniqueidentifier,@P5 datetime2(7),@P6 uniqueidentifier,@P7 int,@P8 nvarchar(4000)',@P1='5AC81E4A-FCB2-4019-AE5B-0C485A5F65BD',@P2=N'New Customer en-US',@P3='2017-02-10 10:47:21.7471581',@P4='410006E1-CA4E-4502-A9EC-E54D922D2C00',@P5='2017-02-10 10:47:21.7511578',@P6='410006E1-CA4E-4502-A9EC-E54D922D2C00',@P7=0,@P8=N''

2. The query with the “Neue kunden de-DE” value in the @P5 argument will be sent to the localization page:

exec sp_executesql N'
INSERT INTO [dbo].[SysAccountTypeLcz]([Id], [ModifiedOn], [RecordId], [SysCultureId], [Name])
    VALUES(@P1, @P2, @P3, @P4, @P5)',N'@P1 uniqueidentifier,@P2 datetime2(7),@P3 uniqueidentifier,@P4 uniqueidentifier,@P5 nvarchar(18)',@P1='6EC9C205-7F8B-455E-BC68-3D9AA6D7B7C0',@P2='2017-02-10 10:47:21.9272674',@P3='5AC81E4A-FCB2-4019-AE5B-0C485A5F65BD',@P4='A5420246-0A8E-E111-84A3-00155D054C03',@P5=N'Neue Kunden de-DE'

ATTENTION

If the code is be executed by a user who has an additional culture set in the profile and the value for the default culture is not specified in the localization string, the record for the user’s culture will be added to the primary AccountType table.

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?