I am searching to include some research lists within the database, however i would like them to become easy localizable (SQL 2005, ADO.Internet)

This could include:

  • Easy Control over multiple languages simultaneously
  • Easy Retrieval of values in the database
  • Fallback language (just in case the chosen language is missing)

I believed about getting a table that will keep multi-language research-list (using for various languages exactly the same id) and employ a function that will return the need for the appearance-up list - by finding the ID and also the Language.

Among the issues is i must by hand give a language parameter to each query that utilizes the research list.

I am considering an answer that will allow me to send the parameter like a "session/global variable", or that will send the parameter instantly using the sql query, and also the function to retrieve it alone (with the idea to attach the parameter instantly, with the idea to have the ability to browse the parameter).

The answer look something similar to this, however i don't mind if it's different, as lengthy because it does not provide the parameter clearly towards the Query (pseudocode):

1. Send the language using "the method"
2. Execute Query
3. Get the localized results

Clarification:

  1. Normally the query would seem like this (remember while using research function):

    SELECT .., GetLookupList1(lookup_ID, language), .. FROM TABLE

The GetLookupList1 is really a user defined function that retrieves the research value for any research table. Applying this function, the SQL Code is simpler to see and keep.

Your body from the function could be something similar to:

SELECT @result = LookupValue FROM LookupTable1 WHERE ID=@Lookup_ID and Language=@lang
RETURN @result
  1. Things I want would be to have the ability to take away the language parameter in the function with a type of a static variable, available just for the present connection/statement/command, therefore the query would seem like

    SELECT .., GetLookupList1(lookup_ID), .. FROM TABLE

Should you structure your computer data such as this:

MessageToken    DisplayText       LangCode
firewood        Fire wood         en
firewood        Bois de chauffage fr

Whenever you build your query, just give you the default languageId (if blank) or even the provided languageId. Make use of a standard listing of tokens for that messages.

Select DisplayText from (some table) where MessageToken = 'firewood' and LangId = 'en'

Since you will find no user-defined global variables in SQL Server, you will need to use 1 of 2 approaches:

  1. Tables - temporary or permanent. Example with permanent tables: http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx.
  2. SET CONTEXT_INFO: http://msdn.microsoft.com/en-us/library/ms187768.aspx. Context_info allows you connect 128 binary bytes to some session/connection. It really works but be cautious. When you get within the practice of utilizing it, you risk accidentally overwriting it in another context. There's just one per session/connection.

Example context_info t-sql:

declare @languagein varchar(30), @contextin varbinary(128),
    @languageout varchar(30), @contextout varbinary(128)

select @languagein = 'ro-RO'
select @contextin = cast(@languagein as varbinary(128))
set context_info @contextin

--do whatever you like here: queries, stored procs. 
--context_info stays 'ro-RO' for the duration of the session/connection

select @contextout = context_info()
set @languageout = replace(cast(@contextout as varchar(30)),0x00, '')
print @languageout

Another technique I have utilized in localization is really a three part coalesce to insure an effect. Look for language-region first, then language, a default. According to your query:

SELECT COALESCE(langregion.LookupValue, lang.LookupValue, fallback.LookupValue) LookupVal
FROM LookupTable1 fallback
LEFT OUTER JOIN LookupTable1 lang 
    ON lang.ID = fallback.ID AND lang.Lang = @language
LEFT OUTER JOIN LookupTable1 langregion 
    ON langregion.ID = fallback.ID AND langregion.Lang = @languagewithregion
WHERE fallback.ID = @Lookup_ID
AND fallback.Lang = @defaultlanguage