Localization of Database Strings in .Net

Comments | Share

I have several database tables that have a description column that I need to display in the UI. .Net has .resx files that will help with the translation of the strings, when the Thread.CurrentCulture.UICulture is set, but I needed a custom approach for the strings that are stored in the database and not in the .resx files. Note: this will only work for static tables that are added to at development time, because each addition will require an addition to the resx file for each language and a re-deployment of the application.

Here’s my approach (revised from yesterday, I found that I didn’t need to add an extra column to each table):

1. Create a resource file for each database table and put them in the /Resources/Database/ directory.

2. Create a method in LocalizationHelpers (GetLocalizedStaticDatabaseString) that will get the string from the table for English (by using a provided function that goes out and gets it) (which should be cached to avoid unneeded service/database calls) or the resx when not English.

4. By convention the resx file will have the same name as the database table (with Resource appended), and the key will be a distinguisher (ex: UnitOfMeasureResource.MPHDescription)
- if there are multiple columns that need translation, then use different keys.

If the resx isn't found, it will just return the string from the service call.

Here’s the method I’m using to pull the string from a resource file:

public static string GetLocalizedString(string resourceName, string resourceKey)
{
  if (callingAssembly == null)
  {
    callingAssembly = Assembly.GetCallingAssembly();
  }
 
  ResourceManager manager = new ResourceManager(resourceName, callingAssembly);
  return manager.GetString(resourceKey);
}

Here’s the method to get the value by convention and uses the ICacheProvider approach for caching. The ILog and ICacheProvider are stored in the IoC (in my case StructureMap) using a custom resolver from http://weblogs.asp.net/shijuvarghese/archive/2008/10/10/asp-net-mvc-tip-dependency-injection-with-structuremap.aspx.

public static string GetLocalizedStaticDatabaseString(string databaseName, string resourceKey, Func<string> getDatabaseValueFunction)
{
  string cacheKey = string.Format("{0}.{1}", databaseName, resourceKey);
  ICacheProvider provider = Resolver.GetConcreteInstanceOf<ICacheProvider>();
  if (Thread.CurrentThread.CurrentUICulture.Name == "en-US")
  {
    // if it's culture is English (default), then call the provided function that will call the service and return the value
    // first check the caching
    if (provider.IsSet(cacheKey))
    {
      return provider.Get(cacheKey).ToString();
    }
    else
    {
      // get the value from the function provided, put these functions in the models for reuse
      string result = getDatabaseValueFunction();

      // cache the result
      // we could be smarter here and cache all the strings for that table, instead of just one at a time
      provider.Set(cacheKey, result);
      return result;
    }
  }
  else
  {
    // get the translated string from the resx file
    string result = string.Empty;
    try
    {
      result = GetLocalizedString(string.Format("MyProject.Web.Resources.Database.{0}Resource", databaseName), resourceKey);
    }
    catch (MissingManifestResourceException ex)
    {
      ILog logger = Resolver.GetConcreteInstanceOf<ILog>();
      logger.Warn("Resource file not found for Localized Static Database String request", ex);
      result = getDatabaseValueFunction();
    }

    provider.Set(cacheKey, result);
    return result;
  }
}

This is how I call the method, note the lambda for the call to get the database string. I’m making all calls through a service, so it’s not directly getting it with Entity Framework to the database.

string result = LocalizationHelpers.GetLocalizedStaticDatabaseString( 
"UnitOfMeasure",            
"MPHDescription",                
() =>
{  
  // call some service or database call to get the database stored English string
  return service.GetUnitOfMeasure(“MPHDescription”); 
}
);