Gift Peddie (8/10/2005)

---

Try these links for localization in .NET because most .NET application localization is in XML. Hope this helps.

tymberwyld (11/30/2007)

---

Peddie, .NET focuses on Application localization which is completely different than "data" localization. The database structure for localizing an application is very, very easy and .NET applications can take advantage of a custom database resource reader, but localizing say your Product Descriptions is a whole other story.

I too am in this boat and I am curious if the original poster has come up with a solution yet?

CREATE TABLE [dbo].[ObjectGlobalization](
[ObjectName] [varchar](128) NOT NULL,
[ColumnName] [varchar](128) NOT NULL,
[Culture] [varchar](5) NOT NULL,
[RowID] [sql_variant] NOT NULL,
[Value] [sql_variant] NOT NULL,
[ValueText] [varchar](max) NULL,
[ValueXml] [xml] NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedDate] [datetime] NULL CONSTRAINT [DF_ObjectGlobalization_CreatedDate] DEFAULT (getutcdate()),
[ModifiedBy] [varchar](50) NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_ObjectGlobalization_ModifiedDate] DEFAULT (getutcdate()),
CONSTRAINT [PK_ObjectGlobalization_1] PRIMARY KEY CLUSTERED ([ObjectName] ASC,[ColumnName] ASC,[Culture] ASC,[RowID] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE FUNCTION [dbo].[ufn_get_GlobalizedValue]
(
@ObjectName VarChar(128),
@ColumnName VarChar(128),
@Culture VarChar(10) = Null,
@RowID Sql_Variant,
@Default Sql_Variant = Null
)
RETURNS Sql_Variant
AS
BEGIN
Declare @Value Sql_Variant
Select @Value = [Value]
From dbo.ObjectGlobalization
Where (ObjectName = @ObjectName)
And (ColumnName = @ColumnName)
And (Culture = @Culture)
And (RowID = @RowID)
Set @Value = IsNull(@Value, @Default)
-- Return the result of the function
RETURN(@Value)
END
GO

CREATE FUNCTION [dbo].[ufn_get_GlobalizedValueText]
(
@ObjectName VarChar(128),
@ColumnName VarChar(128),
@Culture VarChar(10) = Null,
@RowID Sql_Variant,
@Default VarChar(MAX) = Null
)
RETURNS VarChar(MAX)
AS
BEGIN
Declare @Value VarChar(MAX)
Select @Value = IsNull([ValueText], @Default)
From dbo.ObjectGlobalization
Where (ObjectName = @ObjectName)
And (ColumnName = @ColumnName)
And (Culture = @Culture)
And (RowID = @RowID)
-- Return the result of the function
RETURN(@Value)
END
GO

CREATE PROCEDURE [dbo].[usp_get_ProductsByCategoryID]
(
@CategoryID UniqueIdentifier,
@Culture VarChar(10) = Null -- Defaults to Invariant
)
AS
SET NOCOUNT ON

Select
P.ID,
P.Code,
Convert(VarChar(50), dbo.ufn_get_GlobalizedValue('Product.Products', 'Name', @Culture, P.ID, P.Name)) As [Name],
Convert(VarChar(255), Left(dbo.ufn_get_GlobalizedValueText('Product.Products', 'Description', @Culture, P.ID, Null), 255)) As [Description],
IsNull(P.UnitPrice, 0.00) As UnitPrice,
IsNull(P.ListPrice, 0.00) As ListPrice,
P.Attributes,
P.CreatedBy, P.CreatedDate,
P.ModifiedBy, P.ModifiedDate
From Product.Products P
Inner Join Product.ProductCategories C ON (P.ID = C.ProductID)
Where
(C.CategoryID = IsNull(@CategoryID, C.CategoryID))

CREATE PROCEDURE [dbo].[usp_save_GlobalizedValue]
(
@ObjectName VarChar(128),
@ColumnName VarChar(128),
@Culture VarChar(10),
@RowID Sql_Variant,
@Value Sql_Variant = Null,
@ValueText VarChar(MAX) = Null,
@ValueXml Xml = Null,
@CreatedBy VarChar(50) = Null,
@CreatedDate DateTime = Null,
@ModifiedBy VarChar(50) = Null,
@ModifiedDate DateTime = Null
)
AS
Set NoCount On

-- Exit if there is no data
If (@Value Is Null) And (@ValueText Is Null) And (@ValueXml Is Null) RETURN(1)

-- Validate the Data
Select
@ObjectName = NullIf(LTrim(RTrim(@ObjectName)), ''),
@ColumnName = NullIf(LTrim(RTrim(@ColumnName)), ''),
@Culture = NullIf(LTrim(RTrim(@Culture)), ''),
@CreatedBy = IsNull(@CreatedBy, SUser_Name()),
@CreatedDate = IsNull(@CreatedDate, GetUtcDate()),
@ModifiedBy = IsNull(@ModifiedBy, SUser_Name()),
@ModifiedDate = IsNull(@ModifiedDate, GetUtcDate())
If (@ObjectName Is Null) RaisError('The [@ObjectName] Parameter cannot be Null or Empty.', 16, 1)
If (@ColumnName Is Null) RaisError('The [@ColumnName] Parameter cannot be Null or Empty.', 16, 1)
If (@RowID Is Null) RaisError('The [@RowID] Parameter cannot be Null or Empty.', 16, 1)
If (@@ERROR <> 0) RETURN(-1)

BEGIN TRY
If Not Exists(
Select Null From dbo.ObjectGlobalization
Where ObjectName = @ObjectName
And ColumnName = @ColumnName
And Culture = IsNull(@Culture, '')
And RowID = @RowID
)
BEGIN
Insert Into dbo.ObjectGlobalization
(ObjectName, ColumnName, Culture, RowID,
Value, ValueText, ValueXml,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
Values
(@ObjectName, @ColumnName, IsNull(@Culture, ''), @RowID,
@Value, @ValueText, @ValueXml,
@CreatedBy, @CreatedDate, @ModifiedBy, @ModifiedDate)
END
Else
BEGIN
Update dbo.ObjectGlobalization Set
ObjectName = @ObjectName,
ColumnName = @ColumnName,
Culture = IsNull(@Culture, ''),
RowID = @RowID,
Value = @Value,
ValueText = @ValueText,
ValueXml = @ValueXml,
ModifiedBy = @ModifiedBy,
ModifiedDate = @ModifiedDate
Where ObjectName = @ObjectName
And ColumnName = @ColumnName
And Culture = IsNull(@Culture, '')
And RowID = @RowID
END

If (@@TRANCOUNT > 0) COMMIT
RETURN(1)

END TRY
BEGIN CATCH
If (@@TRANCOUNT > 0) ROLLBACK
RETURN(-1)
END CATCH