Click here to Skip to main content
15,867,895 members
Articles / Web Development / ASP.NET

Globalization Resources for Multilanguage Development

Rate me:
Please Sign up or sign in to vote.
4.70/5 (10 votes)
8 Jun 2008CPOL7 min read 52K   653   65   7
Provides required tables and data for multilanguage projects

Introduction

This article contains MUST HAVE tables and data for proper multi language development. I highly recommend to use ISO (International Organizations for Standardization) as the primary source of information, because this is the only way to go for high quality products and compatibility. Please remember that ISO usually only manages standards. Behind each ISO standard, there are companies which supply databases and updates to commercial users. Internet is also a good source of information so I've collected globalization data from various resources and placed into 6 tables. If you have some suggestions, then please contact me by email: Evaldas Jocys <evaldas@jocys.com>.

Data for these tables are required to produce full multilingual support:

  1. Languages - Information about all languages
  2. Cultures - Information about all cultures (Language + Country)
  3. Encodings - Information about all encodings
  4. Ranges - Can help to get customer's culture by IP
  5. Currencies - Business information about each country
  6. Currencies by Country- Currencies used in country

Requirements

  1. Microsoft SQL Server 2005
  2. Microsoft .NET Framework 3.5

Installation

  1. Download ZIP file specified above.
  2. Extract ZIP contents into temp folder.
  3. Navigate to WebApp\Projects\Globalization\SQL\ folder.
  4. Open Globalization-SqlDataImport.bat file.
  5. Go to line 5 and update srv value with your SQL Server name.
  6. Go to line 6 and update cat value with your Database name.
  7. Save and close Globalization-SqlDataImport.bat file.
  8. Execute Globalization-SqlDataImport.bat to recreate [Globalization_*] tables and data.
    Note: Import script will ask for database login and password. Hit [Enter] key if you want to use trusted connection.

Using the Code

Archive contains .zip\Engine folder with LINQ to SQL classes and some methods. You can extract content of this folder into your C# project. For example, Engine\Globalization\Ranges.cs file contains two functions...

C#
/// <summary>
/// Convert IP Address string to long number.
/// </summary> 
/// <param name="ipString">IP Address string: 64.233.167.99</param> 
/// <returns>Numeric representation of IP Address</returns>
public long IpToInt(string ipString)
{
    System.Net.IPAddress address = System.Net.IPAddress.Parse(ipString);
    return (long)BitConverter.ToUInt32(address.GetAddressBytes().Reverse().ToArray(), 0);
}

/// <summary>
/// Get Alpha 3 Code of country by IP Address.
/// </summary>
/// <param name="ipString">IP Address string: 64.233.167.99</param>
/// <returns>ISO 3166-1 Alpha-3 Code. For example: LTU</returns>
public string GetCountryByIp(string ipString)
{
    Data.GlobalizationDataContext db = new Data.GlobalizationDataContext();
    long ip = IpToInt(ipString);
    Data.Range range = db.Ranges.SingleOrDefault
            (item => item.RangeStart <= ip && ip <= item.RangeEnd);
    return (range == null) ? string.Empty : range.CountryA3;
}    

So you can get country code of the visitor and redirect him/her to a specific web page:

C#
// Get visitor's IP Address.
string ipString = Request.ServerVariables["REMOTE_ADDR"];
// Get ISO 3166-1 Alpha-3 Code of Country by IP Address.
string countryA3 = 
    Engine.AppContext.Current.Globalization.Ranges.GetCountryByIp(ipString);
// Define default language of user: 
// Lithuanian if user came from Lithuania or English for all other countries.
string language = (countryA3 == "LTU") ? "ltu" : "enu";
// Redirect user to specific page.
System.Web.HttpContext.Current.Response.Redirect("Default_" + language + ".htm", true);

1. Countries

