Deep talk on Cultures and languages with ASP.NET and SQL

<font color="#F90"> Marla </font> <b>Sukesh</b>

4.80/5 (35 votes)

May 7, 2013

CPOL

13 min read

113183

2452

In this article we will look in detail how to create a multi-lingual and multi-cultural applications using Asp.net.

Introduction

Asp.net is a very interesting technology and its capability to support multiple languages makes us (developers) stand in the international world. Does it mean every asp.net application support multiple languages? Answer is no. We have to make it and for that we have to understand couple of terminologies and guidelines. In this article we will look in detail how to create a multi-lingual and multi-cultural applications using Asp.net.

Who is Audience?

If you have great experience in developing multi lingual Asp.Net application then you may find this article quite boring because lots of things you are about to read will be already known to you. But if you are developer who wants to learn to create web sites using Asp.Net targeting multiple cultures and languages or want to revise whatever you know, you are right place.

Why multi-lingual or multi-cultural?

In current IT world there is a huge competition when it comes to software sale. Customer need more than a system to run their business. While walking on the road when we come across a banner written in our native culture our facial expression changes, we feel happy. Everyone likes to see their native culture, it’s a normal human nature. Same rule even applies to even software applications. People are demanding applications in their native languages and culture.

Agenda

Globalization vs. Localization
How to make the application Globalized application.
How to make the application localized application.
Culture vs. UI Culture
How to explicitly set culture of a page or application.
How to manage user input data in globalized applications.

What is Sql Server collation?
How to deal with collation.
Collation and TempDb.
What is the difference between varchar and nvarchar

How to localize JavaScript messages in Asp.Net?

Globalization vs. Localization

As I have said earlier, everyone wants to see the application in their native language. To achieve this goal one thing we can do is, create multiple versions of same page (one page representing one language or culture) and as per user request show him/her the respective page. This is not the feasible solution as it violates the Object oriented principle DRY (Don’t repeat yourself). That means we have to create an application in such a way that it should be able to adapt to multiple cultures and languages. This process of designing and developing such application is called Globalization.

Now once such application is developed, some inputs will be added to every UI. For instance input means, telling system meaning of every word in every other language (system need to be displayed) so that it can be brought to local level. This process where globalized application is customized for a given culture is called Localization

How to make the application Globalized application.

In order to make the application a globalized application first step would be to identify those sections or parts of the application which do not allow the application to be a globalized application. And for that purpose first we need an application, so let’s create it.

Output

It’s a simple Login UI with 2 textboxes, labels and a button.
At the top section current date is shown in dd/mm/yyyy format.
Just below it, we have an informative text which says, minimum 900 Rs is required in our pocket for successful login.
One last thing which should be noticed about the UI is, everything is in English language.

In order to identify globalization issues we have to take a look at Markup and Code behind.

Problems with the above code are

UI (aspx) contains too many static contents (like messages “in your pocket”, “User Name”, “Password” etc.). In order to make the UI a globalized UI, we should replace these static contents with some placeholders so that contents can be changed or replaced at run time based on specific culture.
For displaying current date we are manually concatenating the individual parts of the date and displaying in dd/mm/yyyy format which is correct for Indian cultures, but what about other cultures? We have to use a generic syntax for representing date.
For displaying amount we are using “ToString” method of our “amount” variable and appending with “Rs” string. Again this is perfect, only when it comes to Indian cultures. For displaying amount we should use some kind of generic way which will automatically format number (amount variable) for decimals, separators, prefixes and suffixes.

Solution

For making static contents dynamic, we can use “Label” in Asp.net and change its Text property at runtime based on culture at runtime.

Note: It’s not like label is the only solution, you can use anything whose content can be changed at runtime. Example: Literal, Textbox etc.

For displaying current date we will use “ToShortDateString” method of DateTime object.
And for amount instead of ordinary “ToString” method we will use overloaded version of it, passing “c” as an argument. (“C” indicates currency).

Final Code

Output

Now this output is different from the previous one

Now current date is displayed in mm/dd/yyyy format.
Formatting of amount is US formatting.

What is the reason for such kind of difference

Earlier whatever was getting displayed it was basically using fixed formats and styles, but now everything is culture and language based and when I say culture and language it means culture and language of a browser.

Let’s change the browser culture to one of the Spanish culture and check the output.

Note: we are not covering how to change browser culture. You may find lots of internet links for the same. For reference find the following link http://www.wikihow.com/Change-Your-Browser's-Language.

As you can see globalized application can adapt easily to multiple cultures.

What is the problem?

