ou never know when your software might be used by people who speak different languages. In a multinational company, branch offices need to share database applications. By localizing your product, you can enormously increase its potential market. In addition, letting people work in the language in which they’re most comfortable increases productivity, reduces errors, and shows respect for their culture.

But how do you make it so? The .NET documentation recommends that you use resource files. Behind each form, there’s a .res file that can be used to store the strings that appear on the form, where you can put corresponding translations into other languages. The following excerpt from the .NET documentation describes how you use them:

“You can localize your application’s resources for specific cultures. This allows you to build localized (translated) versions of your applications. An application loads the appropriate localized resources based on the value of the CultureInfo.CurrentUI-Culture property. This value is set either explicitly in the application’s code or by the common language runtime based on the locale for the current user on the local computer.”

In a nutshell, Microsoft recommends that you create satellite assemblies of resource files, one per culture. Setting or changing the CurrentUICulture setting changes the satellite assembly, and your translations appear.

Unfortunately, that’s not always the best way to do it. Resource files make more sense in ASP.NET. But in the case of Windows Forms applications, if you use resource files, you have to make any required changes to them, which means that:

• You’re responsible for the translations; if your users don’t like your choice of terms, it’s a technical support issue;
• Different countries that speak the same language use different words for the same thing; computadora, ordenador and PC are three Spanish translations for “computer”;
• Users have to wait for a new res file in order to get their screens fixed.

I appreciate Microsoft’s efforts; especially since localization has been one of my favorite topics for many years (I speak five languages, and worked my way through grad school as a simultaneous interpreter.) However, the res file approach doesn’t really put responsibility for the maintenance of the translated captions in the hands of the users, where it belongs.

This article will demonstrate a simpler mechanism for creating localized apps. I’ll show you how to build a class that stores all translatable captions from screen and menu objects in a collection within each screen. When the user changes the screen language using a “Language” combobox on a screen, the SelectedIndexChanged event code looks up and replaces the original captions with their translations. Finally, I’ll show you how to provide a Translation Table Manager to let users provide translations for all captions harvested from the screens in the application. The examples in this article are in Visual Basic .NET, but the downloadable source code is available in C# as well.

The Tables
Table 1 shows the three tables I’ll use in this implementation. You can use the Server Explorer to create the Translations database, and then create the three tables described above. If you have SQL Server Developer Edition, you can also use either the Enterprise Manager or the Query Analyzer. You can also use an Access MDB file or FoxPro tables. The application works with all three data sources.

Table 1: Tables used in the application.

The Languages table contains the list of supported languages. The Original table contains all of the strings found on all of your application’s screens. The Translated table contains one entry for each translation of each of the strings in the Original table for each supported language. If an entry hasn’t yet been translated, it doesn’t have an entry in the Translated table.

The Data Access Class
To allow the use of SQL, Access MDB files or FoxPro DBFs to store translated captions, I wrote the data access class shown in Listing 1. It has three methods: the ReturnDS method returns a dataset; the ExecCmd method executes any other SQL statement (typically an INSERT or DELETE); and the ExecScalar method is used here to determine whether a particular string is already in the table.

Thanks to this class, you can use either centralized or decentralized translation tables. In some installations, you may want to use the same translations for all workstations, and store them all on the same SQL Server where the rest of your data is stored. For others (for example where a country specialist is in charge of all customer contacts in Malasia), you might let each workstation maintain its own translations. All it takes is a change in the App.config file, as we’ll see at the end of this article.

The DataAdapter and Connection fields da and cn are defined as Objects. So I can create either a SQLDataAdapter or an OLEDBDataAdapter and assign it to da, or create either a SQLConnection or an OLEDBConnection and assign it to cn, without getting a complaint from the compiler. If I need to, I can return cn and/or da to the calling program and pass them around the application.

DAC is compiled as a component. This allows me to drop it on a form class and set its properties through the Properties Sheet. To create a component:

1. Create a class library project, then delete the default Class1.vb module.
2. Right-click on the project name in the Solution Explorer.
3. Select Add, then choose New Item, then choose Component.
4. Give the component a name, and write the class code.

