数据库本地化 - 查找列表 - 更聪明的方法 [英] Database Localization - Lookup lists - smarter way

问题描述

我期待添加一些查找列表中的数据库，但我希望他们能很容易本地化（SQL 2005，ADO.NET）

这将包括：

• 在多国语言的易于管理的同时
• 值易于检索的数据库
• 在后备语言（如果所选的语言缺失）

我在想，我将存储多语言查找表（使用不同的语言相同的ID），一张桌子和使用，将返回的查询列表的价值功能 - 通过接收ID和语言。

其中一个缺陷是，我必须手动添加一个语言参数，使用查找列表中的每个查询。

我在寻找到一个解决方案，让我把参数为会话/全局变量，或者说将与SQL查询自动发送的参数，函数本身来检索它（或者附加参数自动，要么能够读取的参数）。

该解决方案可以是这个样子，但我不介意，如果它是不同的，只要它没有明确的查询（伪code）提供的参数：

  1。发送使用方法的语言
2.执行查询
3.获取本地化的结果
 

澄清：

1. 通常情况下，查询应该是这样的（记住使用查找功能）：

   SELECT ...，GetLookupList1（lookup_ID，语言），.. FROM TABLE

该GetLookupList1是一个用户定义函数检索查找​​表中查找值。通过使用该功能，SQL code是更容易阅读和维护。

功能的主体会是这样的：

  SELECT @result = LookupValue从LookupTable1其中id = @ Lookup_ID和语言= @郎
RETURN @result
 

1. 我要的是能够从函数中删除语言参数某种静态变量，仅适用于当前连接/声明/命令，所以查询看起来像

   SELECT ...，GetLookupList1（lookup_ID）。从表

解决方案

由于在SQL Server中没有用户定义的全局变量，你必须使用以下两种方法之一：

1. 表 - 临时或永久的。例如使用永久表： http://weblogs.sqlteam.com/ mladenp /存档/ 2007/04/23 / 60185.aspx 。
2. 设置CONTEXT_INFO： http://msdn.microsoft.com/en-us /library/ms187768.aspx 。 CONTEXT_INFO让你联想128二进制字节到会话/连接。它的工作原理，但要小心。如果您在使用它的习惯得到的，你运行的意外覆盖在另一个方面的风险。只有一个每个会话/连接。

例CONTEXT_INFO T-SQL：

 声明@languagein VARCHAR（30），@contextin VARBINARY（128）
    @languageout VARCHAR（30），@contextout VARBINARY（128）

选择@languagein ='RO-RO
选择@contextin =投（@languagein为varbinary（128））
设置CONTEXT_INFO @contextin

--do任何你喜欢的位置：查询，存储的特效。
--context_info撑RO-RO的会话/连接的持续时间

选择@contextout = CONTEXT_INFO（）
设置@languageout =替换（CAST（@contextout为varchar（30）），0×00，''）
打印@languageout
 

我在本地化使用的另一种技术是三部分组成的聚结，以确保结果。检查语言区域，再语言，然后默认。根据您的查询：

  SELECT COALESCE（langregion.LookupValue，lang.LookupValue，fallback.LookupValue）LookupVal
从LookupTable1回退
LEFT OUTER JOIN LookupTable1郎
    ON lang.ID = fallback.ID和lang.Lang = @language
LEFT OUTER JOIN LookupTable1 langregion
    ON langregion.ID = fallback.ID和langregion.Lang = @languagewithregion
WHERE fallback.ID = @Lookup_ID
与fallback.Lang = @defaultlanguage
 

I'm looking to add some lookup lists in the database, but I want them to be easy localizable (SQL 2005, ADO.NET)

This would include:

• Easy Management of multiple languages at the same time
• Easy Retrieval of values from the database
• Fallback language (in case the selected language is missing)

I was thinking about having a table that would store the multi-language lookup-list (using for different languages the same id) and use a function that would return the value of the look-up list - by receiving the ID and the Language.

One of the pitfalls would be that i have to manually add a language parameter to every query that uses the lookup list.

I'm looking into a solution that would let me send the parameter as a "session/global variable", or that would send the parameter automatically with the sql query, and the function to retrieve it by itself (either to attach the parameter automatically, either to be able to read the parameter).

The solution can look something like this, but I don't mind if it is different, as long as it doesn't give the parameter explicitly to the Query (pseudocode):

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

Clarification:

1. Normally the query would look like this (remember using the lookup function):

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

The GetLookupList1 is a user defined function that retrieves the lookup value for a lookup table. By using this function, the SQL Code is easier to read and maintain.

The body of the function would be something like:

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

1. What I want is to be able to remove the language parameter from the function to some kind of a static variable, available only for the current connection/statement/command, so the query would look like

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

解决方案

Since there are no user-defined global variables in SQL Server, you'll have to use one of two 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 lets you associate 128 binary bytes to a session/connection. It works but be careful. If you get in the habit of using it, you run the risk of accidentally overwriting it in another context. There's only 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've used in localization is a three part coalesce to insure a result. Check for language-region first, then language, then a default. Based on 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

这篇关于数据库本地化 - 查找列表 - 更聪明的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！