fforts to simplify application globalization and localization have definitely succeeded over the past few years. ASP.NET 2.0 made the process of localizing resources and deploying them with applications easier for developers. For example, Visual Studio 2005 can automatically generate local resources on a per-form basis, and can also generate static classes for all strongly-typed resources. Visual Studio 2005 and ASP.NET 2.0 natively provide methods that streamline the process of retrieving resources programmatically. ASP.NET also automatically detects browser culture preferences, assigning the detected culture preference to the culture of the requested thread.

Current Resource Capabilities
For example, to generate a local resource file from an ASP.NET Web Page you can simply go to the Tools Menu in the Visual Studio IDE and click the “Generate Local Resource” item; Visual Studio will place the generated file in an App_LocalResources folder. You can also generate Global Resources by adding a new item of type Assembly Resource File to the Web site and placing that file in the App_GlobalResources folder (which Visual Studio will create for you).

ASP.NET supports “implicit” localization?meaning that if you follow the correct syntax and all the resources are present, localization can occur automatically. To take advantage of implicit localization, resources must follow the conventional Key.Property pattern. For example, for a button (Button1) in a form where you would like to localize the Text and the BackColor attributes, the resource file should contain the Button1.Text content and the Button1.BackColor value.

You activate this implicit localization by using a special meta attribute in the markup as shown below:

You do not have to explicitly indicate which property is being localized if the property is defined in the resource; ASP.NET makes that substitution automatically.

You can also use a resource expression to localize the properties of a control, as opposed to using implicit localization; this explicit method requires one expression per property to be localized. For example:

   <%$Resource:Class, ResourceID %>

In the preceding expression, the Class value is the name of the global resource file without the extension?in other words, the base file name. If you’re pulling the resource out of a local resource file, you don’t need the class value. The ResourceID value is a key that identifies the resource to link to.

So, to localize the button Text property using an expression, you’d write:

Finally, if you need to set a resource value at run time?and the value is unknown at design time, or will depend on a run-time condition?you’re better off accessing the resource programmatically. A clear example is when a developer needs to manage errors in an application. In this case the UI should present localized error messages to the end user, but the developer can’t know which localized message to display in this scenario at design time.

To do this, you can call the GetLocalResourceObject or the GetGlobalResourceObject method of the HttpContext and the TemplateControl classes in the framework:

   Button1.Text= GetLocalResourceObject("Button1.Text").ToString()

ASP.NET and Visual Studio 2005 build a strongly typed resource class behind the scenes for each global resource file and make those classes available through IntelliSense, so you can also use the strongly typed static class to retrieve a global resource with this syntax:

   Resources.Class.Resource

For example, to display a “Welcome” string:

   Dim welcome as String   Welcome = Resources.WebResources.WelcomeText

Need for Additional Resource Management
Despite all the advantages built into ASP.NET 2.0, the process of localizing a Web application poses several challenges that the .NET 2.0 framework does not resolve. Modern applications are not restricted to a few Web pages and controls; instead, the number of resources they must translate and manage increases constantly.

Visual Studio 2005 lacks a mechanism for synchronizing resource files?that is, keeping resource keys in synchrony with the invariant culture. It also lacks a versioning and management system.

The rest of this article describes a project we’ve built while managing internationalization projects called the Localization Management Toolkit, intended to help solve some of the problems associated with managing resources and translation for large global applications.

Introducing the Localization Management Toolkit
Even though Visual Studio 2005 automates resource generation per Web Form, the task of synchronizing, managing, and versioning the resource files, and shipping them to translators for an application quickly becomes cumbersome when more than three cultures and thousands of resources are involved. The entire resource-management and translation process is easier to manage when there is a central resource repository. The Enterprise Localization Management Toolkit (the sample code, in beta as of this writing, accompanies this article) introduces a mechanism that supports this central repository and single file creation, while still keeping the automatic resource generation capabilities of Visual Studio 2005. The Toolkit’s features greatly simplify communication with the translators, reducing the number of files to track while maintaining high developer productivity.

The Toolkit consists of three main parts:

