Multiple Language (English, Dutch, Finnish, French, Hungarian, Bangla, Italian,) in ASP.NET with SQL server !

Question

I am developing a web Application in ASP.NET 3.5 with SQL Server 2008. I need Multiple Language such as English, Dutch, Finnish etc. I can do it by using System.Resources and System.Globalization. but I can't convert the Language which data come from database. How can I solve it ???

Answer 1

I would recommend using two tables for the table that your need localization.
Example:

Product
-------------------------------
ProductID  |  Price   |  Stock 
-------------------------------
10         |   10     |   15


ProductLoc
-----------------------------------------------
ProductID  | Lang   | Name      |  Description
-----------------------------------------------
 10        |  EN    | Bike      |  Excellent Bike 
 10        |  ES    | Bicicleta |  Excelente bici 

This way you can use:

SELECT * FROM 
Product LEFT JOIN ProductLoc ON Product.ProductID = ProductLoc.ProductID 
                               AND ProductLoc.Lang = @CurrentLang

(Left join just in case there is no record for the current lang in the ProductLoc table)

Disclaimer: Text taken from another answer of mine

Answer 2

The most commonly used solution for large, I mean huge application is to have a Languages table with its unique Id, then referencing the LanguageId into your data tables.

CREATE TABLE Languages (
    LanguageId int IDENTITY(1, 1) PRIMARY KEY
    LanguageCode nvarchar(5)
    LanguageName nvarchar(25)
)
GO

INSERT INTO Languages (LanguageCode, LanguageName)
    VALUES (N'en-CA', N'English Canada')
GO
INSERT INTO Languages (LanguageCode, LanguageName)
    VALUES (N'fr-CA', N'French Canada')
GO
INSERT INTO Languages (LanguageCode, LanguageName)
    VALUES (N'de-GE', N'German Germany')
GO

CREATE TABLE LabelTexts (
    LabelTextId int IDENTITY(1, 1) PRIMARY KEY
    FormName nvarchar(50) NOT NULL
    LabelName nvarchar(50) NOT NULL
    LanguageId int NOT NULL
    LabelText nvarchar(2000) NOT NULL
)
GO
ALTER TABLE LabelTexts
ADD CONSTRAINT FK_Language FOREIGN KEY REFERENCING Languages(LanguageId)
GO

INSERT INTO LabelTexts (FormName, LabelName, LanguageId, LabelText)
    VALUES (N'frmMain', N'label1', 1, N'Enter the information for customer here:')
GO
INSERT INTO LabelTexts (FormName, LabelName, LanguageId, LabelText)
    VALUES (N'frmMain', N'label1', 2, N'Saisir les informations du client ici :')
GO
-- etc.

So, when your application is deployed into another language, all you need is to insert this new language into the supported language table, and insert the text strings into that language referencing the correct LanguageId, configure your application to get this LanguageId while querying the database, and you're set! Your application is easily multilanguage!

You could even write yourself parameterized scalar functions which which will perform the right query for you, so instead of:

select LabelText
    from LabelTexts
    where LanguageId = 1
        and FormName LIKE N'frmMain'
        and LabelName LIKE N'label1'

You would only need to create a function and call it like so:

select dbo.GetLabelText(N'frmMain', N'label1', 1)

And the function body coded like this above-mentioned SELECT statement. Don't forget to create indexes on proper fields for increased performance of your queries.