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

问题描述

我想在数据库中添加一些查找列表，但我希望它们易于本地化（SQL 2005，ADO.NET）

这将包括

• 轻松管理多种语言


• 轻松从数据库中检索值


• 后备语言（如果缺少所选语言）

一个表，它将存储多语言查找列表（使用不同的语言相同的id），并使用一个函数，通过接收ID和语言返回查找列表的值。

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

I我们正在寻找一个解决方案，让我发送参数作为一个会话/全局变量，或将自动发送参数与sql查询，以及自己检索它的功能（要自动附加参数，要能够读取参数）。

解决方案可以看起来像这样，但我不介意，如果它是不同的，只要它' t给查询（伪代码）明确的参数：

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

< blockquote>

澄清：

1. 通常查询将如下所示查找函数）：

   
   
   

   SELECT ..，GetLookupList1（lookup_ID，language），.. FROM TABLE p>

GetLookupList1是一个用户定义的函数，用于检索查找表的查找值。通过使用这个函数，SQL代码更容易阅读和维护。

函数的主体如下：

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

2. 我想要的是能够从函数中删除语言参数一个静态变量，只对当前连接/语句/命令可用，因此查询将类似于

   
   
   

   SELECT ..，GetLookupList1 ），.. FROM TABLE

解决方案

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

1. 表 - 临时或常驻。具有永久表格的示例： 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允许将128个二进制字节关联到会话/连接。它工作，但要小心。如果你习惯使用它，你有在另一个上下文中意外覆盖它的风险。每个会话/连接只有一个。

范例context_info t-sql：

  declare @languagein varchar（30），@contextin varbinary（128），
 @languageout varchar（30），@contextout varbinary（128）
 
 select @languagein = RO'
 select @contextin = cast（@languagein as varbinary（128））
设置context_info @contextin 
 
  - 任意你喜欢的：查询，存储过程。 
 --context_info在会话/连接期间保持'ro-RO'
 
 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 
  

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

2. 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屋！