• A central repository in an SQL Server 2005 database
• A Windows application for the technical manager
• A Web application for developers and translators

Developers use the Web application to modify the values of the resources they create or delete resources, while translators use it to review newly created resources and perform their translations based on the invariant culture value of a particular resource.

If translators do not have Web access to this application, they can use the resource editor utility that ships with the .NET Framework 2.0 SDK and work with a single resource file that administrators can generate using the Toolkit administration tool.

Administrators use the Windows application (The Toolkit Administration Tool) to import new resources into the database, and generate updated resource files for an application. The Administration Tool also supports adding a new culture to an application automatically, generating the appropriate file for translation along with the resource files for the newly added culture. It tracks resource file generation, so you can know exactly when each file was generated.

In short, here are the features of the Toolkit:

• Microsoft SQL Server-based repository to store and manage the localized resources across all ASP.NET applications within an organization.
• Web-based resource management application for use by programmers, translators, and deployment personal.
• Support for the generation of loose resource files or satellite assemblies.
• Ability to automatically populate the localization database based on keys specified by a developer or Web designer by importing resource (.resx) files into the database.
• Ability to generate .resx files from the database after they have been translated and reviewed by the translators keeping track of the version.

The Toolkit’s Resource Database
Storing resource information in a database, as opposed to using many separate .resx files, can improve the overall manageability of a localized application. It can also simplify your approach for working with translators as you can easily generate reports, update several keys at the same time, or create new cultures by running T-SQL queries.

	?
Figure 1. Database Diagram: The figure shows the tables, columns, and relationships in the Toolkit resource database.

The database used by the Toolkit was designed to serve as a central repository for an enterprise, storing the resources and cultures of all the applications within the organization. Figure 1 shows a schema diagram. The database contains the following tables:

• Elements: Holds the key names or resources from the key value pairs. It has a foreign key from the DataSource table indicating in which resource file this key is located.
• DataSources: Stores the names of the resource files in an application, using a foreign key ApplicationID column to link each resource file with the application that uses it.
• Applications: Stores the names and attributes of the applications within an organization.
• Cultures: Acts as a lookup table storing all the culture names supported by the .NET Framework.
• ApplicationCultures: Stores all the cultures that are supported per application in the enterprise organization.
• CultureProperties: Stores the Values from the key.value pairs (in a PropertyValue field). There should be at least one value present in this table for the invariant culture. This table allows the translators to store comments, keep track of when the PropertyValue was created by the Toolkit or when it was last updated by a translator.
• ElementTypes: Serves as a lookup table for element types.
• ResxVersions: Keeps track of generated resource files per culture, application, and resource file name. It has an XMLType field that stores the particular version of the resource file acting as an archive of old versions.

Assumptions, Constraints, and Triggers
This Toolkit requires a unique ElementKey?the Key that links the resource with a particular control?for each resource file. It enforces this with a unique index on the combined ElementKey and DataSourceID fields in the Elements table. It also requires resource file names to be unique within an application domain, again, enforced with a unique non-clustered index on the columns ApplicationID and DataSourceName in the DataSources table.

Given a key value or ElementKey, there is a single PropertyValue (value) per ApplicationCulture, which is a single value or translation for that key per culture supported by the application. Explained in the context of the previous example, the key Button1.Text can have only a single value for the French- Canadian Culture in a given application. Each application/culture combination must be unique in the ApplicationCultures table, enforced using a non-clustered unique index.

When deleting an element or key, the deletion must propagate to the CultureProperties table, deleting all the entries (PropertyValues) associated with the deleted ElementKey. A delete trigger in the Elements table performs this action.

   USE [Localization]   GO   /****** Object:  Trigger [delElements]          Script Date: 11/07/2006 04:41:54 ******/   SET ANSI_NULLS ON   GO   SET QUOTED_IDENTIFIER ON   GO      CREATE TRIGGER [delElements]     ON [dbo].[Elements]     INSTEAD OF DELETE   AS   BEGIN         DELETE FROM CultureProperties       WHERE ElementID IN        (SELECT ElementID FROM Deleted)        DELETE FROM Elements        WHERE ElementID IN        (SELECT ElementID FROM Deleted)      END

