Need advice on ASP.NET localization - strings stored in a database & displayed in a gridview

Question

I am looking for some advice on localization. I have an app that has been localized in the usual fashion (i.e., .resx files), which handles about 95% of the strings. However, I still need to localize some strings for category names that are stored in the database.

I'd like to avoid adding 15 new columns named categoryname_ES, categoryname_FR, etc, and then pulling the right column dynamically. If there would be some way to pull the data, and then do a substitution in the code, I think that would be a little less messy. Maybe along the lines of:

• go through gridview row by row
• if the language selected isn't english, look for this text value in a global resources file and replace it.

Anyone have a good idea of how to accomplish this? Or is adding a lot of categoryname columns for each language just the way to go (ewww).

Answer 1

You could create three tables:

Category
    Category_ID (identity)
    Category_Name (string)

Language
    Language_ID (identity)
    Language_Name (string)

CategoryLanguage
    Category_ID (FK)
    Language_ID (FK)
    Translation (string)

The category table would have the names in English. The language table would have the languages that your application supports. The association table would then be able to provide the translated category name based on the selected language, if it exists. If it doesn't exist, then simply display the English version as the default.

Answer 2

You can do it with four database columns:

ID  (unique primary key)
CultureCode
ProductID
ProductName

I presume that you are selecting your products from the database. Pass in the UI culture as part of the stored proc, then when you select from your product table join to the Culture table on the product ID and UICulture. You would phrase your SELECT something like this:

SELECT ProductID
        ,another field
        ,IsNull(Culture.ProductName, Product.ProductName)
        ,etc
FROM    Product
    LEFT JOIN Culture 
        ON Product.ProductID = Culture.ProductID
        AND Culture.CultureCode = @UICulture

you get the idea. You could even check the UICulture for a hyphen (eg: fr-CA), split it into another variable, then do two joins to the Culture table - one for the exact culture, and one for the fallback culture, so in this example the first join would be for fr-CA and the second join would just fallback to fr. If all your culture joins fail (eg. because you don't have Zulu in the culture table), then the IsNull uses just the ordinary ProductName (which is probably in english).

Answer 3

I use the resource file method that you describe.

1. Add a CategoryName.resx file with a row for each category. Make sure the "Name" matches your database value exactly. Put the translation in the "Value".
2. Get the string in code via the resx's generated code file. (sorry for the C#) Resources.CategoryName.ResourceManager.GetString(categoryName, new CultureInfo("fr"));

If you're binding to custom class, just make another property and bind to that property instead. If you're binding to a DataSet, you might want to use the RowDataBound event to do the substitution.