Database Localization - Search Lists - A Smarter Way

Question

I want to add some search lists to the database, but I want them to be easily localized (SQL 2005, ADO.NET)

This will include:

• Easily manage multiple languages ​​at once
• Easy retrieval of values ​​from a database
• Exceptional language (in the absence of a selected language)

I thought about a table that will store a multilingual search list (using the same identifier for different languages), and use a function that will return the value of the search list - by getting the identifier and Language.

One of the problems is that I have to manually add a language setting for each query that uses a search list.

I am looking for a solution that would allow me to send a parameter as "session / global variable" or send this parameter automatically using an SQL query, and the function to receive it by itself (or to attach a parameter automatically to be able to read the parameter).

The solution may look something like this, but I don’t mind if it is different, if it does not explicitly set the Query parameter (pseudocode):

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

Clarification:

• Typically, a query will look like this (remember, using the search function):

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

GetLookupList1 is a user-defined function that retrieves the search value for a lookup table. Using this feature, SQL code is easier to read and maintain.

The body of the function will look something like this:

SELECT @result = LookupValue FROM LookupTable1 WHERE ID=@Lookup_ID and Language=@lang
RETURN @result

2. , - , //,

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

Answer 1

:

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

, ( ) Id. .

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

Answer 2

SQL Server , :

• - . : http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx.
• SET CONTEXT_INFO: http://msdn.microsoft.com/en-us/library/ms187768.aspx. Context_info 128 /. , . , . /.

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

, , , . , , . :

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

Answer 3

:

• SET CONTEXT_INFO, SQL .

• The best option would be to not store localized data in lookup tables. Instead, save some identification strings and use the user's localization logic in the application to match strings with localized data. For the .NET platform, it will be implemented using resources using a special resource provider if I want to get localized information from a database.

Thank you for your responses.