Globalized application never translates anything, it only affects the formatting. You can see in the output that all texts are still in English. In the next step we will manually tell the system what every word means in Spanish and that is called Localization.

How to make the application Localized application.

In order to make the application localized first we should make it globalized.

Now in order to achieve localization Microsoft .Net has something called as resource files.

There are 2 kinds of resource files 1) Local Resource files 2) Global Resource files.

We will talk about both of them in detail with Example.

1. Local resource files

Local resource files will be linked to only one aspx or ascx file. Using it, we will tell the system what every word or sentence will be called in a particular language and culture.

Step 1

For creating resource file go to the design mode of your aspx file and then click on tools->Generate Local Resource Files.

Step 2

As soon as you do that you will notice 2 changes

A new folder called App_LocalResources get created in the solution explorer with a file called Login.aspx.resx.
Every server control in your UI is now attached with a property called “meta:resourcekey”.

In Resource files you will notice couple of key value pairs, where every key represents particular property of a particular control and value represents the content.

Step 3

Copy the resource file and create a new copy of it and rename the new copy to “Login.aspx.es-CR.resx”. Here es-CR is called Culture indentifier. Culture identifiers are made by 2 tokens – the culture name and the language name.For example here ‘es’ represents Spanish language and ‘CR’ represents Costa Rica culture.

Step 4

Open the newly created resource file. This file is just a copy of your previous resource file that is “Login.aspx.resx”, so you will find all the keys in this new file which were there in previous file.

Use Google translator or any other tool and put Spanish representation of every word/sentence in corresponding value field.

Step 5

Execute application for both the cultures and check the output.

Note: If you notice width of the button get increased. Now that is what we are supposed to consider in globalization phase. While designing the UI we should consider the fact that in future we are going to make the UI available for other languages also and that’s why we should either put the width of control(which are going to change its content in future) to ‘auto’ or to max value.

2. Global Resource Files

Global resource files are not linked to any particular aspx or ascx. Let’s say we want to reuse some messages across multiple pages and also these messages shoul be language based. In this situation global resource files comes handy.

Step 1

Right click your asp.net project Add->Add Asp.Net Folder->App_GlobalResources.

Step 2

Right click the folder. Click on add Item. Select Resource file. Name it DisplayMessages.resx.

Step 3

Open the file, it will look like Local resource files but with no entries.

Add an entry into it.

Step 4

Create a duplicate copy of the file and name it DisplayMessages.es-CR.resx.

Step 5

Open the newly created fie. And change value to its Spanish representation.

Note: For demo purpose I just used the demo Text.

Step 6

Create a label in Login page as follows

<asp:Label runat="server" Text="<%$ Resources:DisplayMessages,HeaderMessage %>" />

Step 7

Execute application for both the cultures and check the output.

So now we know what is meant by globalization and what is meant by localization. It’s time to move further and get a deep dive into some terminologies.

Culture vs. UI Culture

Let say you have created a globalized application considering all the facts we discussed above. And later with the help of resource files you try to make it localized. But the question is will your site work as desired? No, you have to set 2 more properties of your page that is Culture and UICulture to value “auto”.

In the page level

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs"
    Inherits="MultilingualSample.Login" Culture="auto"
    UICulture="auto" meta:resourcekey="PageResource1" %>

Or in the application level – at web.config file

<configuration>
    <system.web>
………
      <globalization culture="auto" uiCulture="auto"/>
    </system.web>
</configuration>

What is the difference between them?

UICulture value determines what resource file ASP.NET needs to load. It is for the contents in the page.

For example: If UICulture value is set to es-CR then values will be taken from Login.aspx.es-CR.aspx and displayed.

Culture value determines the results of culture-dependent functions, such as the date, number, and currency formatting, and so on.

For example: when Culture value is set to es-CR DateTime and currency will displayed using Costa Rica formats.

Why 2 different properties required?

It gives us higher level of flexibility. For example, we can switch the language of text and messages according to the browser's configuration while leaving globalization settings (such as dates and currency) constant.

Why the values should be set to auto?

When value is set to auto, values will be considered based on whatever is set for browser. For instance if browser culture is es-CR and UICulture value is set to auto, Spanish resource file will be taken and displayed.

What happen if we won’t set values for these properties?

If you forgot to set the value for culture property your datetime, currencies will start displaying using the default culture of your server. Similarly when UICulture value is not set, values from the default resource file will be considered always.

How to explicitly set culture of a page or application.

We can do it in 3 ways

1. Using @Page directive

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs"
    Inherits="MultilingualSample.Login" Culture="es-CR"
    UICulture="en-US" %>

2. Using web.config