Id Name Data Type Nulls Default Description
1 CountryA3 char(3) NOT NULL ISO 3166-1 Alpha-3 Code. For example: LTU
2 CountryA2 char(2) NOT NULL ISO 3166-1 Alpha-2 Code. For example: GB
3 ShortName nvarchar(256) NOT NULL Short Country Name (Pascal Case). For example: United Kingdom
4 FullName nvarchar(512) NOT NULL Full Name. For example: Republic of South Africa
5 NativeShortName nvarchar(256) Country Short Native Name. For example: Lietuva
6 NativeFullName nvarchar(512) Country Full Native Name. For example: Lietuvos Respublika
7 IsoCode int(4) NOT NULL ISO Code of country
8 IsoName nvarchar(256) NOT NULL '' ISO 3166-1 Short Name. For example: SOUTH AFRICA
9 DialingCode int(4) International Dial Code. For example: 44
10 Remarks nvarchar(2000) NOT NULL '' ISO 3166-1 Remark. For example: Includes Marion Island, Prince Edward Island
11 IsEnabled bit(1) NOT NULL (1) Record activity status
12 DateCreated datetime(8) NOT NULL getdate() Record creation date
13 DateUpdated datetime(8) NOT NULL getdate() Record update date

2. Languages

Id Name Data Type Nulls Default Description
1 LanguageA3 char(3) NOT NULL '' ISO 639-3 Alpha-3 Code. For example: RUS
2 LanguageA2 char(2) NOT NULL '' SO 639-1 Alpha-2 Code. For example: RU
3 LanguageName nvarchar(256) NOT NULL '' Language Name (Pascal Case). For example: Russian
4 NativeName nvarchar(256) NOT NULL '' Native Language Name. For example: Русский
5 Description nvarchar(2000) NOT NULL '' Description and remarks
6 IsPrimary bit(1) NOT NULL Primary language
7 IsWellKnown bit(1) NOT NULL You can use only well known languages: For Example: 1
8 IsEnabled bit(1) NOT NULL (1) Record activity status
9 DateCreated datetime(8) NOT NULL getdate() Record creation date
10 DateUpdated datetime(8) NOT NULL getdate() Record modification date

3. Cultures

Id Name Data Type Nulls Default Description
1 CultureCode varchar(20) NOT NULL Abbreviated culture name. Example: mk-MK
2 ParentCultureCode varchar(20) Parent abbreviated culture name. Example: mk-MK
3 CultureName nvarchar(256) Culture Name
4 NativeName nvarchar(256) Native culture name.
5 AbbreviatedIsoName char(3) ISO/CD 639-5 Alpha-3 code: Example: mkd
6 AbbreviatedWindowsName char(3) Abbreviated windows name. Example: MKI
7 CodePageAnsi int(4) ANSI code page. Example: 1251
8 CodePageEbcdic int(4) EBCDI code page. Example: 500
9 CodePageOem int(4) OEM code page. Example: 866
10 CodePageMac int(4) MAC code page. Example: 10007
11 IsNeutral bit(1) Neutral culture indicator
12 DatePattern nvarchar(40) Date pattern. Example: dd.MM.yyyy
13 TimePattern nvarchar(40) Time patterns. Example: HH:mm
14 CurrencyPatterns nvarchar(100) Currency pattern. Example: n $;-n $
15 CurrencySymbol nvarchar(20) Currency symbol. Example: ден.
16 FirstDayOfWeek nvarchar(40) First day of the week. Example: Monday
17 DayNames nvarchar(512) Day names. Example: недела;понеделник;вторник;...
18 MonthNames nvarchar(512) Month names. Example: јануари;февруари;март;април;...
19 AbbreviatedDayNames nvarchar(256) Abbreviated day names. Example: нед;пон;втр;срд;чет;пет;саб
20 AbbreviatedMonthNames nvarchar(512) Abbreviated month names. Examples: јан;фев;мар;апр;мај;јун;јул;...
21 IsEnabled bit(1) NOT NULL (1) Record activity status
22 DateCreated datetime(8) NOT NULL getdate() Record creation date
23 DateUpdated datetime(8) NOT NULL getdate() Record update date

Cultures also called "Language Identifiers" (RFC 3066). Culture represents information about people who speak one language in one region. Culture code represents combination of language and country/region.

4. Encodings

