Introduction

In Part 1 we briefly looked at how
localizing in .Net is achieved. We then extended the functionality by building
our own ResourceManager class as well as expanding a number of
Server Controls to be localization-aware.

In this 2nd Part, we'll go in more depth about the architecture of creating a
multilingual web application. We'll begin by using URL Rewriting to maintain the
culture the user is in (as opposed to the simpler querystring method we used
before) then talk about database design and integration. Finally we'll discuss
more advanced issues and possible solutions.

URL Rewriting

In our previous samples we simply
used the QueryString to determine a user's language of choice. While great to
showcase localization, it definitely won't do in a real-world application -
namely because it would be a maintenance nightmare across multiple pages. An
alternative I've used in the past is to use different domains or subdomains per
supported culture. While this has the advantage of requiring no maintenance, not
everyone has access to multiple domains or subdomains. Another alternative would
be to store the culture in a cookie on the client. Of course, this has all the
advantages and disadvantages of any other cookie-dependent solution. In the end,
I'm of the strong opinion that using URL Rewriting is the best alternative.

URL Rewriting Basics

If you're new to URL Rewriting you'll soon wonder how you ever lived without
it. There are a lot of reasons to use URL rewriting, but the best is to make
your URLs a little friendlier and less complicated. Basically URL Rewriting
allows you to create a link to a page which doesn't exist, capture the request,
extract information from the URL and send the request to the correct page.
There's no redirect so there's no performance penalty. A simple example would be
if you had some type of member system where each member had their own public
profile. Typically, that page would be accessed something like:

http://www.domain.com/userDetails.aspx?UserId=93923

With URL
Rewriting you could support a much nicer URL, such as:

http://www.domain.com/johnDoe/details.aspx

Even though
johnDoe/details.aspx doesn't really exist, you would capture the URL, parse the
address, lookup the userId for the username johnDoe and rewrite the url to
userDetails.aspx?UserID=93923. While the end effect is the same, the URL is far
more personal, clean and easy to remember.

URL Rewriting Culture

We want to embed the culture's name in the URL, extract that out to load the
culture and rewrite the URL as though the culture had never been in there. For
example:

We'll
now replace the code previously put in the Global.Asax's
Application_BeginRequest method to use URL Rewriting. At the same time we'll
move the code out of the Global.asax and place it in a custom HTTPModules.
HTTPModules are basically more portable Global.asax.

interface and lets us hook into a number of ASP.Net events. The only one
we are interested in in the BeginRequest[line: 3]. Once
we've hooked into the BeginRequest event, the
context_BeginRequest method [line: 6] will fire each time a
new HTTP Request is made to an .aspx page - just like before, this is the ideal
place to set our thread's culture. context_BeginRequest takes the
requested path and removes the application paths from it [line: 13].
LoadCulture is then called [line: 14]. By removing the
application path from the requested path, the culture name should be in the
first segment of our path. LoadCulture attempts to create a culture
out of that first segment [line: 22], if it succeeds it removes the
culture from the path [line: 23]. If it fails the default culture is
loaded [line: 28]. Finally, context_BeginRequest rewrites
the URL to the path modified by LoadCulture[line: 15].

Once this HttpModule is loaded through the web.config, it can be
used as as-is as the core localization engine without any additional work.

Database Design

The main issue I want to
address with database design deals with keeping a multilingual application
properly normalized. I've run across the same pattern of denormalized databases
due to localization far too often.

Sample Application Requirements

To better understand we'll look at a very simple example: a database that'll
be used to sell items (well, a small part of it). The basic requirements are:

Each item has a single category which is selected from a lookup of values,

Each item will have a name, description, Seller ID and price, and

The application must support English and French

The Bad Design

Coming up with a bad design really shouldn't take long, here's the schema I
came up with in a couple seconds:

As you can see, each item has a Seller Id which links to a mythical User
table, it has an EnglishName and FrenchName column, as well as an
EnglishDescription and FrenchDescription column and a price. It also has a
CategoryId which links to the Category table. The Category table has an English
and French name as well as an English and French description. This schema WILL
work, but has some severe problems:

The schema violates the first normal form, it has duplicate columns

The schema puts an additional burden on the developer. The developer needs
to know he wants the column named "EnglishDescription" instead of simply knowing
he wants the description

Many database engines have a maximum row size (like any version of SQL
Server except Yukon which is only in beta). We'll quickly run into that
limitation using the above schema

Even though the requirement clearly stated only English and French had to be
supported, requirements change, and this schema isn't at all flexible. If you
have 200 tables like this, you'll need to modify each one and add the
appropriate column. That'll make the above three points even worse.

The Good Design

Creating a clean and flexible schema is as simple as properly normalizing the
tables - that is removing the duplicate columns. Here's what the improved schema
looks like:

The trick is to identify fields which are culture-specific, which was pretty
easy because they either began with "EnglishXXXX" or "FrenchXXXX", these fields
are extracted into a _Locale table (just a name I came up with it) and
partitioned vertically (rows) instead of horizontally (columns). For vertical
partitioning to work, we introduced a new Culture table. I realize the
single-field Category table isn't very nice. Unfortunately, the Item's
CategoryId can't join directly to the Category_Locale table because it has a
joint primary key. Regardless, its likely that non-culture specific things will
go in the Category table, such as an "Enabled" and "SortOrder" columns.

In case you are having difficulty seeing this model, let's populate our
tables with sample data:

As you can see, all non-culture specific information is held in the main
tables (Item, Category). All culture specific information is held in a _Locale
table (Item_Locale, Category_Locale). The _Locale tables have a row for each
supported culture's. The schema is now normalized, we've helped avoid the row
size limit, and adding culture's is just a matter of adding rows to the Culture
table. You might think that this schema makes querying the database a more
complicated, in the next section we'll see just how easy it is.