Inserting an element or key (ElementKey) creates a corresponding PropertyValue per culture in the CultureProperties table with a null value. The insertion stored procedure stores the given PropertyValue in the row corresponding to the invariant culture, leaving the rest of the values (for the different cultures) with the null value created with the trigger, indicating that PropertyValue is ready to be translated.

   USE [Localization]   GO   /****** Object:  Trigger [insElements]          Script Date: 11/07/2006 04:44:28 ******/   SET ANSI_NULLS ON   GO   SET QUOTED_IDENTIFIER ON   GO      CREATE TRIGGER [insElements]     ON [dbo].[Elements]     FOR INSERT    AS   BEGIN         INSERT INTO CultureProperties(       ApplicationCultureID, ElementID,       PropertyValue,Comments,Updated, Created)     SELECT   AC.ApplicationCultureID,I.ElementID,NULL,NULL,       GETDATE(),GETDATE()     FROM Inserted I INNER JOIN DataSources DS ON         I.DataSourceID =DS.DataSourceID      INNER JOIN ApplicationCultures AC ON        DS.ApplicationID=AC.ApplicationID      END

When a resource file is deleted from an application, the elements stored in that resource file should be deleted as well. This cascade deletion takes place with an instead of trigger in the DataSources table.

A resource administrator should be able to add and delete cultures to and from a given application. Whenever the administrator deletes a culture from an application, a trigger in the ApplicationCultures table deletes the CultureProperties records corresponding to that culture and application from the CultureProperties table:

   USE [Localization]   GO   /****** Object:  Trigger [delApplicationCultures]         Script Date: 11/07/2006 04:56:30 ******/   SET ANSI_NULLS ON   GO   SET QUOTED_IDENTIFIER ON   GO      CREATE TRIGGER [delApplicationCultures]     ON [dbo].[ApplicationCultures]     INSTEAD OF DELETE   AS   BEGIN 
?
Figure 2. Database Tree View: The tree view shows the columns in the ResxVersions table.
        DELETE FROM CultureProperties        WHERE ApplicationCultureID IN        (SELECT ApplicationCultureID FROM Deleted)        DELETE FROM ApplicationCultures        WHERE ApplicationCultureID IN        (SELECT ApplicationCultureID FROM Deleted)      END

The resources archive table ResxVersions lets administrators track the different builds and resources files generated from the database.

Figure 2 shows a tree view of the database.

	?
Figure 3. The ResxVersions Table: The figure shows the data type definitions for the columns in the ResxVersions table, including the field defined as an XML type.

XML Support is Critical for the Toolkit
SQL Server 2005’s XML type lets you store XML documents in a field. The ResxVersions table in the database includes an XML Text field (see Figure 3).

Without the XML type introduced in SQL Server 2005 it would be extremely difficult to store the entire XML document defining the resources inside the database. Storing the data in an XMLType field lets the Toolkit use XQuery, T-SQL, and indexing mechanisms to search for old resource values in older versions of a file, reducing the client workload and letting SQL Server handle most of the processing. Using an XMLType field also simplifies exporting; you can export a set of resources for any particular version or build of the application you’re localizing or even export a particular element key and all its versions. XQuery and T-SQL together provide a powerful tool for versioning and archiving XML data.

Stored Procedures in the Toolkit Database
The Administration Tool and Localization Web Site applications take advantage of stored procedures inside the database to add applications, update and delete elements and cultures for a particular application, and so forth.

Using the Toolkit-supplied T-SQL queries, administrators can research why translators returned a particular PropertyValue with a null, empty, or inadequate value. For example, when our company shipped a master resource file to a group of translators who were unfamiliar with the terms inside the application, they did not translate the technical error messages, assigning a “not available” (NA) tag to these property values. When the user interface of the application began to display the “N/A” strings, the supplied stored procedures simplified the process of identifying and returning for translation only those terms that had a PropertyValue equal to “N/A.” Previously, we would have had to parse all the resource files or create a script to parse the XML text.