Id Name Data Type Nulls Default Description
1 CodePage int(4) NOT NULL Code page. Example: 21866
2 EncodingName nvarchar(256) NOT NULL Encoding name. Example: Cyrillic (KOI8-U)
3 WindowsCodePage int(4) Windows code page. Example: 1251
4 IsBrowserDisplay bit(1) Can be displayed by web browser
5 IsMessageDisplay bit(1) Can be displayer by mail and news applications
6 HeaderName nvarchar(100) Header name. Example: koi8-u
7 BodyName nvarchar(100) Body name to use with messages. Example: koi8-u
8 WebName nvarchar(100) Web name to use with web browsers: koi8-u
9 IsEnabled bit(1) NOT NULL (1) Record activity status
10 DateCreated datetime(8) NOT NULL getdate() Record creation date
11 DateUpdated datetime(8) NOT NULL getdate() Record update date
  • Alternative/Free Data Source: Microsoft Windows 2003 Server Internal Resources

5. Ranges

Id Name Data Type Nulls Default Description
1 RangeStart bigint(8) NOT NULL (0) Numeric representation of IP Address: 3560944688 (212.63.180.48)
2 RangeEnd bigint(8) NOT NULL (0) Numeric representation of IP Address: 3560944691 (212.63.180.51)
3 CountryA3 char(3) '' ISO 3166-1 Alpha-3 Code. For example: FRA (France)
4 IsEnabled bit(1) NOT NULL (1) Record activity status
5 DateCreated datetime(8) NOT NULL getdate() Record creation date
6 DateUpdated datetime(8) NOT NULL getdate() Record modification date

This database will help automatically detect user country and then language by IP or by domain of email. Culture can be detected by using email encoding.

IP To Country Database

6. Currencies

Id Name Data Type Nulls Default Description
1 CurrencyA3 char(3) NOT NULL '' Currency Alpha-3 Code. Example: GBP (Pound)
2 CurrencyName nvarchar(256) NOT NULL '' Currency Name. Example: Pound
3 NativeName nvarchar(256) NOT NULL '' Native name of the currency
4 CurrencySymbol nvarchar(6) NOT NULL '' Currency symbol. Example: £
5 CurrencyCode int(4) NOT NULL (0) ISO 4217 Currency Code.
6 CountryA3 char(3) '' ISO 3166-1 Alpha-3 Country Code. Example: UK (United Kingdom)
7 IsEnabled bit(1) NOT NULL (1) Record activity status
8 DateCreated datetime(8) NOT NULL getdate() Record creation date
9 DateUpdated datetime(8) NOT NULL getdate() Record update date

Provides: Latest information about currency info in each country with all latest exchange rates.

7. Currencies by Country

Id Name Data Type Nulls Default Description
1 RecordId uniqueidentifier(16) NOT NULL newid() HMAC-MD5 Checksum of UTF-8: Key="Currency", Value=[CurrencyA3],[CountryA3]
2 CountryA3 char(3) NOT NULL ISO 3166-1 Alpha-3 Country Code. Example: UK (United Kingdom)
3 CurrencyA3 char(3) NOT NULL Currency Alpha-3 Code. Example: GBP (Pound)
4 IsPrimary bit(1) NOT NULL Indicator of primary currency inside country.
5 IntroductionDate datetime(8) Currency introduction date
6 AbolitionDate datetime(8) Currency abolition date
7 IsEnabled bit(1) NOT NULL (1) Record activity status
8 DateCreated datetime(8) NOT NULL getdate() Record creation date
9 DateUpdated datetime(8) NOT NULL getdate() Record modification date

Provides: Latest information about currency info in each country.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionbinh thuong Pin
hoalv8-Feb-12 21:46
hoalv8-Feb-12 21:46 
GeneralThank you very much Pin
Jonas Sarunas25-Feb-09 2:22
professionalJonas Sarunas25-Feb-09 2:22 
GeneralBest Localization Plug-in for Visual Studio. Pin
Alexander Nesterenko17-Dec-08 21:44
Alexander Nesterenko17-Dec-08 21:44 
Generalamazing work congrats Pin
roni schuetz11-Oct-08 5:24
roni schuetz11-Oct-08 5:24 
Generalsql files Pin
Magick9316-Jul-08 1:27
Magick9316-Jul-08 1:27 
General[Message Removed] Pin
immetoz1-Oct-08 9:44
immetoz1-Oct-08 9:44 
GeneralRe: sql files Pin
CpILL11-Nov-08 5:01
CpILL11-Nov-08 5:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.