.NET Answers

Over the last several weeks we’ve been examining the various aspects of internationalization using ASP.NET. But it doesn’t help to have your resources and images set up for internationalization if your database has all of its data in one language.

The following are several solutions you might consider when setting up your database.

One possible solution would be to have a separate database for each language. You might have your core database be “DatabaseName” and the multiple languages post fixed with the language and culture. For example DatabaseName-en-US.

For this to work you will need to change the connection string on the fly by using a multi-tiered architecture as we’ve discussed earlier in this post.
http://blog.dmbcllc.com/2008/02/29/datasets-objectdatasource-and-3-tier-aspnet/

As I mention in that post, having a separate adapter method is useful because we can use it to change the connection string if needed. You might consider using a base class for setting the connection string:

public abstract class Controller
{
    public Controller()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public static string ConnectionString
    {
        get
        {          // Code that computes the connection string here
        }
    }

}

Then our child classes can use this code to set the connection string in the adapter

private static DataSetTableAdapters.TableAdapter Adapter
{
    get
    {
        DataSetTableAdapters.TableAdapter adapter =             new DataSetTableAdapters.TableAdapter();
        adapter.Connection.ConnectionString =             ConnectionString;
        return adapter;
    }
}

It’s a pretty simple solution really, but it will mean we have to replicate our entire database twice, which may not be what we need.

You could do something similar but just modify the table names that are impacted. But even then, you still have an issue with making sure that IDs stay in sync.

The best solution I’ve seen so far simply adds a new ID value to each table that needs to be internationalized called “MasterID” and another field that specifies the language. The ID points back to the parent record. The rest of the structure stays the same.

Then in our stored procedure that retrieves the row, we pass in the ID we want to retrieve along with the language code.

Using the ID, we do a lookup for the MasterID and then retrieve the row WHERE the MasterID equals the ID we found and the language matches the code we passed in to the stored procedure.

Obviously, it is not necessary to pass in the ID if you know the MasterID, but I find that most internationalization projects are not something we plan up front, but are something that becomes a requirement over time. After the core language site has been up for a while. The method of passing in the ID and looking up the MasterID handles this situation best.

Thanks to my buddy Eric for teaching me this method.