<configuration>
    <system.web>
………
      <globalization culture="es-CR" uiCulture="en-US"/>
    </system.web>
</configuration>

3. Programmatically

protected override void InitializeCulture()
{
    Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
    Thread.CurrentThread.CurrentUICulture = new CultureInfo("es-CR");
    base.InitializeCulture();
}

How to manage user input data in globalized applications

When we say web application it don’t just display information. It also take information from user and ultimately it stores these information in database.

In order to make a complete proper multi lingual application a proper database design is must. And in order to create proper database design we should know following things.

What is Sql Server collation?

Sql server collation is concerned with how character data is interpreted by sql server.

In short collation specifies three properties.

Sort order for non-Unicode data types like char, varchar.
Sort order for Unicode data types like varchar and nchar.
The Code Page used to store non-Unicode character data.

(We will not talk in detail about code page in this article, but in short it defines a set of values for a language each representing a particular character. For detail visit this link http://en.wikipedia.org/wiki/Code_page)

Now sort order also depend on

Case sensitivity – is ‘A’ and ‘a’ same or not?
Accent sensitivity – is ‘a’ and ‘á’ same or not?
Kana sensitivity – is Hiragana character and Katakana character treated as same or not? Belong to Japanese language).
Width sensitivity – is “Sql” as varchar and “Sql” as nvarchar same? In short is single byte character is equal to its double byte representation?

How to deal with collation?

Collation can be applied

At sql server instance level (at the time of installation).

Note: Collation applied at server level can be changed afterwards. For detail visit this link.

http://msdn.microsoft.com/en-IN/library/ms179254(v=sql.105).aspx

At database level (at the time of database creation).

CREATE DATABASE newDatabase
COLLATE SQL_Latin1_General_CP1_CS_AS

At column level

CREATE TABLE [T1]
(
[NormalColumn] [varchar](50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[AdvancedColumn] [nvarchar](50)COLLATE SQL_Latin1_General_CP1_Ci_AS NOT NULL 
)

At query level

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'
and Column2 COLLATE Latin1_General_CI_AS = 'CaseInsensitiveSearch'

Collation and TempDb

TempDb is the place where all our temporary tables and procedure will be stored. This database gets created every time sql server is started. Now the question is what will be the collation of the database? In simple word answer for this question is, same as Model database. And it means database on which we are currently working (for example customer database) and TempDb database have different collations which may lead to unexpected outputs.

In order to avoid such problem one solution will be explicitly set the collation for every column while creating temporary tables.

CREATE TABLE #T1(
	[NormalColumn] [varchar](50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
) ON [PRIMARY]

What is the difference between varchar and nvarchar?

Now in sql server in order to store user input (especially texts) we have choices like text, ntext, char, nchar, varchar, nvarchar etc.

Almost every data type has it’s ’n’ representation. Now the difference between them?

Well one without ‘n’ stores non Unicode characters and one with ‘n’ stores Unicode characters as well.

And so data types such as nvarchar, nchar are capable of storing data of multiple languages.

How to localize JavaScript messages in Asp.Net

1. Using hidden fields and labels

In order to localize JavaScript message one thing we can do is, we will put either label or hidden field in the page.
Assign values to those controls at runtime based on cultures from resource files.
In JavaScript whenever message need to be displayed, we will just access the control and get its value.

Example: alert ($(‘.ConfirmationLabel’).text());

2. Using JSON

We will create a JavaScript file which will contain JsonObject something like this,

var MyAppLocalizedMessages =
{
    DoYouWantToDeleteMessage: {
        'en/US': 'Are you sure?',
        'fr/FR': 'Are you sure in Spanish?'
    }
}

Create a hiddenfield in the page and in the page load or page init assign current culture code to it. Example: HiddenCulture.Value = Session["CurrentCultureCode"].ToString();

Every time you want to display the message use the following syntax var message=MyAppLocalizedMessages.

DoYouWantToDeleteMessage[$(‘#HiddenCulture’).val()];

3. Using a jQuery plugin

Read here

4. Using script Manager

Read here

Conclusion

In this article we have learned how to create a multi lingual website using asp.net. Thank you for your patience. Keep reading, keep learning and keep sharing.

For technical training related to various topics including ASP.NET, Design Patterns, WCF and MVC contact SukeshMarla@Gmail.com or at www.sukesh-marla.com

For more stuff like this click here. Subscribe to article updates or follow at twitter @SukeshMarla

For 500+ videos on MSBI, .NET, SharePoint, Architecture, SQL, WPF, WCF, MVC, ASP.NET etc click @ www.questpond.com