How to deal with MSSQL language portability issue in ASP.NET - Part 2

Apr 15, 2014 by Alexandra

If in Part 1 we've detailed what exactly generated a MS SQL Server language portability issue, now I'll detail the solution. I hope you didn't wait too long :)

A quick workaround

The fastest workaround for the MS SQL Server language portability issue (but still, not a perfect solution; for building a proper code, please see the "Best practices" detailed below) is to simply add the preferred language to your connection string:

<add key="MyConnectionString" value="packet size=4096;Password=pass; 
Persist Security Info=True; User ID=sa; Initial Catalog=MyDatabase; 
Data Source=.\SQL2008;Current Language=English/>

 

The advantages of this approach are:

* Whenever you decide to change the SQL server to one with a different language, it requires only a small change in the same area
* It does not require code alterations which can cause inconsistencies in the long run

Best practices

* When in doubt what date format to use, always go with the ISO date format! This comes in two "flavors": YYYYMMDD (no dashes) and YYYY-MM-DDTHH:MM:SS (dashes are optional) where the "T" separates the date from the time. Both these formats have been accepted since the Microsoft SQL Server 2000 version
* Use the English version of Microsoft SQL Server, since it is widely accepted
* I would recommend to always choose English as the database language. If this is not possible, then keep in mind that:

   - English is the most widely used language in the world, especially in IT. It can be considered the lingua franca of programming.
   - For most programming languages, the instruction set (also the entities' names etc.) are derived from the English language (for example: "for", "if", "table", "where"). It is common courtesy among developers to keep this standard, and also this ensures that the code is more readable for all programmers.
   - Since nowadays most development teams are international, it is difficult to maintain a proper workflow if each developer uses his / her native language. English, as a common language within a team, makes teamwork possible :))
   - Imagine your project requires 10 external libraries, and let's say each of these was written in a different language - how would your source-code look like? Not cool, right?

Hope you find this "how to" useful. Don't be shy and get back with your feedback!


Tags: Database  How To  Microsoft  SQL 


Comments


RobIII commented on 4/23/2014 1:37:44 PM

When not using parameterized queries (there's no excuse!) and insist on butchering strings together then always, *ALWAYS*, use ISO 8601 format. It's not only accepted and always understood correctly by MSSQL but also by other RDBMS'es as MySQL, PostgreSQL etc.

> "English is the most widely used language in the world"
No it isn't. Not even close. It's only spoken by 5.43% of the world's population (as native language) and only in third place. See https://en.wikipedia.org/wiki/List_of_languages_by_number_of_native_speakers


     

Alexandra commented on 4/28/2014 12:04:22 PM

Hi again :)

Also, as I've previously stated, ISO 8601 is the standard, however as not everybody follows this, errors may appear. Let's assume that you've received a legacy database backup for a project which contains over 200 stored procedures which did not use the standardized date format, but rather a localized version of the solution. Going through each takes time and resources, so my approach comes with a workaround for exactly this situation.

>> "English is the most widely used language in the world"
No it isn't. Not even close. It's only spoken by 5.43% of the world's population (as native language) and only in third place. See https://en.wikipedia.org/wiki/List_of_languages_by_number_of_native_speakers<<

Actually, what I meant to say was that English is the most widely used language when it comes to IT. I don't remember seeing many apps written in mandarin :)), although it would be interesting to debug such an application.

     

none commented on 4/24/2014 12:24:05 PM

Alexandara,

This is being pretty naive saying that language should always be English. Setting the culture to English for the logins mess up quite a lot of things - for example start of the week and so on. How are you going to for example transparently support Thai dates in Thailand ? How about Persian or Ethiopian one?

It gets quite complicated if you are either doing SSAS or multitenant thing.

One solution is to maintain your own calendar - which many applications - especially financial ones do.
Calendars are complicated beasts.
     

Kartikeya Sharma commented on 9/14/2015 1:52:19 PM

Thank you for the work you have put into your nice blog. We will bookmark to your blog because it is very informational. We love the site and will come back to see your new posts.

     

Your Comment:







Blog Home   SBP Home
RSS Feed       Contact








 Blog Archives  |  Terms of Use  |  Privacy Policy
© 2017 SBP Romania. All rights reserved.