Localize Your .NET Windows Forms Apps : Page 2

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.

Languages	SQL	MDB	DBF
Lang	Nvarchar ( 10)	Text (10)	Char ( 10)
Original
Original	Nvarchar (128)	Text (128)	Char (128)
Translated
Lang	Nvarchar ( 10)	Text (10)	Char ( 10)
Original	Nvarchar (128)	Text (128)	Char (128)
Translated	Nvarchar (128)	Text (128)	Char (128)

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.

Property Name
AccessType	SQL	MDB	DBF
UID	Sa	Admin	N/A
PWD	Optional	Optional	N/A
Database	Translations	N/A	N/A
Server	(local)	N/A	N/A
FileName	N/A	..\Translations.mdb (1)	..\ (2)

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.

Previous Page

Next Page