Populating the Database
To populate the database, you first use the Visual Studio 2005 Local Resource Generation Tool to generate the local resources fore each of your applications. Then you can script a code parser for any dynamically generated phrase, such as error messages, that will need to be localized. These last phrases can go into a global resource file.

With these .resx files in hand, you can use the Administration Tool to populate the database. At this beta stage, this is the only way to introduce resources into the database. We did this for simplicity and to maintain a strict procedure for the developers. You will have the option in the future to add the ability to create resources to the Web application, so that developers can manually add resources into the database, but at this point, only resources generated by the IDE Local Resource Generation Tool or Global Resource Files can populate the database.

Using the Administration Tool
The Administration Tool is currently a Windows application (see Figure 4), but a subsequent version will add the functionality of this tool to the Web Application.

	?
Figure 4. The Enterprise Administrator Application: This Windows Forms application lets you add applications to the database, import and export resources, and see reports.

The term “Master Resx File” designates a file that comprises all the resources used in the application per defined culture. The main purpose of this file is to hold all the resources shipped for translation, because it’s easier to keep track of a single file in the translation process than to keep track of multiple local resource files sent to a translation team. There is one “Master Resx File” per culture supported by the application

The translators can use any of the available tools, such as the ResxEditor, to read .resx files. We usually ship the culture-specific file along with the Invariant Culture Master file for translation. Translators can also use the Web application to translate terms online. Concurrency is resolved by the “last-in wins” policy; however, the database tracks when a PropertyValue was last updated. Eventually, it will be easy to add a reconciliation mechanism based on the date the file was received from the translators and the Updated column in the CultureProperties table.

Adding a Culture to an Application
Adding a specific culture or neutral culture to the application will not automatically add the invariant culture to the list of cultures?although doing so should be considered a good practice. Unless indicated otherwise, the invariant culture will be the same as the ‘en’ English culture. Figure 5 shows how to manage application cultures using the Administration Tool.

Importing ResX Files with the Administration Tool

	?
Figure 5. Managing Cultures: The figure shows the culture-management screen that lets you manage cultures for multiple applications using the Administration Tool.

The Administration Tool option “Import Resx Files” asks users to select the application name from the list of applications on the enterprise, opens a dialog so users can choose the folder where the application base code resides, and recursively grabs every file with a .resx extension from the given folder and from any child folder. Administrators must have these resource files on a local hard drive. They can generally pull these files from a central repository that has version control and save them locally.

While importing, the Administration Toolkit relies on the common resource file-naming convention where the culture associated with a resource file appears in the file name before the .resx extension. For example, a typical filename might be strings.es-ES.resx, meaning that culture associated with the file is es-ES (Spanish from Spain). Resource files that do not have a culture indicator are considered invariant.

The DataSourceName field in the DataSources table keeps track of the filename where a particular resource is located.

Importing Master Resx With the Administration Tool
The option “Import Master Resx File” also takes into account the filename convention and the name of each resource. In this case, the ElementKey column value is a concatenation of the DataSourceName and the actual ElementKey. This helps ensure that the application imports terms to the correct Element, given that an ElementKey must be unique for each DataSourceName. The key in the key.value pairs on every resource file is unique in that resource file.

	?
Figure 6. Exporting Resource Files: The figure shows the process of exporting the resource files for a given application using the Administration Tool.

Exporting Resx Files with the Administration Tool
Completing the translation process for an entire application usually requires several iterations. Because the resources are stored in the database, you must export them as .resx files for testing or for final deployment, and attach the exported .resx files to your application. The process of exporting one .resx file per data source and per culture is also straightforward with the Administration Tool (see Figure 6).

Exporting Master Resx Files with the Administration Tool
The Administration tool also lets you export one Master Resx File per culture. In this case, to avoid ElementKey collisions, the tool exports resource key names as a DataSourceName.ElementKey combination. Translators can then use any XML reader or resx file editor to perform their translations in an offline mode.

