It's a small world.
For the price of a nice pair of shoes, you can get on a plane, have dinner, watch a movie, sleep a few hours, and wake up on another continent. Your software can travel even more easily. When it gets there, will it be ready to go to work?
You 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.
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.
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. Res files make more sense in ASP.NET. But in the case of Windows Forms applications, if you use res 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.
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
In order 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.
Letting people work in the language in which they're most comfortable increases productivity, reduces errors, and shows respect for their culture.
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:
You'll have to manually add References to System.Data and System.XML, since 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:
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 the 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.
".." 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 of my 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:
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, which I 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 menuitem. 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 menuitems can have their own menuitems collections, the code that translates menuitems recursively calls itself if the menuitem's menuitems.count property is greater than zero. (You read right: menuitem1.menuitems.count > 0 means that menuitem1 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 menuitems 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.
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 combobox at the upper left corner of the screen) loaded from the Translations table. Figure 3 shows the form with the Spanish translations loaded.
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.
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 combobox, cmbLanguagePicker, which I load with the names of the available languages in the Load event. The code for the combobox's SelectedIndex-Changed 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.
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 the data bound to them. In virtually all of the exercises in the VS .NET HELP, you create a Typed Dataset, 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 12-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 (txtCompany-Name, txtContactName, txtContactTitle) are all that the BaseForm.vb inheritable form class code needs in order to automatically bind the three textboxes to their respective data columns in the datasource, which is ds.tables(0). You don't even need a Typed Dataset. I added Next and Previous buttons so you could 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.
MainModule, shown in Listing 6, is the Startup Object for TranslationDemo, 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:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="AccessTypeTranslator" value="MDB" />
<add key="ServerTranslator" value=""></add>
<add key="DatabaseTranslator" (
value="Northwind"></add>
<add key="UIDTranslator" value="admin"></add>
<add key="PWDTranslator" value=""></add>
<add key="FileNameTranslator" (
value="..\Translations.MDB"></add>
<add key="AccessTypeAppData" value="SQL" />
<add key="ServerAppData" value="(local)"></add>
<add key="DatabaseAppData" (
value="Northwind"></add>
<add key="UIDAppData" value="sa"></add>
<add key="PWDAppData" value=""></add>
<add key="FileNameAppData" value=""></add>
</appSettings>
</configuration>
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 cmbLanguage-Picker 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.
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.
Conclusion
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.
Listing 1: The Data Access component (DAC)
Imports System.Windows.Forms
Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.Data.OLEDB
Public Class DAC
Inherits System.ComponentModel.Component
' Generated form code omitted
#Region " Declarations and properties "
Public Const SQLError = "Error connecting to server"
Public Const MDBError = "Error opening MDB file"
Public Const DBFError = "Error with DBF directory or DBC"
Public Const FillError = "Error filling dataset"
Public Const NonQueryError = _
"Error executing nonquery statement"
Public da As Object
Public cn As Object
Public dc As Object
Private _cs As String = ""
<Category("+Pinter")> Public Property cs() As String
Get
Return _cs
End Get
Set(ByVal Value As String)
_cs = Value
End Set
End Property
' Six other property declarations omitted here for brevity:
' AccessType, FileName, Database, Server, UID and PWD
#End Region
#Region " Functions "
Public Function ReturnDS( ByVal cmd As String) As DataSet
If Not cmd = "" Then
cs = BuildConnString(AccessType)
Select Case AccessType
Case "SQL"
Dim cn1 As SqlConnection = New SqlConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, SQLError)
End Try
Dim da1 As SqlDataAdapter = _
New SqlDataAdapter(cmd, cn1)
cn = cn1
da = da1
Case "MDB"
Dim cn1 As OleDbConnection = New OleDbConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, MDBError)
End Try
Dim da1 As OleDbDataAdapter = _
New OleDbDataAdapter(cmd, cn1)
cn = cn1
da = da1
Case "DBF"
Dim cn1 As OleDbConnection = New OleDbConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, DBFError)
End Try
Dim da1 As OleDbDataAdapter = _
New OleDbDataAdapter(cmd, cn1)
cn = cn1
da = da1
End Select
Dim ds As New DataSet
ds.Clear()
Try
da.fill(ds)
Catch ex As Exception
ErrorMessage(ex, FillError)
End Try
If cn.state = ConnectionState.Open Then cn.close()
Return ds
End If
End Function
Public Function ExecCmd(ByVal cmd As String) As String
Dim Status As String = "Ok"
cs = BuildConnString(AccessType)
Select Case AccessType
Case "SQL"
Dim cn1 As SqlConnection = New SqlConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, SQLError)
End Try
Dim dc1 As New SqlCommand(cmd, cn1)
dc = dc1
cn = cn1
Case "MDB"
Dim cn1 As OleDbConnection = New OleDbConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, MDBError)
End Try
Dim dc1 As OleDbCommand = New OleDbCommand(cmd, cn1)
dc = dc1
cn = cn1
Case "DBF"
Dim cn1 As OleDbConnection = New OleDbConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, DBFError)
End Try
Dim dc1 As OleDbCommand = New OleDbCommand(cmd, cn1)
dc = dc1
cn = cn1
End Select
dc.CommandType = CommandType.Text
Try
dc.ExecuteNonQuery()
Catch ex As Exception
Status = "Error"
ErrorMessage(ex, NonQueryError)
Finally
If cn.state = ConnectionState.Open Then cn.close()
End Try
Return Status
End Function
Public Function ExecScalar(ByVal cmd As String) As Integer
Dim dc As Object
Dim RetVal As Integer = 0
cs = BuildConnString(AccessType)
Select Case AccessType
Case "SQL"
Dim cn1 As SqlConnection = New SqlConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, SQLError)
End Try
Dim dc1 As New SqlCommand(cmd, cn1)
dc1.CommandType = CommandType.Text
dc = dc1
cn = cn1
Case "MDB"
Dim cn1 As OleDbConnection = New OleDbConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, MDBError)
End Try
Dim dc1 As OleDbCommand = New OleDbCommand(cmd, cn1)
dc1.CommandType = CommandType.Text
dc = dc1
cn = cn1
Case "DBF"
Dim cn1 As OleDbConnection = New OleDbConnection(cs)
Try
cn1.Open()
Catch ex As Exception
ErrorMessage(ex, DBFError)
End Try
Dim dc1 As OleDbCommand = New OleDbCommand(cmd, cn1)
dc1.CommandType = CommandType.Text
dc = dc1
cn = cn1
End Select
Try
RetVal = dc.ExecuteScalar()
Catch ex As Exception
ErrorMessage(ex, dc.commandtext)
Finally
If cn.state = ConnectionState.Open Then cn.close()
End Try
Return RetVal
End Function
Function BuildConnString(ByVal AccessType As String)
Select Case AccessType
Case "SQL"
BuildConnString = "Server=" + Server _
+ ";Database=" + Database _
+ ";UID=" + UID _
+ ";PWD=" + PWD
Case "MDB"
BuildConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
+ "User ID=" + UID + ";" + "Data Source=" _
+ FileName + ";"
Case "DBF"
BuildConnString = "Provider=VFPOLEDB.1;Data Source=" _
+ FileName + ";Collating Sequence=MACHINE;"
Case Else
BuildConnString = ""
End Select
End Function
Sub ErrorMessage(ByVal e As Exception, _
Optional ByVal s2 As String = "")
Dim s As String = e.Message
If Not e.InnerException Is Nothing Then _
s += ControlChars.CrLf + e.InnerException.Message
MessageBox.Show(s, "Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Sub
#End Region
End Class
Listing 2: The StoreCaptions method in the FormPreparer class
Public Class StoreCaptions
Inherits System.ComponentModel.Component
' Generated form code omitted, except for the following overloaded
' constructor that I added:
Public Sub New(ByVal dac As DAC.DAC)
MyBase.New()
'This call is required by the Component Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
Dac1 = dac
End Sub
Public Captions As New Collection
Function StoreCaptions(ByVal frm As Object) As Collection
Dac1 = frm.TranslatorDAC
frm.Tag = frm.Text
InsertWord(frm.Text)
Dim t As String
For Each Ctrl As Control In frm.Controls
If IsTranslatable(Ctrl) Then
If TypeOf Ctrl Is DataGrid Then
t = CType(Ctrl, DataGrid).CaptionText
Ctrl.Tag = t
Captions.Add(Ctrl.Text, Ctrl.Name)
Else
t = Ctrl.Text
Ctrl.Tag = t
Captions.Add(Ctrl.Text, Ctrl.Name)
End If
InsertWord(t)
End If
Next
Dim MLevel As String = ""
If Not frm.Menu Is Nothing Then _
StoreMenuItems(frm.Menu.MenuItems, MLevel)
Return Captions
End Function
Friend Sub InsertWord(ByVal t As String)
Dim Cmd As String
Cmd = "SELECT COUNT(*) FROM Original where original='"+t+"'"
Dim Howmany As Integer = Dac1.ExecScalar(Cmd)
If Howmany = 0 Then
Cmd = "INSERT INTO Original (original) values ( '"+t+"')"
Dac1.ExecCmd(Cmd)
End If
End Sub
Friend Function IsTranslatable(ByVal Ctrl As Control)
If TypeOf Ctrl Is Label _
Or TypeOf Ctrl Is Button _
Or TypeOf Ctrl Is CheckBox _
Or TypeOf Ctrl Is RadioButton _
Or TypeOf Ctrl Is DataGrid Then _
Return True Else Return False
End Function
Friend Sub StoreMenuItems( _
ByVal micoll As MenuItem.MenuItemCollection, _
ByVal MLevel As String)
For I As Int16 = 0 To micoll.Count - 1
Dim mi As MenuItem
mi = micoll.Item(I)
Dim localMLevel As String = MLevel + I.ToString
Captions.Add(mi.Text, localMLevel)
InsertWord(mi.Text)
If mi.MenuItems.Count > 0 Then _
StoreMenuItems(mi.MenuItems, localMLevel)
Next
End Sub
End Class
Listing 3: The Translation Manager form
Public Class TranslationTableManager
Inherits System.Windows.Forms.Form
' Form generated code omitted
#Region " Declarations and Property Procedures "
Const TurnOn As Boolean = True
Const TurnOff As Boolean = False
Friend _Adding As Boolean
Friend Row As Integer
Friend Cmd As String
Friend Msg As String
Friend Result As String
Friend TransTable As New TranslationTable
Private _Language As String = "Spanish"
Property Adding() As Boolean
Get
Return _Adding
End Get
Set(ByVal Value As Boolean)
_Adding = Value
lblAdding.Visible = Value
End Set
End Property
#End Region
#Region " Form Load event code "
Private Sub Form1_Load( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
StoreCaptions1.StoreCaptions(Me)
LoadLanguages(cmbLanguage)
LoadLanguages(cmbLanguagePicker)
LoadColumn("Original")
LoadColumn("Translation")
End Sub
#End Region
#Region " Miscellaneous event handlers "
Private Sub Form1_Resize( _
ByVal sender As Object, ByVal e As System.EventArgs) _
Handles MyBase.Resize
TextBox2.Size = TextBox1.Size
Dim p As Point = TextBox1.Location
p.X = TextBox1.Location.X + TextBox1.Width + 3
TextBox2.Location = p
End Sub
Private Sub cmdDeleteLanguage_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmdDeleteLanguage.Click
If cmbLanguage.Text.TrimEnd = "Original" Then
MessageBox.Show("You can't delete this entry")
Return
End If
If MessageBox.Show( _
"Delete all entries for this language?", _
"Not undoable", _
MessageBoxButtons.OKCancel, _
MessageBoxIcon.Question, _
MessageBoxDefaultButton.Button2) = DialogResult.OK Then
Cmd = "DELETE from translated" _
+ " WHERE original='" + TextBox1.Text.TrimEnd + "'" _
+ " AND lang='" + cmbLanguage.Text.TrimEnd + "'"
Result = TranslatorDAC.ExecCmd(Cmd)
Cmd = "DELETE from languages" _
+ " WHERE lang='" + cmbLanguage.Text.TrimEnd + "'"
Result = TranslatorDAC.ExecCmd(Cmd)
End If
LoadLanguages(cmbLanguage)
LoadColumn("Original")
LoadColumn("Translation")
End Sub
Private Sub cmbLanguage_SelectedIndexChanged( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmbLanguage.SelectedIndexChanged
LoadColumn(cmbLanguage.Text)
End Sub
Private Sub cmdNew_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmdNew.Click
Adding = True
TextBox1.Text = "Original"
TextBox2.Text = IIf(cmbLanguage.Text = "Original", _
"", "Translation")
Buttons(TurnOn)
cmdNew.Visible = False
End Sub
Private Sub cmdDelete_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmdDelete.Click
Adding = False
If cmdDelete.Text = "&Cancel" Then
Buttons(TurnOff)
Else
Select Case cmbLanguage.Text.TrimEnd
Case "Original"
Dim origval As String = _
DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)
origval = origval.TrimEnd
Msg = "Delete " + origval + " for all languages?"
If MessageBox.Show(Msg, "Permanent", _
MessageBoxButtons.OKCancel, _
MessageBoxIcon.Question, _
MessageBoxDefaultButton.Button2) _
= DialogResult.OK Then
Cmd = "DELETE from translated" _
+ " WHERE original='" + origval + "'"
Result = TranslatorDAC.ExecCmd(Cmd)
Cmd = "DELETE from original" _
+ " WHERE original='" + origval + "'"
Result = TranslatorDAC.ExecCmd(Cmd)
LoadColumn("original")
LoadColumn("translated")
End If
Case Else
Dim TransVal As String = _
DataGrid1.Item(DataGrid1.CurrentRowIndex, 1)
If TransVal.TrimEnd.Length = 0 Then
MessageBox.Show("Nothing to delete")
Return
End If
Dim origval As String = _
DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)
origval = origval.TrimEnd
Msg = "Delete " + origval + "?"
If MessageBox.Show(Msg, "Permanent", _
MessageBoxButtons.OKCancel, _
MessageBoxIcon.Question, _
MessageBoxDefaultButton.Button2) = _
DialogResult.OK Then
Cmd = "DELETE from translated" _
+ " WHERE original='" + origval + "'" _
+ " AND lang='" + cmbLanguage.Text.TrimEnd + "'"
Result = TranslatorDAC.ExecCmd(Cmd)
LoadColumn("original")
LoadColumn("translated")
End If
End Select
End If
End Sub
Private Sub cmdEdit_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmdEdit.Click
If cmdEdit.Text = "&Save" Then
SaveCurrent()
Buttons(TurnOff)
Else
With DataGrid1
TextBox1.Text = .Item(.CurrentRowIndex, 0)
TextBox2.Text = .Item(.CurrentRowIndex, 1)
End With
Buttons(TurnOn)
TextBox1.Focus()
End If
Adding = False
End Sub
Private Sub txtNewLanguage_Leave( _
ByVal sender As Object, ByVal e As System.EventArgs) _
Handles txtNewLanguage.Leave
txtNewLanguage.Visible = False
cmdDeleteLanguage.Visible = True
If txtNewLanguage.Text <> "" Then
Cmd = "SELECT COUNT(*) FROM languages WHERE lang = '" _
+ txtNewLanguage.Text.TrimEnd + "'"
Dim HowMany As Integer = TranslatorDAC.ExecScalar(Cmd)
If HowMany > 0 Then
MessageBox.Show( _
"Already exists", _
"Language already in system", _
MessageBoxButtons.OK, _
MessageBoxIcon.Information)
Return
End If
Cmd = "INSERT INTO Languages VALUES ( '" _
+ txtNewLanguage.Text.TrimEnd + "')"
TranslatorDAC.ExecCmd(Cmd)
Me.LoadLanguages(cmbLanguage)
End If
End Sub
Private Sub cmdAddLanguage_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmdAddLanguage.Click
cmdDeleteLanguage.Visible = False
txtNewLanguage.Text = ""
txtNewLanguage.Visible = True
txtNewLanguage.Focus()
End Sub
Private Sub cmbLanguagePicker_SelectedIndexChanged( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmbLanguagePicker.SelectedIndexChanged
Cmd = "select original, translated from translated" _
+ " where lang = '" + cmbLanguagePicker.Text + "'"
Dim Translations As New DataSet
Translations = TranslatorDAC.ReturnDS(Cmd)
Dim dv As DataView = Translations.Tables(0).DefaultView
dv.Sort = "original"
Dim r As Integer = 0
For Each Ctrl As Control In Controls
If TypeOf Ctrl Is Label _
Or TypeOf Ctrl Is Button _
Or TypeOf Ctrl Is CheckBox _
Or TypeOf Ctrl Is RadioButton _
Or TypeOf Ctrl Is DataGrid _
Then
r = dv.Find(Ctrl.Tag)
If TypeOf Ctrl Is DataGrid Then
If r >= 0 Then
CType(Ctrl, DataGrid).CaptionText = dv(r).Item(1)
Else
CType(Ctrl, DataGrid).CaptionText = Ctrl.Tag
End If
Else
If r >= 0 Then
Ctrl.Text = dv(r).Item(1)
Else
Ctrl.Text = Ctrl.Tag
End If
End If
End If
Next
End Sub
#End Region
#Region " Auxiliary routines "
Sub LoadLanguages(ByVal cmb As ComboBox)
Dim dsLanguages As New DataSet
dsLanguages = TranslatorDAC.ReturnDS( _
"SELECT * FROM languages")
cmb.DisplayMember = "lang"
cmb.ValueMember = "lang"
cmb.DataSource = dsLanguages.Tables("Table")
End Sub
Public Sub LoadColumn( _
Optional ByVal Language As String = "Original")
SuspendLayout()
If Language.ToLower = "original" Then
Dim dsColumn As New DataSet
dsColumn = TranslatorDAC.ReturnDS( _
"SELECT original FROM original")
If dsColumn.Tables(0).Rows.Count = 0 Then
MessageBox.Show("No data found")
Return
End If
TransTable.Clear()
For Each dr As DataRow In dscolumn.Tables(0).rows
Dim newrow As DataRow = TransTable.Tables(0).NewRow
newrow(0) = dr(0)
newrow(1) = ""
TransTable.Tables(0).Rows.Add(newrow)
Next
DataGrid1.DataSource = TransTable.Tables(0)
Else
Dim dsColumn As New DataSet
dsColumn = TranslatorDAC.ReturnDS( _
"SELECT original, translated FROM translated" _
+ " WHERE lang='" & cmbLanguage.Text & "'")
Dim dv As DataView
dv = TransTable.Tables(0).DefaultView
dv.Sort = "original"
' Clear the second column
For Each dr As DataRow In TransTable.Tables(0).Rows
dr.Item(1) = ""
Next
For Each dr As DataRow In dsColumn.Tables(0).Rows
Dim rownum As Integer = dv.Find(dr(0))
If rownum >= 0 Then _
dv(rownum).Item(1) = _
IIf(rownum >= 0, dr(1), "Not found")
Next
End If
DataGrid1.Refresh()
ResumeLayout()
End Sub
Sub SaveCurrent()
' Remove the translated record if it already exists
If Not Adding Then
Cmd = "DELETE from translated" _
+ " WHERE original='" + TextBox1.Text.TrimEnd + "'" _
+ " AND lang='" + cmbLanguage.Text.TrimEnd + "'"
Result = TranslatorDAC.ExecCmd(Cmd)
End If
' Insert the translated entry if Original isn't selected
If cmbLanguage.Text <> "Original" Then
Cmd = "INSERT INTO translated (" _
+ " original, translated, lang ) VALUES ( " _
+ "'" + TextBox1.Text.TrimEnd + " '," _
+ "'" + TextBox2.Text.TrimEnd + " '," _
+ "'" + cmbLanguage.Text + " ')"
Result = TranslatorDAC.ExecCmd(Cmd)
End If
' If the original entry doesn't already exist, add it now
Cmd = "SELECT * FROM original WHERE original = '" _
+ TextBox1.Text.TrimEnd + "'"
Dim HowMany As Integer = TranslatorDAC.ExecScalar(Cmd)
If HowMany = 0 Then
Cmd = "INSERT INTO original ( original ) VALUES ( " _
+ "'" + TextBox1.Text.TrimEnd + " ')"
Result = TranslatorDAC.ExecCmd(Cmd)
End If
LoadColumn("Original")
LoadColumn("Translation")
End Sub
Sub Buttons(ByVal OnOff As Boolean)
If cmdEdit.Text = "&Save" Then
cmdNew.Visible = True
cmdEdit.Text = "&Edit"
cmdDelete.Text = "&Delete"
cmdNew.Visible = True
TextBox1.Visible = False
TextBox2.Visible = False
DataGrid1.Enabled = True
Else
cmdEdit.Text = "&Save"
cmdDelete.Text = "&Cancel"
cmdNew.Visible = False
TextBox1.Visible = True
TextBox2.Visible = _
IIf(cmbLanguage.Text = "Original", False, True)
DataGrid1.Enabled = False
End If
End Sub
#End Region
End Class
Listing 4: The BaseForm inheritable form template class
Public Class BaseForm
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
InitializeComponent()
End Sub
'------------------------------
' Overloaded constructor added:
'------------------------------
Public Sub New( _
ByVal transdac As DAC.DAC, ByVal appdac As DAC.DAC)
MyBase.New()
InitializeComponent()
TranslatorDAC = transdac
AppDataDAC = appdac
End Sub
' Rest of generated form code omitted...
#End Region
Dim CaptionCollection As New Collection
Dim OriginalText As String
Dim MenuLevel As String = ""
Public dv As DataView
Private Sub BaseForm_Load( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
If Not DesignMode Then
Dim ds As New DataSet
ds = TranslatorDAC.ReturnDS( _
"Select lang from languages")
cmbLanguagePicker.Items.Clear()
For Each dr As DataRow In ds.Tables(0).Rows
cmbLanguagePicker.Items.Add(dr("lang"))
Next
ds = Nothing
CaptionCollection = StoreCaptions1.StoreCaptions(Me)
End If
End Sub
#Region " Events "
Sub cmbLanguagePicker_SelectedIndexChanged( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles cmbLanguagePicker.SelectedIndexChanged
If Not designmode Then
Dim cmd As String
cmd = "select original, translated from translated" _
+ " where lang = '" + cmbLanguagePicker.Text + "'"
Dim Translations As New DataSet
Translations = TranslatorDAC.ReturnDS(cmd)
dv = Translations.Tables(0).DefaultView
dv.Sort = "Original"
Dim r As Integer = 0
r = dv.Find(Tag.ToString.TrimEnd)
If r >= 0 Then Text = dv(r).Item("translated") _
Else Text = Tag
For Each Ctrl As Control In Controls
If IsTranslatable(Ctrl) Then
OriginalText = CaptionCollection.Item(Ctrl.Name)
r = dv.Find(OriginalText)
If TypeOf Ctrl Is DataGrid Then
If r >= 0 Then _
CType(Ctrl, DataGrid).CaptionText = dv(r).Item(1) _
Else CType(Ctrl, DataGrid).CaptionText = Ctrl.Tag
Else
If r >= 0 Then Ctrl.Text = _
dv(r).Item("translated") _
Else Ctrl.Text = Ctrl.Tag
End If
End If
Next
If Not Menu Is Nothing Then
ProcessMenuItems(Menu.MenuItems, MenuLevel)
End If
End If
End Sub
Function IsTranslatable(ByVal ctrl As Control) As Boolean
If TypeOf ctrl Is Label _
Or TypeOf ctrl Is Button _
Or TypeOf ctrl Is CheckBox _
Or TypeOf ctrl Is RadioButton _
Or TypeOf ctrl Is MenuItem _
Or TypeOf ctrl Is DataGrid Then
Return True
Else
Return False
End If
End Function
Function ProcessMenuItems( _
ByVal MenuItems As Menu.MenuItemCollection, _
ByVal MLevel As String)
Dim i As Int16
Dim j As Integer
For i = 0 To MenuItems.Count - 1
Dim mi As MenuItem = MenuItems(i)
Dim localMLevel As String = MLevel + i.ToString
OriginalText = CaptionCollection.Item(localMLevel)
Dim r As Integer = dv.Find(OriginalText)
If r >= 0 Then mi.Text = dv(r).Item("translated") _
Else mi.Text = OriginalText
If mi.MenuItems.Count > 0 Then _
ProcessMenuItems(mi.MenuItems, localMLevel)
Next
End Function
#End Region
End Class
Listing 5: The Customers form
Public Class Customers
Inherits BaseForm.BaseForm
' Generated form code omitted...
Public ds As DataSet
Private Sub Customers_Load( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
ds = New DataSet
ds = me.AppDataDAC.ReturnDS("SELECT * FROM CUSTOMERS")
AutoBinding()
End Sub
Private Sub btnNext_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnNext.Click
BindingContext(ds.Tables(0)).Position += 1
End Sub
Private Sub btnPrevious_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnPrevious.Click
BindingContext(ds.Tables(0)).Position += 1
End Sub
Sub AutoBinding()
For Each Ctrl As Control In Controls
If TypeOf Ctrl Is TextBox Then
Ctrl.DataBindings.Clear()
Ctrl.DataBindings.Add("Text", ds.Tables(0), _
Ctrl.Name.Substring(3))
End If
Next
End Sub
End Class
Listing 6: MainModule.vb
Imports DAC.DAC
Module MainModule
Dim TranslatorDAC As New DAC.DAC
Dim AppDataDAC As New DAC.DAC
Sub Main()
InitializeDAC()
Application.Run(New MainForm(TranslatorDAC, AppDataDAC))
End Sub
Sub InitializeDAC()
' Read data access component settings from App.Config file.
Dim AccessType As String = _
System.Configuration.ConfigurationSettings.AppSettings( _
"AccessTypeTranslator") ' "SQL", "MDB", "DBF"
Dim Server As String = _
System.Configuration.ConfigurationSettings.AppSettings( _
"ServerTranslator") ' SQL only
Dim Database As String = _
System.Configuration.ConfigurationSettings.AppSettings( _
"DatabaseTranslator") ' SQL only
Dim UID As String = _
System.Configuration.ConfigurationSettings.AppSettings( _
"UIDTranslator") ' SQL, MDB
Dim PWD As String = _
System.Configuration.ConfigurationSettings.AppSettings( _
"PWDTranslator") ' SQL, MDB
Dim FileName As String = _
System.Configuration.ConfigurationSettings.AppSettings( _
"FileNameTranslator") ' DBF, MDB
With TranslatorDAC
.AccessType = AccessType
.Server = Server
.Database = Database
.UID = UID
.PWD = PWD
.FileName = FileName
End With
AccessType = _
System.Configuration.ConfigurationSettings.AppSettings( _
"AccessTypeAppData") ' "SQL", "MDB", "DBF"
Server = _
System.Configuration.ConfigurationSettings.AppSettings( _
"ServerAppData") ' SQL only
Database = _
System.Configuration.ConfigurationSettings.AppSettings( _
"DatabaseAppData") ' SQL only
UID = _
System.Configuration.ConfigurationSettings.AppSettings( _
"UIDAppData") ' SQL, MDB
PWD = _
System.Configuration.ConfigurationSettings.AppSettings( _
"PWDAppData") ' SQL, MDB
FileName = _
System.Configuration.ConfigurationSettings.AppSettings( _
"FileNameAppData") ' DBF, MDB
' Apply them to the Data Access Component
With AppDataDAC
.AccessType = AccessType
.Server = Server
.Database = Database
.UID = UID
.PWD = PWD
.FileName = FileName
End With
End Sub
End Module
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) |
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) |