You’ll have to manually add references to System.Data and System.XML, because both namespaces are used by the component.

Using a component relieves you of the chore of writing Dim DAC1 As New DAC in the declarations, because it’s added automatically when you drop it on the form. It also places the component in the tray below the design surface and exposes its properties in the Properties Sheet. Once the properties are set, it takes just three lines of code to populate a DataGrid from any data source using the ReturnDS method:

   Dim ds as new dataset   ds = DAC1.ReturnDS("SELECT * FROM EMPLOYEE")   DataGrid1.DataSource = ds.Tables(0)

To add the new component to the Components tab of the toolbox:

1. Compile the project (it will be of type Class Library, which is a DLL).
2. Open the Component tab in the toolbox.
3. Right-click and select Add/Remove Items, and browse to the DLL.

You can now drop the component on a form and set its properties either on the Properties Sheet or programmatically.

The DAC class has six properties, although none of the three data access types requires all six of them, as shown in Table 2. The value of AccessType determines which of the others will be needed. It uses them to construct a connection string with the correct contents, depending on the access type. You can use the Properties Sheet to fill in the required properties. You can also read them from App.config, as I do in the Main module for this application.

Table 2: DAC properties and settings depending on AccessType value.

The “..” in the table means “one level above the bin directory where the executable is located.

The FoxPro driver can use either a directory or a DBC.

All three DAC methods start by building a connection string based on the supplied properties, and then execute the SQL statement that was passed to the method.

Typically, FormPreparer is included in all base forms, although individual forms not based on any class can also use it. It iterates through all controls on the form and inserts any captions (the Text property) that have not already been stored in the Original table into it.

The FormPreparer Component’s StoreCaptions Method
If you’ve read this far, you’ve probably surmised that when the user picks a language, the translation mechanism is going to look up each text string on the form and replace it with the translation corresponding to the currently selected language that it finds in table Translations. But if you’ve already translated a screen with the word Country on it to Spanish, you now have Pa?s on the screen, which you won’t find in the Original column of the Translated table.

The trick to translating GUI elements is that you have to base translations on the initial caption, not on whatever’s currently on the screen. Thus you have to save the original captions that are on the screen when it loads. I use a collection, for reasons that you’ll see shortly. I originally used the Tag property of each control, but … well, you’ll see.

In order to prepare each form for translation, I built a component called FormPreparer, which contains just one method?StoreCaptions. FormPreparer is a component, just as DAC is. To create this component:

1. Create a class library project called FormPreparer.
2. Delete the Class1.vb module and add a new component. Name it FormPreparer.
3. Enter the code in Listing 2.
4. Compile the project and add it to the Components tab as you did with the DAC component.

You can now drop it on any form whose captions need to be stored in the Original table in preparation for translation. It should be called in the form’s Load event, as you’ll see in the inheritable form class shown in Listing 4.

