An existing application that become a success and later needed be bolted with multilingual support is where NHibernate adaptability on
brownfield projects shines. How well does the application that uses NHibernate can adapt to this change? It handles it seamlessly, in fact there's virtually no changes in your application code needed be made. The localization on both retrieving and saving are being done automatically
Changes needed be made to your domain classes to support multiple languages? Zero, zilch, nada
public class Order
{
public virtual int OrderId { get; set; }
public virtual Product Product { get; set; }
public virtual DateTime OrderDateTaken { get; set; }
public virtual int Qty { get; set; }
public virtual string EmailAccount { get; set; }
}
public class Product
{
public virtual int ProductId { get; set; }
public virtual string ProductName { get; set; }
public virtual string ProductDescription { get; set; }
public virtual int YearIntroduced { get; set; }
public virtual IList<Order> Orders { get; set; }
}
Changes needed be made to your persistence code to support multiple languages? Zero, zilch, nada
var pz =
new Product
{
ProductName = "PC",
ProductDescription = "Personal Computer",
YearIntroduced = 1981
};
s.Merge(pz);
s.Flush();
Changes needed be made to your object retrieval code to support multiple languages? Zero, zilch, nada
var ps =
from p in s.Query<Product>()
orderby p.ProductDescription descending
select p;
foreach (var p in ps)
{
Console.WriteLine(
"Name: {0}\nDescription: {1}\nYear Introduced: {2}\n\n",
p.ProductName, p.ProductDescription, p.YearIntroduced);
}
When we get to the bottom of things, multilingual support is something that should not leak to your domain classes. This is where a capable ORM shines, it insulates your domain classes against infrastructure changes needed be made, everything just works automagically. The infrastructure to support multiple languages might be relational, but the domain classes don't have to reflect the underlying infrastructure. Object-wise, your domain class is still the same cohesive and atomic object your app knows. This is what Object-Relational Mapping is all about. Your object truly mimics the real-world object/entity, not a mere one-to-one mapping of object to relational. The beauty of abstraction
To cut to the chase, these are the infrastructure changes needed be made to enable multilingual support for your app.
- Move language-specific fields to another table
- Create a function that fetches the language-specific information
- Adjust the mapping
- Set the language upon database connection
1. Move language-specific fields to another table:
create table Product_Translation
(
ProductId int not null references Product(ProductId),
LanguageCode varchar(5) not null,
ProductName nvarchar(max) not null,
ProductDescription nvarchar(max) not null,
constraint pk_Product_Translation primary key(ProductId, LanguageCode)
);
Then move the language-specific content of your Product table to Product_Translation table.
insert into Product_Translation(ProductId,LanguageCode,ProductName,ProductDescription)
select ProductId,'en',ProductName,ProductDescription from Product;
Then drop the language-specific fields from the main table, your table should not have any language-specific fields left in it, e.g.
create table Product
(
ProductId int identity(1,1) not null primary key,
YearIntroduced int not null
);
2. Create a function that fetches the language-specific information:
create function GetProductTranslation(@LanguageCode varchar(5))
returns table
as
return
with a as
(
select
TheRank =
rank() over(
partition by ProductId
order by
case LanguageCode
when @LanguageCode then 1
when 'en' then 2 -- fallback language
else 3
end
),
ProductId, ProductName, ProductDescription
from Product_Translation t
)
select ProductId, ProductName, ProductDescription
from a
where TheRank = 1;
go
To verify if things are working accordingly:
select p.ProductId, t.ProductName, t.ProductDescription, p.YearIntroduced
from Product p
join dbo.GetProductTranslation('en') t
on t.ProductId = p.ProductId
3. Adjust the mapping
Do note that the parameter substitution on SqlInsert and SqlUpdate is order-dependent. If you declare properties in your class in this order: ProductName, ProductDescription; the ProductName will be passed to the first parameter, and the ProductDescription to the second parameter, the last parameter is where NHibernate passes your entities joining key.
public class ProductMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Product>
{
string updateCommand =
@"
merge Product_Translation as target
using (values({0},?, ?, ?)) as source(LanguageCode, ProductName, ProductDescription, ProductId)
on source.ProductId = target.ProductId and source.LanguageCode = target.LanguageCode
when matched then
update set ProductName = source.ProductName, ProductDescription = source.ProductDescription
when not matched then
insert (LanguageCode, ProductName, ProductDescription, ProductId)
values(LanguageCode, ProductName, ProductDescription, ProductId);";
public ProductMapping()
{
Table("Product");
Id(x => x.ProductId, map =>
{
map.Column("ProductId");
map.Generator(NHibernate.Mapping.ByCode.Generators.Identity);
});
Property(x => x.YearIntroduced);
string sessionLanguage = "(select cast(CONTEXT_INFO() as varchar(5)))";
Join("dbo.GetProductTranslation(:lf.LanguageCode)", j =>
{
j.Inverse(false);
j.Fetch(NHibernate.Mapping.ByCode.FetchKind.Join);
j.Key(k => k.Column("ProductId"));
j.Property(p => p.ProductName);
j.Property(p => p.ProductDescription);
j.SqlInsert(string.Format("insert into Product_Translation(LanguageCode, ProductName, ProductDescription, ProductId) values({0}, ?, ?, ?)", sessionLanguage));
j.SqlUpdate(string.Format(updateCommand, sessionLanguage));
}); // join
Bag<Order>(
list => list.Orders,
rel => rel.Key(y => y.Column("ProductId")),
relType => relType.OneToMany());
}
} // product mapping
Point of interest on the mapping is the dbo.GetProductTranslation function, it receives its values from the filter. Which is defined while building the session factory:
var filterDef =
new NHibernate.Engine.FilterDefinition(
"lf", null,
new Dictionary<string, NHibernate.Type.IType>
{
{ "LanguageCode", NHibernateUtil.String }
}, useManyToOne: false);
cfg.AddFilterDefinition(filterDef);
You'll notice too that the SqlInsert and SqlUpdate doesn't use filters, this is a limitation of NHibernate filters, filters are applied on functions and conditions only. For that limitation, we'll just set Sql Server's CONTEXT_INFO to desired language upon database connection. For SqlUpdate command, we use Sql Server's merge statement, if the ProductId+LanguageCode is existing on Product_Translation it will update the existing row; if not, then it will insert it.
4. Set the language upon database connection
using (var s = Mapper.GetSessionFactory().OpenSession())
using (var tx = s.BeginTransaction().SetLanguage(s,"zh-CHS"))
{
// Your beautiful code goes here
}
public static class TransactionHelper
{
public static NHibernate.ITransaction SetLanguage(this NHibernate.ITransaction tx, NHibernate.ISession session, string languageCode)
{
var cmd = session.Connection.CreateCommand();
tx.Enlist(cmd);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SetLanguage";
var prm = cmd.CreateParameter();
prm.ParameterName = "@LanguageCode";
prm.Value = languageCode;
cmd.Parameters.Add(prm);
cmd.ExecuteNonQuery();
session.EnableFilter("lf").SetParameter("LanguageCode", languageCode);
return tx;
}
}
create procedure SetLanguage(@LanguageCode varchar(5)) as
begin
declare @binvar varbinary(128);
set @binvar = cast(@LanguageCode as varbinary(128));
set context_info @binvar;
end;
That's it! Your application will do business as usual, without it being aware that the infrastructure underneath was somehow changed radically.
Nothing shall change on how the application get and persist objects.
Take that curmudgeoned ADO.NET developers!
Happy Coding! ツ
One of C#'s selling points:
One-stop-shop. Expand to see the full code:
C# code:
using System;
using System.Collections.Generic;
namespace BillionDollarBusinessApp
{
using BusinessEntities;
// If we are using repository pattern, these doesn't need to turn up here:
using TheMapper;
using NHibernate.Linq;
class Program
{
static void Main(string[] args)
{
using (var s = Mapper.GetSessionFactory().OpenSession())
using (var tx = s.BeginTransaction().SetLanguage(s, "zh"))
{
Action showProducts = delegate
{
foreach (var product in s.Query<Product>())
{
Console.WriteLine("Product Name: {0}\nDescription: {1}\nYear Introduced: {2}\n", product.ProductName, product.ProductDescription, product.YearIntroduced);
}
};
Console.WriteLine("***Prior to localizing Apple name***\n");
showProducts();
var prodApple = s.Get<Product>(2);
prodApple.ProductName = "苹果";
s.Merge(prodApple);
Console.WriteLine("***After localizing Apple name***\n");
showProducts();
tx.Commit();
}
Console.ReadKey();
}
}
}
namespace TheMapper
{
using NHibernate.Cfg;
using NHibernate.Linq;
using BusinessEntities;
public static class TransactionHelper
{
public static NHibernate.ITransaction SetLanguage(this NHibernate.ITransaction tx, NHibernate.ISession session, string languageCode)
{
var cmd = session.Connection.CreateCommand();
tx.Enlist(cmd);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SetLanguage";
var prm = cmd.CreateParameter();
prm.ParameterName = "@LanguageCode";
prm.Value = languageCode;
cmd.Parameters.Add(prm);
cmd.ExecuteNonQuery();
session.EnableFilter("lf").SetParameter("LanguageCode", languageCode);
return tx;
}
}
public static class Mapper
{
private static NHibernate.ISessionFactory _isf = null;
public static NHibernate.ISessionFactory GetSessionFactory()
{
var cfg = new NHibernate.Cfg.Configuration();
var mapper = new NHibernate.Mapping.ByCode.ModelMapper();
mapper.AddMappings(new[] { typeof(ProductMapping), typeof(OrderMapping) });
cfg.DataBaseIntegration(c =>
{
c.Driver<NHibernate.Driver.Sql2008ClientDriver>();
c.Dialect<NHibernate.Dialect.MsSql2008Dialect>();
c.ConnectionString = "Server=localhost; Database=TheLocalized; Trusted_Connection=true; MultipleActiveResultSets=true";
// c.LogSqlInConsole = true;
});
NHibernate.Cfg.MappingSchema.HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
cfg.AddMapping(domainMapping);
var filterDef = new NHibernate.Engine.FilterDefinition(
"lf", null,
new Dictionary<string, NHibernate.Type.IType>
{
{ "LanguageCode", NHibernate.NHibernateUtil.String }
}, useManyToOne: false);
cfg.AddFilterDefinition(filterDef);
NHibernate.ISessionFactory sessionFactory = cfg.BuildSessionFactory();
_isf = sessionFactory;
return _isf;
}
}
public class ProductMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Product>
{
string updateCommand =
@"
merge Product_Translation as target
using (values({0},?, ?, ?)) as source(LanguageCode, ProductName, ProductDescription, ProductId)
on source.ProductId = target.ProductId and source.LanguageCode = target.LanguageCode
when matched then
update set ProductName = source.ProductName, ProductDescription = source.ProductDescription
when not matched then
insert (LanguageCode, ProductName, ProductDescription, ProductId)
values(LanguageCode, ProductName, ProductDescription, ProductId);";
public ProductMapping()
{
Table("Product");
Id(x => x.ProductId, map =>
{
map.Column("ProductId");
map.Generator(NHibernate.Mapping.ByCode.Generators.Identity);
});
Property(x => x.YearIntroduced);
string sessionLanguage = "convert(nvarchar, substring(context_info(), 5, convert(int, substring(context_info(), 1, 4)) ) )";
Join("dbo.GetProductTranslation(:lf.LanguageCode)", j =>
{
j.Inverse(false);
j.Fetch(NHibernate.Mapping.ByCode.FetchKind.Join);
j.Key(k => k.Column("ProductId"));
j.Property(p => p.ProductName);
j.Property(p => p.ProductDescription);
j.SqlInsert(string.Format("insert into Product_Translation(LanguageCode, ProductName, ProductDescription, ProductId) values({0}, ?, ?, ?)", sessionLanguage));
j.SqlUpdate(string.Format(updateCommand, sessionLanguage));
}); // join
Bag<Order>(
list => list.Orders,
rel => rel.Key(y => y.Column("ProductId")),
relType => relType.OneToMany());
}
} // product mapping
public class OrderMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Order>
{
public OrderMapping()
{
Table("[Order]");
Id(x => x.OrderId, map =>
{
map.Column("OrderId");
map.Generator(NHibernate.Mapping.ByCode.Generators.Identity);
});
Property(x => x.Qty);
Property(x => x.OrderDateTaken);
Property(x => x.EmailAccount);
ManyToOne(refr => refr.Product, m => m.Column("ProductId"));
}
} // order mapping
}
namespace BusinessEntities
{
public class Product
{
public virtual int ProductId { get; set; }
// SqlInsert's parameter should correspond to this order
public virtual string ProductName { get; set; }
public virtual string ProductDescription { get; set; }
public virtual int YearIntroduced { get; set; }
public virtual IList<Order> Orders { get; set; }
}
public class Order
{
public virtual int OrderId { get; set; }
public virtual Product Product { get; set; }
public virtual DateTime OrderDateTaken { get; set; }
public virtual int Qty { get; set; }
public virtual string EmailAccount { get; set; }
}
}
Database:
/*
drop table [Order];
drop table Product_Translation;
drop table Product;
drop function GetProductTranslation;
drop procedure SetLanguage;
*/
create table Product
(
ProductId int identity(1,1) not null primary key,
YearIntroduced int not null
);
create table Product_Translation
(
ProductId int not null references Product(ProductId),
LanguageCode varchar(5) not null,
ProductName nvarchar(max) not null,
ProductDescription nvarchar(max) not null,
constraint pk_Product_Translation primary key(ProductId, LanguageCode)
);
create table [Order]
(
OrderId int identity(1,1) not null primary key,
EmailAccount nvarchar(100) not null,
ProductId int not null references Product(ProductId),
Qty int not null,
OrderDateTaken datetime not null,
);
insert into Product(YearIntroduced) values(1981);
declare @Computer int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription)
values(@Computer, 'en','PC', 'Personal Computer');
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription)
values(@Computer, 'zh', N'电脑', N'电脑');
insert into Product(YearIntroduced) values(1984);
declare @Apple int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription)
values(@Apple, 'en', 'Apple', 'Truly Personal Computer');
insert into Product(YearIntroduced) values(1886);
declare @CocaCola int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription)
values(@CocaCola, 'en', 'Coca Cola', 'Refreshing');
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription)
values(@CocaCola, 'zh', N'可口可乐', N'可口可乐');
insert into Product(YearIntroduced) values(1903);
declare @TsingTao int = SCOPE_IDENTITY();
insert into Product_Translation(ProductId, LanguageCode, ProductName, ProductDescription)
values(@TsingTao, 'zh', N'青岛', N'青岛');
insert into [Order](EmailAccount, ProductId, OrderDateTaken, Qty) values('a@a.com', @Apple, CURRENT_TIMESTAMP, 3);
go
create function GetProductTranslation(@LanguageCode varchar(max))
returns table
as
return
with a as
(
select
TheRank =
rank() over(
partition by ProductId
order by
case LanguageCode
when @LanguageCode then 1
when 'en' then 2 -- fallback language
else 3
end
),
ProductId, ProductName, ProductDescription
from Product_Translation t
)
select ProductId, ProductName, ProductDescription
from a where TheRank = 1;
go
create procedure [dbo].[SetLanguage](@LanguageCode nvarchar(5)) as
begin
declare @binvar varbinary(128);
set @binvar = cast(datalength(@LanguageCode) as varbinary) + cast(@LanguageCode as varbinary);
set context_info @binvar;
end;
/*
select *
from Product p
join dbo.GetProductTranslation('en') t
on t.ProductId = p.ProductId;
select *
from Product p
join dbo.GetProductTranslation('zh') t
on t.ProductId = p.ProductId;
select * from Product_Translation;
*/