Querying our Localized Database

Believe it or not, querying the database is a lot easier with the localized
schema. Let's look at an example. Say we wanted to get all the items sold by a
specific user (@SellerId) localized in the language your site visitor was using
(@CultureName). In the bad schema we'd have to write:

Hopefully you can see the huge advantage this has. It might be a little less
performant, but there's a single query no matter how many languages you are
going to support which makes it very flexible and easy to maintain. I left the
join to the category table [line: 9] in because, as I've said before, we
could definitely add some columns to Category which would make it desirable to
have. Additionally, getting the @CultureId from the
@CultureName[line: 2-3] is an excellent candidate for a
user defined function as almost every sproc do it. Other than that, the main
difference is that we are joining the _Locale tables [line: 8 and10] to
their parents and for the specified @CultureId.

Wrapping it up

There are only two things to add to our database design discussion. First
off, in case you missed it, the @CultureName parameter which you'll
need to pass into your stored procedures is actually an .Net
CultureInfo.Name. This makes things really easy, since the user's
current culture will be available in
System.Threading.Thread.CurrentThread.CurrentCulture.Name, or from
our code example in Part 1 is accessible from our ResourceManager
in the form of ResourceManager.CurrentCultureName.

The second issue is that our ResourceManager in Part 1 used XML
files, but using a similar schema as above (with a Culture table), it could
easily be modified to use a database. That exercise is left up to you.

Advanced Considerations

In this final
section I'd like to point out and address some advanced issues.

Basics of Placeholders

The first issue was pointed out to me by Frank Froese in the CodeProject's
publication of Part 1. Its an issue all multilingual application developers
have had to face and I'm thankful that he reminded me of it. The issue is that
you'll frequently want to have sentences with a placeholder (or two) in them.
For example, say you wanted to say something along the line's of
"{CurrentUserName}'s homepage", you might be tempted to do something like:

and set the "user" literal in your codebehind. However, not only does this
become tedious when binding, it simply won't work in a lot of languages because
of their grammar is simply different. For example, in French you'd want it to be
"Page d'acceuil de {CurrentUserName}". In the French example, the user's name
comes after the sentence, and our above code simply won't work. The problem only
gets worse when additional placeholders are needed.

What we want to do is have placeholders in the values of our XML files and
replace them at runtime with actual values. While you could use numbered
placeholders, such as {0}, I find using named placeholders, such as {Name},
conveys considerably more meaning and can really be helpful to a translator
trying to understand the context.

[line: 10, 12-20] (a NameValueCollection is the same as a
Hashtable but is specialized to have a string key and string value
as opposed to objects). In our Render method [line: 34] we loop through
the new field [line: 40-44] and replace the key with the specified value.
(As an aside, you might want to consider using a

System.Text.StringBuilder

object for performance if you like this method).

We can use the Replacement collection in our page's codebehind, like so:

It's basically a control with two properties a key [line:
9-12] and a value [line: 14-17].

There's only one more step to be able to use this new control as a child
control of your other Localized controls (well, actually, you can use it now,
but it won't do anything). Here's a new render method, with the explanation
following it:

Basically, in the render of each of your controls, you need to loop through
their Control collection [line: 6-13 (a collection of all
child controls). If the child is a Localized.Parameter[line
7,8] you need to do a replace [line: 11] any placeholder that are
equal to parameter's Key [line: 9] with the parameter's value [line:
10.

In the code that's included with this article, this looping functionality has
been extracted to a helper class, LocalizedUtility, so that it
isn't repeated for each control. Also please note that you can still set the
parameters in codebehind:

I'd like to mention a couple caveats which I ran into doing this. First off,
the Literal control which LocalizedLiteral inherits from doesn't
allow you to have child controls. As such I had to change it to inherit from the
Label control. I also noticed that if you did anything to the base class, the
control collection would get wiped. For example, if at the beginning of the
Render method you did base.Text = "";, the Control's collection
would switch to 0. I'm sure there's a good reason for this, but it did strike me
as odd.

ASP.Net 2.0

The last thing to talk about is how the next version of ASP.Net will change
how you develop multilingual applications. Unfortunately I haven't spent a lot
of time with the alpha and beta packages available thus far. Hopefully in the
near future I'll be in a position to write a follow-up. What I do know is
promising. It looks like they've really beefed up the builtin support - namely
by adding new methods to utilize resources (as opposed to the single
ResourceManager way currently available). Fredrik Normén has done
an excellent job of providing us some initial details in this blog entry.

Download

This download is very familiar to the one from Part 1. The things to keep an
eye out for is the removal of Global.Asax and the introduction of
LocalizationHttpModule (check out the HttpModule
section in the web.config to see how its hooked up). I really think this is a
nice way to store the user's culture. Also take a look at how the
Localization.Parameter works and play with it, I hope you'll find
that it meets your needs. Finally I didn't include any database code in the
sample. I'm hoping the above screenshots and code were self-sufficient. Enough
rambling, Download!.

Conclusion

Hopefully some of this tutorial will be helpful. The goal was to make things
fairly copy and paste friendly while at the same time providing flexible code
and some general guidelines. There are a number of enhancements that could be
done, such as giving intellisense support to the ILocalized
controls in the designer, expanding the ResourceManager to be more
like a provider model, providing management facilities for resources and so on.
The power Microsoft has given us with .Net makes all those things possible. This
has been a real pleasure!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Comments and Discussions

How would you localized the Columns in the header on a datagrid, should i do the same as in the dropdown example.
My site has all the ui hooked to the xml resources, since my client doesnt want to localize content.