Finally, the Administration tool offers you a way to keep track of the generated resx files. Whenever an administrator generates a resx file, the tool tags it with a version number and stores a copy in the database, thus keeping an archive of the resources generated by date. You can view the generated resx file history (see Figure 7), and you can also export any previously generated resource file from the archive.

	?
Figure 7. Resource File Archive: The Administration Tool tracks and archives exported resource files; you can select any archived resource file and export it.

Accessing Archived Resx Files with the Administration Tool
This toolkit assumes the applications will use satellite assemblies as the ultimate repository of resources. You can, however, modify the Resource-Provider Model for ASP.NET 2.0 and use the database directly, pulling out resource key.value pairs. For example, suppose you were shipping the final version of an application to a customer; you won’t have any more chances to update its content. However the customer might like to be able to change the wording of some of the content of the application on the fly. To avoid forcing the customer to generate resource files and deploy them into a bin directory with the application, you can provide a content management system that will let the customer change the content dynamically. You can do this by using the database directly as the unique repository rather than satellite assemblies. This eliminates the need to generate resource files and deploy them, and customers can alter and show the content retrieved directly from the database.

A recent MSDN article, “Extending the ASP.NET 2.0 Resource-Provider Model,” expands on this subject. You can modify the sample code provided in that article to work with this Localization Toolkit.

Using the Toolkit’s Web Interface
Global businesses often teams located all over the world to maintain their applications. It is not uncommon to outsource translation work and even development work. A Web application is a suitable solution to keep the translation process centralized and guarantee reach.

The Toolkit includes a simple Web application that allows users to update the values of the resources per culture and application. There are two main roles: developers and translators. Developers can delete resources or modify the text on the invariant culture values while translators can only update the values for cultures other than the invariant one.

To prevent unauthorized users from changing resources, you can modify the Web application to restrict permissions to users based on culture, application, and role. Figure 8 shows the Toolkit login screen. The Toolkit uses the Membership and Profile Provider in ASP.NET, which facilitates the process of creating users and assigning roles. In addition, using this provider will aid in adding features to the Toolkit in the future, such as a finer-grained role/permissions schema.

? Figure 8. Login Page of the Toolkit Web Application: The application automatically detects the browser culture of the user logging in, as the two different versions in this figure show.

? Figure 9. Working with Resources: Developers can delete or modify the wording of resources to be translated.

This Web application presents developers with an initial Web Form that lets them select an application to work on. Next, they select from a dropdown list containing a list of resource files associated with the application. After selecting a file, they see a grid showing the resources stored in the selected file (see Figure 9). Developers can edit the value of a particular resource for the invariant culture or delete that resource if it is no longer in use.

The Toolkit makes use of the ASP.NET’s two-way databinding feature, using the ObjectDataSources property to bind data to the GridView and FormView controls used in the application.

The Web application is itself fully localized; a language selector allows end users to select a preferred culture.

Future Plans for the Toolkit
Despite the recent features added to Visual Studio 2005 to simplify localization, the IDE does not provide a notification mechanism that warns you when you need to regenerate a resource file because of page changes. The IDE should help synchronize pages with resources associated to those pages, notifying developers when the set of controls changes that those changes should be reflected in the resources. More often than not, resource files grow unnecessarily, because developers tend not to delete resources associated with controls that no longer exist on the page.

There should be automatic selection of the resource provider and a tool for configuring it, similar to the aspnet_regslq.exe tool for the Membership and Profile providers.

Basically, the Toolkit discussed in this article is intended as a management tool that centralizes and tracks resource files shipped to translators. When returned, the tool imports translated files back into the Toolkit database. This database approach supports creating reports of shipments and returns, keeps track of versioning, and simplifies resource management for multiple applications at the enterprise level.

We demonstrated how the Toolkit integrates with the automatic local resource generation in Visual Studio, and briefly discussed IDE extensibility. Finally, we suggested using the database schema proposed in this article and a Resource Provider Extension to store resources in a database as opposed to using satellite assemblies.

We hope this tool can help other developers and translators with the task of globalizing and localizing applications with Visual Studio 2005 and ASP.NET.