The Text properties of labels, buttons, checkboxes, and radiobuttons, as well as grid column headers, will need translation. That’s pretty straightforward; you iterate through the Controls collection of the form and store the text property in a collection named Captions, using the control’s name as the key. (The syntax is collection.add (Value, Key). I do this in my base form class so that every inherited form will automatically be prepared for translation.

My first inclination was to store the initial caption in the Tag property. Labels, checkboxes, and other standard Windows form controls have a Tag property that’s available for “users” (that’s us.) But forms can also have menus, which consist of MenuItem controls. Menuitems are really, really different. For one thing, they don’t have a Tag property. They don’t even have a Name property! Besides, I wanted to handle all form controls with captions in a similar fashion.

In my solution, I create a unique identifier for each MenuItem and store it in a collection named Captions. And as long as I’m using the Captions collection for menu items, why not use it for all of the other controls as well, using each control’s name as its key?

In my inheritable form class, BaseForm, I populate the Captions collection using the control’s Text property for the value and the control’s Name property for the key:

   For Each Ctrl As Control In Controls       Captions.Add ( Ctrl.Text, Ctrl.Name )   End For

But how do I get a unique reference to each MenuItem (they don’t have a Name property, remember)? It really doesn’t matter what the keys are, as long as they’re unique, and can be reproduced again when it’s time to look up the corresponding menu captions for translation. So I create a string called mLevel, which starts life as a null string, and store consecutive integers (converted to strings), representing each MenuItem’s horizontal position in the menu hierarchy, as identifiers. Thus the keys for the first row of MenuItems are “0”, “1”, “2”, etc. But MenuItem “0”, File, has a dependent pad named “Exit” below it, which is a child menu item. So, I call the routine recursively, passing it the current value of mLevel instead of the null string. Hence the generated key for File, Exit is “00”, the generated key for Tables, Clients is “10”, and so forth. If you have several forms with menus in your application, prefix this key with the unique form name.

The FormPreparer component stores the translatable items on each form in the Captions collection, and the InsertWord method stores the caption in the Original table if it’s not there already. And since menu items can have their own MenuItems collections, the code that translates menu items recursively calls itself if the menu item’s MenuItems.Count property is greater than zero. (You read right: menuitem1.menuitems.count > 0 means that menu item1 has a submenu.)

When it’s time to translate a menu on a form, I follow the same methodology to re-create the keys, and then use them to retrieve the original text captions for the corresponding menu items from the Captions collection and translate them. To translate form controls, I just iterate through the Controls collection, use each control’s name to find the original text value in the Captions collection, and again pass them to the translation method, which assigns the translation to the Text property (see BaseForm.vb in Listing 4).

The StoreCaptions method is called in the Load event of each inheritable form; it passes the form itself as its only parameter, returning the resulting collection to a form field named CaptionCollection:

   CaptionCollection =  _      StoreCaptions1.StoreCaptions(Me)

The Translation Table Manager
Figure 1 shows the Translation Table Manager form in the Designer. I include this in my applications so that users can manage their translations themselves (although in most cases, only users with the appropriate rights can see the form). The form inherits from my inheritable form BaseForm, to be described a little later on, so it has a FormPreparer and a DAC component, and is also translatable. This screen initially displays all of the captions that have been harvested from forms in which the StoreCaptions method of the FormPreparer component has been run. Figure 2 shows the form with only the original captions loaded.

Figure 1. Translation Table Manager Form in the Designer.

?

Figure 2. Translation Table Manager in Action.

In the Translation Table Manager’s Load event, the Original table is loaded in the left column of the grid; the right column contains the corresponding strings for the currently selected language (based on the Language combo box at the upper left corner of the screen) loaded from the Translations table. Figure 3 shows the form with the Spanish translations loaded.

Figure 3. Adding Spanish Translations to the Translation Table Manager.

?

Figure 4. Building BaseForm.vb.

Rather than allow editing in the active row of the grid, I put a pair of textboxes below the form to display the original string and allow the user to enter or edit the translation. Captions can be long, and it’s useful to be able to see the entire text of the original language while typing the translation. So the user selects a row, clicks on the “Edit” button, adds or edits the translated text, and clicks Save or Cancel. Delete just deletes the translation, not the original entry, which came from the application’s forms and can’t be altered by the translator. Listing 3 shows the code for the Translation Manager form.

	?
Figure 5: The TranslationDemo Project with MainForm.vb and Customers.vb.

Using the Translation Tables in Your Windows Forms
Now that you know how the text captions are collected and stored, and how translations are created and stored in tables, how do you use them in your forms? Just put the code to translate the form into the inheritable form template classes that all your forms inherit from, and drop a combo box with all available languages on the form. In its SelectedIndexChanged event code, look up the original captions of controls and MenuItems, retrieve the translation for the current language’s translation from the Translated table, and assign it to the control or MenuItem’s Text property.

I built an inheritable form called BaseForm, shown in Figure 4. The distinguishing characteristic is the combo box, cmbLanguagePicker, which I load with the names of the available languages in the Load event. The code for the combobox’s SelectedIndexChanged event, which translates all control and MenuItem captions on the form, is shown in Listing 4. The shaded code is an overloaded constructor (New) method that lets me pass two DAC components to the form when it’s created.

I then added two forms that inherit from this base form class: MainForm.vb and Customers.vb, seen in the TranslationDemo project shown in Figure 5.

Figure 6 and Figure 7 show the MainForm and a Customers form, respectively. To create them, right-click on the TranslationDemo project, select Add, Inherited form, provide the form name, and select BaseForm as the form from which to inherit.

Figure 6. The MainForm.

?

Figure 7. The Customers Form in Action.

On the Customers form, I added three labels and three textboxes corresponding to three of the columns in the Customers table of the Northwind database. The Tables, Customers pad of the MainMenu control in MainForm contains four lines of code to instantiate the customers form, pass in the references to the two DAC objects, and activate the form:

   Dim frm As New Customers   frm.AppDataDAC = AppDataDAC   frm.TranslatorDAC = TranslatorDAC   frm.Show()

Automating Databinding
One of the truly irritating features of .NET is the extra steps that you have to go through to get controls to display data bound to them. In virtually all of the exercises in the VS .NET help, you create a Typed Dataset, and then fill in the Text element of the DataBindings property with dsName.ColName. That’s okay, but it’s not so great if you’re trying to build generic mechanisms. And since the typed dataset for the twelve-column Customers table is nearly a thousand lines of code (albeit automatically generated), it just feels like a bridge too far. You don’t need a typed dataset to use databinding, as you’ll see.

In Listing 5, I’ve included the code for the Customers form that automatically binds the data. It assumes that bindable controls are named with a three-character prefix followed by the name of a column in a table in the dataset (e.g. txtCompanyName). This is a simple example, but it shows what you can do with a little generic code. You can put this into your base inheritable forms and never worry about databinding again, provided you’re willing to use the required naming convention.

The names of the three textboxes (txtCompanyName, txtContactName, txtContactTitle) are all that the BaseForm.vb inheritable form class code needs to automatically bind the three textboxes to their respective data columns in the data source, which is ds.tables(0). You don’t even need a typed DataSet. I added Next and Previous buttons so you can move around in the dataset.

The Forms Translation System in Action
I’ll now show you how to build an application that demonstrates how this works. First, I’ll create a Main module to set up the data access components for the translation system as well as for the application’s data. Then I’ll launch the main form with the application’s menu on it.

The MainModule module (see Listing 6), is the startup object for the TranslationDemo project, which is the startup project for the solution. It creates two DAC components, one for translations and one for user data.

Instead of setting the components’ properties in the Properties Sheet, I used the MainModule code to read the values from App.config:

When I launch the application, MainModule loads the property values for the two DAC instances (one for the translations, one for the application’s data), then launches MainForm. MainForm immediately calls the StoreCaptions method of the FormPreparer component to store the captions that it finds on the form into the Original table, if they aren’t there already (see the InsertWord method in Listing 2). In this case, the only captions on MainForm are the form title and the label beside the cmbLanguagePicker control. If I select “Translations” from the menu, it brings up the Translation Table Manager, where I can enter translations for these captions into Spanish and French, the two languages I previously loaded into the Languages table.

When I launch the Customers form, the captions for the three labels on the form are loaded into the Original table. I can close the form, open the Translation Table Manager, and translate them. The next time I open the Customers form, I can select Spanish, and my translations will be used. Figure 7 shows the Customers form when initially loaded. In Figure 8, I’ve selected Spanish, and the screen captions instantly change to Spanish.

	?
Figure 8: The Customers Form with Spanish Selected as the Language. Note the Controls and the Menus.

For a little thrill, add the Russian keyboard using Control Panel, Regional and Language Settings, then enter Russian translations for these strings. If you don’t know Russian, just make something up; it’ll look right to you. It took me about 30 seconds to add Russian translations for these strings, open the form again, select the latest language, and see the captions change to Russian.

Allowing users to maintain their own translations of their screens solves some serious maintenance headaches, and uses available talent. You can use a local MDB or a set of FoxPro tables for the translation subsystem, or integrate it with your SQL databases. Either way, it’s a powerful tool to add to your toolkit, and will open up new worlds for your software.