Retrieve localizable resources from the database

Level: advanced

Example 1

Example

Retrieve localizable resources for an arbitrary language culture using the Terrasoft.Core.Entities.EntitySchemaQuery class.

The Terrasoft.Core.Entities.EntitySchemaQuery class generates a data selection for an arbitrary language culture, i. e., a language culture that differs from the primary language culture in Creatio and additional language culture of the user.

To retrieve localizable resources for an arbitrary language culture:

1. Call the SetLocalizationCultureId(Guid cultureId) method in the Terrasoft.Core.Entities.EntitySchemaQuery class instance before you retrieve data.
2. Pass the language culture ID whose data you want to retrieve to the SetLocalizationCultureId(Guid cultureId) method.

Generate a data selection for an arbitrary language culture

/* The user connection. */
var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];

/* Retrieve the ID of the needed language culture, for example, Italian. */
var sysCulture = new SysCulture(userConnection);
if (!sysCulture.FetchPrimaryInfoFromDB("Name", "it-IT")) {
    /* Error, no record found. */
    return "No culture found";
}
Guid italianCultureId = sysCulture.Id;

/* Generate a query. */
var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "City");

/* Add a column to the query. */
esqResult.AddColumn("Name");

/* Set the needed localization. */
esqResult.SetLocalizationCultureId(italianCultureId);

/* Execute a database query and retrieve the resulting object collection. */
var entities = esqResult.GetEntityCollection(userConnection);

/* Retrieve the query text. */
var s = esqResult.GetSelectQuery(userConnection).GetSqlText();

/* Return the result. */
return s;

When you run the code above, Creatio executes the SQL query. The @P1 parameter takes the value of the record ID ([Id]) stored in the italianCultureId variable.

SQL query

SELECT
    ISNULL([SysCityLcz].[Name], [City].[Name])[Name]
FROM
    [dbo].[City] [City] WITH(NOLOCK)
    LEFT OUTER JOIN [dbo].[SysCityLcz] [SysCityLcz] WITH(NOLOCK) ON ([SysCityLcz].[RecordId] = [City].[Id]
    AND [SysCityLcz].[SysCultureId] = @P1)

Example 2

Example

Retrieve the value of the Name localizable column in the AccountType object schema using the FetchFromDB() method of the Terrasoft.Core.Entities.Entity class.

The FetchFromDB() methods of the Terrasoft.Core.Entities.Entity class let you retrieve multilingual data. View the example that uses one of the FetchFromDB() method overloads for a user that has a primary (English) and an additional (for example, French) language culture below. For example, you can use these methods in custom web service methods.

Example that uses the FetchFromDB() method

/* The user connection. */
var userConnection = (UserConnection) HttpContext.Current.Session["UserConnection"];

/* Retrieve the [Account type] schema. */
EntitySchema schema = userConnection.EntitySchemaManager.FindInstanceByName("AccountType");

/* Create the Entity (object) instance. */
Entity entity = schema.CreateEntity(userConnection);

/* The collection of column names to generate a selection. */
List <string> columnNamesToFetch = new List <string> {
    "Name",
    "Description"
};

/* Retrieve object data whose [Name] column value is "Customer." */
entity.FetchFromDB("Name", "Customer", columnNamesToFetch);

/* Generate and send a response. */
var name = String.Format("Name: {0}", entity.GetTypedColumnValue <string> ("Name"));
return name;

If the user that has the primary language culture (English) runs the code above, Creatio executes an SQL query to the primary [AccountType] database table. The @P1 parameter contains the "Customer" value that defines the corresponding record of the [AccountType] primary database table.

SQL query

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'

If the user that has an additional language culture (for example, French) runs the code above, Creatio executes an SQL query to the [SysAccountTypeLcz] localization table. The @P1 parameter contains the "Customer" value that defines the corresponding record of the [AccountType] primary database table. The @P2 parameter contains the ID value ([SysCultureId]) of the additional language culture from the [SysCulture] table. The value defines the corresponding record of the [SysAcountTypeLcz] localization table.

SQL query

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'

As a result, Name variable will correspond to "Customer" for a user that has an English language culture and "Client" for a user that has a French language culture.